EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials Oracle Tutorial Oracle Index
 

Oracle Index

Priya Pedamkar
Article byPriya Pedamkar

Updated June 8, 2023

Oracle Index

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

Oracle Index1

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:

Oracle Index2

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:

function based index

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:

Rename

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:

employee IND

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:

Monitor Index Usage

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:

Drop

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 –

  1. Oracle Versions
  2. Oracle Clauses
  3. Oracle String Functions
  4. Career in Oracle

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW