|Oracle8i Application Developer's Guide - Fundamentals
This chapter covers the following topics:
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.
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.
For more information on B*-tree indexes, see Oracle8i Concepts
Because they store rows in the B*-tree index based on the primary key, index-organized tables offer the following advantages over ordinary tables:
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.
When your database must be available 100% of the time, index-organized tables provide the following advantages:
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.
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.
All of the alter options available on ordinary tables are now available for index-organized tables. This includes
CONSTRAINTS. However, the primary key constraint for an index-organized table cannot be dropped, deferred, or disabled.
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:
ROWIDfrom an index-organized table and access the rows using
ROWID = <value predicate>.
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:|
Secondary indexes on index-organized tables differ from indexes on ordinary tables in two ways:
MOVEdoes not make the secondary index unusable.
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.
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:
LOB'sdata and index segments are created in the tablespace in which the primary key index segment of the index-organized table is created.
LOBsin index-organized tables created without an overflow segment are stored out-of-line (that is, the default storage attribute is
ROW). Specifying an
LOBs, will result in an error. However,
LOBsin index-organized tables with overflow segments have the same characteristics as those in ordinary tables.
LOB features--such as
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.
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.
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 (
type>) as index-organized tables.
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.
This utility supports export (both ordinary and direct path) and import of non-partitioned and partitioned index-organized tables.
You can replicate both non-partitioned and partitioned index-organized tables.
The Oracle Enterprise Manager supports generating SQL statements for
ALTER operations on an index-organized table.
Key compression allows elimination of repeated occurrences of key column prefixes in index-organized tables and indexes. The salient characteristics of the scheme are:
There are several occasions when you may prefer to use index-organized tables over ordinary tables.1
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.
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
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
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
MOVE operation can be done on-line. This functionality makes index-organized tables ideal for applications requiring 24X7 availability.
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.
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;
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.
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 index2 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:
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
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.
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;
The following example shows how you declare the
DECLARE Rid UROWID; BEGIN INSERT INTO Docindex VALUES ('Or80', 2, 30) RETURNING Rowid INTO RID; UPDATE Docindex SET Token='Or81' WHERE ROWID = Rid; END;
You can create secondary indexes on index-organized tables to provide multiple access paths. The following example shows the creation of an index on (
This secondary index allows Oracle to efficiently process queries involving predicates on
doc_id, as the following example illustrates.
Applications manipulate the index-organized tables just like an ordinary table, using standard SQL statements for
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.
Storing all non-key columns in the primary key B*-tree index structure may not always be desirable because, for example:
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
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.
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.
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.
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.
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
You can use the
TABLE command to modify physical and storage attributes for both the index and overflow data segments as well as alter
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.
For index-organized tables created without an overflow data segment, you can add an overflow data segment using
OVERFLOW option. The following example shows how to add an overflow segment to the
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
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
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.
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.
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:
INDEXclause to create an index-organized table as part of table-level attributes. This property is implicitly inherited by all partitions.
OVERFLOWoption as part of table-level attribute to create an index-organized table with overflow data segment.
OVERFLOWoption 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.
INCLUDINGcolumn can only be specified at the table-level.
OVERFLOWkeyword are applicable to primary index segments. All the attributes specified after the
OVERFLOWkeyword are applicable to overflow data segments.
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.
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
TABLE operations, except
MERGE, are available for partitioned index-organized tables. However, there are some differences in behavior with respect to ordinary tables:
MOVEpartition operations, all indexes--local, global, and non-partitioned--remain
USABLEbecause the indexes contain logical rowids. However, the guess stored in the logical rowid becomes invalid.
SPLITpartition operations, all indexes or global index partitions remain usable.
EXCHANGEpartition, the target table must be a compatible index-organized table.
ALTER TABLE ADD OVERFLOWcommand 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.
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.
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
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
1. The compress option can be specified during creation of an index-organized table or as part of moving the index-organized table using
MOVE option. For example, you can disable compression as follows:
Similarly, the indexes for ordinary tables and index-organized tables can be compressed using the
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
SPLIT operations, the
COMRPESS option can be altered. The following example rebuilds the partition with key compression enabled.
A new SQL command,
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:
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
6 for index blocks.
The following example rebuilds both the primary key index and overflow data segment.
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.
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.
2 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.