Do you know how many Types of Indexes in Oracle? This article explains the Types of indexes that make considerable performance improvements. Here I explain the main and different types of indexes in Oracle. There are several ways to create an index in Oracle using different keywords.
Most of the time, we are using a B-Tree index to improve the performance. YES, it is indeed the type mostly used in queries. But there are more. Below are highlighted a few types of index type, but you can use them smartly. If you would like to see all other types, then GO TO -> 19 New Ways to Create an Index in Oracle
How to create an index in Oracle?
1. Identify the Column: First, you have to identify the performance issues and also figure out which column or columns need to be there in an index. This may be one of the columns that are available in the WHERE condition in the SQL query.
2. Write the SQL Command: This is the basic syntax for creating indexes in Oracle.
-- Syntax -- CREATE INDEX index_name ON table_name (column_name); --
- index_name is the name of an index. Good to have some standard way of naming
- table_name is the table name, and your column is contained inside this table
- column_name is the name of a column or may be multiple columns
3. Execute the SQL Command: Run the above query in your database once you have identified the column name and the table. The index will be maintained every time data is inserted, deleted, and modified.
4. Considerations: Keep in mind index improves the performance, but same time, it will add some overhead when you are modifying a row in a table. Therefore, add the required indexes that really need improvement when fetching data from the database.
Types of Indexes in Oracle
1. B-Tree Index :
- In this example, create an index in Oracle on the employee table for employee_id. This index is created using a B-tree index. The index improves the performance when retrieving data using queries and employee_id in the WHERE condition. Employee_id column values are unique, then B-tree indexes are well-suited. If Index values are almost unique B-tree index enables efficient lookups, speeding up data retrieval.
- Also, B-tree indexes are suited to multi-column indexes. Oracle B-tree is comparatively different from other databases. To find the specific row, look at the header node and then move to the required value.
Source: dba-oracle.com
-- Syntax CREATE INDEX idx_employee_ix1 ON employees(employee_id); --
2. Bitmap Index:
- A bitmap index is the next index type from Types of Indexes in Oracle that is suitable for columns with low cardinality. This index is efficient on a column like gender, department name, or any other column that has limited distinct values in the column. It stores bitmap representations of values, and below is the syntax for creating an index in Oracle using the BITMAP keyword.
Source: dba-oracle.com
--Syntax CREATE BITMAP INDEX idx_gender ON employees(gender); --
3. Function-Based Index:
- Oracle function-based index enhances the performance of queries that retrieve data using a custom index according to usage. This will improve the performance when it consists of a function or expression in a WHERE condition.
- Function-based index in Oracle is to create an index in Oracle based on a function or expression on one or more columns in a table that you need to filter data by computed values. This computed value can be a Mathematical calculation, String manipulation, or a complex column operation. (Ex: SUBSTRING, AVG, ROUND, CEIL, ETC)
- In this example, Oracle index for the LOWER function and the index saves last_name values as lowercase. That helps to retrieve data from the table without considering the case of the last name.
-- Syntax CREATE INDEX idx_lower_last_name ON employees(LOWER(last_name)); --
4. Reverse Key Index:
- Reverse key index is the fourth type from the Types of Indexes in Oracle article. which is suit for the index column values is added like a sequence. This will speed up Oracle’s INSERT statements.
- If you hope to insert a large batch insert Oracle reverse index performs greatly in big data loads. But the targeting column should be sequentially increased. The reason for this is index value is reversed according to the high-order index key.
- This is not going to change the default functionality of INDEX. Compared to the standard indexes, this reverses the bytes of each column value( This is not happening on the rowid column).
- This results in far faster insert speed. This is because only the lowest level node for the index is changed. But not for the higher-level modified.
-- Syntax CREATE INDEX idx_reverse_order_id ON orders(order_id) REVERSE; --
Source: dba-oracle.com
5. Domain Index:
- Domain indexes are used in such data types as video, LOB, or custom data types and may help with image processing. You are allowed to create a domain index once after building a new index type
- Source: docs.oracle.com
-- Create an Indextype CREATE INDEXTYPE TextIndexType FOR Contains (VARCHAR2, VARCHAR2) USING TextIndexMethods WITH SYSTEM MANAGED STORAGE TABLES; --
-- Create index
CREATE INDEX ResumeTextIndex ON MyEmployees(resume)
INDEXTYPE IS TextIndexType PARAMETERS (':Language English :Ignore the a an');
--6. Clustered Index
- If data is frequently retrieved from 2 tables, then this method saves data in the same block, then once you retrieve the data reduces the IO calls for blocks. Therefore, you have to create a new cluster key for this.
- At this time, we create an Oracle index for the new cluster we created, which is called the Cluster index. There is no new Syntax for it was just created when creating a table.
-- Syntax create table orders_detail ( order_id integer not null, detail_id integer not null, primary key (order_id, detail_id) )organization index; --
7. Descending Index
- Descending index is an unforgettable index from Types of Indexes in Oracle, which is the opposite of the standard index we use, but there are some benefits to descending indexes. If you want to ORDER BY DESC, then those kinds of situations descending index gives some improvement.
- But what is the purpose of having a descending index if the standard one does both directions? It is because of reduced back-end Sorting time in the standard B-tree index. The execution plan shows INDEX RANGE SCAN DESCENDING, but is used according to a problem situation.
--Syntax CREATE INDEX idx_desc_age ON employees(age DESC); --
8. Unique Index:
- A unique index creates a B-tree index for a column that expects non-duplicates. A unique index ensures values are unique, such as email addresses.
- Oracle automatically creates a unique key index by creating the primary key or the unique key columns.
-- Syntax CREATE UNIQUE INDEX idx_unique_email ON employees(email); --
https://ennicode.com/sql-rowcount-in-oracle
9. Composite Index:
- A composite index is created when you need to create an index in Oracle for multiple columns. When you create a Composite index, put the most restrictive column at first (the Most predictive unique value column first).
- But be mindful when you create an index with more than 2 columns, because 3 or more. Index may not perform with compared to a 2-column index. But you have a performance Hit on insertion when you have a composite index.
-- Syntax CREATE INDEX idx_salary ON employees(salary, allowance); --
10. CONTEXT Indexes
This is the last index in our list, Types of Indexes in Oracle. If you have Such a big text or a large amount of text is not normal. There are PDF files, XML files, HTML files, or big plan text like JSON structures, and then the data is saved as a BLOB column. In those cases, we preferred to use CONTEXT indexes.
-- Syntax CREATE INDEX my_docs_doc_idx ON my_docs(doc) INDEXTYPE IS CTXSYS.CONTEXT; --
Conclusion for Types of Indexes in Oracle
The above examples showcase of Types of Indexes in Oracle. Those are designed to approach different problems in performance. Creating an index in Oracle does not solve every problem in performance, but it supplies different Types of Indexes in Oracle to help with performance.
Users need to add the best place according to statistics to the database. But this is a great help in improving performance by adding different types of indexes according to different algorithms.
Index should be added according to the data load and the behavior of data spread in the database. You can use some method to verify performance in your queries and the database. After that, use one of the types from Types of Indexes in Oracle.
- Explain Plan in Oracle
- Check running queries in Oracle
- Performance tool like PROFILER
- AWR Report
- ASH (Active Session History) Reports
- SQL Health Check
- Using Third-Party Tools
