Oracle Create Index – Detailed Guide

In this Oracle tutorial, we will learn how to create an index in oracle database 21c. Moreover, we will take a look at multiple examples where we need to create an index in the oracle database

An index is a schema object that allows fast, direct access to rows by storing entries for each value in the table. Oracle Database supports multiple index types.

Here we will discuss

• How to create an index in oracle
• Oracle create index parallel
• Oracle create index on tablespace
• Oracle create index if not exists
• Oracle create index where clause

Oracle create index

• The usage of indexes allows for quicker data retrieval from databases. Users are only using the indexes to speed up searches and queries.
• They perform nicely when you can use them to locate “a few” rows. Therefore, there’s a high probability you’ll want it to use an index if your query retrieves a small number.

Oracle create index syntax

Till now, we have understood what indexing is in a database and we also discussed the purpose of indexing. Next, we will take a step further and understand how to create an index in oracle 21c.

For this, we need to understand the syntax to create an index in oracle 21c which is given below.

create index <index_name> on <table_name> ( <col_1>, <col_2>, … );

In the above syntax

• The index’s name should be specified first. In addition to the table alias and column name, the index name should be meaningful for example idx_emp_name.
• table_name: The name of the table where the index will be created for example employees
• col_1,col2: This parameter defines the columns to use in the index.

Note: A btree index is automatically created by the CREATE INDEX statement.

Oracle automatically builds a new index for the primary key columns when you create a new table with a primary key.

Read: Oracle Database vs MySQL Database

Oracle Create an index Example

After discussing the syntax, Now we will discuss the example of how to create an index in oracle.

In this section, we will discuss the table that we have created Here is the table for our example execution and this employees table is located in the pdb database.

Once the table is created successfully, expand the Tables (filtered) option and double-click on the table name.

Next, create an index by clicking the ’employees’ icon

Here first we click the right button on the employee icon and it will display the index option after that we select the create index option.

Here we defined the index name “Emp_index_address” and then we mention the type of index that is Bitmap and by default it is b-tree.

Use ‘+’ icon on the right to add columns in the expression and we stored the “emp_address”.

Emp id serves as the primary key column in the employees table. You may run a query using the all indexes view to see all the indexes for a table.

In this there is no index column available now we will add it and for this first, we will create the index by using the query.

CREATE INDEX emp_index_address
ON employees(emp_address);

Now we will display the index by using the below query

SELECT 
    index_name, 
    index_type, 
    visibility, 
    status 
FROM 
    all_indexes
    
WHERE table_name = 'EMPLOYEES';

This is how to create an index on one column

Now if you want to find the details of employees whose address is Chicago. For this, we will use the following query

SELECT * FROM employees
WHERE emp_address = 'Chicago';

The steps below can be used to determine if a query uses the index for lookup or not.

First, we will insert the Explain Plan For before the SQL statement

EXPLAIN PLAN FOR
SELECT * FROM employees
WHERE emp_address = 'Chicago';

First, we will illustrate the execution plan in the plan table. Next, display the contents of the plan table using the DBMS XPLAN.DISPLAY() function.

SELECT 
    PLAN_TABLE_OUTPUT 
FROM 
    TABLE(DBMS_XPLAN.DISPLAY());

Read: How to Check Oracle Database Version

Oracle create index on tablespace

In this section, we will learn and understand how to create an index on tablespace in oracle 21c.

A database is divided into one or more logical storage units, known as tablespaces, by Oracle.

What is tablespace in Oracle

Tables, indexes, big objects, and long data are all stored in a structure called a table space. They are used to arrange data in a database logically so that it can be stored in the appropriate places on a system. In database partition groups, table spaces are kept.

Give the name of the tablespace that will contain the index, index partition, or index subpartition in the tablespace field. If you ignore this clause, Oracle Database will create the index in the default tablespace.

Now we will discuss how to create an index on tablespace but before we will start first we need to create a tablespace.

Let us look at the query statement

CREATE TABLESPACE tbs_02
DATAFILE 'tbs030.dbf'
SIZE 1M;

A TABLESPACE has been created, as seen in the screenshot, and it contains one DATAFILE.

