7
Managing Index-Organized Tables

This chapter covers the following topics:

• Overview of Index-Organized Tables

• Features of Index-Organized Tables

• When to Use Index-Organized Tables

• Example

Overview of Index-Organized Tables

An index-organized table--in contrast to an ordinary table--has its own way of structuring, storing, and indexing data. A comparison with an ordinary table may help to explain its uniqueness.

Index-Organized Tables versus Ordinary Tables

A row in an ordinary table has a stable physical location. Once it is given its first physical location, it never completely moves. Even if the row is partially moved with the addition of new data, there is always a row piece at the original physical address--identified by the original physical rowid--from which the system can find the rest of the row. As long as the row exists, its physical rowid does not change.

When you index a column in an ordinary table, the newly created index stores both the column data as well as the rowid.

A row in an index-organized table does not have a stable physical location. An index-organized table is, on the one hand, like an ordinary table with an index on one or more of its columns. It is unique, however, in that it holds its data, not in stable rows, but in sorted order in the leaves of a B*-tree index built on the table's primary key. These rows may move around to retain the sorted order. An insertion, for example, can cause an index leaf to split and the existing row to be moved to a different slot, or even to a different block.

The leaves of the B*-tree index hold the primary key and the actual row data. Changes to the table data--for example, adding new rows, or updating or deleting existing rows--result only in updating the index.

See Also: For more information on B*-tree indexes, see Oracle8i Concepts

Advantages of Index-Organized Tables

Because they store rows in the B*-tree index based on the primary key, index-organized tables offer the following advantages over ordinary tables:

Fast access to table data for queries involving exact match and/or range search on a primary key

Once a search has located the key values, the remaining data is present at that location. There is no need to follow a rowid back to table data, as would be the case with an ordinary table and index structure. The index-organized table thus shows its efficiency by eliminating one I/O, namely, the read of the table.

Best table organization for 24x7 operations

When your database must be available 100% of the time, index-organized tables provide the following advantages:

• You can reorganize an index-organized table or an index-organized table partition (to recover space or improve performance) without rebuilding its indexes. This results in a short reorganization maintenance window.

• You can reorganize an index-organized table online. This and the ability to perform online reorganization of secondary indexes eliminates the reorganization maintenance window.

Reduced storage requirements

This is because the key columns are not duplicated in both the table and the index, and because no additional storage is needed for rowids.

Figure 7-1 Conventional Table and an Index versus Index-Organized Table

Features of Index-Organized Tables

You can move your existing data into an index-organized table and do all the operations you would perform in an ordinary table. Some of the features now available to you in using index-organized tables include the following.

Same Support for Alter Table Options as in Ordinary Tables

All of the alter options available on ordinary tables are now available for index-organized tables. This includes ADD, MODIFY, and DROP COLUMNS and CONSTRAINTS. However, the primary key constraint for an index-organized table cannot be dropped, deferred, or disabled.

Logical ROWID Support

Because of the inherent movability of rows in a B*-tree index, a secondary index on an index-organized table cannot be based on a physical rowid which is inherently fixed. Instead, a secondary index for an index-organized table is based on what is called the logical rowid. A logical rowid has no permanent physical address and can move across data blocks when new rows are inserted. However, if the physical location of a row changes, its logical rowid remains valid.

A logical rowid includes the table's primary key and a guess which identifies the block location of a row at the time the guess is made. The guess makes rowid access to non-volatile index-organized tables comparable to access of ordinary tables.

Logical rowids are similar to physical rowids in the following ways:

• Users can select ROWID from an index-organized table and access the rows using WHERE ROWID = <value predicate>.

• The access through the logical rowid is the fastest possible way to get to a specific row, even if it takes more than one block access to get it.

• Logical rowid of a row does not change as long as the primary key value does not change. However, unlike the physical rowid which remains immovable through all updates, the logical rowid can move when hew rows are inserted.

Oracle 8i, release 8.1, introduces a single datatype, called universal rowid, to support both logical and physical rowids.

Applications which use rowids today may, if using index-organized tables, have to change to use universal rowids, but the changes are simpler due to the availability of UROWID datatype. This allows applications to access logical and physical rowids in a unified manner.

For more information: See "Declaring a Universal Rowid Datatype".

Secondary Index Support

