Oracle SQL - check missing indexes on SQL query

Question

I need to check if in a query, index or indexes are missing.

For check indexes in a table I use syntax:

SELECT index_name
     , column_position
     , column_name
  FROM user_ind_columns
 WHERE table_name = 'table_name'
 ORDER BY index_name, COLUMN_POSITION;

 SELECT index_name 
 FROM user_indexes
 WHERE table_name = 'table_name';

It is possible to use a program or SQL script to find automatically if is missing indexes on a query and show it for creation them?

Thanks!

Answer 1

Not really. Checking the execution plan will tell you which indexes, if any, the optimizer will use; but if the plan shows no indexes it might be that

1. There are no indexes.
2. There are indexes but they cannot be used for the particular query.
3. There are indexes that can technically be used, but the optimizer calculates that they will not improve the performance.

In the case of #3, it is always possible that it is wrong (e.g. due to incorrect stats or a query beyond the optimizer's ability to model it accurately). If this is the situation you are looking for then there is no simple way to detect it.

You might look at dbms_sqltune for automated tuning suggestions. It'll generally tell you to gather stats, or sometimes suggest a SQL profile or a new index. You can call it from Enterprise Manager or from a script:

http://www.williamrobertson.net/documents/automated-sql-tuning-advice.html http://www.williamrobertson.net/documents/automated-sql-tuning-advice-sqlid.html

Answer 2

With an Oracle Autonomous Database you can enable Automatic indexing. Auto Indexing automates the index management tasks and eliminates the need to knowledge and expertise in creating, maintaining, and retiring/not-using indexes appropriately based on your workload patterns.

Enable Auto Indexing

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

Disable Auto Indexing

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

Refer to Auto Indexing in Oracle documentation for more details.