JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Chapter 14: Index-Organized tables.


Compression in partitioned IOTs (16th Feb 2001)

Adding partitions (16th Feb 2001)

Direct loads

Moving tables online

Partitioned IOTs and partition maintenance


Compression in partitioned IOTs:

I mentioned the problem (p. 319) of ORA-00600 errors being raised too late if you exchange an IOT with a partition of a partitioned IOT and the compress factor of the indexes does not match. This is now listed as bug

Top of page


Adding Partitions:

When you add partitions to any type of partitioned table, you can specify the tablespace and storage parameters, for that new partition. Oracle 8.1.7 has, unfortunately, introduced a bug in this area when you try to add a partition to a partitioned IOT. The name of the tablespace is accepted, but ignored (bug 1541222). The partition is created either in the table-owner's default tablespace, or in the default tablespace specified for the table when it was first created.

You cannot work around this bug by changing the default attributes of the table either, an approach I have used in other cases and earlier versions of 8.1. e.g.:

	alter table test_bug modify default attributes tablespace ts_somewhere_else;
	alter index test_bug_pk modify default attributes tablespace ts_somewhere_else;

Attempting to alter the table results in error "ORA-25189: illegal ALTER TABLE option for an IOT" and attempting to alter the index results in error "ORA-25176: storage specification not permitted for primary key".

Of course, this is not too serious a problem - there is a work around, which is to create the partition and then move it (using the alter table move partition, (yes, table, not index). Since you have to do this anyway with indexes on ordinary partitioned tables, and secondary indexes on IOTs, this bug is usually more of an irritant than a serious defect.

Top of page


Direct loads:

There is a comment about IOTs being indexes, and therefore subject to the usual overhead when you use SQL*Load in direct mode (p.315). Typically the data is loaded into a temporary segment, an index is created on the new data, and the old index is then merged with the new index in a third segment. However, there is one circumstance where IOTs can be loaded incredibly cheaply. If there is no data in the table, and the incoming data is already sorted by the primary key, and there are no secondary indexes on the table, then a direct load, unrecoverable, with the sorted_indexes() option will load the data directly into the IOT with virtually no overhead.

Top of page


Moving tables online:

There is a mention of moving an IOT online (p315) in this chapter. This is the equivalent of rebuilding an index online, and there is a fairly serious bug with this feature, described in the addenda to chapter 7, that is not fixed until version

Top of page


Partitioned IOTs and Partition Maintenance:

I raised the problem of partition maintenance causing "ORA-04020 library cache deadlock" errors (p.320) . The situation has been improved somewhat in Oracle 8.1.7 -you can still get a deadlock if you try to split a partition in one session, then start to query the table in another; however it is now possible to add or drop a partition without causing a deadlock to occur in the query session. Of course, in either case, the querying session has to wait for the table definition to become stable (i.e. for the partition maintenance stage to complete) before it can return to the user.

By the way, the method I used to determine the causes of this problem was to create a before alter and after alter triggers on my schema that called a SYS-owned procedure to select and log the parse lock and object information from x$kgllk, and x$kglob. (My thanks of Stephane Faroult of Oriole Corp. for this idea).

Top of page


Back to Book Index