The previous issue looked at the use of full-table scans in tuning Oracle databases. This issue discusses how and when to use indexes and some of the performance implications.
There are several types of indexes available with Oracle databases:
This article only discusses bitmap and normal (b-tree) indexes.
B-Tree Indexes are most appropriate when retrieving a small amount of data from a very large table. The bigger the table and the lower the number of rows that you want to retrieve then the more effective the index.
If you think of the printed material in the real world, this makes perfect sense, you wouldn't bother to create an index for a short article, but you would certainly want one for a lengthy reference book. The reasons are analogous too. When reading a short article it would take longer to find the areas of interest using the index then it would to read the whole article. It's the same when you're querying the database - for a small table with Oracle's multi-block read feature of Oracle, the whole table would be read in one go, so by going to the index first, the database would have to read more data overall.
Indexes may improve read access to data and update operations (including insert and delete) using sub-queries. However update performance will be impaired because every time a row is inserted into, deleted from or updated (if the indexed column(s) is/are updated) in the database, the indexes have to be adjusted accordingly, thereby increasing the amount of i/o that has to performed.
Therefore having many indexes is ideal when the database is mostly read only, but if there are a high proportion of inserts and deletes and only a few read operations, then you would find that adding more indexes would degrade rather than improve performance. You also need to ensure that the column(s) you are proposing to index is/are used frequently as the limiting conditions for a query, if not you will waste a lot of space and degrade database performance.
Another factor to consider with b-tree indexes in Oracle databases, is how specific they are - their cardinality. The ideal b-tree index is one that refers to only one row in the table, as a primary key does by definition. This reduces considerably the amount of i/o required to retrieve the data, but if the index value relates to many rows because it is not selective, then a lot more data will be retrieved resulting in unnecessary i/o.
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
--------------------------------------Projects over running, over budget or just failing to deliver? Learn 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 ?
Why not 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 of blood, sweat and tears 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.