Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

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

Rainer StenzelDec 10 2014 — edited May 4 2018

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
Post Details
Added on Dec 10 2014
11 comments
780 views