1 2 Previous Next 15 Replies Latest reply: Jan 5, 2014 1:32 AM by Jonathan Lewis RSS

Query tuning: optimizer do not use function index

Mr.D. Explorer
Currently Being Moderated

Hi,

i've a query written by a developer that i cannot modify.

This is the where condition:

 

 

WHERE (   UPPER(TRIM (CODFSC)) = UPPER (TRIM ( '01923980500'))

       OR UPPER(TRIM (CODUIC)) = UPPER (TRIM ( '01923980500')))

 

There is an index on CODFSC and 1 on CODUIC.

the plan is:

Plan

SELECT STATEMENT  ALL_ROWS Cost: 9,194  Bytes: 3,206,502  Cardinality: 15,054  

1 TABLE ACCESS FULL TABLE ANAGRAFICA Cost: 9,194  Bytes: 3,206,502  Cardinality: 15,054

 

So i've created two new index on UPPER(TRIM (CODFSC)) and UPPER(TRIM (CODUIC)) but the plan

stil full scan.

Modifing the where condition in:

 

WHERE (   CODFSC = UPPER (TRIM ( '01923980500'))

       OR CODUIC = UPPER (TRIM ( '01923980500')))

 

the plan is:

 

SELECT STATEMENT  ALL_ROWSCost: 157  Bytes: 426  Cardinality: 2    

5 CONCATENATION   

  2 TABLE ACCESS BY INDEX ROWID TABLE ANAGRAFICA Cost: 5  Bytes: 213  Cardinality: 1  

   1 INDEX RANGE SCAN INDEX ANAGRAFICA_IDX01 Cost: 3  Cardinality: 1 

  4 TABLE ACCESS BY INDEX ROWID TABLE ANAGRAFICA Cost: 152  Bytes: 213  Cardinality: 1  

   3 INDEX SKIP SCAN INDEX ANAGRAFICA_IDX02 Cost: 151  Cardinality: 1

 

 

Why optimizer do not use my funct index?

 

