Oracle Database query indexes on table

When you are developing SQL code in Oracle it is important to keep in mind performance. One of the first things you need to keep in mind when developing code or optimizing it is if you are using all indexes that are available and applicable. Recently someone asked me the question how you can check which indexes are available for a certain object in the database via a query.

The answer is quite simple, all indexed columns of the tables are registerd under the table dba_ind_columns. Here you quickly check which columns are registerd to have an index for a certain table. If, for example, you would like to know which indexed columns are available for “mtl_system_items_b” in your database you should execute the below query:

SELECT 
      *
 FROM 
     dba_ind_columns
WHERE table_name = UPPER('mtl_system_items_b')

This will give you all the information needed on the Oracle database indexes against mtl_system_items_b