[
https://issues.apache.org/jira/browse/DERBY-5235?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13035503#comment-13035503
]
Mike Matrigali commented on DERBY-5235:
---------------------------------------
I am wondering what are the reasons that people want a longer varchar?
Increasing the length may give the user the impression that
it is supported well. It would be good to know why people want this to make
sure just increasing the limit helps. If possible I would like
to see us improve clob support rather than lead users on that varchar will
handle their long strings well. I'll start the list:
1) I can index varchar's but not clobs
o increasing the limit on varchar's is not going to help this. A
successful Derby application should not be counting on
indexing large varchars. The current index support just is not designed
to handle this well.
Even whatever is the max varchar now is not going to be a good index
with each page of an index
tree having as few as 2 nodes. There are a few answers to this problem
which would be new types of indexes. The
standard industry trick in this area is to only index the first N
characters of a long string and then do magic in the
query plan to use this info but know it can't get the actual value of
the column from the index. Derby is architected well
for this solution as it could be implemented today with no changes at
all to the storage system.
2) database x has a bigger limit so derby is not compatible.
o most of the db's I quickly looked at seem to have matching or lower
limits, but would welcome input here as I just did quick
google search.
I like the current architecture that is enforced on users. If you have a short
string choose varchar, if you have a long string choose clob.
Originally cloudscape had only one option as I believe there was no standard
for CLOB.
VARCHARS are optimized for short strings. In most cases we can index them. As
knut has pointed out there are some supported
varchars that we can't index, but with current limit it is possible we could
fix that without a major rewrite of index code by looking at
page sizes. The code chooses memory use for performance reading these into
memory always. Raising the max of the varchar will
insure that we can never index
CLOBS are optimized for long strings. The standard provides limits on the
datatype that enable these optimizations.
Another reason I like the current limits is that it gives us architectural room
in the future to improve the product. Some day we may
want to store blob/clobs differently that would perform better. Having a clear
size difference makes it easier to differentiate, and explain
the performance expectations to users.
> Remove the artificial limit on the length of VARCHAR values, allowing them to
> be java.lang.Integer.MAX_VALUE long
> -----------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-5235
> URL: https://issues.apache.org/jira/browse/DERBY-5235
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.9.0.0
> Reporter: Rick Hillegas
>
> The original Cloudscape limit for the length of VARCHAR values was
> java.lang.Integer.MAX_VALUE. That is the limit in Cloudscape 5.1. Nothing in
> Derby should break if we restore the original limit. The current limit is an
> artificial bound introduced to make Derby agree with DB2. 32672 is the upper
> bound on the length of a DB2 VARCHAR:
> http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0001029.htm
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
