Forum Stats

  • 3,874,091 Users
  • 2,266,678 Discussions
  • 7,911,723 Comments

Discussions

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

Rainer Stenzel
Rainer Stenzel Member Posts: 64 Bronze Badge
edited May 4, 2018 3:15AM in Database Ideas - Ideas

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

User259623 -OracleGugs-Oraclevinaykumar2Carsten KaftanPravin TakpireLothar FlatzRandolf GeistSven W.blessed DBAMohammed SulaimanAndrewSayerUser_G3UV3
15 votes

Active · Last Updated

«1

Comments

  • Rainer Stenzel
    Rainer Stenzel Member Posts: 64 Bronze Badge

    Another may be even more desirable use case:

    Using the common technique of using FBI's to index subsets of a tables data i.e.

    create index ... on ... (decode(status,0,null,status))

    afaik one has to use a very similar expression in a query to enable the usage of such index

    so "where ... decode(status,0,null,status) = 5" would be able to use it,but

    "where ... status = 5" not.

    Again an expansion/substition from an expression as

    "status = 5" to

    "status = 5 and decode(status,0,null,status) = decode(5,0,null,5)" and consequently

    "status = 5 and decode(status,0,null,status) = 5"

    should be possible and enable the index usage.

  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    I think the developers should write what they mean to write. The database should not interpret and guess. I do not want to imagine what kind of issues would come from such enhancement. I think your a searching a solution for a much too narrow defined issue.

  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy

    I think the developers should write what they mean to write. The database should not interpret and guess. I do not want to imagine what kind of issues would come from such enhancement. I think your a searching a solution for a much too narrow defined issue.

    But in recent releases the first sights of such automatic expansions are already there:

    http://www.dbi-services.com/index.php/blog/entry/index-on-truncdate-do-you-still-need-old-index

    So it's not unreasonable to assume that further such re-writes could be added in the future.

    Randolf

    Lothar Flatz
  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    But in recent releases the first sights of such automatic expansions are already there:

    http://www.dbi-services.com/index.php/blog/entry/index-on-truncdate-do-you-still-need-old-index

    So it's not unreasonable to assume that further such re-writes could be added in the future.

    Randolf

    Interessting aspect . Did not see this at first. Preequesite would be that the function is a linear transformation. In the example above I wonder what happens if you write status between 0 and 2, or even status = 0.

    Thus we see there are some difficulties, The idea is better than I thought, but needs IMHO some elaboration.

  • Mohamed Houri
    Mohamed Houri Member Posts: 1,281 Bronze Trophy

    But in recent releases the first sights of such automatic expansions are already there:

    http://www.dbi-services.com/index.php/blog/entry/index-on-truncdate-do-you-still-need-old-index

    So it's not unreasonable to assume that further such re-writes could be added in the future.

    Randolf

    Hi Randolf,

    I have also tried the same experiment creating an index on a virtual column and using a predicate on the column that serves to create the virtual column.

    https://hourim.wordpress.com/2013/10/25/index-on-a-virtual-column-would-it-help-others/

    There is indeed a special treatement for the trunc function that seems to not have been extended to other functions; at least for functions I have tested (abs, ceil, nvl)

    Best regards

    Mohamed Houri

  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy

    Hi Randolf,

    I have also tried the same experiment creating an index on a virtual column and using a predicate on the column that serves to create the virtual column.

    https://hourim.wordpress.com/2013/10/25/index-on-a-virtual-column-would-it-help-others/

    There is indeed a special treatement for the trunc function that seems to not have been extended to other functions; at least for functions I have tested (abs, ceil, nvl)

    Best regards

    Mohamed Houri

    Hi Mohamed,

    thanks for the link, I thought already that I saw this somewhere else in addition to the mentioned link.

    Randolf

  • Lothar Flatz
    Lothar Flatz Member Posts: 687 Silver Badge

    Hi Randolf,

    I have also tried the same experiment creating an index on a virtual column and using a predicate on the column that serves to create the virtual column.

    https://hourim.wordpress.com/2013/10/25/index-on-a-virtual-column-would-it-help-others/

    There is indeed a special treatement for the trunc function that seems to not have been extended to other functions; at least for functions I have tested (abs, ceil, nvl)

    Best regards

    Mohamed Houri

    Well, of course the trunc(date) is a popular mistake. I think when checking that request I still think too much as Oracle develoepr. I mageine the effort to relize the idea.

  • Rainer Stenzel
    Rainer Stenzel Member Posts: 64 Bronze Badge

    Another may be even more desirable use case:

    Using the common technique of using FBI's to index subsets of a tables data i.e.

    create index ... on ... (decode(status,0,null,status))

    afaik one has to use a very similar expression in a query to enable the usage of such index

    so "where ... decode(status,0,null,status) = 5" would be able to use it,but

    "where ... status = 5" not.

    Again an expansion/substition from an expression as

    "status = 5" to

    "status = 5 and decode(status,0,null,status) = decode(5,0,null,5)" and consequently

    "status = 5 and decode(status,0,null,status) = 5"

    should be possible and enable the index usage.

    Found at least an interesting possibility to enable FBI access paths even without matching exactly the index column expression in the query using Query Rewrite Equivalences.

    Example (assuming at least some non VALID database objects) tested on 10.2.0.4

    create table dbs as select object_name,status,cast('waste space only' as char(2000)) s from dba_objects;

    execute dbms_stats.gather_table_stats(user,'DBS',estimate_percent=>100,method_opt=>'for columns status size auto');

    -- create subset index for status<>VALID

    create index invalid_dbs on dbs (decode(status,'VALID',NULL,status))

    -- index will be used when query matches index column expression

    select * from dbs where decode(status,'VALID',NULL,status)='INVALID'

    -- but not for "simple" query

    select * from dbs where status='INVALID'

    Creating adequate equivalence definitions and setting query_rewrite_integrity down to  TRUSTED enables Oracle to use the FBI also for queries not applying the index column expression exactly:

    execute   sys.dbms_advanced_rewrite.declare_rewrite_equivalence(name => 'DBS_INVALID',  source_stmt => 'SELECT * FROM DBS WHERE STATUS=''INVALID''',   destination_stmt => 'SELECT * FROM DBS WHERE DECODE(STATUS,''VALID'',NULL,STATUS)=''INVALID''',  validate => true,  rewrite_mode => 'GENERAL'  );

    execute   sys.dbms_advanced_rewrite.declare_rewrite_equivalence(name => 'DBS_UNUSABLE', source_stmt => 'SELECT * FROM DBS WHERE STATUS=''UNUSABLE''',  destination_stmt => 'SELECT * FROM DBS WHERE DECODE(STATUS,''VALID'',NULL,STATUS)=''UNUSABLE''', validate => true,  rewrite_mode => 'GENERAL'  );

    alter session set query_rewrite_integrity = 'TRUSTED';

    SQL> select * from dbs where status='INVALID';

    -------------------------------------------------------------------------------------------

    | Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |

    -------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT            |             |   479 |   950K|   110   (0)| 00:00:02 |

    |   1 |  TABLE ACCESS BY INDEX ROWID| DBS         |   479 |   950K|   110   (0)| 00:00:02 |

    |*  2 |   INDEX RANGE SCAN          | INVALID_DBS |   191 |       |     3   (0)| 00:00:01 |

    -------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       2 - access(DECODE("STATUS",'VALID',NULL,"STATUS")='INVALID')

  • Rainer Stenzel
    Rainer Stenzel Member Posts: 64 Bronze Badge
    edited Jan 20, 2019 2:22AM

    Interessting aspect . Did not see this at first. Preequesite would be that the function is a linear transformation. In the example above I wonder what happens if you write status between 0 and 2, or even status = 0.

    Thus we see there are some difficulties, The idea is better than I thought, but needs IMHO some elaboration.

    When f may give null an OR branch may be added and left to OR expansion/optimization:

    A predicate

    c=:1

    may be transformed to

    c=:1 and f(:1) is null

    or

    f(c)=f(:1) and c=:1 and f(:1) is not null

    on availability of an index on f(c).

  • Chris Hunt
    Chris Hunt Member Posts: 2,066 Gold Trophy

    I don't want Oracle guessing what I mean and adding conditions to my queries that I can't see. If I've come up with some horrible index full of DECODEs, it's up to me to write queries that will use it.