Pages

Saturday, 1 October 2016

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.

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