What are Oracle Indexes
There is more to a database than simply storing data. Databases are useful because people can retrieve the data that is stored. The faster databases can retrieve the data, the more useful the database becomes.
Indexes are structures that improve the performance of data retrieval. Indexes also serve other purposes, such as guaranteeing that data entered is unique, but their primary purpose is to retrieve meaningful data from the database more quickly.
This module covers the use and management of indexes.
The use of indexes can significantly improve the performance of your database. Understanding the hows and whys of indexes will improve your abilities as a database administrator.
In this module, you will learn:
- Why indexes are used
- The basic types of indexes
- How to create an index
- Storage considerations for indexes
- How to alter an index
- How and why to rebuild an index
- How to find out about indexes from the data dictionary
- How to drop an index
Definition of Index
An index is an optional data structure that you can create on one or more columns of a table. Indexes can increase the performance of data retrieval.
When processing a request, the database can use available indexes to locate the requested rows efficiently. Indexes are useful when applications often query a specific row or range of rows. Indexes are logically and physically independent of the data. Thus, you can drop and create indexes with no effect on the physical tables or other indexes. All applications continue to function after you drop an index.
Oracle Index Characteristics
An Oracle Index is a database object designed to enhance the performance of data retrieval operations in an Oracle Database. It serves as a lookup table that provides quick access to the data rows in a table, based on the indexed columns. Oracle Indexes can significantly improve query performance by reducing the need for full table scans, thereby optimizing resource consumption and enhancing overall system efficiency. The following are the key characteristics of an Oracle Index:
- Types of Indexes: Oracle supports several types of indexes, each catering to different scenarios and requirements. The primary types include B-tree indexes, Bitmap indexes, Function-based indexes, Domain indexes, and Text indexes.
- B-tree Indexes: Balanced Tree (B-tree) indexes are the most common type of index in Oracle. They use a tree structure that allows for fast and efficient searches, insertions, and deletions. B-tree indexes work well with columns that have high selectivity, meaning they have a low percentage of duplicate values.
- Bitmap Indexes: Bitmap indexes are best suited for low-cardinality columns, where the number of distinct values is relatively small compared to the total number of rows in the table. These indexes represent each distinct value in the indexed column as a bitmap, enabling efficient query processing for complex combinations of predicates.
- Function-based Indexes: Function-based indexes allow developers to create indexes on the result of a function or expression. This is particularly useful for case-insensitive searches or queries that involve mathematical computations on columns.
- Domain Indexes: Domain indexes are specialized indexes that can be created for specific data types or application domains, such as spatial data, multimedia content, or custom data types.
- Text Indexes: Text indexes, also known as Oracle Text indexes, are designed to facilitate text-based searches on large character data, such as CLOBs, VARCHAR2s, or XMLType columns.
- Index Storage: Oracle Indexes are stored separately from the table data. They consist of index entries organized in a structure that allows for efficient searching and updating.
- Selectivity: The effectiveness of an index is heavily influenced by its selectivity. Higher selectivity indexes (low percentage of duplicate values) are more efficient in reducing the number of rows scanned during query execution.
- Index Maintenance: Oracle Database automatically maintains indexes during data manipulation operations such as INSERT, UPDATE, and DELETE. However, periodic index maintenance, such as rebuilding or coalescing, may be required to optimize index performance and storage space utilization.
- Cost-based Optimization: The Oracle query optimizer uses a cost-based approach to determine the most efficient access path for a given query. The optimizer considers various factors, including index selectivity, table statistics, and system resources, to decide whether to use an index or perform a full table scan.
Oracle Indexes are essential components in optimizing query performance and ensuring efficient data retrieval. By understanding their characteristics and selecting the appropriate index type for specific use cases, developers can significantly improve database performance and resource utilization.
Indexes are schema objects that are logically and physically independent of the data in the objects with which they are associated. Thus, an index can be dropped or created without physically affecting the table for the index.
The absence or presence of an index does not require a change in the wording of any SQL statement. An index is a fast access path to a single row of data and affects only the speed of execution. Given a data value that has been indexed,
the index points directly to the location of the rows containing that value. The database automatically maintains and uses indexes after they are created. The database also automatically reflects changes to data, such as adding, updating, and deleting rows, in all relevant indexes with no additional actions required by users. Retrieval performance of indexed data remains almost constant, even as rows are inserted. However, the presence of many indexes on a table degrades data manipulation language performance because the database must also update the indexes.