CODEWITHSUNDEEP
sqldatabaseoracle-databaseindexing
Jokke Heikkilä
Jokke Heikkilä

Reputation: 928

How to get list of values stored in index?

I'm having this issue in Oracle 11g R2. Table containing not null column which is indexed with non unique index. The index is not containing other columns.

Then I assumed that if I query distinct values of the column from the table, it would use index to get different values of the column (sounds logical to me). However at least explain plan is telling me it's doing full table scan. Also it took some time so probably the plan was not changed during run time. Optimizer index hint didn't helped.

I tried to search answer for this but no luck. Is there way to get values stored in index or somehow query the table without "touching" the table at all (like multi column index joins can)?

Thanks!

EDIT: This was about Oracle EBS gl_balances table and gl_balances_n2 index. I got answer and this changed the explain plan:

select /*+ index_ffs(gl gl_balances_n2) */
       distinct gl.period_name
  from gl_balances gl;

Upvotes: 0

Views: 568

Answers (1)

David Aldridge
David Aldridge

Reputation: 52346

It may not be more efficient to scan the index than to scan the table -- don't forget that the index segment also contains branch nodes, and each index entry has to contain a ROWID of about 16 bytes (if memory serves).

So a "fast full index scan", which is the plan you're looking to get, may not be as fast as a full table scan. (You'd use an index_ffs() hint for that, by the way.)

edit: It be possible to use a more exotic method

  • Maintaining your own list by periodically querying the table using DBMS_Scheduler.
  • A materialized view. Complete refresh on demand might be adequate, though barely better than just periodically querying the data and maintaining your own unique list.
  • Making the index compressed, though that would only be of value for longish index keys.
  • A bitmap index -- not for a concurrently modified table though.

Upvotes: 2

Related Questions