4 Replies Latest reply on Aug 1, 2013 8:46 PM by Ariean

    Change Access Path For Optimizer

    Ariean

      Hello All,

       

      How do i make or push the Oracle optimizer to choose the range scan instead of skip scan?

       

      Plan hash value: 1371174339
      
      --------------------------------------------------------------------------------
      | Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------
      |   0 | DELETE STATEMENT |             |  8386 |   384K|   800   (0)| 00:00:10 |
      |   1 |  DELETE          | LOAN        |       |       |            |          |
      |*  2 |   INDEX SKIP SCAN| IDX_LOAN_02 |  8386 |   384K|   800   (0)| 00:00:10 |
      --------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         2 - access("L"."VER_NUM"=3 AND "L"."EXTRACT_DT"='2012-12-31' AND
                    "L"."UNINUM"='925000')
             filter("L"."VER_NUM"=3 AND "L"."EXTRACT_DT"='2012-12-31' AND
                    "L"."UNINUM"='925000' AND TO_NUMBER("L"."PROVIDER")=6)
      
      

       

       

      SQL Statement:

      EXPLAIN PLAN FOR

      DELETE /*+ INDEX(L IDX_LOAN_02) */ FROM loan L WHERE L.provider=6 AND L.ver_num=3 AND L.EXTRACT_DT='2012-12-31' AND L.UNINUM='925000'

       

      Table Index:

                                                             

      Column_NameIndex_NameIndex_TypeUniquenessTablespace_NameColumn_Position
      SURROG_LOAN_NUMIDX_LOAN_01NORMALNONUNIQUEFCSDWH_STGIND1
      PROVIDERIDX_LOAN_02NORMALNONUNIQUEFCSDWH_STGIND1
      VER_NUMIDX_LOAN_02NORMALNONUNIQUEFCSDWH_STGIND2
      EXTRACT_DTIDX_LOAN_02NORMALNONUNIQUEFCSDWH_STGIND3
      UNINUMIDX_LOAN_02NORMALNONUNIQUEFCSDWH_STGIND4

       

      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      "CORE 11.2.0.3.0 Production"
      TNS for Linux: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      

      Thank you

        • 1. Re: Change Access Path For Optimizer
          riedelme

          Weird.  You defined all 4 columns listed in the predicate and a skip scan should not be occurring.  Try a new index with access columns as the first ones ver_num, extract_dt, and uninum

           

           

          Message was edited by: riedelme

          1 person found this helpful
          • 2. Re: Change Access Path For Optimizer
            Jonathan Lewis

            The filter predicate shows to_number(provider) = 6 - so the column type is character, hence the skip scan.

             

            Options:

            a) try provider = '6' - which assumes that it's not stored as (e.g.) '0006'.

            b) redefine the index so that the first column is to_number(provider) - but this assumes every value in the column will look like a number

            c) change the column order in the index to put provider at the end

             

            EDIT - d) (harder) change the data definition so that the provider column is numeric and all parts of the application know it.

            Regards

            Jonathan Lewis

            • 3. Re: Change Access Path For Optimizer
              Ariean

              I modified the index to below and re-gathered the table statistics which i did earlier as well,  it looks better now can you explain why please I don't understand why it changed behaviour?

               

              CREATE INDEX IDX_LOAN_02 ON LOAN(VER_NUM,EXTRACT_DT,UNINUM)

              TABLESPACE FCSDWH_STGIND

              COMPUTE STATISTICS

               

              EXPLAIN PLAN FOR

              DELETE /*+ INDEX(L IDX_LOAN_02) */ FROM loan L WHERE L.provider=6 AND L.ver_num=3 AND L.EXTRACT_DT='2012-12-31' AND L.UNINUM='925000'

              Execution Plan:

              Plan hash value: 23028520

               

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

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

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

              |   0 | DELETE STATEMENT             |             |  2516 |   115K|   682   (1)| 00:00:09 |

              |   1 |  DELETE                      | LOAN        |       |       |            |          |

              |*  2 |   TABLE ACCESS BY INDEX ROWID| LOAN        |  2516 |   115K|   682   (1)| 00:00:09 |

              |*  3 |    INDEX RANGE SCAN          | IDX_LOAN_02 | 10220 |       |    49   (0)| 00:00:01 |

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

               

              Predicate Information (identified by operation id):

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

               

                 2 - filter(TO_NUMBER("L"."PROVIDER")=6)

                 3 - access("L"."VER_NUM"=3 AND "L"."EXTRACT_DT"='2012-12-31' AND

                            "L"."UNINUM"='925000')

              • 4. Re: Change Access Path For Optimizer
                Ariean

                JonathanLewis wrote:

                 

                The filter predicate shows to_number(provider) = 6 - so the column type is character, hence the skip scan.

                 

                Options:

                a) try provider = '6' - which assumes that it's not stored as (e.g.) '0006'.

                b) redefine the index so that the first column is to_number(provider) - but this assumes every value in the column will look like a number

                c) change the column order in the index to put provider at the end

                 

                EDIT - d) (harder) change the data definition so that the provider column is numeric and all parts of the application know it.

                Regards

                Jonathan Lewis

                Wow it much better now, cost came down to 13%.

                 

                CREATE INDEX IDX_LOAN_02 ON LOAN(PROVIDER,VER_NUM,EXTRACT_DT,UNINUM)
                TABLESPACE FCSDWH_STGIND
                COMPUTE STATISTICS
                
                
                

                 

                EXPLAIN PLAN FOR
                DELETE /*+ INDEX(L IDX_LOAN_02) */ FROM loan L WHERE L.provider='6' AND L.ver_num=3 AND L.EXTRACT_DT='2012-12-31' AND L.UNINUM='925000'
                

                 

                Plan hash value: 4293273913
                
                ---------------------------------------------------------------------------------
                | Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
                ---------------------------------------------------------------------------------
                |   0 | DELETE STATEMENT  |             |  2244 |   102K|    13   (0)| 00:00:01 |
                |   1 |  DELETE           | LOAN        |       |       |            |          |
                |*  2 |   INDEX RANGE SCAN| IDX_LOAN_02 |  2244 |   102K|    13   (0)| 00:00:01 |
                ---------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                
                   2 - access("L"."PROVIDER"='6' AND "L"."VER_NUM"=3 AND
                              "L"."EXTRACT_DT"='2012-12-31' AND "L"."UNINUM"='925000')