Once we got an idea of how to create a tablespace in oracle, let us discuss how we can create an index on tablespace in oracle 21c. So, here is the syntax of defining an instead of trigger in SQL Server.

create index <index_name> on <table_name> ( <col_1>, <col_2>, … ) tablespace tablespace_name;

Here is the table for the Customers in the pdb database on which the create index with tablespace query will be applied

Next, we will create an index by using the below query

create index first_indx_name
  on customers(first_name)
  tablespace tbs_02;

After that we will fetch the index by using the below query

SELECT 
    index_name, 
    index_type, 
    tablespace_name,
    visibility, 
    status 
FROM 
    all_indexes
    
WHERE table_name = 'CUSTOMERS';

This is how to create an index on tablespace in Oracle.

Read: How to get database name in Oracle

Oracle create index if not exists

• Here we will discuss how to create an index if it does not exist in Oracle.
• First, we will check if there is any index or not but Oracle automatically builds a new index for the primary key columns when you create a new table with a primary key.
• In this case, will create a new table in which we will not use the primary key.

For this, we need to understand the syntax to create an index in oracle 21c which is given below.

create index <index_name> on <table_name> ( <col_1>, <col_2>, … );

Now first we will create the table for our example execution and this sales_visits table is located in the pdb database.

Now we will fetch the index in oracle by using the below query

SELECT 
    index_name, 
    index_type, 
    visibility, 
    status 
FROM 
    all_indexes
    
WHERE table_name = 'sales_visits';

In this there is no index column available now we will add it and for this first, we will create the index by using the query

CREATE INDEX visit_indx_id
ON sales_visits(visit_id);

Now we will fetch the index in oracle by using the below query

SELECT 
    index_name, 
    index_type, 
    visibility, 
    status 
FROM 
    all_indexes
    
WHERE table_name = 'sales_visits';

Read: How to check database status in Oracle

Oracle creates index parallel

• In this section, we will discuss how to create an index parallel in oracle 21 c.
• We cannot instantly generate the necessary index in parallel when we add or enable a UNIQUE or PRIMARY KEY constraint on a table. Instead, of manually establishing an index on the required columns we can easily use the CREATE INDEX statement, and the proper PARALLEL clause, then adds or activate the constraint.
• Although parallel queries increase the efficiency of operations that modify data, the log file entries are written serially and may become a constraint.

Now we will discuss how to create an index parallel but before we will start first we need to create an index and tablespace by using the below query.

create index emp_indx_name on employees(emp_name)
TABLESPACE TBS_02

The parallel sort processes are used in the CREATE INDEX statement to create the emp_ indx_name index. The CREATE TABLE statement uses a parallel operation to choose from an existing employees table to create a new table of employees with a degree of parallelism of five.

In the previous topic, we created a tablespace named TBS_02. Next, we will check whether the tablespace exists or not by using the below query

SELECT * FROM DBA_TABLESPACES

Now we will use the parallel operations for index creation in oracle

create index emp_indx_name on employees(emp_name)
TABLESPACE TBS_02
parallel (degree 5) NOLOGGING;

Now we will fetch the index by using the below query

SELECT 
    index_name, 
    index_type, 
    visibility, 
    status, 
    degree
FROM 
    all_indexes
    
WHERE table_name = 'EMPLOYEES';

This is how to create an index parallel in oracle.

Read: How to create a table in Oracle

Oracle create index where clause

• Now let us discuss how to create an index using where clause in oracle.
• In order to help in query optimization, indexes are also utilized to help filter tables. Optimizing WHERE clauses are the most obvious example of this. Consider the statement “select * from customers where customer name = ‘Potter’.”

In this section, we will discuss the table that we have created which is “employees”

You may also like to read the following Oracle tutorials.

• How to add a foreign key in Oracle
• How to add a primary key in Oracle
• Connect Excel to Oracle Database

In this article, we have discussed how to create an index in oracle database 21c and also we covered the following given topics.

• How to create an index in oracle
• Oracle create index parallel
• Oracle create index on tablespace
• Oracle create index if not exists
• Oracle create index where clause