| |
 |
|
Oracle Tips by Burleson |
Creation of an Index-Only
Table
Index-only tables have been around since
Oracle8. If neither the HASH or INDEX ORGANIZED options are used
with the CREATE TABLE command, then a table is created as a standard
hash table. If the INDEX ORGANIZED option is specified, the table is
created as a B-tree-organized table identical to a standard Oracle
index created on similar columns. Index-organized tables do not have
rowids.
Index-organized tables have the option of allowing overflow storage
of values that exceed optimal index row size, as well as allowing
compression to be used to reduce storage requirements. Overflow
parameters can include columns and the percent threshold value to
begin overflow. An index-organized table must have a primary key.
Index-organized tables are best suited for use with queries based on
primary key values. Index-organized tables can be partitioned in
Oracle8i and in Oracle9i as long as they do not contain LOB or
nested table types. The PCTHRESHOLD value specifies the amount of
space reserved in an index block for row data. If the row data
length exceeds this value, then the row(s) are stored in the area
specified by the OVERFLOW clause. If no overflow clause is
specified, rows that are too long are rejected. The INCLUDING COLUMN
clause allows you to specify at which column to break the record if
an overflow occurs.
See Code Depot
www.oracle-script.com |
