19 New Ways to Create an Index in Oracle

This article explains how to create an index in Oracle and the Types of indexes making considerable performance improvements. Here 19 ways to create an index and below explain how to create an Oracle index. There are a lot of ways to create and index with Oracle using keywords.

how to create an index in Oracle

1. Identify the Column: The first thing you have to identify the performance issues and figure out which columns need to be added to the index. This will be one of the columns that are available in the where condition in SQL queries.

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

3. Execute the SQL Command: Run the above query in your database once you identified the column name and the table. The index will be maintained every time data is inserted deleted and modifications

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 required indexes that really need improvement when fetching data from the database.

Oracle Types of indexes

1. Create a 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 from a table. 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 from the table.
• Also, B-tree indexes are suited to multi-column indexes. Oracle b-tree is comparatively different from other databases. To find the specific row need to 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. Create a Bitmap Index:

• A bitmap index is the next type of index 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 create an index in Oracle using the BITMAP keyword.

Source: dba-oracle.com

--Syntax 
CREATE BITMAP INDEX idx_gender ON employees(gender);
--

3. Create a Function-Based Index:

• Oracle function-based index enhances the performance with queries that retrieve data using a custom index according to usage. This will improve the performance when consists of function or expression in where condition. 
• function-based index in Oracle is to create an index in Oracle based on a function or expression to one or more columns in a table that you need to filter data by computed values. This computed value can be Mathematical calculation, String manipulation, or complex column operation.
• In this example oracle index create 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. Create a Reverse Key Index:

• Reverse key index suit for index column values are added like a sequence. This will speed up Oracle’s INSERT statements. If you hope to insert a large batch insert Oracle reverse index greatly performed in big data-load. 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 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. Create a Bitmap Join Index

• This bitmap join index intention is pre join low cardinality columns together to improve the performance on join tables. This improves the performance once join Orders, and Customers tables are together and where conditions have the column State and Gender together.
• Then this will retrieve the data from the Orders table without referring Customers table. Instead, data fetch using Bitmap join index. That will reduce the production time of a large dataset. This is also great on low cardinality columns.

Source: dba-oracle.com

-- syntax
CREATE BITMAP INDEX customer_orders_ix1 
ON    orders(customers.state, customers.gender) 
FROM  orders, customers 
WHERE orders.cust_id = customers.cust_id;
--

 Usage

-- Query
SELECT oraders.* 
FROM  orders, customers 
WHERE orders.cust_id = customers.cust_id
AND customers.state =’Central’, customers.gender= ‘Male’
--

6. Create a 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');
--

7. Create a 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 Oracle index for the new cluster we created which is called the Cluster index. There is not any new Syntax for it just creates 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;
--

8. Create a Descending Index

• Descending index is the opposite of the standard index we use but there are some benefits to descending indexes. If you would want to ORDER BY DESC then those kinds of situation Descending index giving some improvement.
• But what is the purpose of having a descending index if the standard one doing 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);
--

9. Create a Unique Index:

• A unique index creates a b-tree index for a column that values expecting non-duplicates. 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);
--

10. Create a 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(Most predictive unique value column first).
• But be mindful when you create an index with more than 2 columns because 3 or more columns index may not perform with compared to 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);
--

11. Create a Function-Based Bitmap Index on Lowercase:

• We normally create a function base index using a b-tree index but if you don’t have to join multiple tables then you can create a Bitmap index using a function. This performs well on low cardinality columns but where condition filter value using SQL Function.

--Syntax
CREATE BITMAP INDEX idx_lower_gender ON employees(LOWER(Gender));
--

12. Create a Reverse Key Bitmap Index:

• As you know now Reverse key indexes are most preferred when you have a batch insert or sequentially adding values into column. If you want to create Oracle index without using a B-tree index but want to use a BITMAP type index it also possible to create BITMAP REVERSE index like below.

-- Syntax
CREATE BITMAP INDEX idx_reverse_bitmap ON custormer_order(order_id) REVERSE;
--

13. Create a Range Partitioned Index:

• Range partition indexes are created for range partition tables. If you are familiar with partitioning table cn partition for a specific range. Once you partitioned the table index can be created for it.
• This example create an index in oracle for order_date and indexes are aligned with the specific date range. This enables speedup of the selects if filtered by order_date.

-- Syntax
CREATE INDEX idx_date_partitioned 
ON sales(order_date) 
LOCAL (PARTITION sales_january VALUES LESS THAN (TO_DATE('2023-02-01', 'YYYY-MM-DD')), 
PARTITION sales_february VALUES LESS THAN (TO_DATE('2023-03-01', 'YYYY-MM-DD'))); 
--

14. Create a Hash Partitioned Index:

Hash partitioning tables are partitioned by the hash value of the column. In this case, order_id is taken as the column for partitioning, and we have the opportunity to decide the partition count.

-- Syntax
CREATE INDEX idx_hash_partitioned ON Orders(order_id) PARTITION BY HASH(order_id) PARTITIONS 4;
--

15. Create a Global Partitioned Index:

• Global partition index can be created using HASH or RANGE partitions. A global index is created using GLOBAL keywords. In this case data is organized by customer_order_id.

-- Syntax
CREATE INDEX idx_global_partitioned 
ON Customer_order(customer_order_id) 
GLOBAL PARTITION BY RANGE(customer_order_id) 
(PARTITION p1 VALUES LESS THAN (1000),
 PARTITION p2 VALUES LESS THAN (2000), 
PARTITION p3 VALUES LESS THAN (MAXVALUE));
--

16. Create an Index-Organized Table with a Primary Key:

• An index-organized table is a unique style of table structure that stores table data as a B-tree structure. Without only entering the primary key into the index index-organized table saving non-key column values into an index.
• This is performing well accessing data in the table using a primary key or any other key. But this is not maintained rowids.

-- Syntax
CREATE TABLE employees_organized_pk ( 
employee_id NUMBER,
 last_name VARCHAR2(50),
 first_name VARCHAR2(50),
 PRIMARY KEY (employee_id) ) ORGANIZATION INDEX;

17. Create a Bitmap Index with Compression:

• This is not only a way of creating a BITMAP index but doing the compression as well. Performance-wise time take for uncompress as well, but Storage-wise compression is better than uncompressed type. The most important thing is saving space rather than performance. But it is not a huge drop but a significantly low

-- Syntax
CREATE BITMAP INDEX idx_bitmap_compressed ON Orders(order_id) COMPRESS 2;
--

18. Create a CONTEXT Indexes

create an index in Oracle for 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 then data save as a BLOB column. In those cases, preferred to use CONTEXT indexes.

-- Syntax
CREATE INDEX my_docs_doc_idx ON my_docs(doc) INDEXTYPE IS CTXSYS.CONTEXT;
--

19. Create a Partial Index on High-Value:

This creates index for part of the table. According to the below statement, indexes are created date greater than some value. This feature can be used when some data records in the table usage is high or those data sets are used frequently.

-- Syntax
create table t1 (  id number,   
description   varchar2(50),   
created_date  date)partition by range (created_date) (  
partition part_2014 values less than (date '2015-01-01'),  
partition part_2015 values less than (date '2016-01-01') indexing on,  
partition part_2016 values less than (date '2017-01-01') indexing off);
--

The above examples showcase in Oracle how to create different types of indexes. Those are designed to approach different problems on performance. create an index in Oracle does not solve every problem in performance but Oracle supplies different types of indexes to help with performance.

Users need to add the best place according to statistics in 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 data load and the behavior of data spread in the database Thank you for reading us and keep watch for our other titles.