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).