Understanding Indexes and their key benefits in Oracle10g

The concept of index is to give speedier retrieval. In general it is a keyword accompanied by the location of the information related to the keyword. The same principle applies to oracle indexes too. If a table does not have an index on the key column, oracle has to read every row in the table to match the where clause in a query. This will not create a performance problem if the table is relatively small. But as the table grows in size the performance of the application may be impacted by the retrieval process.

If an index is created on the other hand, when a query is executed oracle looks at the index, which is sorted. The index entry gives the exact location in the table for the value specified in the where clause. In general indexes provide faster access to data for operations that are used to retrieve a small portion of the table’s rows. The standard type of index usually used is a B* -tree index, matching column values to their related Row ID’s.

The index is created by the create index command. Index is also created by default when a primary key or unique constraint is created. In oracle10g the following command is used.

Create [bitmap | unique] index index_name
on table (column[,column]….) [reverse];

Here index_name is the unique name of the index, table is the name of the table on which index is created and column is the name of the columns to be indexed, the reverse key word is used to tell Oracle to reverse the bytes of the indexed value to improve the I/O distribution during insert of data values that are sequential.

A single index can be created on multiple columns by separating the columns with commas. A unique index can be created by either creating a primary key constraint, by creating a unique constraint or by creating a unique index.

Index is generally created in the following situations. The column is queried frequently or a referential integrity constraint exists on the column or a unique key integrity constraint exists on the column. Although the oracle database by default creates index on a column with integrity constraint, explicitly creating index is recommended. If index is created for a column that is not used as specified above the index may take up resources unnecessarily and degrade performance.

The EXPLAIN PLAN feature that shows a theoretical execution of a SQL statement or V$SQL_PLAN view to determine the actual execution plan can help decide which columns need to be indexed.

Guidelines generally used to determine to create indexes are as follows. Indexes are created when frequently less than 15% rows of a table are retrieved or on columns used in joins to improve join performance. Columns that are recommended to be indexed are those, whose values are unique or there are few duplicate entries, having a wide range of values (suitable for regular index), or having a small range of values (suitable for bitmap indexes).

The database can use indexes more effectively when statistical information about the tables involved in queries are gathered.

Two main kinds of indexes are Domain Indexes and Function Based Indexes. A Domain index is an extension of Oracle index system that allows users to create their own type of indexes. These are appropriate for applications implemented using data cartridges. Index types are created using the CREATE INDEXTYPE command. It is usually stored in an index organized table or on an external file.

A Function based index is an index built on an expression. It allows function based accesses to be supported by index access. These indexes are useful because generally any query that performs a function on a column does not use the index related to the column. The advantages of function based indexes are, it increases the number of situations where the optimizer can perform a range scan instead of a full table scan, precompute the value of a function and store it in the index and create more powerful sorts.

Example of a Function Based Index for case insensitive search is

CREATE INDEX Idx1 ON Empl_tab (UPPER(Ename));

When the select command is used with UPPER(Ename) in the WHERE clause the function based index Idx1 will be used.

There is however some restrictions for function based indexes such as only cost based optimization can use function based indexes, expression in function based indexes cannot contain aggregate functions, the table or index must be analyzed before being used etc.

A bitmap index is used when data is infrequently updated and are appropriate when non selective columns are used as limiting conditions in a query. These are not to be used for tables in online transaction processing applications and are to be mainly used only in batch operations. This is because the internal mechanisms of performance cost during data manipulation commands are significant.

To create index in an application it is required to own the table or have the INDEX object privilege for the table. To create an index in another user’s schema the CREATE ANY INDEX system privilege must be present.

Indexes can be dropped if they are no longer required. In circumstances when index do not speed up queries or the queries in the application do not use the index, index is usually dropped. The SQL command DROP INDEX is used. The index must be in the current schema or DROP ANY INDEX system privilege must exist.

Example DROP INDEX Idx1. This causes Index Idx1 to be dropped.

If a table is dropped all associated indexes are also dropped by default.

Indexes can be re-created without being dropped by the fast index rebuild capability provided by Oracle. This is because the currently available index is used as the data source instead of the table itself.

If no index exists on a table a full table scan must be performed for each table referenced in a database query. Usage of indexes can reduce access time significantly, but they have to be carefully chosen to address the needs of the application program and not create adverse impacts on performance.

| Characterization of Materialized views and its types in Oracle10g | Introduction to Capability Maturity Model (CMM) | Introduction to Data Blocks, Extents and Segments –Logical Storage Structures in Oracle 10g Server | Oracle Application Server10g – Business Intelligence Services | Oracle Application Server10g – Content Management Services | Overview of Commit and Rollback – The Transaction Control Statements in Oracle 10g | Overview of Flashback Features in Oracle10g | Overview on Database Change Notification in Oracle10g | Overview on Implicit and Explicit Cursors in PL/SQL | Overview on Sequential Control and Conditional Control Statements in PL/SQL | Overview on some of the new features in Oracle Database 10g | Overview on the usage of Cursor Variables in PL/SQL | Performance Tuning in Oracle10g – Best Practices in Application Development | Representing Conditional Expressions as Data in Oracle10g | Understanding Indexes and their key benefits in Oracle10g | Understanding Oracle10g Application Server –Portal Services |