Oracle High Performance Tuning for 9i and 10g

When considering indexes never be afraid of not indexing. On the contrary, do not always assume that an existing index should exist, simply because it does exist.

When considering use of unusual indexes such as clusters, bitmaps, and hashing be aware of their applications. The only index type amenable to data changes is a BTree index. All other index types are effectively read-only type indexes and are preferably never used otherwise. This is not always the case in some other relational databases but it is more often than not the case for Oracle Database. In most relational databases clusters, bitmaps, and hash indexes do not manage overflow well, if at all. Overflow in an index results in a bounce from the index block to an area outside of the original index structure, often negating the point of using the index in the first place.

Database administrators should always keep a watchful eye on indexing in a database. There is never really available time but when an application is released it is always best to reexamine all indexing. Quite often developers will create many indexes, sometimes each creating their own sets of indexes. The result can be overindexing. Too many indexes on a table will create a performance problem.

Be especially vigilant for the use of bitmap and function-based indexes. Bitmap indexes are generally only effective in read-only situations. Function-based indexes are nice but I have never seen the Optimizer use one effectively in a commercial environment. However, this is often because configuration parameters are inappropriately set. The parameters QUERY_REWRITE_ENABLED and QUERY_REWRITE_INTEGRITY must be set to TRUE and TRUSTED respectively to allow use of function-based indexes. Developers and database administrators tend to like function-based indexes and bitmaps, especially when they have just read about them in the manual. Be alert for these unusual indexes because both bitmap and function-based indexes are largely ineffective and can be exceedingly detrimental to performance. One particular case I remember where many bitmap indexes were used. The database was in excess of 200 Gb and had a fairly large combination online and reporting production database. When one of the bitmaps was changed to a BTree index, a process taking 8 h to run completed in less than 3 min. This case involved heavy DML activity on the table on which the bitmap index was created.

7.1.1 When Not to Use Indexes

There are some circumstances where indexes can be detrimental to performance and sometimes those indexes should not exist. The Optimizer will occasionally ignore indexes and consider reading the entire table a faster option.

• A table with a small number of columns may not benefit from an index if a large percentage of its rows are always retrieved from it.

• Small static data tables may be so small that the Optimizer will simply read those tables as a full table scan and ignore indexes altogether. In some cases the Optimizer may use an index on a small table where it should not since a full table scan would be faster. An exception to this rule will be in mutable joins where unique index hits are often used, even on small tables. If full table scans are faster than index reads in those mutable joins you might want to remove indexes from the small tables altogether or override with hints. Examine your small static data tables. Do they really need indexing?

  Tip

  A hint is a comment added to an SQL command allowing overriding of the Optimizer.

• Sometimes tables created for reporting or data warehousing may already be in the correct physical order, commonly if those tables are only ever appended to using mass insertions. Creating an index may be pointless in this type of situation since the data may already be in the required physical order.

• Indexes should usually be created on a small percentage of the columns in a table. Large composite indexes may be relatively large compared with the table. The relative size between index and table is important. The larger the ratio of index to table physical size then the less helpful the index will be in terms of decreasing physical space to be read. Also many columns not in the index may contain NULL values. It may be faster to read the entire table. Why create indexes?

• NULL values are generally not included in indexes. Do not index columns containing many NULL values unless there is a specific use for it such as filtering the column as NOT NULL, thus only scanning the index and the rows without NULL values.

7.1.2 Utilizing Referential Integrity Indexes

Referential Integrity uses primary and foreign keys to validate relationships between rows in related tables. Oracle Database does not automatically create indexes on foreign keys and it is advisable to do so manually.

Data models of different forms can have either single-column unique integer identifiers at one extreme or large composite-column indexes at the other extreme. It is quite often possible that application SQL code can make good use of Referential Integrity indexes. This is generally the case when the data model matches the functionality of the application well. This is especially true in two cases.

• Java object applications are often compatible with data model structure containing single-column unique integer identifiers, where the integers match Java object identifiers. This type of data model is often highly normalized and usually in great danger of being over-normalized. Alternate indexing is not common in Java top-down designed data models but can be used to validate non-identifying unique values such as names.

• More traditional key-structured data models containing multiple-column composite keys and indexes can also match application functionality well but not as often as Java applications object identifier structures. Typically these types of data models have difficulty avoiding extensive alternate indexing. Since composite indexes contain multiple columns alternate keys are not only additional in nature but are also used to apply different orders to primary and foreign keys, or even comprise additional, perhaps reordered subsets of those keys. The Oracle Database Optimizer is over time becoming more capable of matching SQL code with dissimilar index orders but there will always be limitations.

Any kind of reporting or data warehousing application requirements will cause requirements for alternate indexing. Any online application with any type of reporting will generally be inclined in this direction as well.

If Referential Integrity indexing can be used for general SQL code tuning then do so as much as possible. Ultimately fewer indexes will be required and thus applications will perform better in general.

Alternate and Secondary Indexing

Alternate indexing is often referred to as secondary indexing. Alternate indexing includes any indexes created against tables in a data model which are not part of Referential Integrity constraints. Quite often the need for alternate indexing is a mismatch between the data model and functionality required by applications. Sometimes unique indexes are required on specific columns in data models other than unique integer identifier columns in every table. The application could deal with this particular issue but it may be best to place unique constraints in the database for the same reasons that Referential Integrity is not placed in the application or in triggers. Once again excessive alternate indexing could indicate data model problems or simply a mismatch between data model and application requirements.