This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Sep 23, 2013 1:41 AM by Hemant K Chitale Go to original post RSS
  • 15. Re: RBO and Index access
    sybrand_b Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    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

Legend

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