JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Chapter 7: Enhanced Indexing.


Referential Integrity and indexes (6th Feb 2002)

Function based indexes (6th Feb 2002)

Compressed Indexes (21st Oct 2001)

Function based indexes - clarification (1st June 2001)

Skip Unusable Indexes (2-May-2001)

Function based indexes and Subqueries (25th Feb 2001)

Function based indexes and direct load (16th Feb 2001)

Index Joins (9th Feb 2001)

Rebuilding indexes online




Back to Book Index


Referential Integrity and indexes (6th Feb 2002)

This note appears as a consequence of reading the bug fixes note that came with a recent upgrade to Version There are some bugs in 8.1.7 that are fixed in this release, so if you are running an earlier release, be careful.. Note - At the time of writing this addendum, I have not visited Metalink to read up the details of the bugs reported.

Bug 1388843 - Unique or primary key constraints enforced with non-unique compressed indexes could result in the insertion of rows with duplicate keys and raise Oracle error ORA-8102 on delete. This thus allows logical data corruption. The suggested workaround is to not use compressed indexes. I have not read the full details of the bug, but I presume the intent is that you should not use compressed indexes for this particular circumstance, rather than it being a guideline to avoid compressed indexes completely. Note that it is probably a bad idea to use compressed indexes in this case anyway since (except for mid-transaction when the constraint was deferred) you would expect the index to be unique at almost all times even though it had been declared as non-unique, therefore you would get the extra space costs due to the prefix storage, with none of the space benefits of prefix re-use.

Bug 1660471 - Delete cascade does not work when there is a reversed index defined on the foreign key column(s).  Deletion in the parent table does not cascade down to the child table. This can result in logical corruption.

Top of page


Function Based Indexes (6th Feb 2002)

This note appears as a consequence of reading the bug fixes note that came with a recent upgrade to Version There are some bugs in 8.1.7 that are fixed in this release, so if you are running an earlier release, be careful. Bear in mind that indexes with descending columns are also considered to be function-based indexes, so they also may run into these bugs. Note - At the time of writing this addendum, I have not visited Metalink to read up the details of the bugs reported.

Bug 1805146 (for which an alert also exists) - If you drop a functional index, the data dictionary (specifically col$) is not cleaned up properly, and this can lead to a corrupt export file. See note 148740.1 on Metalink for more details.

Bug 1950099 - Function-based indexes are not updated with new rows when SQL*Loader direct path is used. This leads to a table / index data mismatch. YOu options are to use normal load, or to rebuild the index after the load is complete.

Bug 2096565 - Queries on tables having descending indexes sometimes dump during runtime or give incorrect results. The column with the descending index has to appear in the query.

Bug 1664189 - Query rewrite does not occur if base table has a FUNCTIONAL index on it. (Is this a side-effect of the conflict between the required values for query_rewrite_integrity when using function based indexes and the required value for particular types of query rewrite ?)


Top of page


Compressed Indexes

I have had an interesting email conversation recently with Jared Still who has been doing some serious experimentation with compressing indexes. An important conclusion from his experimentation is worth repeating here - If you try to get maximum compression from your indexes by re-arranging the order of columns, then you seriously damage the performance of your application.

Remember - the best order of colums in an index is primarily dictated by the way in which your application accesses your data. Once you have confirmed a column order that results in the most effective use of the index in the most important cases, then it is safe to go for the extra benefit of compression. Do not swap column orders simply because it allows you to compress the index to an extreme degree. Remember also, that compression tends to make the index appear more desirable to Oracle from an I/O point of view so the CBO may choose a very small, but logical bad, index over a larger, but more CPU-effective index.

In passing, I have had a couple of questions about the figure 7-1 (p.110) showing the different storage characteristics of compressed and uncompressed indexes. Just for the record, these are only symbolic diagrams - in particular the content of a compressed index block is much more obscure than shown in the figure. In a compressed index block, each prefix is actually followed its list of 'tail-ends'.

Top of page


Function Based Indexes - a clarification

I have had a couple of email messages, and seen a few comments on the internet which highlight an ambiguity in my comments (p.122) about how function-based indexes worked. The offending phrase is 'Oracle creates a hidden column in the table definition (in SYS.COL$) with a name like SYS_NC0004$, and gives that column a default value - in this case, UPPER("NAME")...'.

