Oracle Index Management


CREATE Index

Indexes can be created implicitly using unique or primary key constraints or explictly using the CREATE INDEX command. BTree indexes are created by default. Only index columns or groups of columns with wide ranges of values. If a table is small an index may decrease the speed of a query since small tables may be cached in memory or may be better searched with a full table scan. Foreign keys must have indexes otherwise excessive table-scans will result between primary and foreign keys - in some ways it may be best not to use foreign keys at all but it is probably more efficient for enforcing referential integrity than using application triggers. Even though primary and foreign keys may be the most effective application code is probably more efficient. Triggers are definitely not a good idea because Oracle triggers are simply messages and do not act on multiple statement processing SQL DML statements such as in the statement shown below. In fact the statement shown below shold cause an Oracle error since an Oracle trigger is unable to be processesed.

INSERT INTO table1 SELECT * FROM table2;

In Oracle8 indexes can be 32 columns and bitmap indexes 30 columns.

ALTER Index

Indexes can have physical storage clauses altered and rebuilt. Columns can not be added, modified or removed other than dropping and recreating the index. ALTER INDEX is generally used in deallocation of oversized index space usage, changing index storage characteristics or index rebuilding.

An index can be moved from one location to another using the TABLESPACE clause.

DROP Index

About Indexes

Index Types

What is an Index ?

An index of columns on a table contains a one-to-one ratio of rows between index and indexed table. Note that an index is effectively a separate table to that of the data table. This is why sometimes developers will refer to index space and data space. An index contains the indexed columns plus a ROWID value for each of those column combination rows. Thus when searching an index one is only searching through the indexed columns rather than all the data in the row of a table. The index space ROWID is then used to access the table row directly. Note that since the index row is generally much smaller than the table row that the more records are effecitvely stored in fewer blocks. Thus less of the database is accessed when using indexes as opposed to tables to search for data.

Loading Data into Tables with Indexes

It is always better to create indexes after data has been mass-inserted into tables such as with SQL*Loader. Index creation requires sorting. Alter the SORT_AREA_SIZE parameter to allow for more sorting space when generating indexes. Also the NOLOGGING option mcan be used when rebuilding indexes. Another option to rebuilding is coalescing. Coalescing is less time-intensive and will attempt to consolidate and defragment extents, ie. place blocks closer to each other physically on the disk.

Howmany Indexes per Table ?

Always limit indexes per table. For every table update all indexes must also be updated. Thus inserting into a table with five indexes which actually involve one table insert and five index insertions, ie. six updates to the database altogether. Thus static tables or read-only tables can have more indexes than dynamic tables.

Index Efficiency

Determine an index's space usage by performing the following steps.