SQL Performance (MOSC)

MOSC Banner

Better explain plan but much worse performance

edited Oct 1, 2019 6:58PM in SQL Performance (MOSC) 7 commentsAnswered

I have a problem query that touches a large EBS table (fnd_attached_documents). One strategy I tried was to rebuild the N1 index which already has 3 of the 5 columns referenced in the query, to have all 5. the first indexed column is entity_name.

If I leave entity_name off the query - the optimizer cost id 59, and shows what I would expect - a skip scan on the N1 index. The query returns about 2000 rows of data for the given parameters  pretty fast - under a second.

If I then add to the where clause - and entity_name = ''MTL_SYSTEM_ITEMS' - the explain plan cost drops to 8 and shows what I would expect - a unique scan - but the query now sucks and takes over 10 seconds to return the same data.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center