More about Oracle Indexes
Also see How to debug PL/SQL code
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.
Finding Unused Indexes
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 or Table Scan ?
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 determine 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 global 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 following 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.
