In Oracle, an index is a database object that creates and stores records for all values in specific columns or clusters. With the help of indexes, users can access the necessary data portion much faster and easier.

Contents

• Types of indexes in Oracle
• Oracle CREATE INDEX statement
  • How to create a Normal Index
  • How to create a Function-Based Index
• Rename an Index in Oracle
• Delete an Index in Oracle
• Manage Indexes in Oracle with GUI tools
• Conclusion

Indexes are among the most popular means of Oracle database performance tuning. But they are not a “silver bullet” for all cases.

They are applicable in SELECT statements, where the usage of indexes can improve the overall performance significantly. As for the INSERT, UPDATE, and DELETE commands, the effect is the opposite – indexes slow the operations down.

That’s why it is important to understand indexes in Oracle to apply them correctly.

Types of indexes in Oracle

Oracle defines two types of indexes: the B-Tree (Balanced Tree) Index and the Bitmap Index.

B-Tree Index is the default Oracle index created whenever we use the CREATE INDEX command. It compiles a list of values divided into ranges and associates a key with a single row or range of rows. This structure works efficiently in a majority of scenarios, including both the exact match and range of searches.

In its turn, a B-Tree Index is divided into:

• Normal Index. It is the most common type created if a user does not specify any additional parameters. In particular, Oracle creates it automatically for the primary key column whenever you create a new table with the primary key.
  Note: Oracle won’t create an index for the columns with foreign keys.

• Function-Based Index. It is an index that calculates the result of a function involving one or more table columns (an arithmetic expression, an SQL function, a PL/SQL function, or a package function). The results are stored in the index. It is convenient when you use queries with expressions multiple times. The database must calculate that expression each time, but a Function-Based Index with the same expression lets you avoid those computations.

Bitmap Index is an index type used in scenarios with repetitive values. For instance, a traditional B-Tree index would be too expensive for data warehouses, but Bitmap indexes will save space. In addition, Bitmap indexes work best with complicated queries containing WHERE clauses with multiple conditions, reducing the response type. 

Besides, Oracle differentiates unique and non-unique indexes.

• Unique Index. Key column(s) can’t have duplicate values. The simplest example is the staff database for any organization – two employees can’t have the same ID. Then, the row ID is specific for each data value in this index.

• Non-unique Index. Indexed column(s) can have duplicate values. For instance, several employees can have the same first names. Thus, the respective column may contain duplicates. The row ID will be in the key in sorted order. Non-unique indexes are sorted by the index key and the row ID.

Both the unique and non-unique indexes are the B-Tree index structure versions. By default, the B-Tree index is non-unique. To create unique index in Oracle, you need to use the UNIQUE keyword in the CREATE INDEX statement.

And now, let’s proceed to the process of creating different indexes in Oracle.

Oracle CREATE INDEX statement and how to apply it

Before starting to look for the best way to create index on table in Oracle, you should answer one question. Do you need to create that index at all? As we already know, indexes are helpful to speed up access to data, but they are not universal. Besides, indexes consume physical storage.

Oracle uses and maintains indexes automatically and stores them separately. Indexes are independent of the tables – you can create or drop them whenever you need – it won’t affect the tables and other indexes. On the other hand, the more indexes you have and the larger they are, the more storage they will take.

When you alter the table by inserting or deleting rows, all indexes related to that table must be updated. And any change in the indexed column also requires updating the index. The data maintenance costs only grow.

There are conditions to check when you want to create an index on a table in Oracle:

• Do you query the particular column often?
• Is there a UNIQUE key integrity constraint existing on the column?
• Is there an integrity constraint existing on the column?

If the answer is “no,” creating an index won’t improve performance. In any case, you should analyze the possible benefits you get from applying indexes and compare them with the possible troubles of the data and index updating.

Create a Normal Index in Oracle for one or several columns

As we have already defined, the default index created in Oracle is a non-unique B-Tree index. To create a new one, we need to apply the CREATE INDEX command with the below syntax:

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

This statement is the simplest form of syntax. The parameters are as follows:

index_name – the name of the index you’ll create
table_name – the name of the table for which you are creating that index
column1, column2, … columсnN – the table columns to include in that index

If you want to make Oracle create table unique index, you should modify that basic command syntax in the following way:

CREATE [UNIQUE] INDEX index_name
  ON table_name (column1, column2, ...columnN);

In this statement, the keyword UNIQUE defines that the indexed columns must contain unique combinations of values.

One more parameter used in the CREATE INDEX statement is COMPUTE STATISTICS. It is an optional parameter telling Oracle to collect the statistics during index creation. Further, this stats data would serve when choosing the “plan of execution” of SQL statements. However, since Oracle 10, the statistics is collected by default. Thus, you don’t have to add this parameter additionally.  

Now, let us have a look at some practical examples. We want to tell Oracle create non-unique index for one column.

Our sample database belongs to the organization that offers tickets to the Olympic Games and shares some other related information. We want to check the ticket prices. There will surely be the same prices for the tickets from specific categories.

