Oracle Scratchpad

Index Definitions

Here’s a quck and dirty I use occasionally to check the definitions and statistics of all the indexes on a single table. I’ve probably left a couple of hundred versions of it scattered around the world including or excluding various bits of information – I know that I don’t often include dba_ind_expressions.

You might want to put in some of your own formatting commands, and write versions that cater for partitions and sub-partitions. (If you want it all in one, a simple UNION ALL approach is the easiest thing to do).

The code picks out a few things like uniqueness of index, nullability of columns, descending columns in indexes, and various index and column stats. It’s really aimed at B-tree indexes – IOTs and bitmap indexes merit a slightly different piece of SQL:

set pagesize 60
set linesize 180

column index_name  format a20
column column_name format a20
column func        format a24

break   on index_name skip 1 on uniqueness on pref_len -
        on t_rows on i_rows on distinct_keys on blevel on leaf_blocks

select
        ind.index_name,
        ind.uniqueness,
        ind.prefix_length       pref_len,
        tab.num_rows            t_rows,
        ind.num_rows            i_rows,
        ind.distinct_keys,
        ind.blevel,
        ind.leaf_blocks,
        inc.column_name,
        case inc.descend
            when 'DESC'
                then 'D'
                else null
        end                     descend,
        tbc.nullable,
        tbc.num_distinct,
        tbc.num_nulls,
        inx.column_expression	func
from
        dba_tables          tab,
        dba_indexes         ind,
        dba_ind_columns     inc,
        dba_tab_cols        tbc,
        dba_ind_expressions inx
where
        tab.owner        = 'XXX'
and     tab.table_name   = 'YYYYYYYY'
/*					*/
and     ind.table_owner  = tab.owner
and     ind.table_name   = tab.table_name
/*					*/
and     inc.table_owner  = ind.table_owner
and     inc.table_name   = ind.table_name
and     inc.index_owner  = ind.owner
and     inc.index_name   = ind.index_name
/*					*/
and     tbc.owner        = inc.table_owner
and     tbc.table_name   = inc.table_name
and     tbc.column_name  = inc.column_name
/*					*/
and     inx.table_owner(+)     = inc.table_owner
and     inx.table_name(+)      = inc.table_name
and     inx.index_owner(+)     = inc.index_owner
and     inx.index_name(+)      = inc.index_name
and     inx.column_position(+) = inc.column_position
order by
        ind.index_name,
        inc.column_position
;

Update Feb 2010: Just as one very simple idea of what you can look for in this report – if the number of distinct keys in the index is a lot smaller than the number of rows in the index then the index is likely to be a good candidate for compression.

As a secondary check, if the number of distinct values of the first column is small relative to the number of rows, then compression on the first column might be a good option; similarly, if the product of the number of distinct values in the first “N” columns is small compared to the number of rows the index is still a good candidate for compression on “N” columns.

2 Comments »

  1. [...] usage: a script to list the extents and free space chunks in a tablespace in file and block order Index definitions: a script to describe the indexes on a single table – column and statistical [...]

    Pingback by Simple scripts « Oracle Scratchpad — February 13, 2010 @ 9:02 am BST Feb 13,2010 | Reply

  2. [...] http://jonathanlewis.wordpress.com/index-definitions/ indicates that if Distinct_Keys from DBA_Indexes is “a lot smaller” than num_rows then the index is a good candidate for compression. This helps some, but isn’t definitive and doesn’t help determine the number of columns. He does give some guidelines for that, but nothing that can be determined programatically without a bunch of dynamic SQL. This entry was posted in dba and tagged index, oracle, oracle-11g, oracle-11g-r2 by admin. Bookmark the permalink. [...]

    Pingback by Determine Index Compression Candidates Online | Q&A System — December 16, 2011 @ 2:21 pm BST Dec 16,2011 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,873 other followers