Designing Indexes - Concepts
My
OracleGuide

Designing Indexes - Concepts

 About    Feedback Products

Designing Indexes - Concepts

Index Concepts

Creating the right indexes can improve the search performance in database applications. Also for tables with heavy DML's the indexes will cause more write IO. So for an application that is both transaction and query intensive a compromise has to be reached regarding the creation of indexes. Too many indexes can increase the write IO and less indexes will lead to full table scan and increase in total reads and physical disk reads.

Different type of indexes can be considered for different situations. A normal BTree index will be suitable in most situations. If the repeating columns are present in the index then creating the index with compress option will reduce the space and memory required. But this can increase the CPU time.

If the number of distinct values are very less, less than 5, then bitmap indexes will be more suitable. For indexes on large tables one option is to use global partitioned indexes. This will reduce the index maintenance cost.

There are two approaches to index creation: proactive and reactive. Proactive index creation involves anticipating which columns will be most often used for selection, joining, grouping and ordering and then building indexes over those columns. Deciding the primary keys and unique keys during the database design is a proactive approach.

In the reactive approach, indexes are created based on optimizer feedback, query implementation plan, and system performance measurements. For applications where it is not possible to modify the queries or schema, the strategy is to obtain the statistics from data dictionary views and create the necessary indexes. The views V$SQL and V$SQL_PLAN can be used to find out the sql queries that are doing full table scan and also the cost and disk reads associated.

It is useful to initially build indexes based on the database model and application(s) and not any particular query. As a starting point, consider designing basic indexes based on the following criteria:

  • Primary and foreign key columns based on the database model
  • Commonly used local selection columns
  • Commonly used join columns not considered primary or foreign key columns
  • Commonly used grouping columns
To perform reactive tuning, build a prototype of the proposed application without any indexes and start running some queries or build an initial set of indexes and start running the application to see what gets used and what does not. Typically you will create an index for the most selective columns.

Indexing Strategies

This section contains a few recommendations about what to index, what not to index, and other indexing strategies. It even includes a small test of your indexing savvy.

What to Index

  • Columns used frequently in Where clauses
  • Columns used in joins, usually primary and foreign keys
  • Columns used in Group by clauses or Order by clauses
  • Tables where the average row length is high. An index can avoid table fetch in favor of an index scan

What Not to Index

  • Tables with a small number of rows
  • Tables with heavy transaction-based I/O
  • Columns not used in Where clause
  • Columns with greater than 5 percent selectivity
  • Wide columns (greater than 25 bytes in width)

Partitioned Indexes

Indexes can be partitioned either independently (global indexes) or automatically linked to a table's partitioning method (local indexes). Local partitioned indexes are easier to manage than other types of partitioned indexes. They also offer greater availability and are common in DSS environments. Global partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method. They are commonly used for OLTP environments and offer efficient access to any individual record.

Partitioning reduces the index maintenance cost. Updating the BTree structure for a large index is more expensive. In partitioned indexes only one partition may be updated.

Other Strategies

Narrow indexes are sometimes better than multiple-column composite indexes unless they are used as covering queries. When the number of columns are less then more leaf nodes can be placed in a block. Wider indexes cause more index blocks.

Do not have more than one index with the same first column.You can have more indexes on a table in a data warehouse and fewer indexes in an online transaction processing table.

In general, integer fields make more efficient indexes than character fields and fixed-length data types are more efficient than variable types.

Tables with a small number of rows should have a unique index only to prevent duplicates. It is not advisable to have an index when the table has fewer than 200 rows. Here a full scan for this table is preferred over access using index. The only time the optimizer may choose to use an index on a small table is in a join operation. There are join queries that can benefit from index on small tables.

If the table contains large text columns or the average row length is high, then the table can occupy more number of blocks and this can increase the disk IO. In this case an index works better even for small tables. If all fields required by the query is present in the index then the optimizer can choose an index scan instead of table fetch.

While importing data in batches, it may be faster to drop the indexes, insert all the data, then create the indexes. It is also better to rebuild the indexes after lots of DML opeartions on the table. In production environments a seperate batch job may be run for rebuilding indexes on tables with heavy transactions either weekly or monthly.

Index Access Statistics


--- To select TOP 10 Indexes by usage

select * FROM(
select b.object_owner, b.object_name, sum(a.executions)
FROM v$sql a, v$sql_plan b
where 
a.address=b.address 
and a.hash_value=b.hash_value
and b.operation = 'INDEX'
GROUP BY b.object_owner, b.object_name
ORDER BY sum(a.executions) DESC)
WHERE rownum<11;

--- To select TOP 10 Indexes with UNIQUE SCANS by usage

select * FROM(
select b.object_owner, b.object_name
FROM v$sql a, v$sql_plan b
where 
a.address=b.address 
and a.hash_value=b.hash_value
and b.operation = 'INDEX'
and b.options = 'UNIQUE SCAN'
GROUP BY b.object_owner, b.object_name
ORDER BY sum(a.executions) DESC)
WHERE rownum<11;

--- To select TOP 10 Indexes with RANGE SCANS

select * FROM(
select b.object_owner, b.object_name, sum(a.executions)
FROM v$sql a, v$sql_plan b
where 
a.address=b.address 
and a.hash_value=b.hash_value
and b.operation = 'INDEX'
and b.options = 'RANGE SCAN'
GROUP BY b.object_owner, b.object_name
ORDER BY sum(a.executions) DESC)
WHERE rownum<11;

--- To select TOP 10 Indexes with FULL SCANS

select * FROM(
select b.object_owner, b.object_name, sum(a.executions)
FROM v$sql a, v$sql_plan b
where 
a.address=b.address 
and a.hash_value=b.hash_value
and b.operation = 'INDEX'
and b.options = 'FULL SCAN'
GROUP BY b.object_owner, b.object_name
ORDER BY sum(a.executions) DESC)
WHERE rownum<11;

--- To select TOP 10 TABLES with ACCESS BY ROWID

select * FROM(
select b.object_owner, b.object_name, sum(a.executions)
FROM v$sql a, v$sql_plan b
where 
a.address=b.address 
and a.hash_value=b.hash_value
and b.operation = 'TABLE ACCESS'
and b.options = 'BY ROWID'
GROUP BY b.object_owner, b.object_name
ORDER BY sum(a.executions) DESC)
WHERE rownum<11;

--- To select TOP 10 TABLES with FULL SCAN

select * FROM(
select b.object_owner, b.object_name, sum(a.executions)
FROM v$sql a, v$sql_plan b
where 
a.address=b.address 
and a.hash_value=b.hash_value
and b.operation = 'TABLE ACCESS'
and b.options = 'FULL'
GROUP BY b.object_owner, b.object_name
ORDER BY sum(a.executions) DESC)
WHERE rownum<11;