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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
