Trending September 2023 # How To Alter And Drop Index In Oracle # Suggested October 2023 # Top 14 Popular

You are reading the article How To Alter And Drop Index In Oracle updated in September 2023 on the website Uyenanhthammy.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 How To Alter And Drop Index In Oracle

Introduction to Oracle Index

Hadoop, Data Science, Statistics & others

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.

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.

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:

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:

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:

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.

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:

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.

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.