Secondary indexes on index-organized tables differ from indexes on ordinary tables in two ways:

• They store logical rowids instead of physical rowids. Thus, a table maintenance operation such as ALTER TABLE MOVE does not make the secondary index unusable.

• The logical rowid also includes a guess that provides a direct access to the primary key index leaf block. If the guess is correct, a secondary index scan would incur a single additional I/O once the secondary key is found. The performance would be similar to that of a secondary index-scan on an ordinary table.

Both unique and non-unique secondary indexes, as well as function-based secondary indexes, are supported. However, bit-mapped secondary indexes on index-organized tables are currently not supported.

LOB Columns

You can create internal and external LOB columns in index-organized tables to store large unstructured data such as audio, video, and images. The SQL DDL, DML, and piece-wise operations on LOBs in index-organized tables exhibit the same behavior as in ordinary tables. The main differences are:

• Tablespace mapping--By default (or unless specified otherwise), the LOB's data and index segments are created in the tablespace in which the primary key index segment of the index-organized table is created.

• Inline vs. Out-of-line storage--By default, all LOBs in index-organized tables created without an overflow segment are stored out-of-line (that is, the default storage attribute is DISABLE STORAGE IN ROW). Specifying an ENABLE STORAGE IN ROW for such LOBs, will result in an error. However, LOBs in index-organized tables with overflow segments have the same characteristics as those in ordinary tables.

Other LOB features--such as BFILEs, temporary LOBs, and varying character width LOBs--are also supported in index-organized tables. You use them as you would ordinary tables. Support for LOBs in partitioned index-organized tables is not currently available.

Parallel Query

Queries on index-organized tables involving primary key index scan can be executed in parallel. However, parallel execution of secondary index-only scan queries is not yet supported.

Object Support

Most of the object features are supported on index-organized tables, including Object Type, VARRAYs, Nested Table, and REF Columns. However, you cannot create object tables (TABLE OF <object type>) as index-organized tables.

SQL*Loader

This utility supports both ordinary and direct path load of index-organized tables and their associated indexes (including partitioning support). However, direct path parallel load to an index-organized table is not supported. An alternate method of achieving the same result is to perform parallel load to an ordinary table using SQL*Loader, then use the parallel CREATE TABLE AS SELECT option to build the index-organized table.

Export/Import

This utility supports export (both ordinary and direct path) and import of non-partitioned and partitioned index-organized tables.

Distributed Database and Replication Support

You can replicate both non-partitioned and partitioned index-organized tables.

Tools

The Oracle Enterprise Manager supports generating SQL statements for CREATE and ALTER operations on an index-organized table.

Key Compression

Key compression allows elimination of repeated occurrences of key column prefixes in index-organized tables and indexes. The salient characteristics of the scheme are:

• Key compression breaks an index key into a prefix entry and suffix entry. Compression is achieved by sharing the prefix entries among all the suffix entries in an index block.

• Only keys in the leaf blocks of a B*-tree are compressed. Keys in the branch blocks of a B*-tree are still suffix truncated but not subjected to key compression.

When to Use Index-Organized Tables

There are several occasions when you may prefer to use index-organized tables over ordinary tables.¹

When You Want to Avoid Redundant Data Storage

For tables, where the majority of columns form the primary key, there is a significant amount of redundant data stored. You can avoid this redundant storage by using an index-organized table. Also, by using an index-organized table, you increase the efficiency of the primary key-based access to non-key columns.

When Developing VLDB and OLTP Applications

The ability to partition an index-organized table on a range of column values makes the use of index-organized tables suitable for VLDB applications.

One major advantage of an index-organized table over an ordinary table stems from the logical nature of the index-organized table's secondary indexes. After an ALTER TABLE MOVE and SPLIT operation, global indexes on index-organized tables remain usable because the index rows contain logical rowids. In the case of ordinary tables, by contrast, these operations result in making the global index unusable, requiring a complete index rebuild, which can be very expensive.

Similarly, after an ALTER TABLE MOVE operation, local indexes on index-organized tables are still usable. On the other hand, for ordinary tables, the MOVE operation results in making a secondary local index unusable.

The partition maintenance operations described above do make the local and global indexes on index-organized table perform slower as the guess component of logical rowid becomes invalid. However, the indexes are still usable via the primary key-component of the logical rowid.