The command to create non-unique index in Oracle is as follows:

CREATE INDEX OLYMPIC_GAMES.UK_TICKET_PRICE
ON OLYMPIC_GAMES.TICKET (PRICE);

To illustrate the execution of commands, we’ll use dbForge Studio for Oracle. A part of this software IDE is a multi-functional PL/SQL Code editor that simplifies all coding tasks. Pay attention to colorizing the statement parts, as it improves the code readability greatly, whether you deal with a simple or a sophisticated query.

The next time you retrieve the information about tickets and refer to the prices, it will be faster and easier due to having an index on that column.

If you want to create a unique index, use the modified command:

CREATE UNIQUE INDEX OLYMPIC_GAMES.UK_TICKET_TID
ON OLYMPIC_GAMES.TICKET (SID);

Indexes can be created on several columns. You can include as many columns in the index as necessary. In our example, we want a new index that will include ticket prices and ticket categories.

CREATE INDEX OLYMPIC_GAMES.UK_TICKET_PRICE
ON OLYMPIC_GAMES.TICKET (PRICE), OLYMPIC_GAMES.TICKET (CAT);

With the help of that index, you can retrieve the ticket information according to several criteria at once, thus obtaining the results faster and causing less load.

Create a Function-Based Index in Oracle

We use Function-Based indexes to improve the performance of queries that contain functions in the WHERE clauses. To tell Oracle create function based index on your table, use the below syntax:

CREATE [UNIQUE] INDEX index_name
ON table_name (function1, function2, ... functionN);

UNIQUE is an optional keyword specifying that we want an Oracle unique index created on the column.
index_name is the name of the index we will create
table_name is the name of the table for which we will create this index
function1, function2, … functionN are the functions we will use in the index

Let’s get back to our ticket sales database. It has a column with the countries. Assume we want to convert the country names to lowercase before we retrieve them. In this case, having a Function-Based index on that column will help us do that more efficiently.

The SQL statement would be as follows:

CREATE INDEX OLYMPIC_GAMES.UK_COUNTRY
ON OLYMPIC_GAMES.UK_COUNTRY (LOWER(CNAME));

Whenever we want to get the list of converted names, we can use this index and get the necessary data much faster.

How to rename an index in Oracle

Quite often, we need to change the name of the index. This operation is common in Oracle. If the user has the ALTER right for an index (if not, check it with the administrators), the task is straightforward.

The Oracle alter index rename command is as follows:

ALTER INDEX current_index_name
RENAME TO new_index_name;

In this statement,
current_index_name specifies the name of an existing index we want to rename
new_index_name specifies a new name of an existing index

For example, we want to change the OLYMPIC_GAMES.UK_COUNTRY_CNAME index:

ALTER INDEX OLYMPIC_GAMES.UK_COUNTRY_CNAME
RENAME to INDCNAME;

This way, we rename index in Oracle – it is a simple operation. Note that the index renaming does not affect the columns and tables in any way.

How to delete an index in Oracle

The DROP INDEX command in Oracle allows the users to delete any existing index from the current database schema.

It won’t affect the table physically because indexes are independent objects and are stored separately. Still, check if you might still use that index in some queries. In that case, the queries using the dropped index will take longer.

The statement to drop Oracle index is the following:

DROP INDEX [schema_name.]index_name;

index_name specifies the name of the index you want to delete
schema_name specifies an optional parameter. If it is absent, Oracle considers that you want to delete an index from your current schema.

In the previous examples, we created both unique and non-unique indexes. If you want to drop unique index Oracle, you don’t need to specify that index type. The index name is enough for the command:

DROP INDEX OLYMPIC_GAMES.UK_COUNTRY;

One thing to note is that you can’t drop a non-existing index – Oracle will produce an error. Thus, it would be helpful to have a variant of the Oracle DROP INDEX IF EXISTS statement. Unfortunately, Oracle does not support the IF EXISTS option. Pundits recommend Oracle users check for the index they want to delete separately before executing the drop command.

Note that dropping the table also deletes all the corresponding triggers and indexes automatically.

Manage Indexes in Oracle with GUI tools

Each database has lots of indexes, and managing them takes plenty of time if this work is not automated.

Powerful modern GUI tools, such as dbForge Studio for Oracle, let the users create and edit indexes in a visual mode (among the many other database-related tasks).

dbForge Studio for Oracle speeds up such tasks and ensures the highest quality of the code. The PL/SQL Formatter detects any syntax errors and beautifies the code. All types of database-related jobs become much more straightforward with this and many other features of this powerful GUI solution.

Conclusion

Indexes are standard schema objects in all popular relational database management systems due to their ability to make task performance more efficient and save server resources. Access to the necessary data portion at once, directly, without scanning the entire table is helpful in all work scenarios.

We used dbForge Studio for Oracle to illustrate this article. It lets you work with indexes in Oracle in a visual mode, but it is not the only benefit of the Studio – it helps you with any tasks related to Oracle databases. The fully-functional Free Trial of the software is available for you to test all its capacities appropriately.