Oracle Index Organized Tables (IOT)

General

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

[@more@]

Oracle Index Organized Tables (IOT)

Version 10.2

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_columns all_tab_columns user_tab_columns 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_all_tables; 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; 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 uw_data; Complex IOT with Including Clause And Partitioning CREATE TABLE ( , , CONSTRAINT PRIMARY KEY ()) ORGANIZATION INDEX INCLUDING OVERFLOW TABLESPACE PARTITION BY RANGE () (); 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, mod_user_id, mod_user_date; OVERFLOW TABLESPACE uwdata PARTITION BY RANGE (work_date) (PARTITION yr80 VALUES LESS THAN (TO_DATE('01-JAN-1990', 'DD-MON-YYYY')) TABLESPACE data1, PARTITION yr90 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY')) TABLESPACE data2, PARTITION yr00 VALUES LESS THAN (TO_DATE('01-JAN-2001', 'DD-MON-YYYY')) TABLESPACE data3, PARTITION yr01 VALUES LESS THAN (TO_DATE('01-JAN-2002', 'DD-MON-YYYY')) TABLESPACE data4, PARTITION yr02 VALUES LESS THAN (TO_DATE('01-JAN-2003', 'DD-MON-YYYY')) TABLESPACE data5, PARTITION yr03 VALUES LESS THAN (TO_DATE('01-JAN-2004', 'DD-MON-YYYY')) TABLESPACE data6, PARTITION yr99 VALUES LESS THAN (MAXVALUE) TABLESPACE data0); 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);

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/330796/viewspace-885113/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/330796/viewspace-885113/