To create an IOT, use the organization index clause of the create table command. You must
specify a primary key when creating an IOT. Within an IOT, you can drop columns or mark them
as inactive via the set unused clause of the alter table command.
Tuning Issues for Index-Organized Tables
Like indexes, IOTs may become internally fragmented over time, as values are inserted, updated,
and deleted. To rebuild an IOT, use the move clause of the alter table command. In the following
example, the EMPLOYEE_IOT table is rebuilt, along with its overflow area:
alter table EMPLOYEE_IOT
move tablespace DATA
overflow tablespace DATA_OVERFLOW;
You should avoid storing long rows of data in IOTs. In general, you should avoid using an IOT
if the data is longer than 75 percent of the database block size. If the database block size is 4KB,
and your rows will exceed 3KB in length, you should investigate the use of normal tables and
indexes instead of IOTs. The longer the rows are, and the more transactions are performed against
the IOT, the more frequently it will need to be rebuilt.
NOTE
You cannot use LONG datatypes in IOTs, but you can use LOBs.
?–
?–
262 Oracle Database 11g DBA Handbook
As noted earlier in this chapter, indexes impact data load rates. For best results, the primary
key index of an index-organized table should be loaded with sequential values to minimize the
costs of index management.
Tuning Data Manipulation
Several data manipulation tasks??”usually concerning the manipulation of large quantities of
data??”may involve the DBA.
Pages:
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443