Thank you.

  • 1. Re: Query tuning: optimizer do not use function index
    Aman.... Oracle ACE
    Currently Being Moderated

    Two things come to mind. One, any transformation on the column via a function applied would not let the index be used. Second, the data type conversion , if happens, also would not let the index be used. Since you have not mentioned much details about the data type of the columns, information about the rows etc, anything further can't be said.

     

    HTH

    Aman....

  • 2. Re: Query tuning: optimizer do not use function index
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    After creating the two function-based indexes did you collect stats on the hidden columns that Oracle created to support the index definitions ? (method_opt=>'for all hidden columns'   may be the best choice). If not then your problem may simply be one of a bad cardinality estimate. If this isn't the problem then try adding the /*+ use_concat */ to force the optimizer into the CONCATENATION path when you use the original query, and we may get a clue about why the optimizer doesn't want to take the path. (Do show the predicate section with the rest of the plan).

     

    Finally, you haven't stated your Oracle version - and it's only in very recent versions that the optimizer has extended the concatenation operation to take advantage of function-based indexes.

     

    Regards

    Jonathan Lewis

  • 3. Re: Query tuning: optimizer do not use function index
    Mr.D. Explorer
    Currently Being Moderated

    Hi Jonathan,

    thank you.

    My db is in 11.2.0.2.

    I've collect statistics on the table with method_opt=>'for all hidden columns' but nothing, optimizer choose full scan.

    Using  /*+ use_concat */ hint optimizer choose the concatenation with my function index.


    Any ideas?


    Thank you

  • 4. Re: Query tuning: optimizer do not use function index
    Mr.D. Explorer
    Currently Being Moderated

    Hi,

    i think the problem is that in where condition developer attach:

     

    WHERE (   UPPER(TRIM (CODFSC)) = UPPER (TRIM ( '01923980500'))

           OR UPPER(TRIM (CODUIC)) = UPPER (TRIM ( '01923980500')))

       AND (:1 = 0 OR id_anggrp = :2)


    where :1 and :2 is null.

     

     

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

    | Id  | Operation          | Name            | E-Rows | Cost (%CPU)|

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

    |   0 | SELECT STATEMENT   |                 |        |     1 (100)|

    |*  1 |  FILTER            |                 |        |            |

    |*  2 |   TABLE ACCESS FULL| ANGGRP          |  15054 |  9194   (1)|

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

     

    Predicate Information (identified by operation id):

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

     

       1 - filter(NULL IS NOT NULL)

       2 - filter(("STATO"=0 AND (UPPER(TRIM("CODFSC"))='01923980500' OR

                  UPPER(TRIM("CODUIC_UIC"))='01923980500')))

     


    Removing AND (:1 = 0 OR id_anggrp = :2) it uses concatenation.


    Plan hash value: 361750341

     

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

    | Id  | Operation                    | Name                   | E-Rows | Cost (%CPU)|

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

    |   0 | SELECT STATEMENT             |                        |        |     7 (100)|

    |   1 |  CONCATENATION               |                        |        |            |

    |*  2 |   TABLE ACCESS BY INDEX ROWID| ANGGRP                 |      1 |     5   (0)|

    |*  3 |    INDEX RANGE SCAN          | IDX_ANGGRP_61          |      1 |     3   (0)|

    |*  4 |   TABLE ACCESS BY INDEX ROWID| ANGGRP                 |      1 |     2   (0)|

    |*  5 |    INDEX RANGE SCAN          | IDX_ANGGRP_62          |      1 |     1   (0)|

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

     

    Predicate Information (identified by operation id):

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

     

       2 - filter("STATE"=0)

       3 - access("ANGGRP"."SYS_NC00089$"='01923980500')

       4 - filter(("STATE"=0 AND LNNVL(UPPER(TRIM("CODFSC"))='01923980500')))

       5 - access("ANGGRP"."SYS_NC00088$"='01923980500')

  • 5. Re: Query tuning: optimizer do not use function index
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    There's something you're not telling us that may be relevant.

     

    a) Your first plan above has a predicate "STATO"=0 that doesn't appear in the where clause - STATO seems to change to STATE in the second plan

    b) The first plan has a filter operation with predicate NULL IS NOT NULL - this can't have arisen from the WHERE clause you supplied, moreover it would have stopped the table scan in line 2 from running (even though the plan says that IF Oracle had reached line 2 it would have done a table scan).

     

     

    Regards

    Jonathan Lewis

  • 6. Re: Query tuning: optimizer do not use function index
    Mr.D. Explorer
    Currently Being Moderated

    a) When i've perform cut&paste on this forum, i've change the name for error.

    b) NULL IS NOT NULL is caused by this condition AND (:1 = 0 OR id_anggrp = :2), unfortunately i cannot modify the query.


    Thank you

    Davide

  • 7. Re: Query tuning: optimizer do not use function index
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    Mr.D. wrote:

     

    a) When i've perform cut&paste on this forum, i've change the name for error.

    b) NULL IS NOT NULL is caused by this condition AND (:1 = 0 OR id_anggrp = :2), unfortunately i cannot modify the query.


    Thank you

    Davide

     

    Davide,

     

    "Null is not null" should not be generated by (:1 = 0 or id_agggrp = :2) since Oracle's predicate is always false, and your predicate could be true.

     

    Regards

    Jonathan Lewis

  • 8. Re: Query tuning: optimizer do not use function index
    Mr.D. Explorer
    Currently Being Moderated

     

    "Null is not null" should not be generated by (:1 = 0 or id_agggrp = :2) since Oracle's predicate is always false, and your predicate could be true.

     

    Regards

    Jonathan Lewis

     

    I've re-execute the explain, NULL IS NOT NULL now is not present! bah....

     

    Sorry

  • 9. Re: Query tuning: optimizer do not use function index
    Mr.D. Explorer
    Currently Being Moderated

    ARGH!

    Sorry, there are 3 query very similar and i mistake!

    The where condition is:

     

    WHERE ( LENGTH ( :b7) IS NULL OR

             UPPER (TRIM (CODFSC)) = UPPER (TRIM ( :b8)) or

             UPPER (TRIM (CODUIC)) = UPPER (TRIM ( :b9)))

           AND STATE = 0;


    where :b7, :b8, :b9 are bind that contains the same value.

    With this where i've a FULL SCAN:

     

    Plan hash value: 4014069819

     

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

    | Id  | Operation         | Name            | E-Rows | Cost (%CPU)|

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

    |   0 | SELECT STATEMENT  |                 |        |  9214 (100)|

    |*  1 |  TABLE ACCESS FULL| ANGGRP          |  37917 |  9214   (1)|

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

     

    Predicate Information (identified by operation id):

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

     

       1 - filter(("STATE"=0 AND (LENGTH(:B7) IS NULL OR

                  UPPER(TRIM("CODUIC"))=UPPER(TRIM(:B9)) OR

                  UPPER(TRIM("CODFSC"))=UPPER(TRIM(:B8)))))

     

    Removing LENGTH ( :b7) IS NULL it uses my indexes in concatenation:


    Plan hash value: 361750341

     

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

    | Id  | Operation                    | Name                   | E-Rows | Cost (%CPU)|

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

    |   0 | SELECT STATEMENT             |                        |        |     7 (100)|

    |   1 |  CONCATENATION               |                        |        |            |

    |*  2 |   TABLE ACCESS BY INDEX ROWID| ANGGRP                 |      1 |     5   (0)|

    |*  3 |    INDEX RANGE SCAN          | IDX_ANGGRP_61          |      1 |     3   (0)|

    |*  4 |   TABLE ACCESS BY INDEX ROWID| ANGGRP                 |     96 |     2   (0)|

    |*  5 |    INDEX RANGE SCAN          | IDX_ANGGRP_62          |      1 |     1   (0)|

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

     

    Predicate Information (identified by operation id):

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

     

       2 - filter("STATE"=0)

       3 - access("ANGGRP"."SYS_NC00089$"=UPPER(TRIM(:B8)))

       4 - filter(("STATE"=0 AND LNNVL(UPPER(TRIM("CODFSC"))=UPPER(TRIM(:B8)))))

       5 - access("ANGGRP"."SYS_NC00088$"=UPPER(TRIM(:B9)))

  • 10. Re: Query tuning: optimizer do not use function index
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    The first path is what I would have predicted as the probably path - it looks just a little too complicated for the optimizer to use what I call the "conditional SQL" ( Conditional SQL (2) | Oracle Scratchpad )mechanism - ideally the front-end code should have been written as: "if variable is null then execute statement 1 else execute statement 2".

     

    Since length(:7) will be null if and only iff :7 is null then you could test this query with a modified predicate just to see if it that allows Oracle to generate the three-way concatenation (I suspect it won't).

     

     

    UPDATE: This should have come out as a reply to what is currently your most recent post: https://community.oracle.com/message/11317625#11317625

     

    Regards

    Jonathan Lewis

  • 11. Re: Query tuning: optimizer do not use function index
    Franck Pachot Journeyer
    Currently Being Moderated

    Hi,

     

    This may depend on the version, but I've seen that the optimizer considers concatenation only when there is an index access - even with the USE_CONCAT hint.

    This can be seen in the 10053 trace file by 'Or-expansion bypassed: No index driver found.'

     

    There are undocumented hint options for the USE_CONCAT that helps to force that. For example in a query similar to yours, I get the 3 concatenation branches with: '/*+ use_concat( or_predicates(2) ) */'

     

       PLAN_TABLE_OUTPUT

       SQL_ID 2tt1a2txmgwqf, child number 0

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

       select /*+ use_concat( or_predicates(2) ) */ count(*) from TEST where

       state=0 and ( length(:v) is null or ( upper(trim(a))=upper(trim(:v))

       ) or ( upper(trim(b))=upper(trim(:v)) ) )

     

     

       Plan hash value: 1851972013

     

     

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

       | Id  | Operation                             | Name  |

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

       |   0 | SELECT STATEMENT                      |       |

       |   1 |  SORT AGGREGATE                       |       |

       |   2 |   CONCATENATION                       |       |

       |*  3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TEST  |

       |*  4 |     INDEX RANGE SCAN                  | TESTB |

       |*  5 |    TABLE ACCESS BY INDEX ROWID BATCHED| TEST  |

       |*  6 |     INDEX RANGE SCAN                  | TESTA |

       |*  7 |    FILTER                             |       |

       |*  8 |     TABLE ACCESS FULL                 | TEST  |

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

     

     

       Predicate Information (identified by operation id):

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

     

     

       3 - filter("STATE"=0)

       4 - access("TEST"."SYS_NC00005$"=UPPER(TRIM(:V)))

       5 - filter(("STATE"=0 AND LNNVL(UPPER(TRIM(TO_CHAR("B")))=UPPER(TRIM(:V)))))

       6 - access("TEST"."SYS_NC00004$"=UPPER(TRIM(:V)))

       7 - filter(LENGTH(:V) IS NULL)

       8 - filter(("STATE"=0 AND LNNVL(UPPER(TRIM(TO_CHAR("A")))=UPPER(TRIM(:V))) AND

       LNNVL(UPPER(TRIM(TO_CHAR("B")))=UPPER(TRIM(:V)))))

     

    Regards,

    Franck.

  • 12. Re: Query tuning: optimizer do not use function index
    Hoek Guru
    Currently Being Moderated

    On a side-note:

    You should pay close attention to the results of your query.

    Apparently you may get wrong results in 11.2.0.2, see MetaLink/MOS bug document:

    Bug 17650879 : WRONG RESULTS FROM QUERY WITH OR EXPANSION AND FUNCTION BASED INDEX

  • 13. Re: Query tuning: optimizer do not use function index
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    Franck,

     

    I keep forgetting that the default for OR-expansion depends on having an indexed access path for each branch.

     

    The 2 in your use of or_predicates(2) depends on the position of complex predicate that is to be expanded.  If you change the predicate order so that "state=0" appears AFTER the complex predicate you'd have to change the hint to "or_predicates(1)".

     

    Apart from the current undocumented state of the hint, it does also introduce the worrying thought that for a more complex query a change in transformation may result in a different set of query blocks being generated with a different ordering of predicates. Yet another case of ensuring that if you hint anything you hint everything (or create the SQL Baseline).

     

    Regards

    Jonathan Lewis

  • 14. Re: Query tuning: optimizer do not use function index
    Mr.D. Explorer
    Currently Being Moderated

    Thank you Jonathan,

    your blog is very helpful and i like to read it.

    So i could ask the developer to modify the application and create a dynamic sql where if :7 is not null then

    add the condition :

     

             UPPER (TRIM (CODFSC)) = UPPER (TRIM ( :b8)) or

             UPPER (TRIM (CODUIC)) = UPPER (TRIM ( :b9))


    to the statement, correct?

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points