In this tutorial, we will learn about the different dictionary views on Indexes in Oracle database.
Indexes are stored in Oracle database as Database Objects. We can get more information about the different indexes present in a user schema by accessing these data dictionary views. We will see some of these data dictionary views and how they describe the indexes on all tables in database.
For getting information about all the indexes present in user schema, we use this query:
SELECT * FROM USER_INDEXES;
Result:
It gives us a list of all indexes and relevant metadata associated with it in a user schema, in this case, HR schema.
To specifically get information about the column on which index has been created, we use the following data dictionary view:
SELECT * FROM USER_IND_COLUMNS;
Result:
This data dictionary view gives us information about the name of index, name of table and name of column but some of the other metadata information is missing in this view.
If we need all the relevant information about Indexes from the above 2 data dictionary views then we take a natural join of these 2 views. We use the following query to take a natural join of 2 data dictionary views and filter results based on just one table:
SELECT INDEX_NAME, TABLE_NAME, INDEX_TYPE, TABLE_OWNER, UNIQUENESS, COLUMN_NAME, COLUMN_POSITION FROM USER_INDEXES NATURAL JOIN USER_IND_COLUMNS WHERE TABLE_NAME = 'EMPLOYEES'
Result:
This Natural Join query combines the two data dictionary that we saw above to give us all relevant columns that give most important information about the Indexes in ‘Employees’ table.
If there are any function based indexes in our Schema then we can use the following data dictionary view to get more information about it:
SELECT * FROM USER_IND_EXPRESSIONS;
Result:
As of now there are no function based indexes in our schema.
Similarly, the following query can give us more information about the Function based Indexes in your schema if they exist.
SELECT INDEX_NAME, TABLE_NAME, COLUMN_POSITION, INDEX_TYPE, TABLE_OWNER, TABLE_TYPE, UNIQUENESS, COLUMN_NAME, COLUMN_EXPRESSION FROM USER_INDEXES NATURAL JOIN USER_IND_COLUMNS NATURAL JOIN USER_IND_EXPRESSIONS;
In this tutorial, we learned about the different data dictionary views which give us all the information we need about different types of Indexes in our schema.