In addition, the ALTER TABLE MOVE operation can be done on-line. This functionality makes index-organized tables ideal for applications requiring 24X7 availability.

When Developing Time-series Applications

The ability to cluster rows based on the primary key makes index-organized tables attractive for time-series applications. Typically, a time-series is a set of time-stamped rows belonging to a single item such as stock price. Data is typically accessed through an item identifier such as a stock symbol and a time stamp. By defining, an index-organized table with primary key (stock symbol, time stamp), the Oracle8 Time Series Data Cartridge is able to store and manipulate time-series data efficiently. You can achieve further storage savings by compressing repeated occurrences of the item identifier (for example. the stock symbol) in a time series by using an index-organized table with key compression.

When Using Nested Tables

For a nested table column, Oracle internally creates a storage table to hold all the nested table rows. The rows belonging to a single nested table instance are identified by a NESTED_TABLE_ID column. If an ordinary table is used as nested table column storage, the nested table rows typically get de-clustered. By contrast, when you use an index-organized table, the nested table rows can be clustered based on the NESTED_TABLE_ID column. In Oracle 8i, Release 8.1, you can specify the storage of the nested table to be an index-organized table, as illustrated in the following.

CREATE TYPE Project_t AS OBJECT(Pno NUMBER, Pname VARCHAR2(80));
CREATE TYPE Project_set AS TABLE OF Project_t;
CREATE TABLE Employees (Eno NUMBER, Projects PROJECT_SET)
   NESTED TABLE Projects_ntab STORE AS Emp_project_tab
                ((PRIMARY KEY(Nested_table_id, Pno)) ORGANIZATION INDEX)
                RETURN AS LOCATOR;

When Using Extensible Indexing

Oracle 8i, Release 8.1 introduces the Extensible Indexing Framework which allows you to add a new access method to the database. Typically, domain-specific indexing schemes need some storage mechanism to hold their index data. Index-organized tables are ideal candidates for such domain index storage. Oracle8 Spatial and Text Database Cartridges have implemented domain-specific indexing schemes that use index-organized tables for storing their index data.

Example

Note: You may need to set up the following data structures for certain examples to work; such as: CONNECT system/manager GRANT CREATE TABLESPACE TO scott; CONNECT scott/tiger CREATE TABLESPACE Ind_tbs DATAFILE 'disk1:moredata2' SIZE 100K; CREATE TABLE Doc_tab DATAFILE 'disk1:moredata2' SIZE 100K; CREATE TABLESPACE Ovf_tbs DATAFILE 'disk1:moredata3' SIZE 100K; CREATE TABLESPACE Ind_ts0 DATAFILE 'disk1:moredata5' SIZE 100K REUSE; CREATE TABLESPACE Ov_ts0 DATAFILE 'disk1:moredata6' SIZE 100K REUSE; CREATE TABLESPACE Ind_ts1 DATAFILE 'disk1:moredata7' SIZE 100K REUSE; CREATE TABLESPACE Ov_ts1 DATAFILE 'disk1:moredata8' SIZE 100K REUSE; CREATE TABLESPACE Ind_ts2 DATAFILE 'disk1:moredata9' SIZE 100K REUSE; CREATE TABLESPACE Ov_ts2 DATAFILE 'disk1:moredata10' SIZE 100K REUSE; CREATE TABLE Doc_tab (tok VARCHAR2(4),id VARCHAR2(14),freq NUMBER);

This example illustrates some of the basic tasks in creating and using index-organized tables. In this example, a text search engine uses an inverted index² to allow a user to query for specific words or phrases over the Web. It then returns to the user a list of hypertext links to documents containing the queried words and phrases, and it ranks those links in the order of relevance.

This example illustrates the following tasks:

• Moving Existing Data from an Ordinary Table into an Index-Organized Table

• Creating Index-Organized Tables

• Declaring a Universal Rowid Datatype

• Creating Secondary Indexes on Index-Organized Tables

• Manipulating Index-Organized Tables

• Specifying an Overflow Data Segment

• Determining the Last Non-key Column Included in the Index Row Head Piece

• Storing Columns in the Overflow Segment

• Modifying Physical and Storage Attributes

• Partitioning an Index-Organized Table

• Rebuilding an Index-Organized Table

Moving Existing Data from an Ordinary Table into an Index-Organized Table

