Creating an Index-Organized Table- Tables and Constraints

Index-organized tables (IOTs) are efficient objects when the table data is typically accessed through querying on the primary key. Use the ORGANIZATION INDEX clause to create an IOT:

SQL> create table prod_sku (prod_sku_id number, sku varchar2(256), create_dtt timestamp(5), constraint prod_sku_pk primary key(prod_sku_id) ) organization index including sku pctthreshold 30 tablespace inv_data overflow tablespace inv_data;

An IOT stores the entire contents of the table’s row in a B-tree index structure. IOTs provide fast access for queries that have exact matches or range searches, or both, on the primary key.

All columns specified, up to, and including the column specified in the INCLUDING clause are stored in the same block as the PROD_SKU_ID primary key column. In other words, the INCLUDING clause specifies the last column to keep in the index segment. Columns listed after the column specified in the INCLUDING clause are stored in the overflow data segment. In the previous example, the CREATE_DTT column is stored in the overflow segment.

PCTTHRESHOLD specifies the percentage of space reserved in the index block for the IOT row. This value can be from 1 to 50 and defaults to 50 if no value is specified. There must be enough space in the index block to store the primary key.

The OVERFLOW clause details which tablespace should be used to store overflow data segments. Note that DBA/ALL/USER_TABLES includes an entry for the table name used when creating an IOT. Additionally, DBA/ALL/USER_INDEXES contains a record with the name of the primary key constraint specified. The INDEX_TYPE column contains a value of IOT – TOP for IOTs:

SQL> select index_name,table_name,index_type from user_indexes;

Managing Constraints

The next several sections in this chapter deal with constraints. Constraints provide a mechanism for ensuring that data conform to certain business rules. You must be aware of what types of constraints are available and when it is appropriate to use them. Oracle offers several types of constraints:
• Primary key
• Unique key
• Foreign key
• Check
• NOT NULL

The following sections discuss implementing and managing these constraints.