CODEWITHSUNDEEP
sqldatabaseoracle-databaseindexingsqlplus
Moeb
Moeb

Reputation: 10861

How can I see the contents of an Oracle index?

Is it possible to have a look at what is there inside an index using SQL*Plus?

If I have a table like this:

Table A
------------------------
rowid  |  id   name
123    |  1    A
124    |  4    G
125    |  2    R
126    |  3    P

where id is the primary key, I expect the index to be something like this

index on A.id
-------------
id  rowid
1   123
2   125
3   126
4   124

Is there some SQL query using which I can actually see the contents of an index?

Upvotes: 2

Views: 11957

Answers (2)

HankerPL
HankerPL

Reputation: 21

If you are indexing EMP_ID - so all values from that particular column are indexed as leafs - e.g. B-Tree ASC index - on left site of the leafs part you should see MIN(indexed_value) or right site you should see MAX(indexed_value) - for DESC in opposite. But if you are asking for BRANCHES/ROOT values I can't help you - but in general it is not that important. explain plan for:

select **min**(emp_id) 
from emp
where emp_id < 100;

select **max**(emp_id) 
from emp
where emp_id < 100;

and compare it to each other (CARDINALITY 1) - it read only one row from object IND_EMP_ID

and then explain plan for:
select **min**(employee_id), **max**(employee_id) 
from employees 
where employee_id < 100;

--CARDINALITY 40 - between min and max only 40 different values in index are placed - all values from column emp_id.

Do not forget that even for PK you can still using B-TREE/BITMAP INDEX UNIQUE or NON-UNIQUE.

Upvotes: 0

Adam Musch
Adam Musch

Reputation: 13583

There's not a simple query - you can dump table or index blocks to trace files, but you have to identify the block you're interested in. Dion Cho has an example of how you can process this block dump with SQL, but it's not for the faint of heart.

However, you can do this:

select /* index_ffs (a [name of index]) */
       id, rowid 
  from a
 where id is not null
 order by id, rowid;

Oracle doesn't write index entries where all of the values being indexed are null, so if id is nullable, we need to filter those out. The index_ffs hint forces Oracle to satisfy the query reading the index blocks, not the data blocks. This "solution" doesn't show the data which would be in root or branch blocks, whereas a block dump can.

Upvotes: 4

Related Questions