SQL Performance (MOSC)

MOSC Banner

Is there a better way to generate the distinct set of values from the leading edge of a primary key

edited Sep 19, 2017 5:01AM in SQL Performance (MOSC) 3 commentsAnswered

I have a large table with a mutl-column primary key supported by a B-Tree index.  The number of distinct values in the first column is (comparatively) small,

I want to get the set of distinct values at the leading edge of that primary key.  The explain plan tells me that Oracle is going to do a HASH (Unique) of an INDEX (Fast Full Scan).

I would think Oracle would have a plan that would use the B-Tree structure to at least limit the set of leaf blocks it needs to look at, rather than having to scan the entire index.  An index skip scan seems to know how to navigate around when the leading column changes, I would expect

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