Skip navigation

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