1 2 Previous Next 16 Replies Latest reply: Feb 24, 2014 6:59 AM by NightWing RSS

    Why function based multiple column indexes don't use index skip scan?

      Hello All,

       

      I have just been hired by a new company and I have been exploring its database infrastructure. Interestingly, I see that multiple column function based indexed used for every tables. I found it strange but they told me 'We use Axapta, in order to connect Axapta with Oracle, function based indexes should be used for better performance. Therefore, our DBAs create multiple function based indexes for each table in the database.' Unfortunately, I cannot judge their business logic.

       

      My question is, I have just created similar tables to my local database in order to understand the behaviour of multiple column function based indexes. In order to create function based index (substr and nls_lower) I have to declare columns as varchars2. Because, in my company our DBAs had created some number columns as varchar2 data type. I have created two excatly same table for my experiment. I create miltiple function based index to my_first table, then I create normal multiple index to my_sec table. The interesting thing is, index skip scan cannot be performed on multiple function based indexes (my_first table). However, it can be performed to normal multiple index on my_sec table. I hope I did express myself clearly.

       

      Note: I also ask the logic of function based index rule, they told me when they indexing a column they do ((column length) * 2 + 1) formula. For example, I want to create index on areacode column, the column data type in varchar2(3), so I have to use 3*2+1 = 7, (substr(nls_lower(areacode), 1, 7). substr(nls_lower()) notation is used nested for all function based indexes. I know these things are very illogical but they told me, they use this kind of implementation for Axapta.

       

      Anyway, in this thread my question is reletad to the function based indexes with index skip scanning, not bussiness logic, because I cannot change the business logic.

       

      Also, can you please give the tricks or clues for multiple function based indexes?

       

      Thanks for your help.

       


      SQL> create table my_first as select '201' areacode, to_char(rownum+100) account_num, dbms_random.st
      ring('A', 10) name  from dual connect by level <= 5000;

      Table created.

       

      SQL> create table my_sec as select '201' areacode, to_char(rownum+100) account_num, dbms_random.st

      ring('A', 10) name  from dual connect by level <= 5000;

      Table created.

       

      SQL> alter table my_first modify account_num varchar2(12);

      Table altered.


      SQL> alter table my_sec modify account_num varchar2(12);

      Table altered.

       

      SQL> alter table my_first modify areacode varchar2(3);

      Table altered.

       

      SQL> alter table my_sec modify areacode varchar2(3);

      Table altered.

       

      SQL> create index my_first_i on my_first (substr(nls_lower(areacode), 1, 7), substr(nls_lower(account_num),1,15));

      Index created.

       

      SQL> create index my_sec_i on my_sec (areacode, account_num);

      Index created.

       

      SQL> analyze table my_first compute statistics for all indexed columns for all indexes;

      Table analyzed.

       

      SQL> analyze table my_sec compute statistics for all indexed columns for all indexes;

      Table analyzed.

       

      SQL> exec dbms_stats.gather_table_stats(USER,'MY_FIRST');

      PL/SQL procedure successfully completed.

       

      SQL> exec dbms_stats.gather_table_stats(USER,'MY_SEC');

      PL/SQL procedure successfully completed.

       

      SQL> desc my_first;
      Name                                      Null?    Type
      ----------------------------------------- -------- ----------------------------
      AREACODE                                           VARCHAR2(3)
      ACCOUNT_NUM                                        VARCHAR2(12)
      NAME                                               VARCHAR2(4000)

       

      SQL> desc my_sec
      Name                                      Null?    Type
      ----------------------------------------- -------- ----------------------------
      AREACODE                                           VARCHAR2(3)
      ACCOUNT_NUM                                        VARCHAR2(12)
      NAME                                               VARCHAR2(4000)

       

      SQL> select * from my_sec where account_num = '4000';


      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 1838048852

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

      | Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti
      me     |

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

      |   0 | SELECT STATEMENT            |          |     1 |    19 |     3   (0)| 00
      :00:01 |

      |   1 |  TABLE ACCESS BY INDEX ROWID| MY_SEC   |     1 |    19 |     3   (0)| 00
      :00:01 |

      |*  2 |   INDEX SKIP SCAN           | MY_SEC_I |     1 |       |     2   (0)| 00
      :00:01 |

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


      Predicate Information (identified by operation id):
      ---------------------------------------------------

         2 - access("ACCOUNT_NUM"='4000')
             filter("ACCOUNT_NUM"='4000')


      Statistics
      ----------------------------------------------------------
                1  recursive calls
                0  db block gets
                7  consistent gets
                0  physical reads
                0  redo size
              543  bytes sent via SQL*Net to client
              384  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
                1  rows processed

       

      SQL> select * from my_first where substr(nls_lower(account_num),1,25) = '4000';


      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 1110109060

      ------------------------------------------------------------------------------
      | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
      ------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT  |          |     1 |    20 |     9  (12)| 00:00:01 |
      |*  1 |  TABLE ACCESS FULL| MY_FIRST |     1 |    20 |     9  (12)| 00:00:01 |
      ------------------------------------------------------------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

         1 - filter(SUBSTR(NLS_LOWER("MY_FIRST"."ACCOUNT_NUM"),1,15)='4000'
                    AND SUBSTR(NLS_LOWER("ACCOUNT_NUM"),1,25)='4000')


      Statistics
      ----------------------------------------------------------
               15  recursive calls
                0  db block gets
               26  consistent gets
                0  physical reads
                0  redo size
              543  bytes sent via SQL*Net to client
              384  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
                1  rows processed

       

      SQL> select /*+ INDEX_SS(MY_FIRST)  */ * from my_first where substr(nls_lower(account_num),1,25) = '4000';


      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 2466066660

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

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

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

      |   0 | SELECT STATEMENT            |            |     1 |    20 |    17   (6)|
      00:00:01 |

      |*  1 |  TABLE ACCESS BY INDEX ROWID| MY_FIRST   |     1 |    20 |    17   (6)|
      00:00:01 |

      |*  2 |   INDEX FULL SCAN           | MY_FIRST_I |     1 |       |    16   (7)|
      00:00:01 |

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


      Predicate Information (identified by operation id):
      ---------------------------------------------------

         1 - filter(SUBSTR(NLS_LOWER("ACCOUNT_NUM"),1,25)='4000')
         2 - access(SUBSTR(NLS_LOWER("ACCOUNT_NUM"),1,15)='4000')
             filter(SUBSTR(NLS_LOWER("ACCOUNT_NUM"),1,15)='4000')


      Statistics
      ----------------------------------------------------------
               15  recursive calls
                0  db block gets
              857  consistent gets
                0  physical reads
                0  redo size
              543  bytes sent via SQL*Net to client
              384  bytes received via SQL*Net from client
                2  SQL*Net roundtrips to/from client
                0  sorts (memory)
                0  sorts (disk)
                1  rows processed

        • 1. Re: Why function based multiple column indexes don't use index skip scan?
          Jonathan Lewis

          Check MoS for a bug with FBI and Skip Scan - it looks like it might be a bug.

           

          On 11.2.0.4 with your sample code, the 10053 trace shows the optimizer considering an index FULL scan at the point where it should be considering an index SKIP scan for the "Single table access path".

           

          Someone with 12.1.0.1 handy might like to run your test and see if it's fixed in that version.

           

          Regards

          Jonathan Lewis

          • 2. Re: Why function based multiple column indexes don't use index skip scan?
            Mohamed Houri

            As always in such a kind of situation I ask myself two questions

             

            1) If this application is a multi-user concurrent application, how are they protecting it against the deadlock threat of unindexed Foreign Keys as far as function based indexes do not protect against this particular threat (Unless the function based indexes start with the FK columns which is not the case here)

             

            2) Are they aware about the danger of setting the cursor sharing parameter to FORCE?

             

            SQL> select /*+ INDEX_SS(MY_FIRST)  */ * from my_first where substr(nls_lower(account_num),1,25) = '4000';

             

            SQL> select * from table(dbms_xplan.display_cursor);

             

             

            SQL_ID  2ssdnz3k37ayg, child number 0

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

            select /*+ INDEX_SS(MY_FIRST)  */ * from my_first where

            substr(nls_lower(account_num),:"SYS_B_0",:"SYS_B_1") = :"SYS_B_2"

             

            Plan hash value: 1110109060

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

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

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

            |   0 | SELECT STATEMENT  |          |       |       |    10 (100)|          |

            |*  1 |  TABLE ACCESS FULL| MY_FIRST |    50 |  1000 |    10  (10)| 00:00:01 |

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

             

            Predicate Information (identified by operation id):

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

               1 - filter(SUBSTR(NLS_LOWER("ACCOUNT_NUM"),:SYS_B_0,:SYS_B_1)=:SYS_B_2)


            Their indexes will cease to be used because they will not match anymore the function based index definition


            Best regards

            Mohamed Houri

            • 3. Re: Why function based multiple column indexes don't use index skip scan?
              Martin Preiss

              looks similar in 12.1.0.1 - the only difference is a TABLE ACCESS BY INDEX ROWID BATCHED (described here by Timur Akhmadeev) instead of the simple TABLE ACCESS BY INDEX ROWID:

              -- plan for the hinted query in 12.1.0.1


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

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

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

              |   0 | SELECT STATEMENT                    |            |    50 |  1250 |    16   (0)| 00:00:01 |

              |*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MY_FIRST   |    50 |  1250 |    16   (0)| 00:00:01 |

              |*  2 |   INDEX FULL SCAN                   | MY_FIRST_I |     1 |       |    15   (0)| 00:00:01 |

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

               

              Predicate Information (identified by operation id):

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

                 1 - filter(SUBSTR(NLS_LOWER("ACCOUNT_NUM"),1,25)='4000')

                 2 - access(SUBSTR(NLS_LOWER("ACCOUNT_NUM"),1,15)='4000')

                     filter(SUBSTR(NLS_LOWER("ACCOUNT_NUM"),1,15)='4000')

              The CBO trace for the second query (the version without the hint) looks quite strange - I didn't do anything with the cost_io value ...

              Access path analysis for MY_FIRST

              ***************************************

              SINGLE TABLE ACCESS PATH

                Single Table Cardinality Estimation for MY_FIRST[MY_FIRST]

              SPD: Return code in qosdDSDirSetup: NOCTX, estType = TABLE

                Column (#5): SYS_NC00005$(VARCHAR2)

                  AvgLen: 5 NDV: 5000 Nulls: 0 Density: 0.000000

                Table: MY_FIRST  Alias: MY_FIRST

                  Card: Original: 5000.000000  Rounded: 50  Computed: 50.00  Non Adjusted: 50.00

              ***** Logdef predicate Adjustment ******

              Final IO cst 0.00 , CPU cst 400.00

              ***** End Logdef Adjustment ******

              ***** Logdef predicate Adjustment ******

              Final IO cst 0.00 , CPU cst 400.00

              ***** End Logdef Adjustment ******

                Access Path: TableScan

                  Cost:  8.08  Resp: 8.08  Degree: 0

                    Cost_io: 8.00  Cost_cpu: 3007672

                    Resp_io: 8.00  Resp_cpu: 3007672

              kkofmx: index filter:SUBSTR(NLS_LOWER("MY_FIRST"."ACCOUNT_NUM"),1,15)='4000'

               

              SPD: Return code in qosdDSDirSetup: NOCTX, estType = INDEX_FILTER

               

              ***** Virtual column  Adjustment ******

              Column name       SYS_NC00005$ 

              cost_cpu 400.00

              cost_io  179769313486231570000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.00

              ***** End virtual column  Adjustment ******

              ***** Logdef predicate Adjustment ******

              Final IO cst 0.00 , CPU cst 400.00

              ***** End Logdef Adjustment ******

                Access Path: index (FullScan)

                  Index: MY_FIRST_I

                  resc_io: 16.00  resc_cpu: 1114533

                  ix_sel: 1.000000  ix_sel_with_filters: 0.000200

              ***** Logdef predicate Adjustment ******

              Final IO cst 0.00 , CPU cst 0.00

              ***** End Logdef Adjustment ******

                  Cost: 16.03  Resp: 16.03  Degree: 1

                Best:: AccessPath: TableScan

                       Cost: 8.08  Degree: 1  Resp: 8.08  Card: 50.00  Bytes: 0

              • 4. Re: Why function based multiple column indexes don't use index skip scan?

                Mohamed Houri wrote:

                 

                As always in such a kind of situation I ask myself two questions

                 

                1) If this application is a multi-user concurrent application, how are they protecting it against the deadlock threat of unindexed Foreign Keys as far as function based indexes do not protect against this particular threat (Unless the function based indexes start with the FK columns which is not the case here)

                 


                 

                There is not any foreing key7 primary key relation ship in the database. In other words, of cource, there are links between tables but database don't know about it. Because tables are not created with PK, FK constraints. As I said beforeonly function based indexes are used. Unfourtanetely, very illogical and meaningless.

                 

                 

                 

                 

                2) Are they aware about the danger of setting the cursor sharing parameter to FORCE?

                 

                 

                 

                What do you mean by saying this? Could you please clarify?

                 

                 

                 

                By the way can you give some clues about function based indexes?

                 

                Regards

                • 5. Re: Why function based multiple column indexes don't use index skip scan?

                  Martin Preiss wrote:

                   

                  looks similar in 12.1.0.1 - the only difference is a TABLE ACCESS BY INDEX ROWID BATCHED (described here by Timur Akhmadeev) instead of the simple TABLE ACCESS BY INDEX ROWID:

                   

                  Thanks Martin for your remarkable thoughts. So, can we say this is unsolved bug, right?

                   

                  Regards

                  • 6. Re: Why function based multiple column indexes don't use index skip scan?

                    Jonathan Lewis wrote:

                     

                    Check MoS for a bug with FBI and Skip Scan - it looks like it might be a bug.

                     

                    On 11.2.0.4 with your sample code, the 10053 trace shows the optimizer considering an index FULL scan at the point where it should be considering an index SKIP scan for the "Single table access path".

                     

                    Someone with 12.1.0.1 handy might like to run your test and see if it's fixed in that version.

                     

                    Regards

                    Jonathan Lewis

                     

                    First of all thanks for your important ideas. I have just visited https://support.oracle.com/  However I couldn't cope how to check it. I am also wondering what are your recommendations about function based indexes. Can you lead me please?

                     

                    Yours Faithfully

                    Charlie

                    • 7. Re: Why function based multiple column indexes don't use index skip scan?
                      Martin Preiss

                      it looks like a bug - but I have currently no MoS access to check if it is a known bug.

                      • 8. Re: Why function based multiple column indexes don't use index skip scan?

                        By the way, I would like to ask very simple question. As I mentioned before, in my company all tables used multiple column function based indexes, when I use it which notation should I use after the equal sign?


                        select * from my_first where substr(nls_lower(account_num),1,25) = '4000'

                         

                        select * from my_first where substr(nls_lower(account_num),1,25) = substr(nls_lower('400'),1,25)

                         

                         

                        Which is do you think giving me better performance?

                         

                        Regards

                        Charlie

                        • 9. Re: Why function based multiple column indexes don't use index skip scan?
                          Jonathan Lewis

                          I did a quick search - but depending on my search criteria I either found only a couple of hits which didn't match, or a couple of hundred that I wasn't going to check.

                           

                          You have a repeatable test case - raise an SR with Oracle Support and send them the code.

                          If it's a known bug they might already have a patch.

                           

                          Your site's use of function-based indexes looks quite bizarre; as a general principle, though, function-based indexes can be a great aid to optimising performance

                           

                           

                          Regards

                          Jonathan Lewis

                          • 10. Re: Why function based multiple column indexes don't use index skip scan?

                            Jonathan, what would you like to say about my last question? You ideas are very important for me.

                             

                            select * from my_first where substr(nls_lower(account_num),1,25) = '4000'

                             

                            select * from my_first where substr(nls_lower(account_num),1,25) = substr(nls_lower('400'),1,25)

                             

                             

                            Which is do you think giving me better performance?

                             

                            Regards

                            • 11. Re: Why function based multiple column indexes don't use index skip scan?
                              rp0428

                              select * from my_first where substr(nls_lower(account_num),1,25) = '4000'

                               

                              select * from my_first where substr(nls_lower(account_num),1,25) = substr(nls_lower('400'),1,25)

                               

                               

                              Which is do you think giving me better performance?

                              Here are some questions for you:

                               

                              1. Which do YOU think is giving you better performance?

                               

                              2. Why do you think it makes sense to use SUBSTR from 1 to 25 if the literal you are using ('4000') is only 4 characters long?

                               

                              3. Why do you think it makes sense to use NLS_LOWER for a literal ('4000', '400') that contains ONLY numeric digits?

                               

                              4. Don't you think both of those WHERE clauses are kind of suspect?

                              • 12. Re: Why function based multiple column indexes don't use index skip scan?

                                rp0428 wrote:

                                 

                                Here are some questions for you:

                                 

                                1. Which do YOU think is giving you better performance?

                                 

                                 

                                 

                                I think this one is giving better performance. select * from my_first where substr(nls_lower(account_num),1,25) = substr(nls_lower('400'),1,25)

                                 

                                However, I am not sure, I am wondering your suggests.

                                 

                                 

                                 

                                2. Why do you think it makes sense to use SUBSTR from 1 to 25 if the literal you are using ('4000') is only 4 characters long?

                                 

                                 

                                I have already answered it in the beginning of this thread. Please read it.

                                 

                                 

                                 

                                3. Why do you think it makes sense to use NLS_LOWER for a literal ('4000', '400') that contains ONLY numeric digits?

                                 

                                 

                                I have already answered it in the beginning of this thread. Please read it.

                                 

                                 

                                 

                                4. Don't you think both of those WHERE clauses are kind of suspect?

                                 

                                I have already answered it in the beginning of this thread. Please read it.

                                • 13. Re: Why function based multiple column indexes don't use index skip scan?
                                  Jonathan Lewis

                                  NightWing wrote:

                                   

                                  Jonathan, what would you like to say about my last question? You ideas are very important for me.

                                   

                                  select * from my_first where substr(nls_lower(account_num),1,25) = '4000'

                                   

                                  select * from my_first where substr(nls_lower(account_num),1,25) = substr(nls_lower('400'),1,25)

                                   

                                   

                                  Which is do you think giving me better performance?

                                   

                                  Regards

                                   

                                  I wrote an answer to this question yesterday afternoon, but it seems to have got lost.

                                   

                                  You shouldn't be asking the forum this question - the DBAs have told you to use substr(nls_lower()) function on all the columns, which presumably means on all joins as well - so they should also tell you how to deal with incoming values. 

                                   

                                  Note - for consistency of rules (and possibly for correct results) they ought to say that you should use the second form - literal comparison is just a special case of column comparison - whatever impact that may have on performance.

                                   

                                  Regards

                                  Jonathan Lewis

                                  • 14. Re: Why function based multiple column indexes don't use index skip scan?
                                    Jonathan Lewis

                                    Have you raised an SR (or shown the example to your DBA's and suggested they need to raise an SR) for the buggy behaviour.

                                     

                                    Regards

                                    Jonathan Lewis

                                    1 2 Previous Next