Database Reference
In-Depth Information
Figure 18.10
CREATE TABLE
Syntax for an
Index-Organized
Table.
18.3.4
Creating Index-Organized Tables (IOTs)
An index-organized table (IOT) simply organizes data in the table in the
order of the primary key index, in a BTree structure. In other words, the
entire table becomes a sorted BTree index. That sorted table can subse-
quently be accessed as an index in index-sorted order. The obvious perfor-
mance benefit is primary key sorted order scanning. The downside is that
an IOT is more likely to be a better performer when reading rather than
changing data, although the word out there is that IOTs can perform very
well in even OLTP databases.
1
Figure 18.10 shows a syntax diagram containing syntax details relevant
to creating IOTs.
As an example, we create an IOT version of the MUSIC schema SALES
table called SALESIOT.
CREATE TABLE SALESIOT(
SALES_ID NUMBER NOT NULL
, MUSICCD_ID NUMBER NOT NULL
, CUSTOMER_ID NUMBER NOT NULL
, RETAILER_ID NUMBER
, CONTINENT_ID NUMBER
, COUNTRY_ID NUMBER
, LIST_PRICE FLOAT
, DISCOUNT FLOAT
, SALE_PRICE
FLOAT
, SALE_DATE
DATE
