| Index skip scan [message #554922] |
Sat, 19 May 2012 09:40  |
 |
chandusatyam
Messages: 2
Registered: July 2011
|
Junior Member |
|
|
Hi Leaders,
I am working on oracle 11.2.0.2 version. Can you please correct on the below?
Is the Index skip scan is faster than the Index unique scan or
Is the Index unique scan is faster than the Index skip scan ? Below example is just for reference.
Assume, I have a table EMP and we have a unique index on col1, col2.
If I perform the query select * from emp where col1=:c1 and col2=:c2; , Index Unique scan is happening.
and If I perform the query select * from emp where col1=:c1; , Index Skip scan is happening
and both will result the same number of rows.
Plese suggest which will be the better query as per the performace point of view. Thank you in advance.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Index skip scan [message #555172 is a reply to message #554936] |
Mon, 21 May 2012 22:24  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Here are some notes on Index Skip Scan that may help.
Generally speaking, a Skip Scan is going to be slower than a Range-Scan or Unique-Scan ON THE SAME COLUMNS. How much slower will depend on the number of distinct values of the 1st column in the index. Read the link for more information.
eg.
select * from emp where col1=:c1 and col2=:c2;
A range-scan on index (COL2) will be better than a skip-scan on index (COL3, COL2). However a range/unique scan on index (COL1, COL2) would be faster still.
The only exceptions to this rule that I can think of are:
- If the index on (COL2) was highly fragmented, it could under-perform.
- If the index on (COL2) contained other columns not referenced by the query [eg. (COL2, COL4, COL5, COL6, COL7)] then it could increase IO to the point that it was slower than a Skip-Scan on an index without the junk columns, such as (COL3, COL2)
Ross Leishman
|
|
|
|
|
|
| Re: Index skip scan [message #555273 is a reply to message #555228] |
Tue, 22 May 2012 16:30 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If you have an index I1 on (C1, C2) in that order, then
- C2 = <value> could give a SKIP SCAN on I1
- C1 = <value> should give a RANGE SCAN on I1
- C1 = <value> AND C2 = <value> should also give a RANGE SCAN on I1 or - if the index is unique - a UNIQUE SCAN
The 3rd option would certainly be fastest
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
