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;

• UNIQUE - unique index where column or composite of the columns is/are unique across all rows in the table.
• BITMAP - create the index as a BITMAP not a BTree. Bitmap indexes store ROWIDs associated with a key value as a bitmap. Each bit in that bitmap correspnds to a possible ROWID. If a bit is set then the row with the corresponding ROWID contains the key value. BITMAP indexes are best suited to low concurrency, low interactivity, data warehousing, read-only type environments.
• index_name - unique.
• table_name - the table must exist.
• column_name - maximum of 16 columns for a composite index. Composite indexes are usually created to increase speed of specific queries (other than referential primary or unique keys). Note that only queries which access composite indexes in the order of that index will use that index. For instance, a composite index with three columns will only be used by queries matching columns in the 1,2,3 or 1,2 or 1 sequence. Any other order will use another index or perform a full table scan.
• tablespace_name - tablespace to store the index.
• storage_clause - standard storage clause (FREELIST GROUPS are not permitted).
• NOSORT - do not sort the index and should only be used if the table is already loaded in ascending order.
• LOGGING or NOLOGGING - generates redo/rollback information. For large index creations use UNRECOVERABLE to speed-up index creation.
• UNIQUE - primary and unique key constraints automatically generate indexes.
• BITMAP - this is a type of index. Use for low cardinality data such as sex, race, etc. BITMAP indexes require much less storage space. This option is included with the parallel query option.
• PCTFREE - the amount of space reserved for insertion of additional index entries into an existing block.
• column expression - can be columns list or a combination of columns and functions.
• COMPRESS - elimination of repeated occurrences of prefix key column values. Compression should reduce storage and increase index access speed since indexes are in fewer blocks. Use compression with non-unique indexes and unique composite indexes. Obviously there is no point whatsoever in using compression to eliminate suffix column values when there is only one column in a unique index, there is simply nothing to compress.
• NOSORT - only allowed when rows are stored in ascending order thus Oracle not required to sort on index creation.
• ONLINE - allows table SQL DML operations during index creation.
• COMPUTE STATISTICS - statistics collection during index collection. Very efficient.
• Partitioning clauses - please refer to the separate section on Partitioning.
• INDEXTYPE - this is a domain index. A domain index is an object which specifies routines which manage a domain specific index. INDEXTYPE ultimately binds an indextype name to user-defined index functions.
  

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.

• PCTFREE n.
• INITRANS n.
• MAXTRANS n.
• STORAGE storage_clause - the REBUILD clause is the only part of ALTER INDEX which differs from CREATE INDEX. The REBUILD clause allows rebuilding of indexes on-the-fly.
• DEALLOCATE UNUSED - deallocates and frees space at the end of an index above the high-water mark.
• COALESCEM - merge index blocks contents freeing used blocks for reuse.

DROP Index

About Indexes

Index Types

• BTree - the most common.
• BTree Cluster - tables clustered together for multi-table join read access efficiency.
• Hash Cluster - a cluster using a hashing algorithm for indexing.
• Reverse Key - an index in reverse order.
• Bitmaps - store ROWIDs associated with a key value as a bitmap. Each bit in that bitmap correspnds to a possible ROWID. If a bit is set then the row with the corresponding ROWID contains the key value. BITMAP indexes are best suited to low concurrency, low interactivity, data warehousing, read-only type environments and are generally more efficient than hashing algorithms.
• Function-based - index built by a query returning a function or expression. The resulting value is stored in the index. Always disable the index if working on the body of the index function.
• Domain - namespace specific indexes.
• Index Organised Tables - a cluster for an individual table.

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.

• Analyse statisitics.
• Validate indexes.
• Examine PCT_USED.
• Drop and recreate, rebuild or coalesce.