This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Sep 23, 2013 1:41 AM by Hemant K Chitale RSS

RBO and Index access

Sekar_BLUE4EVER Newbie
Currently Being Moderated


Hi I have a 9i database which is using a RBO and we have a query as follows

 

select min(a) from tab where b='111' and c='1234444' and a>0;

 

We have a primary key index on the columns a,b and c.Unfortunately the query is doing a Full table scan even though the index is present.Is this the expected behaviour will rule based optimizer?Shouldn't it go for the index as we are selecting a single row?

 

Note:Please note the RBO is used in a production environment and we cannot change it now.

  • 1. Re: RBO and Index access
    sb92075 Guru
    Currently Being Moderated

    SHOW PARAMETER OPTIMIZER_MODE

     

    V9  is so  old & been obsoleted for so long I have forgotten details on CBO behavior

  • 2. Re: RBO and Index access
    Sekar_BLUE4EVER Newbie
    Currently Being Moderated

    its RULE

  • 3. Re: RBO and Index access
    sb92075 Guru
    Currently Being Moderated

    Sekar_BLUE4EVER wrote:

     

    its RULE

    So by definition, you accept the RBO results.

    Either upgrade to supported version or accept the RBO results.

  • 4. Re: RBO and Index access
    Sekar_BLUE4EVER Newbie
    Currently Being Moderated

    So we use hint to force the Index.and the full table scan is expected behaviour of RBO even though it is selecting only one row

  • 5. Re: RBO and Index access
    sb92075 Guru
    Currently Being Moderated

    So by definition, you accept the RBO results.

    Either upgrade to supported version or accept the RBO results.

  • 6. Re: RBO and Index access
    sybrand_b Guru
    Currently Being Moderated

    As you don't post a table definition and a break down of indexes, who can tell.

    But this

     

    b='111' and c='1234444'


    makes me very suspicious about some implicit conversion.



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

    Sybrand Bakker

    Senior Oracle DBA

     

  • 7. Re: RBO and Index access
    Aman.... Oracle ACE
    Currently Being Moderated

    There is no such mandatory thing that if you are selecting a single row, it must use an index. Your query is using an implicit conversion of numbers to chars and that's possibly one of the reasons that you are not having the index used-though there may be other reasons as well.

     

    That said, you are using a release, in production, which is long time back got desupported. Upgrade your db to at least 11.2.

     

    Aman....

  • 8. Re: RBO and Index access
    Sekar_BLUE4EVER Newbie
    Currently Being Moderated

    Actually we use bind variables in the query

    select min(a) from tab where b=:n  and c=:m and a>0;

    The bind variables are passed from a COBOL program and I am not sure if implicit conversion occurs.The datatypes in the COBOL program is declared as alpha numeric and we are using CHAR in Oracle table for those fields.But actually only numbers are stored in them I am not sure why the application is designed in such a way

  • 9. Re: RBO and Index access
    sybrand_b Guru
    Currently Being Moderated

    Ok,

     

    But it is the root cause of the full table scan you see.

    You have implicit conversion

    Implicit conversion is always

    <conversion function>(<indexed column>) = value

     

    As the result of the conversion function is unknown, both RBO and CBO will not use the index, and go for a full table scan.

     

    If you don't want to remove this *bug* in your application, in 10g and higher you can put a *function based index* on that column, without paying extra.

    Whether this is available in 9i as a *free* option, I don't know anymore.

     

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

    Sybrand Bakker

    Senior Oracle DBA

  • 10. Re: RBO and Index access
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    For the predicate a>0   Oracle has to read ALL the column a values that are greater than zero.  Assuming that there are no negative values, this would mean all the rows.  However, if column a is defined as CHAR, Oracle has to do datatype conversion.   It cannot use the index.

     

     

    Hemant K Chitale

  • 11. Re: RBO and Index access
    Sekar_BLUE4EVER Newbie
    Currently Being Moderated

    Hi Sybrand,

       Thanks for the help.Yes implicit datatype conversion is going on in the query and I feel that is why the index is not getting used .However in the same database for other tables which have the same implicit datatype conversion it is picking up the index.This is what is confusing me.

    This is happening in a 10g database with CBO.

    Please look at the below sql it is using the index even though implicit data conversion is happening.

     

    SELECT /*+ gather_plan_statistics index_asc (XXX XXXPK) */ R_NO
          FROM inct               WHERE I_NO = 003                AND
    A_NO = 3116882000000028                AND  R_NO  > 0
    Plan hash value: 2978222010
    ------------------------------------------------------------------------------------
    | Id  | Operation       | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ------------------------------------------------------------------------------------
    |*  1 |  INDEX FULL SCAN| XXXPK |      1 |      1 |     48 |00:00:00.23 |    1299 |
    ------------------------------------------------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter((TO_NUMBER("A_NO")=3116882000000028 AND
                  TO_NUMBER("R_NO")>0 AND TO_NUMBER("I_NO")=3))
    20 rows selected.
    

     

     

    This is the sql I am having trouble with

     

    SQL_ID  2ta5f2qg7grat, child number 0
    -------------------------------------
    select /*+ index_asc (ddd ddd_ind) */ * from ddd where id=2000
    Plan hash value: 632076411
    -------------------------------------------
    | Id  | Operation         | Name | E-Rows |
    -------------------------------------------
    |*  1 |  TABLE ACCESS FULL| DDD  |      1 |
    -------------------------------------------
    Predicate Information (identified by operation id):
    ---------------------------------------------------
       1 - filter(TO_NUMBER("ID")=2000)
    

     

    I went through an article in ASK TOM and enabled the 10053 trace for the session and the optimizer is not even considering the index.

     

    Here is the extract from trace file

     

    select /*+ index_asc (a ddd_ind) */ * from sekar.ddd a where id=2000
    *********************
    QUERY BLOCK SIGNATURE
    *********************
    qb name was generated
    signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
      fro(0): flg=0 objn=59517 hint_alias="A"@"SEL$1"
    *****************************
    SYSTEM STATISTICS INFORMATION
    *****************************
      Using NOWORKLOAD Stats
      CPUSPEED: 865 millions instruction/sec
      IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
      IOSEEKTIM: 10 milliseconds (default is 10)
    ***************************************
    BASE STATISTICAL INFORMATION
    ***********************
    Table Stats::
      Table: DDD  Alias:  A
        #Rows: 111916  #Blks:  1022  AvgRowLen:  56.00
    Index Stats::
      Index: DDD_IND  Col#: 14
        LVLS: 1  #LB: 141  #DK: 55958  LB/K: 1.00  DB/K: 1.00  CLUF: 10776.00
        User hint to use this index
    ***************************************
    SINGLE TABLE ACCESS PATH
      Column (#14): ID(CHARACTER)
        AvgLen: 5.00 NDV: 56096 Nulls: 56108 Density: 1.7827e-05
      Table: DDD  Alias: A
        Card: Original: 111916  Rounded: 1  Computed: 0.99  Non Adjusted: 0.99
      Access Path: TableScan
        Cost:  231.74  Resp: 231.74  Degree: 0
          Cost_io: 225.00  Cost_cpu: 69951072
          Resp_io: 225.00  Resp_cpu: 69951072
      Best:: AccessPath: TableScan
             Cost: 231.74  Degree: 1  Resp: 231.74  Card: 0.99  Bytes: 0
    

     

    Why is the optimizer behaving differently in case of these two queries.

  • 12. Re: RBO and Index access
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    Are both queries in the same 10g database ?

     

    What are the definitions of the two indexes (XXXPK and DDD_IND) ?

     

     

    Hemant K Chitale


  • 13. Re: RBO and Index access
    Sekar_BLUE4EVER Newbie
    Currently Being Moderated

    Hi here is the ddl

     

    CREATE UNIQUE INDEX "XXXXX"."XXXPK" ON "xxxxx"."XXX" ("I_NO", "A_N O", "R_NO")   PCTFREE 5 INITRANS 5 MAXTRANS 255 COMPUTE STATISTICS   STORAGE(INITIAL 23068672 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)   TABLESPACE "TSIDX"

     

     

    CREATE INDEX "SEKAR"."DDD_IND" ON "SEKAR"."DDD" ("ID")   PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)   TABLESPACE "USERS"

     

    If you wanted to check if either index is made with to_number ,no its not.However i tried creating a index with to_number and it worked.

    Now what I am confused about is how can the other query use the index even though implicit datatype conversion is taking place.

     

    Thanks

    sekar

  • 14. Re: RBO and Index access
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    Are the two tables (XXX and DDD) in the same database ?  You are querying two different schemas -- are optimizer parameters the same in the two different user sessions (if using different users) ?  Do you have a 10053 trace for the first query ?

     

    Hemant K Chitale

1 2 Previous Next

Legend

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