Indexes are the easiest way to improve the performance of long running queries with full table scans. Indexes allow the database to search the smaller indexes as opposed to searching the large table. This can improve not only the SELECT queries, but also UPDATEs and DELETEs.
Finding the most efficient index is difficult. Most indexes will be the default B_Tree type. This type of index has been in use for years and has been highly optimized.
Having too many indexes can slow down performance of UPDATEs and DELETEs. Use too few indexes, and all types of queries may run slower, even UPDATEs and DELETEs. If it were just a trade off, performance of UPDATEs and INSERTs versus performance of SELECTs, optimizing indexes in a database would be fairly easy. However, it is not that simple and finding the right balance is the challenge.
Learn secrets for finding balance when index planning:
http://oracle-tips.c.topica.com/maam5JAabQj2zbLGJrib/
*****************************************
Can you see your Oracle bottlenecks?
After several years of hard work, Ion for Oracle is finally available for general release. Ion is unlike any other Oracle tool on the market, a tool that encapsulates expert techniques to allow a DBA to quickly visualize important trends and signatures.
Predictive analytics if the key to repairing Oracle problems before they cripple your database, and I created Ion for Oracle to allow any DBA to quickly identify changing workloads and unobtrusive patterns of data access. Follow the link to try Ion for free:
http://oracle-tips.c.topica.com/maam5JAabQj2AbLGJrib/
Indexing....The Good, The Bad, and the Ugly
