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
|
|
|
|
|
|
|
|
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.
Regards,
Manu
|
|
|
|
|
|
|
|
|
|
|
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 #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
|
|
|
|
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
|
|
|