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

    RBO and Index access

    Sekar_BLUE4EVER


      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

          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

            its RULE

            • 3. Re: RBO and Index access
              sb92075

              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

                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

                  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

                    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....

                      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

                        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

                          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

                            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

                              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

                                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

                                  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

                                    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