Indexes in Oracle
An optionally constructed database item, a database index is mostly used to improve query performance. The idea behind an index is the same as that of the index at the back of a real book: it links a search word or column value to the location of the relevant data.
An index in a database holds the value of a column and its Row Identifier (ROWID). By providing a shortcut that contains the actual location of the table row on the disc, the ROWID enables Oracle to efficiently retrieve table data with the fewest possible disc reads. Without an index, Oracle could have to read every row in the table in order to fulfil a query, which is known as a full-table scan.
While indexes significantly enhance the speed of SELECT statements, particularly those involving WHERE clauses, joins, ORDER BY clauses, or MAX/MIN functions, they are not without cost. Indexes consume disk space. Every Data Manipulation Language (DML) operation (INSERT, UPDATE, or DELETE) performed on an indexed column requires the index itself to be updated, which slows down DML performance.
The B*-tree index is the most popular and default form of index that Oracle offers.
Create Table and Insert Example
Using SQL Data Definition Language (DDL) instructions, which are frequently the first step in object creation, create a simple table and populate it to illustrate different index types:
CREATE TABLE CUST (
CUST_ID NUMBER PRIMARY KEY,
LAST_NAME VARCHAR2(30),
FIRST_NAME VARCHAR2(30),
GENDER VARCHAR2(6)
) TABLESPACE USERS;This table is populated using the INSERT DML command:
INSERT INTO CUST VALUES(1,'SMITH','JOHN', 'M');
INSERT INTO CUST VALUES(2,'JONES','JANE', 'F');
INSERT INTO CUST VALUES(3,'BROWN','BOB', 'M');
INSERT INTO CUST VALUES(4,'BROWN','MARY', 'F');
COMMIT;-- Output of SELECT statement
SELECT CUST_ID, LAST_NAME, FIRST_NAME, GENDER FROM CUST;
CUST_ID | LAST_NAME | FIRST_NAME | GENDER
--------|-----------|------------|--------
1 | SMITH | JOHN | M
2 | JONES | JANE | F
3 | BROWN | BOB | M
4 | BROWN | MARY | FCreate Index
An index can be manually created using the CREATE INDEX command. To enforce a PRIMARY KEY or UNIQUE constraint that has been defined on a table, Oracle automatically generates a unique index. If not, indexes need to be explicitly established with the CREATE INDEX statement.
The table name, the index name, the column(s) to be indexed, and the index type (optional) are all specified in the basic syntax:
CREATE INDEX CUST_IDX_LASTNAME ON CUST(LAST_NAME);Additionally, you can incorporate options such as tablespace, which is often advised for improved availability and manageability, to physically isolate the index from the table:
CREATE INDEX CUST_IDX_FIRSTNAME ON CUST(FIRST_NAME) TABLES PACE CUST_INDEXES;Drop Index
An index can be deleted from the database using the DROP INDEX statement. This DDL action cannot be undone; it is permanent. The index may become more difficult to manage in the future if it was dropped first if it was automatically generated to support a PRIMARY KEY or UNIQUE constraint.
DROP INDEX CUST_IDX_LASTNAME;When an index is eliminated, the database no longer has to maintain the index structure, which lowers the overhead related to DML operations on the table.
Unique Index
One type of B*-tree index that is used to ensure that column values are unique is called a Unique Index. It ensures that none of the indexed columns in any two table rows have the same non-NULL value. A NULL value in the indexed column may appear in more than one row, though, because a unique index does not guarantee uniqueness for NULL values.
The UNIQUE keyword allows for the explicit creation of unique indexes:
CREATE UNIQUE INDEX CUST_UK1 ON CUST(FIRST_NAME, LAST_NAME);An error notice will be returned by the database if a user does a DML operation later on that breaks this uniqueness rule:
INSERT INTO CUST VALUES(5,'BROWN','MARY', 'F');
-- Attempted Output (Failure):
ERROR at line 1: ORA-00001: unique constraint (CUST_UK1) violatedFunction-based Index
An index that is based on a SQL function or expression applied to one or more columns is called a function-based index. These indexes are especially useful when the function or expression in the WHERE clause is used frequently in queries.
When a query applies a function (like UPPER()) to a column, the optimiser usually cannot use a standard index on that column. In order to rectify this and give the optimiser an access path, a function-based index is created.
If the last name is frequently converted to uppercase using the CUST table example:
CREATE INDEX UPPER_LASTNAME_IX ON CUST (UPPER(LAST_NAME));This index makes it possible for the query optimiser to resolve statements such as these effectively:
SELECT * FROM CUST WHERE UPPER(LAST_NAME) = 'BROWN';Bitmap Index
A bitmap index does not index each row individually; instead, it keeps the ROWIDs linked to a key value as a bitmap. Each bit in the bitmap that corresponds to each unique column value in the index shows whether or not a particular row has that key value (1 for presence, 0 for absence).
For columns with low cardinality that is, columns with few different values, like the GENDER column in our example (M or F) bitmap indexes are designed. When data is loaded in batches and updated infrequently, they are widely utilised in reporting or data warehouse settings.
For Online Transaction Processing (OLTP) systems with a large volume of INSERT/UPDATE/DELETE operations, bitmap indexes are crucially inappropriate. One index entry points to numerous rows, hence DML procedures may result in serious locking problems. You can’t make a bitmap index that is unique.
A bitmap index is created using the BITMAP keyword:
CREATE BITMAP INDEX CUST_BMX_GENDER ON CUST(GENDER);The bitmap structure for GENDER (Male/Female) in the CUST table example would conceptually map rows using 1s and 0s, enabling queries using AND or OR operators on these low-cardinality columns to be swiftly resolved by evaluating the bitmaps. For example, the value ‘M’ is mapped to the ROWIDs for rows 1 and 3, and ‘F’ to rows 2 and 4, according to the index.