Oracle Indexes Performance and Creation Guidelines

These guidelines will help you create and manage indexes and help improving the performance by correct usage of indexes.

DON’T ADD INDEXES WORTHLESSLY:

Addition of indexes increases performance but also ingest disk space.Based on the performance improvement add as many indexes as required sensibly.

MARK INDEXES AS UNUSABLE OR INVISIBLE RATHER THAN DROPPING

Before dropping an index think over marking the indexes as unusable and invisible. This give us an extra option to check for any performance issues before dropping the index. If there are any performance issues we can revert back by rebuilding or re-enable the index without requiring the data definition language (DDL) creation statement.

You can read more about Invisible Indexes here:

Invisible Indexes - Oracle 11g New Feature

It’s better to drop the indexes that are not used by any database objects as it would free up the physical space and improve the performance.

INDEXING METHODOLOGY:

Indexing the columns that are used in queries executed against a table will help improve the performance.

CREATE PRIMARY /UNIQUE CONSTARINTS:

Build primary constraints on all tables and unique constraints wherever applicable. This will automatically create a B-tree index if the columns are not already indexed.

USING SEPARATE TABLESPACE FOR INDEXES

Using distinct table space helps in managing indexes separately from tables. Table and index data may have different storage and/or backup and recovery requirements.

USE BITMAP INDEXES IN DATAWAREHOUSE ENVIRONMENT

Bitmap indexes are used for complex queries in a data warehouse environment to prevent spending long time to access and retrieve answers for the queries. B-Tree index technique is used for high cardinality column and Bitmap Indexes have predominantly been used for low cardinality columns.

Bitmap indexes achieve important functions in answering data warehouse’s queries because they have capability to perform operations at the index level before fetching data

To learn more about Bitmap & B-tree indexes check our previous post

http://dwhlaureate.blogspot.in/2012/08/type-of-indexing-used-in.html

USE APPROPRIATE NAMING STANDARDS

Correct naming standards would help in the maintenance and troubleshooting easier.

If you like this post, please share it on google by clicking on the Google +1 button.

Please go through similar Oracle Posts @DWHLAUREATE:

     WHAT IS A PARTITION IN ORACLE

   WHAT IS AN INLINE VIEW?

  WHAT ARE EXTERNAL TABLES IN ORACLE

 WHAT ARE INDEXES IN ORACLE