RE: Rebuilding Index Organised Tables (IOTs)

Many thanks to all of those people who advised.

Unfortunately we are running on 8.1.6.3, so cannot do "alter table move.. " as well :(
If you get any info, please let me know.

Meanwhile, here are few bits that I gathered in IOTs.

Cheers
Kumanan

Why use Index-Organized Tables
* Fast key-based access to table data for queries involving exact
match and range searches.
* Changes to the table data (such as adding new rows, updating rows,
or deleting rows) result only in updating the index structure (because there is no separate table storage area). i.e. fast.
* Also, storage requirements are reduced because key columns are not
duplicated in the table and index. The remaining non-key columns are stored in the index structure.
* Index-organized tables are particularly useful when you are using
applications that must retrieve data based on a primary key.
* Index-organized tables are also 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.
* If all your columns contain numbers only, I would say your benefit
from this usage is minimized. However you will still benefit from the storage reduction :
* Index-organized tables are like regular tables with a primary key
index on one or more of its columns. However, instead of maintaining two separate storage spaces for the table and B*tree index, an index-organized table only maintains a single B*tree index containing the primary key of the table and other column values.
* Bad for updates as entire rows may have to be moved - as per the
index tree structure.
* Not good for frequent deletes/updates/ inserts.

• Cannot be rebuilt like an index.

NB:
Except for 8i and above, no other indexes can be created on IOT.

For an index-organized table, use the index_organized_table_clause of the syntax. The move_table_clause rebuilds the index-organized table's primary key index B*-tree. The overflow data segment is not rebuilt unless the OVERFLOW keyword is explicitly stated, with two exceptions:
* If you alter the values of PCTTHRESHOLD or the INCLUDING column as
part of this ALTER TABLE statement, the overflow data segment is rebuilt.
* If any of out-of-line columns (LOBs, varrays, nested table columns)
in the index-organized table are moved explicitly, then the overflow data segment is also rebuilt.
The index and data segments of LOB columns are not rebuilt unless you specify the LOB columns explicitly as part of this ALTER TABLE statement.

117168.1: TS for IOT won't be visible as data can go into TWO different TS's:
CREATE TABLE DOCINDEX

        (token                   CHAR(20),
         doc_oid                 INTEGER,
         token_frequency         SMALLINT,
         token_occurrence_data   VARCHAR2(512),
           CONSTRAINT pk_docindex PRIMARY KEY (token, doc_oid) )
        ORGANIZATION INDEX TABLESPACE text_collection
        PCTTHRESHOLD 20 INCLUDING token_frequency
        OVERFLOW TABLESPACE text_collection_overflow;

Generate the DDL for the table again with property "Index Organised = Yes" for full table IOT?

126392.996
  create table EASY (
  ...,
  constraint PK_EASY_IDPRIMARY KEY(EASY_ID))   ORAGANIZATION INDEX [STORAGE CLAUSE]; 116899.996
One more thing is if you are going to have lot of manipulations like update, delete on Index organised table, it will increase the burden since it has to reorganise the entire table. It is advisable to have Index orgnised table with less number of manipulations.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: kumanan.balasundaram_at_qxl.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).