Index-Organized Tables

▣ IOT Table

▶ 장점 : Primary Key 가 중복이 안됨 => 공간 절약

▶ 단점 : 중간 삽입 많은 작업에서 느려짐

   

▣ 일반 테이블과 IOT 테이블의 비교

• Have faster key-based access to table data
• Do not duplicate the storage of primary key values
• Require less storage
• Use secondary indexes and logical row IDs
• Have higher availability because table reorganization does not invalidate secondary indexes

     

▣ IOT 테이블 제한

• Must have a primary key that is not deferrable
• Cannot be clustered
• Cannot use composite partitioning
• Cannot contain a column of type ROWID or LONG

   

▣ IOT 테이블 생성

   

▣ IOT 테이블 생성 by EM

   

   

▣ Show SQL

CREATE TABLE "SCOTT"."COUNTRY" ( "COUNTRY_ID" CHAR(2), "COUNTRY_NAME" VARCHAR2(40), CONSTRAINT "COUNTRY_C_ID_PK" PRIMARY KEY ("COUNTRY_ID") VALIDATE ) ORGANIZATION INDEX TABLESPACE "EXAMPLE" PCTTHRESHOLD 20 OVERFLOW TABLESPACE "USERS"