Oracle8i Application Developer's Guide - Fundamentals Release 2 (8.1.6) A76939-01 |
|
This chapter covers the following topics:
For the syntax of the ORGANIZATION INDEX clause of the CREATE TABLE statement, see Oracle8i SQL Reference.
See Also:
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.
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 ADD
, MODIFY
, and DROP
COLUMNS
and 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:
ROWID
from an index-organized table and access the rows using WHERE
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.
Secondary indexes on index-organized tables differ from indexes on ordinary tables in two ways:
ALTER
TABLE
MOVE
does 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's
data and index segments are created in the tablespace in which the primary key index segment of the index-organized table is created.
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.
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 (TABLE
OF
<object
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 CREATE
and 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 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.
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.
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:
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.
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 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;
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;
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.
Storing all non-key columns in the primary key B*-tree index structure may not always be desirable because, for example:
or because
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.
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 6-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.
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.
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;
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.
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:
ORGANIZATION
INDEX
clause to create an index-organized table as part of table-level attributes. This property is implicitly inherited by all partitions.
OVERFLOW
option as part of table-level attribute to create an index-organized table with overflow data segment.
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.
PCTTHRESHOLD
and INCLUDING
column can only be specified at the table-level.
OVERFLOW
keyword are applicable to primary index segments. All the attributes specified after the OVERFLOW
keyword 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 6-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 ALTER
TABLE
operations, except MERGE
, are available for partitioned index-organized tables. However, there are some differences in behavior with respect to ordinary tables:
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.
SPLIT
partition operations, all indexes or global index partitions remain usable.
ALTER
TABLE
EXCHANGE
partition, the target table must be a compatible index-organized table.
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.
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 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;
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:
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.
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.
1
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.
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|