More about Indexes

Introduction

Are my indexes correct? Does all the queries use indexes? Do I need to create more indexes?
These are some of the questions developers frequently ask. Creating too many indexes can slow down the OLTP applications very much. 
So it is required to decide on creating the indexes as accurately as possible. 

One option is to create as many indexes as possible and then monitor them for usage for about a week. 
All applications will be running in a weeks time. Indexes that are not used at least once can be dropped.

alter index index_name monitoring usage;
alter index index_name nomonitoring usage;

After one week check to see if the index has been used by a query

select index_name, used from v$object_usage where owner = 'SCOTT';

Access using index rowid involves two steps. First reading the index blocks and then using the rowid scan the table data. 
When more than 20% of the table data is processed, an index access looks costly compared to Full Table Scan.
If the schema is analyzed then Oracle optimizer can determine whether to use index or not.

Bitmap Indexes? 
bitmap indexes are useful when the number of distinct values is very less.

The following query can be used to get a list of indexes where the leading column has very few distinct values.

select * from( select a.index_name, 
round(b.num_distinct*1000/b.sample_size) cardinality, b.num_distinct
from user_ind_columns a, user_tab_col_statistics b
where a.table_name=b.table_name and a.column_name=b.column_name
and a.column_position=1 and nvl(b.sample_size,0)>100)
where cardinality<2 and num_distinct<6;


 Compressed Indexes

Oracle introduced a compression option for indexes in Oracle 8.1. 
You can create an index as compressed, or rebuild it to compress it.
A properly compressed index uses a smaller number of leaf blocks and less I/O and less amount of buffer cache. 
The optimizer is likely to calculate a lower cost for using that index for range scans.
Compressing indexes increase the CPU cost and compressing wrong number of columns can reduce performance. 
If you compress more columns than you should, the 'compressed' index may be larger than the uncompressed index. 
The above query can be used to calculate the cardinality and can be helpful to alter an index as compressed.

 Global Partitioned Indexes

It is possible to create Global Partitioned Indexes irrespective of whether the underlying table is partitioned or not.
Partitioning is a key tool for building extremely high availability systems involving large amount of data. 
It can provide tremendous benefits by improving manageability, performance, and availability. 
Global Partitioned Indexes can reduce the height of BTree index and hence update operations are less complex.

Please see the link for more examples

Consider the customer transaction table having 10 million rows. The column is customer_id is to be indexed. 
Partitioning the index on customer_id will result in creating smaller index segments. 
The partition boundaries have to be determined so that all partitions are equally sized.
The following script can be used to determing the partition boundaries

SQL> select * from (select customer_id, row_number() over(order by customer_id) r1 from customer) where mod(r1,1000000)=999999;
customer_id	         R1
----------------- ----------
             1005     999999
             7872    1999999
             8111    2999999
            10385    3999999
            11898    4999999
            12158    5999999
            13660    6999999
            14773    7999999
            16005    8999999

The above values can be used as partition boundaries for creating the gloabl partitioned index

create index customerid_idx ON customer(customer_id)
global partition by range(customer_id)
(
 partition p1 values less than(1005),
 partition p1 values less than(7872),
 partition p1 values less than(8111),
 partition p1 values less than(10385),
 partition p1 values less than(11898),
 partition p1 values less than(12158),
 partition p1 values less than(13660),
 partition p1 values less than(14773),
 partition p1 values less than(16005),
 partition p1 values less than(MAXVALUE));


The followinfg query can be used to verify whether the partitions are equally sized.

SQL> select partition_name, leaf_blocks from user_ind_partitions where index_name='CUSTOMERID_IDX';

 When to rebuild indexes?

When there are lots of DML operations on the table, the indexes can get fragmented. The height of the BTree index can increase.
In this situation it is required to rebuild index.
One option is to use the command "analyze index validate structure". The will create a single row in 'index_stats' view.
The index stats table can only hold one record of information at a time, therefore you will need to analyze each index individually.

 Lets do some Analysis using the customer table

SQL> select count(*) from customer;
     96546

SQL> create index customer_idx on customer(address);

Index created.

SQL> analyze index customer_idx compute statistics;

Index analyzed.

SQL> select leaf_blocks from user_indexes where index_name='CUSTOMER_IDX';
        235                                                                     

SQL> update customer set address='aa'||address;

96546 rows updated.

SQL> analyze index customer_idx compute statistics;

Index analyzed.

 The index uses more blocks after DML on customer table

SQL> select leaf_blocks from user_indexes where index_name='CUSTOMER_IDX';
        691                                                                     

SQL> alter index customer_idx rebuild;

Index altered.

SQL> analyze index customer_idx compute statistics;

Index analyzed.

 After rebuild of the index

SQL> select leaf_blocks from user_indexes where index_name='CUSTOMER_IDX';
        293                                                                     

SQL> analyze table customer compute statistics;

Table analyzed.

SQL> select avg_col_len from user_tab_columns where table_name='CUSTOMER' and column_name='ADDRESS';
         10                                                                     

 When the column length changes the number of blocks also changee
SQL> update customer set address=address||address;

96546 rows updated.

SQL> analyze index customer_idx compute statistics;

Index analyzed.

SQL> select leaf_blocks from user_indexes where index_name='CUSTOMER_IDX';
       1069                                                                     

SQL> alter index customer_idx rebuild;

Index altered.

SQL> analyze index customer_idx compute statistics;

Index analyzed.

 The number of blocks also depend on the Average column length
SQL> select leaf_blocks from user_indexes where index_name='CUSTOMER_IDX';
        425                                                                     

SQL> analyze table customer compute statistics;

Table analyzed.

SQL> select avg_col_len from user_tab_columns where table_name='CUSTOMER' and column_name='ADDRESS';
         20                                                                     

 Lets calculate the number of blocks manually
SQL> select 8196*(100-PCT_FREE)/100 from user_indexes where index_name='CUSTOMER_IDX';
                 7376.4


SQL> select 96546*20/7376.4 from dual;
     261.769969

From the above it is found that 261 blocks are required for storing the
column value alone and 163 blocks are used for storing the BTree information.