Home » RDBMS Server » Performance Tuning » Index skip scan (oracle,11.2.0.2,aix6.1)
Index skip scan [message #554922] Sat, 19 May 2012 09:40 Go to next message
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 #554923 is a reply to message #554922] Sat, 19 May 2012 09:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post EXPLAIN PLAN for both SQL statements
Re: Index skip scan [message #554929 is a reply to message #554922] Sat, 19 May 2012 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Plese suggest which will be the better query as per the performace point of view


The best query from a performances point of view is "select 1 from dual;"

Regards
Michel
Re: Index skip scan [message #554930 is a reply to message #554929] Sat, 19 May 2012 11:25 Go to previous messageGo to next message
chandusatyam
Messages: 2
Registered: July 2011
Junior Member
Thank you Michel. I want the better query from the below 2 queries with respect to performance.
Please consider that I have unique index on col1 and col2 of EMP table
select * from emp where col1=:c1 and col2=:c2; 
select * from emp where col1=:c1;
Re: Index skip scan [message #554934 is a reply to message #554930] Sat, 19 May 2012 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post EXPLAIN PLAN for both SQL statements
Re: Index skip scan [message #554936 is a reply to message #554930] Sat, 19 May 2012 11:40 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
chandusatyam wrote on Sat, 19 May 2012 18:25
Thank you Michel. I want the better query from the below 2 queries with respect to performance.
Please consider that I have unique index on col1 and col2 of EMP table
select * from emp where col1=:c1 and col2=:c2; 
select * from emp where col1=:c1;


Is it better to use a boat or a car?
These queries do not answer the same question so it is meaningless (and silly) to compare them from a performance point of view.

Regards
Michel
Re: Index skip scan [message #555172 is a reply to message #554936] Mon, 21 May 2012 22:24 Go to previous messageGo to next message
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 #555228 is a reply to message #554922] Tue, 22 May 2012 09:40 Go to previous messageGo to next message
vijenderkdba
Messages: 28
Registered: May 2012
Location: mumbai
Junior Member
say for example you have same index I1 on both the columns like C1,C2.but your select only one column C1,in this case SKIP INDEX scan gives
better performance.If you select both the columns C1,C2 then index scan will gives the better performance.

Thanks,
Vijender
Re: Index skip scan [message #555273 is a reply to message #555228] Tue, 22 May 2012 16:30 Go to previous message
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
Previous Topic: Tune data coming from legacy system into Oracle DB
Next Topic: BULK COLLECT issue
Goto Forum:
  


Current Time: Thu Sep 16 20:36:18 CDT 2021