Forum Stats

  • 3,741,519 Users
  • 2,248,442 Discussions
  • 7,861,847 Comments

Discussions

INDEX_RS_ASC vs INDEX

Cherif bh
Cherif bh Member Posts: 158 Bronze Badge
edited Apr 1, 2013 6:26AM in General Database Discussions
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
Tagged:

Answers

  • 995200
    995200 Member Posts: 22
    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
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,681 Gold Crown
    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
  • Cherif bh
    Cherif bh Member Posts: 158 Bronze Badge
    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
  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,755 Blue Diamond
    edited Mar 26, 2013 5:26AM
    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
    Hemant K Chitale
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,681 Gold Crown
    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
    Jonathan Lewis
  • Cherif bh
    Cherif bh Member Posts: 158 Bronze Badge
    edited Mar 26, 2013 5:20AM
    >
    > >
    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
  • Cherif bh
    Cherif bh Member Posts: 158 Bronze Badge
    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
This discussion has been closed.