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

    Query tuning: optimizer do not use function index

    Mr.D.

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

          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

            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.

              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.

                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

                  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.

                    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

                      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.

                         

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

                          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

                            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

                              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

                                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

                                  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.

                                    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