As mentioned before the ideal index will refer to just one row in the table and the same rule applies to concatenated indexes. With concatenated indexes, therefore, the column that is used most frequently in the limiting conditions of queries should be the most selective and should be the leading column in the concatenated index because this will have the most impact on the biggest number of queries.
If the leading column of a concatenated index is not the most selective and not the one used the most often in queries, then the application should be redesigned to ensure that this is the case to avoid performance problems or the index should be replaced with a better one.
If the leading column is the most selective and the most frequently used in the limiting conditions then the second and subsequent columns of the index will be of most benefit if they are the next most selective and the next most frequently queried. Assuming that these other columns are frequently referenced together with the leading column, there will be significant performance advantages by creating a concatenated index instead of two separate indexes, because Oracle will have to perform less i/o and won't need to merge the results from the two separate index reads, resulting in less filtering.
If your Oracle application is designed to query on non-selective columns (i.e. those with low cardinality), then you should consider the use of bitmap indexes, but only when the data is infrequently updated as bitmap indexes add a considerable overhead to update operations - each new value of the indexed column will require a whole new bitmap to be created and deletes and updates of the indexed column will also require the bitmaps to be adjusted, as there is one bitmap for each distinct value in the column.
Another disadvantage of bit-map indexes in a transaction processing environment is that each update would lock a large number of rows, degrading throughput even further. Where bitmap indexes are most valuable is in a data warehouse where non selective columns in large tables are frequently queried and where little or no updating is done.
In summary, to obtain maximum performance from your Oracle databases you need to consider how much data will be retrieved from each of the tables; how selective the indexes are; and how frequently the indexed columns are queried compared to the frequency of update of the same columns before deciding whether or not to use an index. For b-tree indexes, the fewer rows being selected by the query, the bigger the performance increase would be.
Generally if you're reading a small percentage (1-10%) of a very large table, your Oracle database will have much better performance if the data is accessed via an index scan of a b-tree index followed by table lookup, rather than a full table scan. If a query will access more than 1-10% of the table then a full table scan may produce better performance but you would need to test this under real-world conditions. One last thing to remember is that the more indexes there are then the longer updates and inserts will take.
See the Oracle resources section for other web sites and recommended books etc.
For links to more tips and tricks on Oracle see the Oracle Tips & Tricks page.
Are your Oracle developments over running, over budget or just failing to deliver? Discover how to develop your Oracle systems better, faster and smarter with high quality Oracle training from our top consultants . See the Oracle training page for more info.
Looking to sky-rocket productivity, save time and reduce costs ? Learn the short cuts with help from our top consultants . Contact us for more info.
Looking for more Oracle tips and tricks? Subscribe to our ezine. Published monthly, it's jam-packed full of tips and tricks to help you make more of your Oracle systems and save you hours searching for information. Subscribe today and your first issue will soon be winging its way to your mailbox.
Smartsoft Computing Ltd
Tel: 0845 0031320
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
© Copyright Smartsoft Computing Ltd 2001-2009. All rights reserved.