Indexes are used to fasten access to the record in Oracle. Optimizer chooses an index over a full table scan based on the Index statistics. The statistics allow the optimizer to calculate the effectiveness of using one index over another.
Let’s check out how to find the index statistics
How to check Index statistics for a table
SELECT index_name, index_type, uniqueness, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, clustering_factor, num_rows, sample_size, last_analyzed, partitioned FROM dba_indexes WHERE table_name = '&table_name' and table_owner='&table_owner' ;
Here
blevel is the depth of the index from the root block to the leaf block
leaf_blocks is the number of leaf blocks
distinct_keys is the number of distinct index values
AVG_LEAF_BLOCKS_PER_KEY statistic is the average number of leaf blocks in which
each distinct value in the index appears, rounded to the nearest integer. For indexes that
enforce UNIQUE and PRIMARY KEY constraints, this value is always 1.
AVG_DATA_BLOCKS_PER_KEY statistic is the average number of data blocks in the table
that is pointed to by a distinct value in the index rounded to the nearest integer. This statistic
is the average number of data blocks that contain rows that contain a given value for the
indexed columns.
clustering_factor tells us about how much the table rows are ordered as per index value
we can get the same data through dba_ind_statistics view also
SELECT index_name, index_type, uniqueness, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, clustering_factor, num_rows, sample_size, last_analyzed, partitioned FROM dba_ind_statistics WHERE table_name = '&table_name' and table_owner='&table_owner' ;
How to check Index statistics for an index
SELECT index_type, uniqueness, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, clustering_factor, num_rows, sample_size, last_analyzed, partitioned FROM dba_indexes WHERE index_name = '&index_name' and owner='&index_owner' ;
How to check Index statistics together will column name for a table
set linesize 200
set pages 250
set verify off
col blevel format 99
col table_name format a22 heading 'TABLE NAME'
col u format a1 heading 'U'
col index_name format a25 heading 'INDEX NAME'
col column_name format a23 heading 'COLUMN NAME'
col column_position format 99 heading 'SEQ'
col column_length format 9999 heading 'LEN'
col leaf_blocks format 999990 heading 'LEAF|BLOCKS'
col distinct_keys format 9999990 heading 'DISTINCT|KEYS'
col avg_leaf_blocks_per_key format 999990 heading 'LEAF|BLKS|/KEY'
col avg_data_blocks_per_key format 999990 heading 'DATA|BLKS|/KEY'
select i.table_name,i.blevel, i.leaf_blocks, i.distinct_keys,i.avg_leaf_blocks_per_key, i.avg_data_blocks_per_key,
decode( i.uniqueness, 'NONUNIQUE', null, 'UNIQUE', 'U', 'BITMAP', 'B', '?' ) u,
i.index_name,i.last_analyzed, c.column_position, c.column_name, c.column_length
from sys.dba_ind_columns c, sys.dba_indexes i
where (i.table_owner,i.table_name) in ('&1','&2')
and i.owner = c.index_owner
and i.index_name = c.index_name
order by i.table_owner, i.table_name, i.index_name, c.column_position
/
How to check detailed index statistics for the index used in sql hash value
set linesize 200
set pages 250
set verify off
col blevel format 99
col table_name format a22 heading 'TABLE NAME'
col u format a1 heading 'U'
col index_name format a25 heading 'INDEX NAME'
col column_name format a23 heading 'COLUMN NAME'
col column_position format 99 heading 'SEQ'
col column_length format 9999 heading 'LEN'
col leaf_blocks format 999990 heading 'LEAF|BLOCKS'
col distinct_keys format 9999990 heading 'DISTINCT|KEYS'
col avg_leaf_blocks_per_key format 999990 heading 'LEAF|BLKS|/KEY'
col avg_data_blocks_per_key format 999990 heading 'DATA|BLKS|/KEY'
rem
break on table_name skip 1 on index_name on u
rem
select i.table_name,i.blevel, i.leaf_blocks, i.distinct_keys,i.avg_leaf_blocks_per_key, i.avg_data_blocks_per_key,
decode( i.uniqueness, 'NONUNIQUE', null, 'UNIQUE', 'U', 'BITMAP', 'B', '?' ) u,
i.index_name,i.last_analyzed, c.column_position, c.column_name, c.column_length
from sys.dba_ind_columns c, sys.dba_indexes i
where (i.table_owner,i.table_name) in (select OBJECT_OWNER,OBJECT_NAME from V$SQL_PLAN where HASH_VALUE= &1)
and i.owner = c.index_owner
and i.index_name = c.index_name
order by i.table_owner, i.table_name, i.index_name, c.column_position
/
I have given the dba_indexes example here but we can use any of these views
DBA_INDEXES
USER_INDEXES
ALL_INDEXES
we can now view CDB_INDEXES starting with 12c with the container database. In the root of a multitenant container database (CDB), CDB_* views can be used to obtain information about tables
I hope you like this article on How to check Index statistics in Oracle,How to check detailed index statistics for the index used in sql hash value. Please do provide the feedback
Related Articles
how to check last gather stats on table in Oracle : Check out this post on How to check gather stats on a table,how to check last gather stats on the table in oracle
How to check column statistics in Oracle
Optimizer statistics preferences in Oracle
Histograms in Oracle