Index are schema objects that can speed access to table rows.
Indexes are schema objects that are logically and physically independent of the data in the objects with which they are associated.
the presence of many indexes on a table degrades DML performance because the database must also update the indexes.
B-trees, short for balanced trees, are the most common type of database index.
B-tree indexes: the default and the most common
B-tree cluster indexes: defined specifically for cluster
Hash cluster indexes: defined specifically for a hash cluster
Global and local indexes: relate to partitioned tables and indexes
Reverse key indexes: most useful for Oracle Real Application Clusters applications
Bitmap indexes: compact; work best for columns with a small set of values
Function-based indexes: contain the precomputed value of a function/expression
Domain indexes: specific to an application or cartridge.
A B-tree index is an ordered list of values divided into ranges.
By associating a key with a row or range of rows, B-trees provide excellent retrieval performance for a wide range of queries, including exact match and range searches.
Branch Blocks and Leaf Blocks
A B-tree index has two types of blocks: branch blocks for searching and leaf blocks that store values. The upper-level branch blocks of a B-tree index contain index data that points to lower-level index blocks. In Figure 3-1, the root branch block has an entry 0-40, which points to the leftmost block in the next branch level. This branch block contains entries such as 0-10 and 11-19. Each of these entries points to a leaf block that contains key values that fall in the range.
A B-tree index is balanced because all leaf blocks automatically stay at the same depth. Thus, retrieval of any record from anywhere in the index takes approximately the same amount of time.
Index Scans – In an index scan, the database retrieves a row by traversing the index, using the indexed column values specified by the statement. If the database scans the index for a value, then it will find this value in n I/Os where n is the height of the B-tree index. This is the basic principle behind Oracle Database indexes. If a SQL statement accesses only indexed columns, then the database reads values directly from the index rather than from the table. If the statement accesses columns in addition to the indexed columns, then the database uses rowids to find the rows in the table. Typically, the database retrieves table data by alternately reading an index block and then a table block.
Creating a Function-Based Index: CREATE INDEX upper_ix ON employees (UPPER(last_name));
Index the Correct Tables and Columns
– Create an index if you frequently want to retrieve less than 15% of the rows in a large table. The percentage varies greatly according to the relative speed of a table scan and how the distribution of the row data in relation to the index key. The faster the table scan, the lower the percentage; the more clustered the row data, the higher the percentage.
– To improve performance on joins of multiple tables, index columns used for joins.
– Small tables do not require indexes. If a query is taking too long, then the table might have grown from small to large.
– Primary and unique keys automatically have indexes, but you might want to create an index on a foreign key.
Columns That Are Suitable for Indexing
Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:
-Values are relatively unique in the column.
-There is a wide range of values (good for regular indexes).
-There is a small range of values (good for bitmap indexes).
-The column contains many nulls, but queries often select all rows having a value. In this case, use the following phrase:
WHERE COL_X > -9.99 * power(10,125)
Using the preceding phrase is preferable to:
WHERE COL_X IS NOT NULL
This is because the first uses an index on COL_X (assuming that COL_X is a numeric column).
Columns That Are Not Suitable for Indexing
Columns with the following characteristics are less suitable for indexing:
-There are many nulls in the column and you do not search on the not null values.
-LONG and LONG RAW columns cannot be indexed.
-You can create unique or non-unique indexes on virtual columns.
Order Index Columns for Performance
The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first.
If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries that access just col1, or that access just col1 and col2, are also speeded up. But a query that accessed just col2, just col3, or just col2 and col3 does not use the index.
Limit the Number of Indexes for Each Table
A table can have any number of indexes. However, the more indexes there are, the more overhead is incurred as the table is modified. Specifically, when rows are inserted or deleted, all indexes on the table must be updated as well. Also, when a column is updated, all indexes that contain the column must be updated.
Thus, there is a trade-off between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable.
Rebuilding an Existing Index
ALTER INDEX emp_name REBUILD;
Monitoring Index Usage
Oracle Database provides a means of monitoring indexes to determine whether they are being used. If an index is not being used, then it can be dropped, eliminating unnecessary statement overhead.
To start monitoring the usage of an index, issue this statement:
ALTER INDEX index MONITORING USAGE;
Later, issue the following statement to stop the monitoring:
ALTER INDEX index NOMONITORING USAGE;
The view V$OBJECT_USAGE can be queried for the index being monitored to see if the index has been used. The view contains a USED column whose value is YES or NO, depending upon if the index has been used within the time period being monitored.
If key values in an index are inserted, updated, and deleted frequently, the index can lose its acquired space efficiently over time. Monitor index efficiency of space usage at regular intervals by first analyzing the index structure, using the ANALYZE INDEX…VALIDATE STRUCTURE statement, and then querying the INDEX_STATS view:
SELECT PCT_USED FROM INDEX_STATS WHERE NAME = ‘index‘;
When you find that index space usage drops below its average, you can condense the index space by dropping the index and rebuilding it, or coalescing it.
Some reasons for dropping an index include:
- The index is no longer required.
- The index is not providing anticipated performance improvements for queries issued against the associated table. For example, the table might be very small, or there might be many rows in the table but very few index entries.
- Applications do not use the index to query the data.
- The index has become invalid and must be dropped before being rebuilt.
- The index has become too fragmented and must be dropped before being rebuilt.
You cannot drop only the index associated with an enabled UNIQUE key or PRIMARY KEY constraint. To drop a constraints associated index, you must disable or drop the constraint itself.
If a table is dropped, all associated indexes are dropped automatically.
Some views: DBA_INDEXES, ALL_INDEXES, USER_INDEXES, DBA_IND_COLUMNS, ALL_IND_COLUMNS, USER_IND_COLUMNS, INDEX_STATS, V$OBJECT_USAGE