7 Replies Latest reply: Apr 1, 2013 5:26 AM by Cherif bh RSS

    INDEX_RS_ASC vs INDEX

    Cherif bh
      Hi all,


      I need to avoid the hint INDEX_RS_ASC, I am using the hint index but when I check the hint INDEX_RS_ASC is used instead of hint INDEx.

      Could you please have any idea about this behavior?

      Thanks
        • 1. Re: INDEX_RS_ASC vs INDEX
          995200
          Hi,

          INDEX_RS_ASC means Optimizer is using index and access method its using is Range scan. Since we have specified in the hint that, we want index to be used for the index and optimizer is actually using the index...Now for accessing the Index, either Index will be full scanned or it can be range scanned...So its optimizer decision to pick the type of access method which in your case is Range scan. From Oracle 10gr2 (and i guess 10.2.0.3), there were two new hints introduced INDEX_RS_ASC and INDEX_RS_DESC.

          And if we dont specify the access path like you did by specifying that index should be used, internally oracle will call those new hints.

          Thanks,
          Navneet
          • 2. Re: INDEX_RS_ASC vs INDEX
            Jonathan Lewis
            BIJGA wrote:

            I need to avoid the hint INDEX_RS_ASC, I am using the hint index but when I check the hint INDEX_RS_ASC is used instead of hint INDEx.
            Could you explain why you need to avoid the index_rs_asc hint ?

            Regards
            Jonathan Lewis
            • 3. Re: INDEX_RS_ASC vs INDEX
              Cherif bh
              Hi Jonathan,

              I am working on a query having elapsed time per execution 0,3 seconds. I need to improve performance. I have added a hint index on one table. When I have checked that Oracle do INDEX_RS_ASC instead of index access.

              I need to test the impact if I avoid the INDEX_RS_ASC.
              I thought it might improve performance. if I understand this hint requires access to sorted data therefore a sort should be done.


              Thanks,
              Cherif
              • 4. Re: INDEX_RS_ASC vs INDEX
                Hemant K Chitale
                Data returned by an Index RangeScan is in Ascending order. The Hint does not require a sort to be done.

                Clarification : The "Data" in the above sentence relates to the intermediate result set from this step in an execution plan. The ordering of the final result set is still reliant on the proper use of the ORDER BY clause.


                Hemant K Chitale

                Edited by: Hemant K Chitale on Mar 26, 2013 5:25 PM
                Added Clarification
                • 5. Re: INDEX_RS_ASC vs INDEX
                  Jonathan Lewis
                  BIJGA wrote:
                  Hi Jonathan,

                  I am working on a query having elapsed time per execution 0,3 seconds. I need to improve performance. I have added a hint index on one table. When I have checked that Oracle do INDEX_RS_ASC instead of index access.
                  Does this mean that you have seen the INDEX() hint that you supplied turn into an INDEX_RS_ASC() hint in the outline generated by the full execution plan ?

                  If you didn't want an index range scan ascending, what did you want Oracle to do when you gave it the index hint - the only options the optimizer has in response to that hint (when it's legal at all) are ascending range or ascending full scan. (Technically, I suppose, it might use your hint to drive an index join or index combine (bitmap conversion), but I'd have to run up a couple of tests to check if that were the case.)

                  Regards
                  Jonathan Lewis
                  • 6. Re: INDEX_RS_ASC vs INDEX
                    Cherif bh
                    >
                    > >
                    Does this mean that you have seen the INDEX() hint that you supplied turn into an INDEX_RS_ASC() hint in the outline generated by the full execution plan ?

                    Regards
                    Jonathan Lewis
                    Hi Jonathan,
                    Yes, I get it from outline generated by the full execution plan.

                    Thanks for your clarification.

                    Edited by: BIJGA on Mar 26, 2013 10:20 AM
                    • 7. Re: INDEX_RS_ASC vs INDEX
                      Cherif bh
                      Hi Hermant, Jonathan,

                      what this difference between these two hints ?

                      Below an example where I can the two hints :

                      -------------------------------------------------------------------------------------------------------
                      | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
                      -------------------------------------------------------------------------------------------------------
                      .........
                      | 27 | NESTED LOOPS ANTI | | 1 | 52 | 1 (0)| 00:00:01 |
                      | 28 | NESTED LOOPS | | 1 | 41 | 1 (0)| 00:00:01 |
                      | 29 | INDEX UNIQUE SCAN | IND_TABLE2 | 1 | 5 | 0 (0)| |
                      | 30 | TABLE ACCESS BY INDEX ROWID| TABLE1 | 1 | 36 | 1 (0)| 00:00:01 |
                      | 31 | INDEX RANGE SCAN | IND_TABLE1 | 1 | | 0 (0)| |
                      | 32 | INDEX UNIQUE SCAN | IND_TABLE1 | 1 | 11 | 0 (0)| |
                      .........

                      Outline Data

                      /*+
                      INDEX_RS_ASC(@"SEL$42283777" "X"@"SEL$14" ("TABLE1"."COL1" "TABLE1"."COL2"))
                      INDEX (@"SEL$42283777" "U"@"SEL$15" ("TABLE1"."COL1" "TABLE1"."COL2"))

                      */


                      Thanks