Non-Selective Index Statistics (dstatbi.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script identifies indexes with poor selectivity by analyzing the average number of leaf blocks and data blocks per key. It helps DBAs find indexes that may be causing performance problems due to low cardinality, excessive index range scans, or poor clustering factor, making them candidates for redesign or removal.

Script

rem  dstatbi.sql
rem
ttitle 'Non-selective Index Statistics'
rem
col table_name format a24 heading 'TABLE NAME'
col index_name format a23 heading 'INDEX NAME'
col u format a1 heading 'U'
col blevel format 0 heading 'BL'
col leaf_blocks format 999990 heading 'LEAF|BLOCKS'
col distinct_keys format 9999990 heading 'DISTINCT|KEYS'
col avg_leaf_blocks_per_key format 990 heading 'LEAF|BLKS|/KEY'
col avg_data_blocks_per_key format 990 heading 'DATA|BLKS|/KEY'
rem
break on table_name
rem
select table_name, index_name,
       decode( uniqueness, 'UNIQUE', 'U', null ) u,
       blevel, leaf_blocks, distinct_keys,
       avg_leaf_blocks_per_key, avg_data_blocks_per_key
  from sys.dba_indexes
 where table_owner like upper('&owner')
   and table_name like upper('&table')
   and ( avg_leaf_blocks_per_key >= 5
      or avg_data_blocks_per_key >= 100 )
 order by table_owner, table_name, index_name;

Usage

-- Run the script in SQL*Plus or SQLcl
@dstatbi.sql

-- When prompted, enter parameters:
-- owner: Schema owner (% for all)
-- table: Table name pattern (% for all)

-- Examples:
Enter value for owner: HR
Enter value for table: %

-- Check specific table
Enter value for owner: SALES
Enter value for table: ORDERS

Parameters

• &owner - Schema owner (supports wildcards)
• &table - Table name pattern

Required Privileges

• SELECT on DBA_INDEXES
• Typically requires DBA role

Sample Output

Non-selective Index Statistics

                              U    LEAF  DISTINCT LEAF DATA
TABLE NAME               INDEX NAME              BL BLOCKS     KEYS BLKS BLKS
                                                                    /KEY /KEY
------------------------ ----------------------- -- ------ -------- ---- ----
EMPLOYEES                EMP_DEPT_IDX             2   1234       12  103  856
                         EMP_STATUS_IDX           2    567        3  189  412

ORDER_ITEMS              ITEM_STATUS_IDX          3   8901        5 1780  234
                         ITEM_CATEGORY_IDX        3   4567       45  101  156

TRANSACTIONS             TRANS_TYPE_IDX           3  12345       10 1234  890
                         TRANS_FLAG_IDX           2   3456        2 1728  567

Key Output Columns

• TABLE NAME - Name of the indexed table
• INDEX NAME - Name of the index
• U - Uniqueness indicator (U=Unique, blank=Non-unique)
• BL - B-level (height of index tree)
• LEAF BLOCKS - Number of leaf blocks in index
• DISTINCT KEYS - Number of distinct values
• LEAF BLKS/KEY - Average leaf blocks per distinct key
• DATA BLKS/KEY - Average data blocks per distinct key

Understanding the Metrics

Selectivity Indicators

• High LEAF BLKS/KEY (≥5) - Many index entries per distinct value
• High DATA BLKS/KEY (≥100) - Poor clustering, data scattered
• Low DISTINCT KEYS - Low cardinality, poor selectivity

Performance Impact

• Non-selective indexes cause excessive I/O
• Range scans read many blocks for few rows
• May perform worse than full table scans
• Increases buffer cache pressure

Common Use Cases

Find Low Cardinality Indexes

SELECT table_name, index_name,
       distinct_keys,
       num_rows,
       ROUND(distinct_keys/num_rows*100, 2) selectivity_pct
FROM dba_indexes
WHERE table_owner = '&owner'
AND num_rows > 0
AND distinct_keys/num_rows < 0.01  -- Less than 1% selective
ORDER BY distinct_keys/num_rows;

Identify Clustering Issues

SELECT table_name, index_name,
       clustering_factor,
       num_rows,
       blocks
FROM dba_indexes
WHERE table_owner = '&owner'
AND clustering_factor > blocks * 10
ORDER BY clustering_factor DESC;

Check Index Efficiency

SELECT index_name,
       leaf_blocks,
       distinct_keys,
       ROUND(leaf_blocks/GREATEST(distinct_keys,1), 2) blocks_per_key
FROM dba_indexes
WHERE table_owner = '&owner'
AND leaf_blocks/GREATEST(distinct_keys,1) > 10
ORDER BY leaf_blocks/GREATEST(distinct_keys,1) DESC;

Performance Analysis

Index Usage Monitoring

-- Enable monitoring
ALTER INDEX owner.index_name MONITORING USAGE;

-- Check usage after workload
SELECT index_name, monitoring, used
FROM v$object_usage
WHERE index_name = 'INDEX_NAME';

Cost Analysis

-- Compare index scan vs full table scan cost
EXPLAIN PLAN FOR
SELECT * FROM table_name
WHERE indexed_column = 'value';

SELECT operation, options, cost, cardinality
FROM plan_table
WHERE plan_id = (SELECT MAX(plan_id) FROM plan_table);

Remediation Strategies

Improve Selectivity

-- Add columns to make index more selective
CREATE INDEX new_idx ON table_name(low_card_col, high_card_col);

-- Use function-based index for better selectivity
CREATE INDEX func_idx ON table_name(UPPER(column_name));

Handle Low Cardinality

-- Consider bitmap index for low cardinality (OLAP only)
CREATE BITMAP INDEX bitmap_idx ON table_name(status_column);

-- Or use partitioning instead
ALTER TABLE table_name PARTITION BY LIST (status_column);

Rebuild for Better Clustering

-- Rebuild index after table reorganization
ALTER INDEX owner.index_name REBUILD;

-- Rebuild with better storage parameters
ALTER INDEX owner.index_name REBUILD
TABLESPACE new_ts
PCTFREE 5;

Best Practices

Index Design

1. Avoid indexing low cardinality columns alone
2. Place selective columns first in composite indexes
3. Consider bitmap indexes for data warehouses
4. Monitor index usage before dropping

Maintenance

• Regularly gather index statistics
• Monitor clustering factor trends
• Rebuild indexes after bulk operations
• Consider invisible indexes for testing

Troubleshooting

False Positives

-- Check if statistics are stale
SELECT table_name, index_name,
       last_analyzed,
       ROUND(SYSDATE - last_analyzed) days_old
FROM dba_indexes
WHERE table_owner = '&owner'
AND last_analyzed < SYSDATE - 30;

Verify with Real Queries

-- Check actual execution plans
SELECT sql_id, child_number,
       operation, options,
       cardinality, cost
FROM v$sql_plan
WHERE object_name = 'INDEX_NAME'
AND operation LIKE '%INDEX%';

Related Scripts

• Index Column Analysis (../schema-analysis/dindcol.md) - Index composition
• Index Storage Analysis (../schema-analysis/dindex.md) - Index storage details
• Index Statistics Analysis (../schema-analysis/dindstats.md) - Comprehensive stats

Advanced Analysis

Histogram Impact

-- Check if histograms exist for indexed columns
SELECT c.table_name, c.column_name,
       c.num_distinct, c.histogram,
       i.index_name
FROM dba_tab_col_statistics c, dba_ind_columns i
WHERE c.owner = i.table_owner
AND c.table_name = i.table_name
AND c.column_name = i.column_name
AND c.owner = '&owner'
AND c.histogram != 'NONE';

Composite Index Analysis

-- Analyze multi-column index selectivity
SELECT index_name,
       column_position,
       column_name
FROM dba_ind_columns
WHERE table_owner = '&owner'
AND index_name IN (
    SELECT index_name
    FROM dba_indexes
    WHERE avg_data_blocks_per_key > 100
)
ORDER BY index_name, column_position;