db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5149) <column> LIKE UPPER( <string constant> ) result in a table scan even if a valid index exists on <column>
Date Thu, 24 Mar 2011 12:53:05 GMT

    [ https://issues.apache.org/jira/browse/DERBY-5149?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13010650#comment-13010650
] 

Rick Hillegas commented on DERBY-5149:
--------------------------------------

I suspect that the optimizer is not smart enough to see the right side of the comparison as
a constant so I would say this is related to DERBY-4791.

> <column> LIKE UPPER( <string constant> ) result in a table scan even if a
valid index exists on <column>
> --------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-5149
>                 URL: https://issues.apache.org/jira/browse/DERBY-5149
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.7.1.1
>            Reporter: Stephane Claret
>
> I am currently trying to use generated columns to do some some case insensitive search
query, here's a simplified version of my table :
> CREATE TABLE PRODUCTS (
> 		ID VARCHAR(100) NOT NULL,
> 		NAME VARCHAR(100) NOT NULL,
> 		UPPERNAME VARCHAR(100) DEFAULT GENERATED ALWAYS AS ( UPPER(NAME) ) 
> 	);
> CREATE UNIQUE INDEX PRIMARY_KEY_F ON PRODUCTS (ID ASC);
> CREATE INDEX PRODUCTS_UNAME ON PRODUCTS (UPPERNAME ASC);
> ALTER TABLE PRODUCTS ADD CONSTRAINT CONSTRAINT_F PRIMARY KEY (ID);
> The table is filled with about 30k records.
> When running the following query 
> SELECT id, name 
> FROM PRODUCTS 
> WHERE uppername LIKE 'PC%'
> the index is correctly used while this one :
> SELECT id, name 
> FROM PRODUCTS 
> WHERE uppername LIKE  UPPER('pc%')
> triggers a table scan. I have not tested yet but I suspect it works the same for every
SQL function (not only UPPER). 
> This behavior could (should?) be optimized when the right operand of LIKE or "=" is a
function taking a constant in parameter.
> This might be linked to this issue :
> https://issues.apache.org/jira/browse/DERBY-4791

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message