Home » RDBMS Server » Performance Tuning » The predicate prevents the optimizer from using indices efficiently (12.2)
The predicate prevents the optimizer from using indices efficiently [message #684678] Fri, 23 July 2021 11:21 Go to next message
lisyloo12
Messages: 1
Registered: July 2021
Junior Member
Hi,

We have a badly performing query (sometimes), picking a bad plan index for a query with high cardinality variable value.
I tried tuning advisor but it simply advises a restructure with the advice in the title.

However we reckon the issue is not in that part of the query for example

WHERE variable = <value> # issue is here
and DECODE (blah, blah) = DECODE (blah, blah) # tuning advisor is fixated on this

has anyone come across this issue?

Thanks

Report message to a moderator

Re: The predicate prevents the optimizer from using indices efficiently [message #684679 is a reply to message #684678] Fri, 23 July 2021 13:04 Go to previous message
John Watson
Messages: 8960
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

You need to show the query and the index structure before anyone can advise. All I can say at the moment is that wrapping columns in functions (which seems to be what you are doing) will usually make indexed access paths impossible, which is more-or-less what the advisor is telling you.

Report message to a moderator

Previous Topic: SQL Access Advisor from SQL*Plus
Next Topic: Slow Archive Apply in Physical Standby
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Nov 21 07:15:17 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.