Oracle Create Index

In Oracle databases, an index refers to a data structure that advances the speed of data retrieval operations on a database table. However, this may come at a penalty of extra write operations and storage space on your database.

One example of where a database index would be useful is in a customer relationship management system.

In such a system, we can have a database table that stores customer information. This could include the name, address, payment methods, contact information, etc.

If the table holds many records, perhaps millions of them, it can take a long time and resources to search for specific customer information from the database. This is a negative phenomenon, especially in databases where performance is critical.

To circumnavigate this, we can use a database index.

For example, we can create an index on the customer’s name column that would allow the database system to quickly find and retrieve a specific customer’s information using the name. Therefore, instead of the database engine going through all the rows and columns in the table, it only uses the index to look up customer information.

In this tutorial, you will learn how to use the CREATE INDEX command in the Oracle database to initialize a new index.

Oracle Create Index Statement

The following shows the syntax of the CREATE INDEX statement in Oracle databases:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

The above syntax creates an index named index_name on the table with the name table_name using the specified columns (column1, column2, etc.) as the key for the index.

In Oracle, a primary key is a column or set of columns that uniquely identifies each row in a table. By default, Oracle automatically creates a unique index on the primary key columns of a table to enforce the uniqueness constraint and improve the performance of primary key lookups.

However, in some cases, you may need to create a new index for a specific table manually.

Let us look at some examples of how we can accomplish this.

Oracle Create Index Example

Suppose we have a table containing employee information as shown in the output below:

select first_name, last_name, salary, hire_date from EMPLOYEES;

Oracle Create Index for a Single Column

Suppose we wish to create an index using the first_name column. We can run a query as shown:

create index first_name_lookup on EMPLOYEES(FIRST_NAME);

This CREATE INDEX statement creates an index named first_name_lookup on the EMPLOYEES table, using the FIRST_NAME column as the key for the index. This index can be used to improve the performance of queries that search for employees by their first name.

Once we have the index created, we can use it to search for a specific employee as shown:

SELECT first_name, last_name, salary, hire_date
FROM employees
WHERE first_name = 'William';

Result:

Without the first_name_lookup index, the database system would have to scan the entire EMPLOYEES table to find all rows where the FIRST_NAME column is equal to ‘William.’ However, with the index in place, the database system can quickly look up the rows in the index using the ‘John’ value as the key and then retrieve the requested rows from the table, which will be much faster.

You can view the steps used when making the query using the explain plan command as shown:

explain plan for SELECT first_name, last_name, salary, hire_date
FROM employees
WHERE first_name = 'William';

Resulting query plan:

Example 2 – Oracle Create Index with Multiple Columns

Similarly, we can create an index comprised of more than one column in a given table. For example, suppose we want to create an index comprised of the first_name and last_name column.

We can use the code as shown:

create index multi_lookup on EMPLOYEES(FIRST_NAME, LAST_NAME);

This CREATE INDEX statement creates an index named multi_lookup on the EMPLOYEES table, using the FIRST_NAME and LAST_NAME columns as the key for the index.

Once created, we can use this index as shown in the sample query as shown:

SELECT first_name, last_name, salary, hire_date
FROM employees
WHERE first_name = 'William' AND last_name = 'Smith';

Resulting value:

And there, you have a method of speeding up your database queries by using indexes to limit the search scope.

Conclusion

The CREATE INDEX statement in Oracle allows us to create an index on a table to improve the performance of data retrieval operations. However, although indexes can improve query performance, they also incur storage space penalties, leading to reduced write speed operations, use them only when necessary.