This does not mean that Oracle adds an extra column to the physical table data, and wastes the space needed to populate. It is purely a kludge in the data dictionary that gives Oracle a position for holding a column name (the sys_nc0004$) that can be referenced in an index definition, and the text of a function (the upper("NAME")) that can be used when calculating the actual values to be stored as the physical data for the index.

Top of page


Skip Unusable Indexes

There is a reference to the parameter skip_unusable_indexes in the section on function-based indexes (p.127/8) that points out that when a function-based index has become invalid then you can still insert, delete, and update the table in ways which should affect the index if this parameter is set to true. There is a throwaway comment that select statements that want to use this index will, however, still fail. This was true in Oracle 8.1.5, but the behaviour has changed from Oracle 8.1.6+; if Oracle recognises that an index is unusable, and this parameter is set to true, then the optimizer will not consider the index as a possible path to the data, so queries will not fail they will simply operate through some other path. There are further comments on this change that appear twice in the addenda to Chapter 12 (Introducing Partitioning) and Chapter 13 (Using Partitioning).

Top of page


Function based indexes and subqueries

I recently received a note from Connor McDonald (www.oracledba.co.uk) who has been doing a lot of work with function-based indexes. He has discovered major problems when function-based indexes (whether built-ins or user-defined) can be used to resolve sub-queries. In this case, 'major' means "ORA-03113 end of file on communications channel", or an "ORA-07445" with core dump. Further details of this problem can be found on metalink by searching for bugs: 1308442, 1359122, 1363508 and 1408453. Fixes are apparently targetted for

Top of page


Function based indexes and direct load

I pointed out (p.123) that function-based indexes using built-in functions have an advantage of triggers in that SQL*Loader understands and maintains them - even when loading using the direct path option. The same is NOT quite true of function-based indexes on user-defined PL/SQL functions. If you use the normal path for SQL*Load, then everything works well; if you try to use the direct path SQL*Load crashes with the following error messages:

	Error calling col array build, block formatter
	ORA-03113: end-of-file on communication channel
	Error calling once/data file finishing
	ORA-03114: not connected to ORACLE
	Error opening cursor: [102]
	ORA-03114: not connected to ORACLE
	ORA-24323: value not allowed
	SQL*Loader-704: Internal error: ulHstRel: svcrh [-1]

This is logged with Oracle as bug number 860599, fixed in version 9.

Top of page


Index Joins

One of the new features of Oracle 8.1 is the index join, which hashes data from two or more indexes (p.136) to avoid visiting the table. However I had to admit that I could only get this working by using the /*+ index_join() */ hint. I have just realised why this was the case. There is a hidden parameter in 8.1 named _index_join_enabled, and this default to FALSE, even in version 8.1.7. So currently you have to use a hint if you want to get the optimizer to use an index join, unless you want to risk the usual 'not supported' cry by setting an undocumented parameter.

Top of page


Rebuilding indexes online:

This chapter introduces the mechanism of rebuilding index online (p.129, p.143). I discovered after going to press that there is a fairly serious bug in this feature, fixed in version that means you should generally avoid the feature unless you are very confident that no other transactions are changing the index at the time (in which case you might as well not use the online facility anyway). This item is also referenced from the addenda to chapters 5, 12, 13, and 14..

The bug is number 1475310 for those of you with access to Metalink; but in brief, the bug affects all releases of 8.1.5 and 8.1.6, as well as, and is only fixed in

The problem arises when an online index rebuild (which includes moving an IOT table online) is taking place and another process executes a transaction that affects the journal for the index being rebuilt. The UNDO (rollback) for this transaction will be corrupt. If the process (or the instance) crashes before it commits, then the corrupt UNDO will be applied either by pmon, or at instance recovery time. The effect of this is to leave the index inconsistent with the table (e.g. a table row exists for which there is no index row).

If you suspect the index is corrupt, you can use alter table xxx validate structure cascade; to check the consistency, but this introduces a table lock and could take some time. The problem can only be safely fixed by dropping and rebuilding the index. In fact, because Oracle is now capable of rebuilding one index by scanning another, the problem can propagate itself so that you may end up having to drop and recreate all the indexes on the table to clear the problem

Top of page.



Back to Book Index