DBMS_IOT, 2 of 2

Summary of Subprograms

Table 16-1 DBMS_IOT Package Subprograms

Subprogram	Description
BUILD_CHAIN_ROWS_TABLE Procedure	Creates a table into which references to the chained rows for an index-organized table can be placed using the `ANALYZE` command.
BUILD_EXCEPTIONS_TABLE Procedure	Creates an exception table into which rows of an index-organized table that violate a constraint can be placed during the `enable_constraint` operation.

BUILD_CHAIN_ROWS_TABLE Procedure

The BUILD_CHAIN_ROWS_TABLE procedure creates a table into which references to the chained rows for an index-organized table can be placed using the ANALYZE command.

Syntax

DBMS_IOT.BUILD_CHAIN_ROWS_TABLE (
   owner               IN VARCHAR2,
   iot_name            IN VARCHAR2,
   chainrow_table_name IN VARCHAR2 default 'IOT_CHAINED_ROWS');

Parameters

Table 16-2 BUILD_CHAIN_ROWS_TABLE Procedure Parameters

Parameter	Description
owner	Owner of the index-organized table.
iot_name	Index-organized table name.
chainrow_table_name	Intended name for the chained-rows table.

Example

CREATE TABLE l(a char(16),b char(16), c char(16), d char(240), 
PRIMARY KEY(a,b,c)) ORGANIZATION INDEX pctthreshold 10 overflow;    
EXECUTE DBMS_IOT.BUILD_CHAIN_ROWS_TABLE('SYS','L','LC');

A chained-row table is created with the following columns:

Column Name                    Null?    Type  
------------------------------ -------- ----  
OWNER_NAME                              VARCHAR2(30)  
TABLE_NAME                              VARCHAR2(30)  
CLUSTER_NAME                            VARCHAR2(30)  
PARTITION_NAME                          VARCHAR2(30)  
SUBPARTITION_NAME                       VARCHAR2(30)  
HEAD_ROWID                              ROWID  
TIMESTAMP                               DATE  
A                                       CHAR(16)  
B                                       CHAR(16)  
C                                       CHAR(16)

BUILD_EXCEPTIONS_TABLE Procedure

The BUILD_EXCEPTIONS_TABLE procedure creates an exception table into which rows of an index-organized table that violate a constraint can be placed during the enable_constraint operation.

A separate chained-rows table and an exception table should be created for each index-organized table to accommodate its primary key.

Note:
This form of chained-rows table and exception table are required only for servers running with Oracle8, Release 8.0 compatibility.

Syntax

DBMS_IOT.BUILD_EXCEPTIONS_TABLE (
   owner                 IN VARCHAR2,
   iot_name              IN VARCHAR2,
   exceptions_table_name IN VARCHAR2 default 'IOT_EXCEPTIONS');

Parameters

Table 16-3 BUILD_EXCEPTIONS_TABLE Procedure Parameters

Parameter	Description
owner	Owner of the index-organized table.
iot_name	Index-organized table name.
exceptions_table_name	Intended name for exception-table.

Example

EXECUTE DBMS_IOT.BUILD_EXCEPTIONS_TABLE('SYS','L','LE');

An exception table for the above index-organized table with the following columns:

Column Name                    Null?    Type  
------------------------------ -------- ----  
ROW_ID                                  VARCHAR2(30)  
OWNER                                   VARCHAR2(30)  
TABLE_NAME                              VARCHAR2(30)  
CONSTRAINT                              VARCHAR2(30)  
A                                       CHAR(16)  
B                                       CHAR(16)  
C                                       CHAR(16)