1 2 Previous Next 17 Replies Latest reply: Sep 23, 2013 3:41 AM by Hemant K Chitale Go to original post RSS
      • 15. Re: RBO and Index access
        sybrand_b

        Query 1 uses an index full scan, it reads ALL of the index.

        A full table scan will be replaced by an index full scan if there is an unique key of the table,

        Your interpretation of the explain plan results of query 1 is incorrect: because of incorrect table design and incorrect code it has to use index FULL scan, and it can not use normal index access.

        So BOTH statements are inefficient due to implicit conversion! BOTH of them. Not only one.

        And due to lack of system statistics in the database Oracle uses defaults for IOSEEKTIM and IOTFRSPEED.

        Also the ID column in the second query is NULL allowed, so to_number(ID) results in NULL, which is never in a B-Tree index.

        Oracle is just doing what you ask it. Database design and/or application needs to be fixed, or you need to put some crutches (function based indices)  in the database allover, to make Oracle compensate for the erroneous database and application design.

         

        ---------

        Sybrand Bakker

        Senior Oracle DBA

        • 16. Re: RBO and Index access
          Sekar_BLUE4EVER

          Yes it's in the same database and I used in same session.

           

          ***************************************
          
          BASE STATISTICAL INFORMATION
          
          ***********************
          
          Table Stats::
          
            Table: xxx  Alias: xxx
          
              #Rows: 236505  #Blks: 
          11405  AvgRowLen:  335.00
          
          Index Stats::
          
            Index: xxxPK  Col#: 1 2 3
          
              LVLS: 2  #LB: 1293 
          #DK: 236505  LB/K: 1.00  DB/K: 1.00 
          CLUF: 74107.00
          
              User hint to use this index
          
            Index: xxxUK_02  Col#: 1 2 19 3
          
              LVLS: 2  #LB: 1487 
          #DK: 236505  LB/K: 1.00  DB/K: 1.00 
          CLUF: 74133.00
          
            Index: xxxUK_03  Col#: 1 2 7 17
          
              LVLS: 2  #LB: 1487 
          #DK: 236505  LB/K: 1.00  DB/K: 1.00 
          CLUF: 78231.00
          
            Index: xxxUK_04  Col#: 1 2 18 3
          
              LVLS: 2  #LB: 1487 
          #DK: 236505  LB/K: 1.00  DB/K: 1.00 
          CLUF: 74110.00
           ***************************************
          
          SINGLE TABLE ACCESS PATH
          
            Column (#1): I_NO(CHARACTER)
          
              AvgLen: 3.00 NDV: 1 Nulls: 0 Density:
          1
          
            Column (#2): A_NO(CHARACTER)
          
              AvgLen: 16.00 NDV: 24294 Nulls: 0
          Density: 4.1162e-05
          
            Table: xxx  Alias: xxx     
          
              Card: Original: 236505  Rounded: 1 
          Computed: 0.49  Non Adjusted:
          0.49
          
          kkofmx: index filter:TO_NUMBER("xxx"."I_NO")=003 AND
          TO_NUMBER("xxx"."A_NO")=3116882000000028 AND
          TO_NUMBER("xxx"."R_NO")>0
          
          kkofmx: index
          filter:TO_NUMBER("xxx"."A_NO")=3116882000000028 AND
          TO_NUMBER("xxx"."R_NO")>0
          
          kkofmx: index filter:TO_NUMBER("xxx"."R_NO")>0
          
            Access Path: index (FullScan)
          
              Index: INCTPK
          
              resc_io: 1295.00  resc_cpu: 56523265
          
              ix_sel: 1  ix_sel_with_filters: 1
          
              Cost: 1307.28  Resp: 1307.28 
          Degree: 1
          
            Best:: AccessPath: IndexRange  Index: xxxPK
          
                   Cost: 1307.28  Degree: 1 
          Resp: 1307.28  Card: 0.49  Bytes: 0
          
          ***************************************
          
          OPTIMIZER STATISTICS AND COMPUTATIONS
          
          ***************************************
          
          GENERAL PLANS
          
          ***************************************
          
          Considering cardinality-based initial join order.
          
          Permutations for Starting Table 
          
          ***********************
          
          Join order[1]:  xxx[xxx]#0
          
          ***********************
          
          Best so far: Table#: 0  cost:
          1307.2762  card: 0.4868  bytes: 28
          
          (newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:80000
          
          *********************************
          
          Number of join permutations tried: 1
          
          *********************************
          
          Final - All Rows Plan:  Best join order:
          1
          
            Cost: 1307.2762  Degree: 1 
          Card: 1.0000  Bytes: 28
          
            Resc: 1307.2762  Resc_io: 1295.0000  Resc_cpu: 127476225
          
            Resp: 1307.2762  Resp_io: 1295.0000  Resc_cpu: 127476225
          
          kkoipt: Query block SEL$1 (#0)
          
          • 17. Re: RBO and Index access
            Hemant K Chitale

            The 10053 trace on the column in DDD shows that there are NULLs present in the ID column for about half the rows

            •   Column (#14): ID(CHARACTER)  
            •     AvgLen: 5.00 NDV: 56096 Nulls: 56108 Density: 1.7827e-05  

             

            In the XXX 10053 trace, we see column statistics for I_NO and A_NO  (both having 0 nulls) but not for R_NO.   Oracle chooses to do a Full Scan of the Index -- are all three columns in the index definition ?

             

             

            Hemant K Chitale

            1 2 Previous Next