Creating an index can certainly do marvellous things to SQLs. Even after all those years in the field, it is still always thrilling for me to witness huge performance improvement seen after applying a suitable index to an unturned query. The performance gain is simply spectacular and the notices slump in resource consumption this query is taking (I/O, CPU, etc) is immediate.
Nevertheless, adding an index does have its cost. Good things never come for free. Every index occupies storage, and each index has a side effect on DMLs, causing some overhead (Oracle need to updated index block, therefore must read them before, from memory or from disk).
I often see cases when indexes are created too often, tables end up with dozen indexes and more, and yet, SQL are still not tuned.
Moreover, before applying an index, you should always ask yourself whether this SQL worth adding an index. Sometimes it could be SQL which runs only few times a day, and the way it performs is good enough for its customers. It does a Full Table Scan? so what. If it runs only once a day, during night, and takes 2 minutes, than this could be just fine as it is. No need to add extra index just for this once a day execution.
So basically, every situation needs to be carefully examined, then according to the required needs, decide whether it is worthwhile adding an index to improve SQL performance.
Another important advice before applying a new index is to tune the text itself (assuming you can change the text and it is not coming from some third-party product), i.e:
- Understand what the SQL is aimed to do. Ask yourself (and go back to the application if necessary) what is it that this SQL is doing. Being able to say it in your own words makes it easier to understand it, then to rewrite it.
- Make sure there are no unnecessary joins. This is extremely important. You will be surprise to know how many times I have identified unnecessary joins in SQLs written by application, seeing join with large tables without a reason. Just imagine what a needless join with a table that has 18M rows can do to SQL performance (small clue: really bad things).
- Make sure all join predicates exist (to avoid Cartesian joins)
- Make sure all needed filter predicates exists (where clauses match the relevant conditions). Apply filters which yield the smallest result set as possible.
Of course there are many more issues with writing a tuned SQL, though this post will not cover them all.
Then, if, and only if, text is 100% tuned, go ahead and search whether a suitable index can improve query performance.
Yet, remember that though you are tuning a specific SQL, a new index can and probably will influence other SQLs (not just DMLs but also other queries) for better or for worse. It’s always a tradeoff.
Therefore, before you adjust the index to your system:
Try to identify what will be its influence on other SQLs running on the system: which SQLs will be influence and how will they be influence by the adding the new index (there are tools which do that). Of course, whenever possible, first apply the index in your testing environment, which resembles production as much as possible (load, schema, data), and examine index influence on your specific SQL, on other queries, and on DMLs.
Merav Kedem,
Founder of
DB OPTimize
Oracle Performance Tuning & DBA Consulting