[@more@]

索引組織表的資料按主鍵排序手段被儲存在B-樹索引中，除了儲存主鍵列值外還儲存非鍵列的值。普通索引只儲存索引列，而索引組織表則儲存表的所有列的值。
索引組織表一般適應於靜態表，且查詢多以主鍵列。當表的大部分列當作主鍵列時，且表相對靜態，比較適合建立索引組織表！（8i以上）

索引組織表的建立：
CREATE TABLE docindex(
token char(20),
doc_id NUMBER,
token_frequency NUMBER,
token_offsets VARCHAR2(512),
CONSTRAINT pk_docindex PRIMARY KEY (token, doc_id))
ORGANIZATION INDEX TABLESPACE ind_tbs;
必須給索引結構表指定主鍵。

索引組織表的分割槽：
CREATE TABLE docindex(
token char(20),
doc_id NUMBER,
token_frequency NUMBER,
token_offsets VARCHAR2(512),
CONSTRAINT pk_docindex PRIMARY KEY (token, doc_id))
partition by list (doc_ID)
(
partition P_0 values (0)
)
ORGANIZATION INDEX TABLESPACE ind_tbs;

二：

Oracle Index Organized Tables (IOT)

Version 11.1

Oracle Index Organized Tables (IOT)

Version 11.1

General

Note: Index Organized Tables are tables that, unlike heap tables, are organized like B*Tree indexes.

Note: From Jonathan Lewis on secondary indexes

I think secondary indexes on IOTs need some careful testing. It's probably not an area that many people have used in a high-stress environment. There are two main issues:

The primary key is used in the secondary index instead of a rowid so for large primary keys, the index would be bigger than the equivalent index on a simple table.
The secondary index holds a 'guess' block address for the row it points to so that a query can go to the right block more cheaply. But if the row has moved (e.g. leaf block split) then the guess is wrong and is a cost, not a benefit. But this won't be a problem if your application is always adding data at the 'right-hand' edge of the index.

Depending of version, there are various features and limitations on what you can do with secondary indexes that you will have to trade, balance and test, if you go down that path.

(And yes, the key compression could well have a very similar benefit to the varray idea, whilst avoiding the overhead of 'object unpickling')

Data Dictionary Objects

dba_tables	all_tables	user_tables
dba_tab_cols	all_tab_cols	user_tab_cols

Create

Simple Create IOT

CREATE TABLE (
,
,
CONSTRAINT
PRIMARY KEY ())
ORGANIZATION INDEX;

CREATE TABLE labor_hour (
WORK_DATE DATE,
EMPLOYEE_NO VARCHAR2(8),
CONSTRAINT pk_labor_hour
PRIMARY KEY (work_date, employee_no))
ORGANIZATION INDEX;

SELECT table_name, iot_name, iot_type
FROM user_tables;

DROP TABLE labor_hour;

SELECT object_name, original_name, type, related
FROM user_recyclebin;

FLASHBACK TABLE labor_hour TO BEFORE DROP;

SELECT object_name, object_type
FROM user_objects
ORDER BY 1,2;

ALTER INDEX "BIN$jzohHP0LRqusV3X3jtaDRQ==$0" RENAME TO pk_labor_hour;

Index Compressed IOT

CREATE TABLE (
,
,
CONSTRAINT
PRIMARY KEY ())
ORGANIZATION INDEX
COMPRESS ;

CREATE TABLE compressed_iot
(owner, object_type, object_name,
CONSTRAINT pk_compressed_iot
PRIMARY KEY(owner, object_type, object_name))
ORGANIZATION INDEX
COMPRESS 2 AS
SELECT owner, object_type, object_name
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

Complex IOT with Including Clause

CREATE TABLE (
,
,
CONSTRAINT
PRIMARY KEY ())
ORGANIZATION INDEX
INCLUDING
OVERFLOW TABLESPACE ;

