This section describes aspects of managing index-organized tables, and contains the following topics:
What Are Index-Organized Tables?
An index-organized table has a storage organization that is a variant of a primary B-tree. Unlike an ordinary (heap-organized) table whose data is stored as an unordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Each leaf block in the index structure stores both the key and nonkey columns.
The structure of an index-organized table provides the following benefits:
They are not stored both in the index and underlying table, as is true with heap-organized tables. Index-organized tables have full table functionality. They support features such as constraints, triggers, LOB and object columns, partitioning, parallel operations, online reorganization, and replication. And, they offer these additional features:
Index-organized tables are ideal for OLTP applications, which require fast primary key access and high availability. Queries and DML on an orders table used in electronic order processing are predominantly primary-key based and heavy volume causes fragmentation resulting in a frequent need to reorganize. Because an index-organized table can be reorganized online and without invalidating its secondary indexes, the window of unavailability is greatly reduced or eliminated. Index- organized tables are suitable for modeling application-specific index structures. For example, content-based information retrieval applications containing text, image and audio data require inverted indexes that can be effectively modeled using index-organized tables. A fundamental component of an internet search engine is an inverted index that can be modeled using index-organized tables. These are but a few of the applications for index-organized tables.
Creating Index-Organized Tables
You use the CREATE TABLE statement to create index-organized tables, but you must provide additional information:
Optionally, you can specify the following:
Creating an Index- Organized Table
The following statement creates an index-organized table:
Specifying ORGANIZATION INDEX causes the creation of an index-organized table, admin_docindex, where the key columns and nonkey columns reside in an index defined on columns that designate the primary key or keys for the table. In this case, the primary keys are token and doc_id. An overflow segment is specified and is discussed in "Using the Overflow Clause".
Creating Index-Organized Tables that Contain Object Types
Index-organized tables can store object types. The following example creates object type admin_typ, then creates an index-organized table containing a column of object type admin_typ:
Another example, that follows, shows that index-organized tables store nested tables efficiently. For a nested table column, the database 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 to store nested table columns, the nested table rows typically get de-clustered. But when you use an index-organized table, the nested table rows can be clustered based on the nested_table_id column.
Using the Overflow Clause
The overflow clause specified in the statement shown in "Creating an Index-Organized Table" indicates that any nonkey columns of rows exceeding 20% of the block size are placed in a data segment stored in the admin_ tbs2 tablespace. The key columns should fit the specified threshold.
If an update of a nonkey column causes the row to decrease in size, the database identifies the row piece (head or tail) to which the update is applicable and rewrites that piece.
If an update of a nonkey column causes the row to increase in size, the database identifies the piece (head or tail) to which the update is applicable and rewrites that row piece. If the target of the update turns out to be the head piece, note that this piece can again be broken into two to keep the row size below the specified threshold.
The nonkey columns that fit in the index leaf block are stored as a row head-piece that contains a rowid field linking it to the next row piece stored in the overflow data segment. The only columns that are stored in the overflow area are those that do not fit.
Choosing and Monitoring a Threshold Value
You should choose a threshold value that can accommodate your key columns, as well as the first few nonkey columns (if they are frequently accessed). After choosing a threshold value, you can monitor tables to verify that the value you specified is appropriate. You can use the ANALYZE TABLE ... LIST CHAINED ROWS statement to determine the number and identity of rows exceeding the threshold value.
Using the INCLUDING Clause
In addition to specifying PCTTHRESHOLD, you can use the INCLUDING clause to control which nonkey columns are stored with the key columns. The database accommodates all nonkey columns up to the column specified in the INCLUDING clause in the index leaf block, provided it does not exceed the specified threshold. All nonkey columns beyond the column specified in the INCLUDING clause are stored in the overflow area.
The following CREATE TABLE statement is similar to the one shown earlier in "Creating an Index-Organized Table" but is modified to create an index-organized table where the token_offsets column value is always stored in the overflow area:
Here, only nonkey columns prior to token_offsets (in this case a single column only) are stored with the key column values in the index leaf block.
Parallelizing Index-Organized Table Creation
The CREATE TABLE ... AS SELECT statement enables you to create an index-organized table and load data from an existing table into it. By including the PARALLEL clause, the load can be done in parallel.
The following statement creates an index- organized table in parallel by selecting rows from the conventional table hr.jobs:
This statement provides an alternative to parallel bulk-load using SQL*Loader.
Using Key Compression
Treating an index- organized table using key compression enables you to eliminate repeated occurrences of key column prefix values. Key compression breaks an index key into a prefix and a suffix entry. Compression is achieved by sharing the prefix entries among all the suffix entries in an index block. This sharing can lead to huge savings in space, allowing you to store more keys in each index block while improvin performance.
You can enable key compression using the COMPRESS clause while:
You can also specify the prefix length (as the number of key columns), which identifies how the key columns are broken into a prefix and suffix entry.
The preceding statement is equivalent to the following statement:
For the list of values (1,2,3), (1,2,4), (1,2,7), (1,3,5), (1,3,4), (1,4,4) the repeated occurrences of (1,2), (1,3) are compressed away.
You can also override the default prefix length used for compression as follows:
For the list of values (1,2,3), (1,2,4), (1,2,7), (1,3,5), (1,3,4), (1,4,4), the repeated occurrences of 1 are compressed away.
You can disable compression as follows:ALTER TABLE admin_iot5 MOVE NOCOMPRESS;
One application of key compression is in a time-series application that uses a set of time-stamped rows belonging to a single item, such as a stock price. Index-organized tables are attractive for such applications because of the ability to cluster rows based on the primary key. By defining an index-organized table with primary key (stock symbol, time stamp), you can store and manipulate time-series data efficiently. You can achieve more 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.
Maintaining Index-Organized Tables
Index- organized tables differ from ordinary tables only in physical organization. Logically, they are manipulated in the same manner as ordinary tables. You can specify an index-organized table just as you would specify a regular table in INSERT, SELECT, DELETE, and UPDATE statements.
Altering Index-Organized Tables
All of the alter options available for ordinary tables are 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. You can use the ALTER TABLE statement to modify physical and storage attributes for both primary key index and overflow data segments. All the attributes specified prior to the OVERFLOW keyword are applicable to the primary key index segment. All attributes specified after the OVERFLOW key word are applicable to the overflow data segment. For example, you can set the INITRANS of the primary key index segment to 4 and the overflow of the data segment INITRANS to 6 as follows:ALTER TABLE admin_docindex INITRANS 4 OVERFLOW INITRANS 6;
You can also alter PCTTHRESHOLD and INCLUDING column values. A new setting is used to break the row into head and overflow tail pieces during subsequent operations. For example, the PCTHRESHOLD and INCLUDING column values can be altered for the admin _docindex table as follows:ALTER TABLE admin_docindex PCTTHRESHOLD 15 INCLUDING doc_id;
By setting the INCLUDING column to doc_id, all the columns that follow token_ frequency and token_offsets, are stored in the overflow data segment. For index- organized tables created without an overflow data segment, you can add an overflow data segment by using the ADD OVERFLOW clause. For example, you can add an overflow segment to table admin _iot3 as follows:ALTER TABLE admin_iot3 ADD OVERFLOW TABLESPACE admin_tbs2;
Moving (Rebuilding) Index-Organized Tables
Because index- organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE ... MOVE statement to rebuild the index and reduce this fragmentation.
The following statement rebuilds the index-organized table admin_docindex:ALTER TABLE admin_docindex MOVE;
You can rebuild index-organized tables online using the ONLINE keyword. The overflow data segment, if present, is rebuilt when the OVERFLOW keyword is specified. For example, to rebuild the admin_docindex table but not the overflow data segment, perform a move online as follows:ALTER TABLE admin _docindex MOVE ONLINE;
To rebuild the admin _docindex table along with its overflow data segment perform the move operation as shown in the following statement. This statement also illustrates moving both the table and overflow data segment to new tablespaces.
In this last statement, an index organized table with a LOB column (CLOB) is created. Later, the table is moved with the LOB index and data segment being rebuilt and moved to a new tablespace.
Creating Secondary Indexes on Index-Organized Tables
You can create secondary indexes on an index organized tables to provide multiple access paths. Secondary indexes on index-organized tables differ from indexes on ordinary tables in two ways:
Unique and nonunique secondary indexes, function-based secondary indexes, and bitmap indexes are supported as secondary indexes on index-organized tables.
Creating a Secondary Index on an Index-Organized Table
The following statement shows the creation of a secondary index on the docindex index- organized table where doc_id and token are the key columns:
CREATE INDEX Doc _id _index on Docindex(Doc_id, Token);This secondary index allows the database to efficiently process a query, such as the following, the involves a predicate on doc_id:SELECT Token FROM Docindex WHERE Doc_id = 1;
Maintaining Physical Guesses in Logical Rowids
A logical rowid can include a guess, which identifies the block location of a row at the time the guess is made. Instead of doing a full key search, the database uses the guess to search the block directly. However, as new rows are inserted, guesses can become stale. The indexes are still usable through the primary key-component of the logical rowid, but access to rows is slower.
Collect index statistics with the DBMS_STATS package to monitor the staleness of guesses. The database checks whether the existing guesses are still valid and records the percentage of rows with valid guesses in the data dictionary. This statistic is stored in the PCT_DIRECT_ACCESS column of the DBA _INDEXES view (and related views). To obtain fresh guesses, you can rebuild the secondary index. Note that rebuilding a secondary index on an index-organized table involves reading the base table, unlike rebuilding an index on an ordinary table. A quicker, more light weight means of fixing the guesses is to use the ALTER INDEX ... UPDATE BLOCK REFERENCES statement. This statement is performed online, while DML is still allowed on the underlying index-organized table. After you rebuild a secondary index, or otherwise update the block references in the guesses, collect index statistics again.
Bitmap indexes on index-organized tables are supported, provided the index-organized table is created with a mapping table. This is done by specifying the MAPPING TABLE clause in the CREATE TABLE statement that you use to create the index-organized table, or in an ALTER TABLE statement to add the mapping table later.
Analyzing Index- Organized Tables
Just like ordinary tables, index-organized tables are analyzed using the DBMS_STATS package, or the ANALYZE statement.
Collecting Optimizer Statistics for Index-Organized Tables
To collect optimizer statistics, use the DBMS_STATS package. For example, the following statement gathers statistics for the index-organized countries table in the hr schema:EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('HR','COUNTRIES');
The DBMS_STATS package analyzes both the primary key index segment and the overflow data segment, and computes logical as well as physical statistics for the table.
Validating the Structure of Index-Organized Tables
Use the ANALYZE statement if you want to validate the structure of your index-organized table or to list any chained rows.
Using the ORDER BY Clause with Index-Organized Tables
If an ORDER BY clause only references the primary key column or a prefix of it, then the optimizer avoids the sorting overhead, as the rows are returned sorted on the primary key columns.
The following queries avoid sorting overhead because the data is already sorted on the primary key:
If, however, you have an ORDER BY clause on a suffix of the primary key column or non-primary-key columns, additional sorting is required (assuming no other secondary indexes are defined).
Converting Index-Organized Tables to Regular Tables
You can convert index- organized tables to regular tables using the Oracle import or export utilities, or the CREATE TABLE ... AS SELECT statement. To convert an index-organized table to a regular table:
Oracle 10g Related Interview Questions
|Oracle 10g Interview Questions||Oracle 9i Interview Questions|
|Oracle 8i Interview Questions||Informatica Interview Questions|
|PL/SQL Interview Questions||Oracle 11g Interview Questions|
|SQL Interview Questions||Oracle apps Interview Questions|
|Sybase Interview Questions||Oracle Apps ERP Interview Questions|
|Oracle 7.3 Interview Questions||Oracle Access Manager Interview Questions|
|Oracle Application Framework Interview Questions||Oracle Apps DBA Interview Questions|
Oracle 10g Related Practice Tests
|Oracle 10g Practice Tests||Oracle 9i Practice Tests|
|Oracle 8i Practice Tests||Informatica Practice Tests|
|PL/SQL Practice Tests||Oracle 11g Practice Tests|
|SQL Practice Tests||Oracle apps Practice Tests|
|Sybase Practice Tests||Oracle Apps ERP Practice Tests|
|Oracle 7.3 Practice Tests|
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.