Home » Articles » 8i » Right here

Index Arranged Tables (IOT) in Oracle

Index Arranged Tables (IOT) have their number one key information and non-key column information saved inside the similar B*Tree construction. Successfully, the information is saved inside the principle key index. There are so many causes to virtue this kind of desk.

• Why Use Index Organized Tables
• Creation Of Index Organized Tables
• Maintenance Of Index Organized Tables

Indistinguishable articles.

• Index Organized Table Enhancements (9i)

Why Worth Index Arranged Tables

• Having access to information by means of the principle secret’s sooner as the important thing and the information are living in the similar construction. There’s no wish to learn an index
  after learn the desk information in a distant construction.
• Shortage of duplication of the important thing columns in an index and desk heartless the entire attic necessities are decreased.

Origination Of Index Arranged Tables

To build an index arranged desk you will have to:

• Specify the principle key the use of a column or desk constraint.
• Worth the ORGANIZATION INDEX.

As well as you’ll be able to:

• Worth PCTTHRESHOLD to outline the proportion of the ban this is reserved for an IOT row. If the row exceeds this measurement the important thing columns (head piece) is saved as commonplace, however the non-key information (tail piece) is saved in an spillage desk. A pointer is saved to find the tail piece.
• Worth OVERFLOW TABLESPACE to outline the tablespace that the spillage information will probably be saved in.
• Worth INCLUDING to outline which non-key columns are saved with the important thing columns within the head piece, must spillage be important.

CREATE TABLE places
(identification           NUMBER(10),
 description  VARCHAR2(50)  NOT NULL,
 map          BLOB,
 CONSTRAINT pk_locations PRIMARY KEY (identification)
)
ORGANIZATION INDEX 
TABLESPACE iot_tablespace
PCTTHRESHOLD 20
INCLUDING description
OVERFLOW TABLESPACE overflow_tablespace;

Upkeep Of Index Arranged Tables

As with B*Tree indexes, IOTs can grow to be fragmented and might wish to be rebuilt. If the IOT has incorrect spillage it may be rebuilt offline or on-line.

ALTER TABLE table_name MOVE INITRANS 10;
ALTER TABLE table_name MOVE ONLINE INITRANS 10;

If the IOT does have spillage it could handiest be rebuilt offline.

ALTER TABLE table_name MOVE TABLESPACE iot_tablespace OVERFLOW TABLESPACE overflow_tablespace;

For more info see:

• Index Organized Table Enhancements (9i)
• Overview of Index-Organized Tables
• Managing Index-Organized Tables

Hope this is helping. Regards Tim…

Back to the Top.