create index-organized table

How to create index-organized table
===
key clause
ORGANIZATION
:The ORGANIZATION clause lets you specify the order in which the data rows of the table are stored.
INCLUDING column_name
:Specify a column at which to divide an index-organized table row into index and overflow portions.
The primary key columns are always stored in the index. column_name can be either the last primary key column or any non primary key column.
All non primary key columns that follow column_name are stored in the overflow data segment.
If an attempt to divide a row at column_name causes the size of the index portion of the row to exceed the specified or default PCTTHRESHOLD value,
then the database breaks up the row based on the PCTTHRESHOLD value
PCTTHRESHOLD:
Specify the percentage of space reserved in the index block for an index-organized table row.
PCTTHRESHOLD must be large enough to hold the primary key.
All trailing columns of a row, starting with the column that causes the specified threshold to be
exceeded, are stored in the overflow segment. PCTTHRESHOLD must be a value from 1 to 50.
If you do not specify PCTTHRESHOLD, the default is 50


Index-Organized Table Example The following statement is a variation of the sample table hr.countries, which is index organized:

CREATE TABLE countries_demo
    ( country_id      CHAR(2)
      CONSTRAINT country_id_nn_demo NOT NULL
    , country_name    VARCHAR2(40)
    , currency_name   VARCHAR2(25)
    , currency_symbol VARCHAR2(3)
    , region          VARCHAR2(15)
    , CONSTRAINT    country_c_id_pk_demo
                    PRIMARY KEY (country_id ) )
    ORGANIZATION INDEX
    INCLUDING   country_name
    PCTTHRESHOLD 2
    STORAGE
     ( INITIAL  4K
      NEXT  2K
      PCTINCREASE 0
      MINEXTENTS 1
      MAXEXTENTS 1 )
   OVERFLOW
    STORAGE
      ( INITIAL  4K
        NEXT  2K
        PCTINCREASE 0
        MINEXTENTS 1
        MAXEXTENTS 1 );
====
CREATE TABLE admin_docindex2(

token CHAR(20),

doc_id NUMBER,

token_frequency NUMBER,

token_offsets VARCHAR2(512),

CONSTRAINT pk_admin_docindex2 PRIMARY KEY (token, doc_id))

ORGANIZATION INDEX

TABLESPACE admin_tbs

PCTTHRESHOLD 20

INCLUDING token_frequency

OVERFLOW TABLESPACE admin_tbs2;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7336830/viewspace-688202/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7336830/viewspace-688202/