Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Development" <derby-dev@db.apache.org>
Received-SPF: neutral (asf.osuosl.org: local policy)
Message-ID: <42C02895.2000708@sbcglobal.net>
Date: Mon, 27 Jun 2005 09:25:57 -0700
From: Mike Matrigali <mikem_app@sbcglobal.net>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: Derby Development <derby-dev@db.apache.org>
Subject: Re: [jira] Updated: (DERBY-392) Disable creating indexes on long
 varchar for bit data. Long varchar column doesn't allow creating indexes
 already.
References: <1657793075.1119765240347.JavaMail.jira@ajax.apache.org>
In-Reply-To: <1657793075.1119765240347.JavaMail.jira@ajax.apache.org>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

"ineffective" is an understatement.  Inserting a value that is bigger 
than 1/2 of a page will cause the insert to fail - with some sort of key 
too big error.  Current btree mechanism does not support keys bigger 
than 1/2 of a page.

Many systems get around this by only indexing the first N bytes/chars (I 
think 512 bytes is the mySQL value of N), of the datatype.  Derby does
not have this support, but could be implemented.  Building and 
maintaining such an index would be easy.  Some work would have to be 
done in the language layer to teach it that the value in the index key
could only be used for indexing into the actual value in the base row, 
it could never be used as a covering key.  No changes would be necessary 
at the store btree level, as it does not really know what it is storing.

Satheesh Bandaram (JIRA) wrote:
>      [ http://issues.apache.org/jira/browse/DERBY-392?page=all ]
> 
> Satheesh Bandaram updated DERBY-392:
> ------------------------------------
> 
>     Description: 
> I guess I did not articulate my reasons for suggesting removal of index support for 'long varchar for bit data' completely.
>  
>     1) Long varchar types are not comparable... If they are not comparable, it should not be possible to use them in GROUP BY, ORDER BY or allow regular B-Tree indexes.
>    2) Also, long varchar types tend to be long in size and hence the regular B-Tree mechanism is not a suitable way to index them. Dan also mentioned they become ineffective for keys longer than half a page size.
> 
> It should not be possible to create an index on 'long varchar for bit data' datatypes. Derby currently doesn't allow creating indexes on 'long varchar' datatypes and the same should apply for it's bit data equivalent too.
> 
> ij> create table longchar ( i int, c long varchar);
> 0 rows inserted/updated/deleted
> ij> create index longIdx on longchar(c);
> ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORD
> ER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because compari
> sons are not supported for that type.
> ij> create table longcharBitData ( i int, c long varchar for bit data);
> 0 rows inserted/updated/deleted
> ij> create index longIdx on longcharBitData(c);
> 0 rows inserted/updated/deleted
> 
> Derby also seems to allow GROUP BY and/or ORDER BY on LONG VARCHAR FOR BIT DATA types. I believe this is incorrect too.
> select c from longcharBitData group by c;
> C
> 
> --------------------------------------------------------------------------------
> ------------------------------------------------
> 
> 0 rows selected
> ij> select c from longcharBitData group by c order by c;
> C
> 
> --------------------------------------------------------------------------------
> ------------------------------------------------
> 
> 0 rows selected
> 
>   was:
> It should not be possible to create an index on 'long varchar for bit data' datatypes. Derby currently doesn't allow creating indexes on 'long varchar' datatypes and the same should apply for it's bit data equivalent too.
> 
> ij> create table longchar ( i int, c long varchar);
> 0 rows inserted/updated/deleted
> ij> create index longIdx on longchar(c);
> ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORD
> ER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because compari
> sons are not supported for that type.
> ij> create table longcharBitData ( i int, c long varchar for bit data);
> 0 rows inserted/updated/deleted
> ij> create index longIdx on longcharBitData(c);
> 0 rows inserted/updated/deleted
> 
> 
> 
>>Disable creating indexes on long varchar for bit data. Long varchar column doesn't allow creating indexes already.
>>------------------------------------------------------------------------------------------------------------------
>>
>>         Key: DERBY-392
>>         URL: http://issues.apache.org/jira/browse/DERBY-392
>>     Project: Derby
>>        Type: Bug
>>  Components: SQL
>>    Versions: 10.0.2.2, 10.1.1.0
>> Environment: generic
>>    Reporter: Satheesh Bandaram
>>    Priority: Minor
> 
> 
>>I guess I did not articulate my reasons for suggesting removal of index support for 'long varchar for bit data' completely.
>> 
>>    1) Long varchar types are not comparable... If they are not comparable, it should not be possible to use them in GROUP BY, ORDER BY or allow regular B-Tree indexes.
>>   2) Also, long varchar types tend to be long in size and hence the regular B-Tree mechanism is not a suitable way to index them. Dan also mentioned they become ineffective for keys longer than half a page size.
>>It should not be possible to create an index on 'long varchar for bit data' datatypes. Derby currently doesn't allow creating indexes on 'long varchar' datatypes and the same should apply for it's bit data equivalent too.
>>ij> create table longchar ( i int, c long varchar);
>>0 rows inserted/updated/deleted
>>ij> create index longIdx on longchar(c);
>>ERROR X0X67: Columns of type 'LONG VARCHAR' may not be used in CREATE INDEX, ORD
>>ER BY, GROUP BY, UNION, INTERSECT, EXCEPT or DISTINCT statements because compari
>>sons are not supported for that type.
>>ij> create table longcharBitData ( i int, c long varchar for bit data);
>>0 rows inserted/updated/deleted
>>ij> create index longIdx on longcharBitData(c);
>>0 rows inserted/updated/deleted
>>Derby also seems to allow GROUP BY and/or ORDER BY on LONG VARCHAR FOR BIT DATA types. I believe this is incorrect too.
>>select c from longcharBitData group by c;
>>C
>>--------------------------------------------------------------------------------
>>------------------------------------------------
>>0 rows selected
>>ij> select c from longcharBitData group by c order by c;
>>C
>>--------------------------------------------------------------------------------
>>------------------------------------------------
>>0 rows selected
> 
>