To help improve
performance further, index-organized tables offer additional features:
An overflow area By setting the pctthreshold parameter when the IOT is created, you
can store the primary key data apart from the row data. If the row??™s data exceeds the
threshold of available space in the block, it will dynamically be moved to an overflow
?–
Chapter 8: Database Tuning 261
area. You can designate the overflow area to be in a separate tablespace, improving your
ability to distribute the I/O associated with the table.
Secondary indexes You can create secondary indexes on the IOT. Oracle will use the
primary key values as the logical RowIDs for the rows.
Reduced storage requirements In a traditional table/index combination, the same key
values are stored in two places. In an IOT, they are stored once, reducing the storage
requirements.
TIP
When specifying an overflow area, you can use the including column
clause to specify the column (and all successive columns in the table
definition) that will be stored in the overflow area:
create table ord_iot
(order_id number,
order_date date,
order_notes varchar2(1000), primary key(order_id,order_date))
organization index including order_date
overflow tablespace over_ord_tab
PARTITION BY RANGE (order_date)
(PARTITION p1 VALUES LESS THAN ('01-JAN-2005')
TABLESPACE data01,
PARTITION p2 VALUES LESS THAN (MAXVALUE)
TABLESPACE data02);
Both order_date and order_notes will be stored in the overflow area.
Pages:
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442