Summary
|
"LIKE" should use expression index if pattern is besed on system function e.g. UPPER
|
"LIKE" queries should use expression index if expression uses system functions e.g. UPPER
|
Description
|
CREATE TABLE NAMES
(
NAME VARCHAR(100)
);
commit;
INSERT INTO names
SELECT RF.RDB$FIELD_NAME FROM RDB$RELATION_FIELDS RF;
commit;
CREATE ASCENDING INDEX IXAE_NAMES__NAME ON NAMES COMPUTED BY(UPPER(NAME COLLATE PXW_PLK));
commit;
------------------------------------------------------------------------------------
SELECT * FROM NAMES N WHERE UPPER(N.NAME COLLATE PXW_PLK) LIKE 'RDB%SYSTEM_FLAG%'
PLAN (N INDEX (IXAE_NAMES__NAME))
this works as expected
------------------------------------------------------------------------------------
SELECT * FROM NAMES N WHERE UPPER(N.NAME COLLATE PXW_PLK) LIKE UPPER('RDB%SYSTEM_FLAG%')
PLAN (N NATURAL)
but this one not - because of function used in LIKE pattern.
------------------------------------------------------------------------------------
I understand current logic, because someone can use custom function and change order of characters.
But system function UPPER does not change anything in like pattern, and index still can be used.
I do not know which other (only system) functions can be used this way but UPPER and LOWER are obvious one.
|
CREATE TABLE NAMES
(
NAME VARCHAR(100)
);
commit;
INSERT INTO names
SELECT RF.RDB$FIELD_NAME FROM RDB$RELATION_FIELDS RF;
commit;
CREATE ASCENDING INDEX IXAE_NAMES__NAME ON NAMES COMPUTED BY(UPPER(NAME COLLATE PXW_PLK));
commit;
------------------------------------------------------------------------------------
SELECT * FROM NAMES N WHERE UPPER(N.NAME COLLATE PXW_PLK) LIKE 'RDB%SYSTEM_FLAG%'
PLAN (N INDEX (IXAE_NAMES__NAME))
this works as expected
------------------------------------------------------------------------------------
SELECT * FROM NAMES N WHERE UPPER(N.NAME COLLATE PXW_PLK) LIKE UPPER('RDB%SYSTEM_FLAG%')
PLAN (N NATURAL)
but this one not - because of function used in LIKE pattern.
------------------------------------------------------------------------------------
I understand current logic, because someone can use custom function and change order of characters.
But system function UPPER does not change anything in like pattern, and index still can be used.
I do not know which other (only system) functions can be used this way but UPPER and LOWER are obvious one.
|