OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Index skip scan (oracle,11.2.0.2,aix6.1)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Re: Index skip scan [message #554923 is a reply to message #554922]

Sat, 19 May 2012 09:49

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post EXPLAIN PLAN for both SQL statements

Report message to a moderator

Re: Index skip scan [message #554929 is a reply to message #554922]

Sat, 19 May 2012 11:08

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Index skip scan [message #554930 is a reply to message #554929]

Sat, 19 May 2012 11:25

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

Code: [Select all] [Show/ hide]

select * from emp where col1=:c1 and col2=:c2; 
select * from emp where col1=:c1;

Report message to a moderator

Re: Index skip scan [message #554934 is a reply to message #554930]

Sat, 19 May 2012 11:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

post EXPLAIN PLAN for both SQL statements

Report message to a moderator

Re: Index skip scan [message #554936 is a reply to message #554930]

Sat, 19 May 2012 11:40

Michel Cadot
Messages: 68757
Registered: March 2007
Location: Saint-Maur, France, https...

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
Code: [Select all] [Show/ hide]
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

Report message to a moderator

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.

Code: [Select all] [Show/ hide]

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

Report message to a moderator

Re: Index skip scan [message #555228 is a reply to message #554922]

Tue, 22 May 2012 09:40

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

Report message to a moderator

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

Report message to a moderator

Previous Topic:	Tune data coming from legacy system into Oracle DB
Next Topic:	BULK COLLECT issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 29 05:52:46 CDT 2025