How come the SQL Optimizer is not giving me any index suggestions?
1. What does SQL Optimizer look for in a query to suggest indexes?
2. What can the user do to get more index suggestions, besides turning up the intelligence?
3. Is this limit the product can do or is there a user error that he is not using the product correctly?
4. Is it possible for the user to suggest the index to create and the SQL Optimizer test the suggestion and return whether or not the suggestion was better?
5. Are there any privileges that the user may not have that could be causing no suggestions to be returned?
1. Optimizer checks for the columns in the conditions inside the WHERE or HAVING clause; and also the columns referenced in the SELECT LIST or ORDER BY to consider if adding a index or a combination of indexes would help the data retrieval. It will consider how the columns are used in the conditions and start with those columns with higher probability in giving better performance, then try columns that are less likely to help performance. As long as there is quota left, it will continue to try different index combinations untill all combinations have been exhausted or or the quota is reached.
The quota is set in the Options | Index Generation. This might be set by default if using predefined settings but can be customized.
2. Turning up the intelligence level or increasing the quota can help. But this is only true when the index generation was stop because of the quota reached. If the index generation was stop because the program had exhausted all possible combination, increasing the intelligence level will not help.
3. If no indexes are suggestion it is not necessarily a limitation of the product or a mistake by the user. It may be that there are simply no index to suggest.
Note in some situations, how the SQL is written could retrict the use of indexes in general. In such case, Optimizer cannot give any alternatives and will return as 0 indexes found. In other cases, it is possible that running with different indexes still generate the same explain plan. Optimizer will only note alternatives that produce a different execution plan.
Example:
SELECT last_name FROM employees
WHERE commission_pct IS NULL
OR commission_pct NOT IN (.2, .1, .15);
Optimizer will consider an index on the column COMMISSION_PCT. But will the database be able to use the index even if it is created?
Look at how the column is used in the conditions. Given that it is in the IS NULL and NOT IN condition, there is no way for database to use the index. So no there will be no index alternative on COMMISSION_PCT.
4. There is an option on the toolbar to "Define User Index" if there in something that exists or has been manually created, and you would like to test. Click on the drop down arrow next to the "index" icon on the toolbar. Define the index here and it will display in the alternatives window below and can be tested like any other system generated rewrite or index.
Note this is a newer feature and may not be available in older versions of SQL Optimizer.
5. in order to return index suggestions, the user needs to have the "Create any index" privilege granted. Optimizer needs this to be able to create virtual indexes when it is testing scenarios to find suggestions. If it is not possible to grant the user the Create Any Index privilege a workaround is to grant the user an object privilege for speciifc objects he/she will be working with and where index may be created on.
