Home » RDBMS Server » Performance Tuning » Force Index Unique Scan (Oracle 10g 10.2.0.4 HP-UX)
Force Index Unique Scan [message #557670] Thu, 14 June 2012 12:42 Go to next message
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
Re: Force Index Unique Scan [message #557671 is a reply to message #557670] Thu, 14 June 2012 12:45 Go to previous messageGo to next message
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
Re: Force Index Unique Scan [message #557674 is a reply to message #557671] Thu, 14 June 2012 12:54 Go to previous messageGo to next message
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

Re: Force Index Unique Scan [message #557675 is a reply to message #557674] Thu, 14 June 2012 13:01 Go to previous messageGo to next message
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?
Re: Force Index Unique Scan [message #557676 is a reply to message #557675] Thu, 14 June 2012 13:06 Go to previous messageGo to next message
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
Re: Force Index Unique Scan [message #557677 is a reply to message #557676] Thu, 14 June 2012 13:16 Go to previous messageGo to next message
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

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


2. then run

alter session set statistics_level=all;


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

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


5. upload the last formatted output.

Regards
Leonid
Re: Force Index Unique Scan [message #557680 is a reply to message #557677] Thu, 14 June 2012 13:47 Go to previous messageGo to next message
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
Re: Force Index Unique Scan [message #557693 is a reply to message #557680] Thu, 14 June 2012 16:03 Go to previous messageGo to next message
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?
Re: Force Index Unique Scan [message #557747 is a reply to message #557693] Fri, 15 June 2012 07:29 Go to previous messageGo to next message
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
Re: Force Index Unique Scan [message #557749 is a reply to message #557747] Fri, 15 June 2012 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is the index UNIQUE?

Regards
Michel
Re: Force Index Unique Scan [message #557751 is a reply to message #557670] Fri, 15 June 2012 08:10 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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).
Re: Force Index Unique Scan [message #557758 is a reply to message #557751] Fri, 15 June 2012 09:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Force Index Unique Scan [message #557824 is a reply to message #557758] Sat, 16 June 2012 00:30 Go to previous messageGo to next message
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
Re: Force Index Unique Scan [message #557828 is a reply to message #557824] Sat, 16 June 2012 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Force Index Unique Scan [message #557835 is a reply to message #557828] Sat, 16 June 2012 02:55 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Force Index Unique Scan [message #557989 is a reply to message #557845] Mon, 18 June 2012 08:10 Go to previous messageGo to next message
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
Re: Force Index Unique Scan [message #559952 is a reply to message #557989] Sun, 08 July 2012 02:19 Go to previous messageGo to next message
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.
Re: Force Index Unique Scan [message #559989 is a reply to message #559952] Sun, 08 July 2012 20:32 Go to previous message
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
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:
  


Current Time: Thu Mar 28 11:46:32 CDT 2024