Skip navigation
ANNOUNCEMENT: community.oracle.com is currently Read only due to planned upgrade until 29-Sep-2020 9:30 AM Pacific Time. Any changes made during Read only mode will be lost and will need to be re-entered when the application is back read/write.

more automatic query expansions to use available function based index (e.g. UPPER,DECODE)

score 80
You have not voted. Active

I would really like to see the query expansion already implemented for TRUNC and SUBSTR applied more commen e.g. for UPPER and DECODE (for better support of "partial" indexes as already outlined in the DECODE use case below).

 

Index on trunc(date) – do you still need old index?

Index on SUBSTR(string,1,n) – do you still need old index?/

 

original idea:

 

A query has an equality predicate eq(t.c,constant_expression) on column t.c and table t has a function based index f(t.c).

 

Shoudn't it then be possible to add a predicate eq(f(t.c),f(constant_expression)) to the query to enable additional access pathes via index f(t.c) ?

 

Example:

 

With index

 

create index upper_DEPARTMENT_NAME on DEPARTMENTS (upper(DEPARTMENT_NAME));

 

queries with a predicate

 

... from ... DEPARTMENTS ... where ... DEPARTMENT_NAME=:b1 ...

 

should be rewritten to and optimized with

 

... from ... DEPARTMENTS ... where ... (DEPARTMENT_NAME=:b1 AND upper(DEPARTMENT_NAME)=upper(:b1)) ...

 

to enable the INDEX RANGE SCAN access path via index upper_DEPARTMENT_NAME.

 

B.t.w. nice terms of use for new ideas (at least for Oracle).

Comments

Vote history