The CREATE TABLE AS SELECT command allows you to move existing data from an ordinary table into an index-organized table. In the following example, an index-organized table, called docindex, is created from an ordinary table called doctable.

CREATE TABLE Docindex
   (   Token,
       Doc_id,
       Token_frequency,
       CONSTRAINT Pk_docindex PRIMARY KEY (Token, Doc_id)
   )
ORGANIZATION INDEX TABLESPACE Ind_tbs
PARALLEL (DEGREE 2)
AS SELECT * from Doc_tab;

Note that the PARALLEL clause allows the table creation to be performed in parallel.

Creating Index-Organized Tables

To create an index-organized table, you use the ORGANIZATION INDEX clause. In the following example, an inverted index--typically used by Web text-search engines--uses an index-organized table.

CREATE TABLE Docindex
   (  Token           CHAR(20),
      Doc_id          NUMBER,
      Token_frequency NUMBER,
      CONSTRAINT Pk_docindex PRIMARY KEY (Token, Doc_id)
   )
ORGANIZATION INDEX TABLESPACE Ind_tbs;

Declaring a Universal Rowid Datatype

The following example shows how you declare the UROWID datatype.

DECLARE 
     Rid UROWID;
BEGIN
     INSERT INTO Docindex VALUES ('Or80', 2, 30) 
                 RETURNING Rowid INTO RID;
     UPDATE Docindex SET Token='Or81' WHERE ROWID = Rid;
END;

Creating Secondary Indexes on Index-Organized Tables

You can create secondary indexes on index-organized tables to provide multiple access paths. The following example shows the creation of an index on (doc_id, token).

CREATE INDEX Doc_id_index on Docindex(Doc_id, Token);

This secondary index allows Oracle to efficiently process queries involving predicates on doc_id, as the following example illustrates.

SELECT Token FROM Docindex WHERE Doc_id = 1;

Manipulating Index-Organized Tables

Applications manipulate the index-organized tables just like an ordinary table, using standard SQL statements for SELECT, INSERT, UPDATE, or DELETE operations. For example, you can manipulate the docindex table as follows:

