Updated June 8, 2023
Introduction to Oracle Index
The index in Oracle can be defined as a schema object which stores an entry for each value that appears in the columns and for each value also the location of the rows that have that value which helps the database in improving efficiency as it helps the database to provide fast access to those rows which have a particular data. It can be termed as a performance tuning mechanism as it allows faster retrieval of data from Oracle database.
Syntax
In this section of the article, we will discuss how we can create an index in the Oracle database. Let us take a look into the syntax of how to create an INDEX in Oracle first.
CREATE INDEX index_name
ON table_name(column1,column2,...,columnN);
Parameters
Below are the Parameters:
index_name: It refers to the name we want to give to the index we are creating.
table_name: It refers to the name of the table on which we want to create an index.
column1, column2,…, column: It refers to the column or columns to use in the index.
Examples to Implement Oracle Index
In order to have a better understanding, we will take a few examples.
Example #1
In the first example, we will try to create an INDEX for the table employee but for only one column. We will create an INDEX for the column name in the employee table.
Code:
CREATE INDEX employee_index
ON employee (name);
Output:
Explanation: As we can see in the screenshot, the INDEX has been created successfully.
Example #2
In the second example, we will create an INDEX for more than one column. In this example, we will create an INDEX for columns city and vehicle id present in the table employee. Let us look at the query.
Code:
CREATE INDEX employeeCity_index
ON employee (city, vehicle_id);
Output:
Explanation: As we can see in the screenshot, the INDEX has been successfully created.
Example #3
In the third example, we will create a FUNCTION BASED INDEX. In this example, we will create an INDEX based on the employee table’s lowercase evaluation of the column NAME.
Code:
CREATE INDEX employee_name
ON employee (LOWER(name));
Output:
Explanation: As we can see in the screenshot, the FUNCTION BASED INDEX has been successfully created.
How to Alter Index in Oracle?
In this section, we will check how to ALTER an index. One important point to note is that the schema must contain the INDEX which we want to alter, and also, the user must have the right to ALTER the INDEX. In general, these type of rights is usually present with the DBA team.
We can do many types of activities using ALTER INDEX.
1. Rename An Index
In this case, we will change the name of an existing INDEX that is already present in the schema. Let us look at the SYNTAX for the same.
Code:
ALTER INDEX currentindex_name
RENAME TO new_indexname;
Parameters:
currentindex_name: It refers to the current name of the index which we want to alter
new_indexname: It refers to the new name which we want to give the INDEX.
Code:
ALTER INDEX EMPLOYEE_NAME
RENAME TO EMPLOYEE_IND;
Output:
Explanation: As we can see in the screenshot, the INDEX has been altered successfully.
2. Making an Index Invisible
In this case, we will make an existing INDEX that is visibly invisible. In this example, we are going to make the INDEX EMPLOYEE_IND invisible. Let us look at the query.
Code:
ALTER INDEX EMPLOYEE_IND INVISIBLE;
Output:
Explanation: As we can see in the screenshot, the INDEX has been altered successfully.
3. To Monitor Index Usage
In this case, we will monitor the index so that the customer or user knows whether the INDEX is being used; f not, it can be dropped by the user. Let us look at the syntax.
Code:
ALTER INDEX index_name MONITORING USAGE
Parameters:
Index_name: This refers to the INDEX which we want to monitor.
Code:
ALTER INDEX EMPLOYEE_IND MONITORING USAGE;
Output:
Explanation: As we can see in the screenshot, the INDEX EMPLOYEE_IND has been altered successfully.
How to Drop an Index in Oracle?
In this section, we will discuss how to DROP an INDEX. The important point is that we need to have permission to perform this activity, and also, the INDEX should be present in the database. When we execute the drop statement, the database invalidates all objects that depend on the table with which the INDEX was associated.
Code:
DROP INDEX EMPLOYEE_IND;
Output:
Explanation: As we can see in the screenshot, the INDEX has been dropped successfully.
Conclusion
In this article, we discussed the INDEX in Oracle. We started with the definition of the INDEX, and then we discussed how we could create, alter, and drop indexes in Oracle. All scenarios were discussed with the help of examples.
Recommended Articles
This is a guide to Oracle Index. Here we discuss an introduction to Oracle Index, and how to alter and drop it with query examples. You can also go through our other related articles to learn more –
