db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mike Matrigali (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5235) Remove the artificial limit on the length of VARCHAR values, allowing them to be java.lang.Integer.MAX_VALUE long
Date Wed, 18 May 2011 17:26:47 GMT

    [ 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

Mime
View raw message