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

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

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

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

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

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

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

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