INSERT INTO Docindex VALUES (`Oracle8.1', 3, 17);
SELECT * FROM Docindex;
UPDATE Docindex SET Token = `Oracle8' WHERE Token = `Oracle8.1';
DELETE FROM Docindex WHERE Doc_id = 1;

Also, you can use SELECT FOR UPDATE statements to lock rows of an index-organized table. All of these operations result in manipulating the primary key B*-tree index. Both query and DML operations involving index-organized tables are optimized by using this cost-based approach.

Specifying an Overflow Data Segment

Storing all non-key columns in the primary key B*-tree index structure may not always be desirable because, for example:

• Each additional non-key column stored in the primary key index reduces the dense clustering of index rows in the B*-tree index leaf blocks

  or because

• A leaf block of aB*-tree must hold at least two index rows, and putting all non-key columns as part of an index row may not be possible.

To overcome these problems, you can associate an overflow data segment with an index-organized table. In the following example, an additional column, token_offsets, is required for the docindex table. This example shows how you can create an index-organized table and use the OVERFLOW option to create an overflow data segment.

CREATE TABLE Docindex2
   (   Token           CHAR(20),
       Doc_id          NUMBER,
       Token_frequency NUMBER,
       Token_offsets   VARCHAR(512),
       CONSTRAINT Pk_docindex2 PRIMARY KEY (Token, Doc_id)
   )
ORGANIZATION INDEX TABLESPACE Ind_tbs PCTTHRESHOLD 20
OVERFLOW TABLESPACE Ovf_tbs INITRANS 4;

For the overflow data segment, you can specify physical storage attributes such as TABLESPACE, INITRANS, and so on.

For an index-organized table with an overflow segment, the index row contains a <key, row head> pair, where the row head contains the first few non-key columns and a rowid that points to an overflow row-piece containing the remaining column values. Although this approach incurs the storage cost of one rowid per row, it nevertheless avoids key column duplication.

Figure 7-2 Overflow Segment

Determining the Last Non-key Column Included in the Index Row Head Piece

To determine the last non-key column to include in the index row head piece, you use the PCTTHRESHOLD option specified as a percentage of the leaf block size. The remaining non-key columns are stored in the overflow data segment as one or more row-pieces. Specifically, the last non-key column to be included is chosen so that the index row size (key +row head) does not exceed the specified threshold (which, in the following example, is 20% of the index leaf block). By default, PCTTHRESHOLD is set at 50 when omitted.

The PCTTHRESHOLD option determines the last non-key column to be included in the index on a per row basis. It does not, however, allow you to specify that the same set of columns be included in the index for all rows in the table. For this purpose, the INCLUDING option is provided.

The CREATE TABLE statement in the following example includes all the columns up to the token_frequency column in the index leaf block and forces the token_offsets column to the overflow segment.

CREATE TABLE Docindex3
   (   Token           CHAR(20),
       Doc_id          NUMBER,
       Token_frequency NUMBER,
       Token_offsets   VARCHAR(512),
       CONSTRAINT Pk_docindex3 PRIMARY KEY (Token, Doc_id)
   )
ORGANIZATION INDEX TABLESPACE Ind_tbs INCLUDING Token_frequency
OVERFLOW TABLESPACE Ovf_tbs;

Such vertical partitioning of a row between the index and data segments allows for higher clustering of rows in the index. This results in better query performance for the columns stored in the index. For example, if the token_offsets column is infrequently accessed, then pushing this column out of the index results in better clustering of index rows in the primary key B*-tree structure (Figure 7-3). This in turn results in overall improved query performance. However, there is one additional block access for columns stored in the overflow data segment, and this can slow performance.

Storing Columns in the Overflow Segment

The INCLUDING option ensures that all columns after the specified including column are stored in the overflow segment. If the including column specified is such that corresponding index row size exceeds the specified threshold, then the last non-key column to be included is determined according to the PCTTHRESHOLD option.

Figure 7-3 PCTTHRESHOLD versus INCLUDING Column Usage

Modifying Physical and Storage Attributes

You can use the ALTER TABLE command to modify physical and storage attributes for both the index and overflow data segments as well as alter PCTTHRESHOLD and INCLUDING column values. The following example sets the INITRANS of index segment to 4, PCTTHRESHOLD to 20, and the INITRANS of the overflow data segment to 6. The altered values are used for subsequent operations on the table.

ALTER TABLE Docindex INITRANS 4 PCTTHRESHOLD 20 OVERFLOW INITRANS 6;

For index-organized tables created without an overflow data segment, you can add an overflow data segment using ALTER TABLE ADD OVERFLOW option. The following example shows how to add an overflow segment to the docindex table.

ALTER TABLE Docindex ADD OVERFLOW;

Analyzing an Index-Organized Table

Index-organized tables are analyzed just like ordinary tables using the ANALYZE command. The following example illustrates how you could use the ANALYZE command to analyze the docindex table.

ANALYZE TABLE Docindex COMPUTE STATISTICS;

Using the ANALYZE command analyzes both the primary key index segment and the overflow data segment, and computes logical as well as physical statistics for the table. Also, you can determine how many rows have one or more chained overflow row-pieces using the ANALYZE LIST CHAINED ROWS option. However, to identify the chain rows, you must create a slightly different CHAINED_ROWS table that includes primary key columns. With the logical rowid support added in Oracle8i, Release 8.1.5, a separate CHAINED_ROWS table is no longer needed.

Loading, exporting/importing, replicating an Index-Organized Table

Data can be loaded into both non-partitioned and partitioned index-organized tables using the ordinary or direct path with the SQL*Loader. The data can also be exported or imported using the Export/Import utility. Index-organized tables can also be replicated in a distributed database just like ordinary tables.

Partitioning an Index-Organized Table

You can partition index-organized tables by range of column values. However, to create such partitioned index-organized tables the set of partitioning columns must be a subset of primary key columns. By imposing this restriction, only a single partition needs to be searched for to verify the uniqueness of the primary key during DML operations. This preserves the partition independence property.

The following are key aspects of partitioned index-organized tables:

• You must specify the ORGANIZATION INDEX clause to create an index-organized table as part of table-level attributes. This property is implicitly inherited by all partitions.

• You must specify the OVERFLOW option as part of table-level attribute to create an index-organized table with overflow data segment.

• The OVERFLOW option results in the creation of overflow data segments, which are themselves equi-partitioned with the primary key index segments. That is, each partition has an index segment and an overflow data segment.

• As in ordinary partitioned tables, you can specify default values for physical attributes at the table-level. These can be overridden for each partition (both for index and overflow data segment).

• The tablespace for index segment, if not specified for a partition, is set to the table level default. If the table level default is not specified, then the default tablespace for the user is used.

• The default values for PCTTHRESHOLD and INCLUDING column can only be specified at the table-level.

• All the attributes that are specified before the OVERFLOW keyword are applicable to primary index segments. All the attributes specified after the OVERFLOW keyword are applicable to overflow data segments.

• The tablespace for an overflow data segment, if not specified for a partition, is set to the table level default. If the table-level default is not specified, then the tablespace of the corresponding partition's index segment is used.

The following example continues the example of the docindex table. It illustrates a range partition on token values.

CREATE TABLE Docindex4
   (Token           CHAR(20),
    Doc_id          NUMBER,
    Token_frequency NUMBER,
    Token_offsets   VARCHAR(512),
    CONSTRAINT Pk_docindex4 PRIMARY KEY (Token, Doc_id)
   )
ORGANIZATION INDEX INITRANS 4  INCLUDING Token_frequency 
OVERFLOW INITRANS 6 
      PARTITION BY RANGE(token)
          ( PARTITION P1 VALUES LESS THAN ('j')
TABLESPACE Ind_ts0 OVERFLOW TABLESPACE Ov_ts0,
   PARTITION P2 VALUES LESS THAN ('s')  
TABLESPACE Ind_ts1 OVERFLOW TABLESPACE Ov_ts1,
  PARTITION P3 VALUES LESS THAN (MAXVALUE)  
TABLESPACE Ind_ts2 OVERFLOW TABLESPACE Ov_ts2);

This will result in creation of the table shown in Figure 7-4. The INCLUDING column results in storing the token_offsets in the overflow data segment for each partition.

Figure 7-4 Range-partitioned Index-organized Table with Overflow Segment

Support for partitioned indexes on index-organized tables is very similar to that for an ordinary table. Local prefixed, local non-prefixed, and global prefixed partitioned indexes are supported on index-organized tables. The only difference is that these indexes store logical rowids instead of physical rowids.

All of the ALTER TABLE operations, except MERGE, are available for partitioned index-organized tables. However, there are some differences in behavior with respect to ordinary tables:

• For ALTER TABLE MOVE partition operations, all indexes--local, global, and non-partitioned--remain USABLE because the indexes contain logical rowids. However, the guess stored in the logical rowid becomes invalid.

• For SPLIT partition operations, all indexes or global index partitions remain usable.

• For ALTER TABLE EXCHANGE partition, the target table must be a compatible index-organized table.

• Users can use the ALTER TABLE ADD OVERFLOW command to add an overflow segment and specify table-level default and partition-level physical and storage attributes. This operation results in adding an overflow data segment to each partition.

ALTER INDEX operations are very similar to those on ordinary tables. The only difference is that operations that reconstruct the entire index--namely, ALTER INDEX REBUILD and SPLIT_PARTITION--result in reconstructing the guess stored as part of the logical rowid.

Query and DML operations on partitioned index-organized tables work the same as on ordinary partitioned tables.

Key Compression

You enable key compression by using the COMPRESS clause when specifying physical attributes for the index segment. In addition, the prefix length (as number of columns) can be specified to identify how the key can be broken into a prefix and a suffix. The valid range of values for prefix length are [1, number of primary key columns minus 1].

CREATE TABLE Docindex5
   ( Token           CHAR(20),
     Doc_id          NUMBER,
     Token_frequency NUMBER,
     Token_offsets   VARCHAR(512),
     CONSTRAINT pk_docindex5 PRIMARY KEY (Token, Doc_id)
   )
ORGANIZATION INDEX TABLESPACE Ind_tbs COMPRESS 1 INCLUDING Token_frequency
OVERFLOW TABLESPACE Ovf_tbs;

Common prefixes of length 1 (that is, token column) will be compressed in the primary key (token, doc_id) occurrences. For the list of primary key values (`DBMS', 1), (`DBMS', 2), (`Oracle', 1), (`Oracle', 2), the repeated occurrences of `DBMS' and `Oracle' are compressed away.

If a prefix length is not specified, by default it is set to number of primary key columns minus 1. The compress option can be specified during creation of an index-organized table or as part of moving the index-organized table using ALTER TABLE MOVE option. For example, you can disable compression as follows:

ALTER TABLE Docindex5 MOVE NOCOMPRESS;

Similarly, the indexes for ordinary tables and index-organized tables can be compressed using the COMPRESS option.

Key Compression for Partitioned Index-Organized Tables

Key compression is also supported for partitioned index-organized tables. The compression clause must be specified as part of table-level defaults. For each partition, compression can be enabled or disabled. However, the prefix length cannot be changed at partition level.

CREATE TABLE Docindex6
   ( Token           CHAR(20),
     Doc_id          NUMBER,
     Token_frequency NUMBER,
     Token_offsets   VARCHAR(512),
     CONSTRAINT Pk_docindex6 PRIMARY KEY (Token, Doc_id)
   )
ORGANIZATION INDEX INITRANS 4 COMPRESS 1 INCLUDING Token_frequency
OVERFLOW INITRANS 6 
            PARTITION BY RANGE(Token)
           ( PARTITION P1 VALUES LESS THAN ('j')  
TABLESPACE Ind_ts0 OVERFLOW TABLESPACE Ov_ts0,
   PARTITION P2 VALUES LESS THAN ('s')  
TABLESPACE Ind_ts1 NOCOMPRESS OVERFLOW TABLESPACE Ov_ts1,
  PARTITION P3 VALUES LESS THAN (MAXVALUE)  
TABLESPACE Ind_ts2 OVERFLOW TABLESPACE Ov_ts2
);

All partitions inherit the table-level default for prefix length. Partitions P1 and P3 are created with key-compression enabled. For partition P2, the compression is disabled by the partition level NOCOMPRESS option.

For ALTER TABLE MOVE and SPLIT operations, the COMRPESS option can be altered. The following example rebuilds the partition with key compression enabled.

ALTER TABLE Docindex6 MOVE PARTITION P2 COMPRESS;

Rebuilding an Index-Organized Table

A new SQL command, ALTER TABLE MOVE, allows you to move, that is, rebuild the table. This should be used when the B*-tree structure containing an index-organized table gets fragmented due to a large number of inserts, updates, or deletes. The MOVE option rebuilds the primary key B*-tree index.

By default, the overflow data segment is not rebuilt, except when:

• The OVERFLOW clause is explicitly specified,

• The PCTHRESHOLD and/or INCLUDING column value are altered as part of the MOVE statement.

• Any LOBs are moved explicitly

By default, LOB columns related to index and data segments are not rebuilt, except when the LOB columns are explicitly specified as part of the MOVE statement. The following example rebuilds the B*-tree index containing the table data after setting the INITRANS to 6 for index blocks.

ALTER TABLE docindex MOVE INITRANS 6;

The following example rebuilds both the primary key index and overflow data segment.

ALTER TABLE docindex MOVE TABLESPACE Ovf_tbs OVERFLOW TABLESPACE ov_ts0;

By default, during the move, the table is not available for other operations. However, you can move an index-organized table using the ONLINE option. The following example allows the table to be available for DML and query operations during the actual move operation. This feature makes the index-organized table suitable for applications requiring 24X7 availability.

Caution: You may need to set your COMPATIBLE initialization parameter to '8.1.3.0' or higher to get the following to work:

ALTER TABLE Docindex MOVE ONLINE;

The MOVE option is also available for ordinary tables. However, ONLINE move is supported only for index-organized tables which do not have an overflow segment.

---

¹ If you use Oracle Advanced Queuing, you may be familiar with index-organized tables already. Oracle Advance Queuing provides message queuing as an integrated part of the Oracle8i server, and uses index-organized tables to hold metadata information for multiple consumer queues. In this case, the index-organized table acts as an "index," storing queue metadata as part of a primary key B*-tree index on the queue identifier. DML operations in turn have to update the "index," and this occurs efficiently by updating the underlying index-organized table.
² An inverted index breaks each document into individual words or tokens. For each word, the inverted index builds a list of documents in which the word occurs, then stores that list in the database. The application performs a content-based search by scanning the inverted index looking for tokens of interest. From a development standpoint, an inverted index typically contains entries of the form <token, document_id, occurrence_data> for each distinct word in a document.