CREATE TABLE labor_hour (
WORK_DATE DATE,
EMPLOYEE_NO VARCHAR2(8),
SUMMIT_WORK_ORDER_NO VARCHAR2(7),
DASH VARCHAR2(2),
CLASS_CODE VARCHAR2(6),
PAYCODE VARCHAR2(2),
ASSIGNED_CREW_NUMBER VARCHAR2(5),
TRANSFER_CREW_NUMBER VARCHAR2(5),
REFERENCE_TYPE VARCHAR2(1),
REFERENCE_NUMBER VARCHAR2(10),
OVERTIME_CODE VARCHAR2(1),
SHIFT_DIFFERENTIAL VARCHAR2(1) NOT NULL,
HOURS NUMBER(4,2) NOT NULL,
MOD_USER_ID VARCHAR2(30) DEFAULT USER,
MOD_USER_DATE DATE DEFAULT SYSDATE,
CONSTRAINT pk_labor_hour
PRIMARY KEY (work_date, employee_no, summit_work_order_no, dash, class_code, paycode, assigned_crew_number, transfer_crew_number, reference_type, reference_number, overtime_code, shift_differential))
ORGANIZATION INDEX
INCLUDING hours
OVERFLOW TABLESPACE uwdata;

Complex IOT with Including Clause And Partitioning

CREATE TABLE (
,
,
CONSTRAINT
PRIMARY KEY ())
ORGANIZATION INDEX
INCLUDING
OVERFLOW TABLESPACE
PARTITION BY RANGE ()
();

-- DDL for the tablespaces required for this demo are [here]

CREATE TABLE labor_hour (
WORK_DATE DATE,
EMPLOYEE_NO VARCHAR2(8),
SUMMIT_WORK_ORDER_NO VARCHAR2(7),
DASH VARCHAR2(2),
CLASS_CODE VARCHAR2(6),
PAYCODE VARCHAR2(2),
ASSIGNED_CREW_NUMBER VARCHAR2(5),
TRANSFER_CREW_NUMBER VARCHAR2(5),
REFERENCE_TYPE VARCHAR2(1),
REFERENCE_NUMBER VARCHAR2(10),
OVERTIME_CODE VARCHAR2(1),
SHIFT_DIFFERENTIAL VARCHAR2(1) NOT NULL,
HOURS NUMBER(4,2) NOT NULL,
MOD_USER_ID VARCHAR2(30) DEFAULT USER,
MOD_USER_DATE DATE DEFAULT SYSDATE,
CONSTRAINT pk_labor_hour
PRIMARY KEY (work_date, employee_no, summit_work_order_no, dash, class_code, paycode, assigned_crew_number, transfer_crew_number, reference_type, reference_number, overtime_code, shift_differential))ORGANIZATION INDEXINCLUDING hoursOVERFLOW TABLESPACE uwdata
PARTITION BY RANGE (work_date) (
PARTITION yr06 VALUES LESS THAN (TO_DATE('01-JAN-2007', 'DD-MON-YYYY')) TABLESPACE part1,
PARTITION yr07 VALUES LESS THAN (TO_DATE('01-JAN-2008', 'DD-MON-YYYY')) TABLESPACE part2,
PARTITION yr08 VALUES LESS THAN (TO_DATE('01-JAN-2009', 'DD-MON-YYYY')) TABLESPACE part3,
PARTITION yr99 VALUES LESS THAN (MAXVALUE) TABLESPACE part4);

Mapping Table Clause

Specify MAPPING TABLE to instruct Oracle to create a mapping of local to physical ROWIDs and store them in a heap-organized table. This mapping is needed in order to create a bitmap index on the index-organized table.

Oracle creates the mapping table in the same tablespace as its parent index-organized table. You cannot query, perform DML operations on, or modify the storage characteristics of the mapping table.

You cannot specify the mapping_table_clause for a partitioned index-organized table.

Create IOT with mapping table

CREATE TABLE (
,
,
CONSTRAINT (ORGANIZATION INDEX
MAPPING TABLE;

CREATE TABLE t (
x INT,
y INT,
CONSTRAINT pk_t_iot PRIMARY KEY(x))
ORGANIZATION INDEX
MAPPING TABLE;

col iot_map_table new_val iot_map

SELECT 'SYS_IOT_MAP_' || object_id iot_map_table
FROM user_objects
WHERE object_name = 'T';

desc &iot_map

-- as rows are inserted they are mapped to mapping table

column SYS_NC_01 format a20

INSERT INTO t VALUES (1, 2);
INSERT INTO t VALUES (2, 2);

SELECT rowid, a.* FROM &iot_map a;

-- on update logical row changes but mapping table row doesn't
update t set x = 3 where x = 1;

SELECT rowid, a.* FROM &iot_map a;

-- create a bitmapped index
CREATE BITMAP INDEX bix_t
ON t(y);

index table (IOT)

相關文章