OraFAQ Forum: Performance Tuning » Force Index Unique Scan

Home » RDBMS Server » Performance Tuning » Force Index Unique Scan (Oracle 10g 10.2.0.4 HP-UX)

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

Force Index Unique Scan [message #557670]

Thu, 14 June 2012 12:42

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Hi,

Is there any hint to force "Index Unique Scan" over "Index Range Scan".

My query is generating different plans with the above two, and very slow when it uses "Index Range Scan".
Can you suggest me any hint to do so?

Thanks,
Manu

Report message to a moderator

Re: Force Index Unique Scan [message #557671 is a reply to message #557670]

Thu, 14 June 2012 12:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

It is unwise to use HINT in production code.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:470638000346821096

Report message to a moderator

Re: Force Index Unique Scan [message #557674 is a reply to message #557671]

Thu, 14 June 2012 12:54

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

OK.

I don't have option of SQL Stored outlines, or profiles to bind the execution plan with my query, as its dyanamically generated query.

I have just asked if any such hint exist (how I am using it upto me), can you tell me what I am not following in the first link provided by you?

Thanks,
Manu

Report message to a moderator

Re: Force Index Unique Scan [message #557675 is a reply to message #557674]

Thu, 14 June 2012 13:01

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

How many problematic sql's do you have? Are your optimizer stattistics up-to-date and accurate?

Report message to a moderator

Re: Force Index Unique Scan [message #557676 is a reply to message #557675]

Thu, 14 June 2012 13:06

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Hi LNossov,

First Answer : I have many problematic sql's.

Second Answer: I don't know what are optimizer statistics, but all my table and index statistics are up to date.

Thanks,
Manu

Report message to a moderator

Re: Force Index Unique Scan [message #557677 is a reply to message #557676]

Thu, 14 June 2012 13:16

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Hell Manu

if you have a lot of sql's, that do index range instead of unique index scan, then you indeed cannot fix execution plans via stored outlines. Could you please send one problematic sql with execution plan statistics. You can upload sql trace for that. Or you can do the following:

1. make the following settings

Code: [Select all] [Show/ hide]

set linesize 1000
set pagesize 1000
set trimspool on
set trim on

2. then run

Code: [Select all] [Show/ hide]

alter session set statistics_level=all;

3. then run your sql,
4. after that run the following select:

Code: [Select all] [Show/ hide]

select plan_table_output from table ( sys.dbms_xplan.display_cursor( '','','ADVANCED ALLSTATS LAST'));

5. upload the last formatted output.

Regards
Leonid

Report message to a moderator

Re: Force Index Unique Scan [message #557680 is a reply to message #557677]

Thu, 14 June 2012 13:47

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Ahhh, I said - "I have lot of problematic SQL's", but all don't have the same problem.

I have only 1 SQL with this problem, I have achieved Index Unique Scan, but I have achieved it through Query re-write, and don't know, when CBO opt for which execution plan next time for the same query. So seeking for unique index hint, but seems its not available. Sad

Regards,
Manu

Report message to a moderator

Re: Force Index Unique Scan [message #557693 is a reply to message #557680]

Thu, 14 June 2012 16:03

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

And if you use simply the hint index(<table alias> <unique index name>)? What happens?

Report message to a moderator

Re: Force Index Unique Scan [message #557747 is a reply to message #557693]

Fri, 15 June 2012 07:29

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Its using that index, but access path is different.

Its using unique index, say "INDEX02", but the access path is range scan rather than unqiue index scan.

Regards,
Manu

Report message to a moderator

Re: Force Index Unique Scan [message #557749 is a reply to message #557747]

Fri, 15 June 2012 07:59

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

Senior Member
Account Moderator

Is the index UNIQUE?

Regards
Michel

Report message to a moderator

Re: Force Index Unique Scan [message #557751 is a reply to message #557670]

Fri, 15 June 2012 08:10

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

YES, also its a composite index having 4 columns and we are refering all 4 columns with equality condition.

[Updated on: Fri, 15 June 2012 08:11]

Report message to a moderator

Re: Force Index Unique Scan [message #557754 is a reply to message #557751]

Fri, 15 June 2012 08:46

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Could you please upload 2 explain plans for index range and for unique scans (after your correction).

Report message to a moderator

Re: Force Index Unique Scan [message #557758 is a reply to message #557751]

Fri, 15 June 2012 09:00

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

Senior Member
Account Moderator

manubatham20 wrote on Fri, 15 June 2012 15:10

YES, also its a composite index having 4 columns and we are refering all 4 columns with equality condition.

Prove it!

Regards
Michel

Report message to a moderator

Re: Force Index Unique Scan [message #557824 is a reply to message #557758]

Sat, 16 June 2012 00:30

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Hi Michel,

Do you want me to post both the execution plans?

I am not able to see any other way to prove it, could you please suggest.

Regards,
Manu

Report message to a moderator

Re: Force Index Unique Scan [message #557828 is a reply to message #557824]

Sat, 16 June 2012 00:53

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

Senior Member
Account Moderator

How did you see the index is unique and it has 4 columns (in the database where index unique scan is not taken)?

Regards
Michel

Report message to a moderator

Re: Force Index Unique Scan [message #557835 is a reply to message #557828]

Sat, 16 June 2012 02:55

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Hi Michel,

I got it through querying dba_indexes, dba_ind_columns data dictionary tables that type of index is unique and has 4 columns.

While when I am seeing the execution plan, its doing range scan some time, some time unique scan, some time full scan, many times join order is not correct, used merge join cartension on very large tables. Overall, execution plan changes almost everytime even for the same inputs. I got fed up with this absurd behavior of CBO. Let me get back to the ofc on Monday, and I will post different execution plans I got even though stats are up to date.

Thanks,
Manu

[Updated on: Sat, 16 June 2012 02:57]

Report message to a moderator

Re: Force Index Unique Scan [message #557845 is a reply to message #557835]

Sat, 16 June 2012 03:55

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

Senior Member
Account Moderator

Quote:

I got it through querying dba_indexes, dba_ind_columns data dictionary tables that type of index is unique and has 4 columns.

Good, "prove it" means copy and paste the queries and results.
Generally do not just explain what you do and get, show us.

Regards
Michel

Report message to a moderator

Re: Force Index Unique Scan [message #557989 is a reply to message #557845]

Mon, 18 June 2012 08:10

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

IF you have a unique index with 4 columns
AND you are using all of those columns in equality conditions
AND all of those equality conditions are using constants or bind variables (not joins to other tables)
THEN I predict you are casting the 2nd, 3rd, or 4th by comparing it to a numeric bind variable or constant.

But my guess is that one or more of those equality conditions are join conditions, and the other table(s) in the join are joined after the table you are looking at.

Ross Leishman

Report message to a moderator

Re: Force Index Unique Scan [message #559952 is a reply to message #557989]

Sun, 08 July 2012 02:19

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Hmmm, your guess is right rleishman, 2 columns are compared with constants and other 2 are used in join condition. I tried force unique index scan through index(table_alias index (col1 col2 col3 col4)) hint, but I don't know, why CBO is not going for unique index scan.

Please see my query here:

http://www.orafaq.com/forum/mv/msg/177601/537105/156155/#msg_537105

Finally I come to the following hint, that gives me some performance improvement:

/*+ leading ( tvd vd1 vd2 vd3 tvr ) no_use_merge ( tvd vd1 vd2 vd3 tvr ) index( vd1 VENDOR_DATA_IDX04 ) index( vd2 VENDOR_DATA_IDX04 ) index( vd3 VENDOR_DATA_IDX04 ) no_merge(tvd) */

But its still using index range scan. Sad

Thanks.

Report message to a moderator

Re: Force Index Unique Scan [message #559989 is a reply to message #559952]

Sun, 08 July 2012 20:32

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Please post both the current SQL and the Explain Plan and identify the index you think should be performing a Unique scan.

Ross Leishman

Report message to a moderator

Previous Topic:	where fiter result rows save before join and grop by operation?
Next Topic:	DB time in AWR Report - (CPU time,I/O and waits)?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jun 16 18:23:40 CDT 2025