读书笔记 Expert Oracle Database Architecture

Chapter 10: Database Tables

PCTFREE and PCTUSED
When you are using ASSM, PCTFREE still limits if a new row may be inserted into a block, but it does not control whether a block is on a freelist or not, as ASSM does not use freelists at all. In ASSM, PCTUSED is simply ignored.

There are three settings for PCTFREE: too high, too low, and just about right. If you set PCTFREE for blocks too high, you will waste space. If you set PCTFREE to 50 percent and you never update the data, you have just wasted 50 percent of every block. On another table, however, 50 percent may be very reasonable. If the rows start out small and tend to double in size,
setting PCTFREE too small will cause row migration as you update the rows.

Row Migration
Row migration is when a row is forced to leave the block it was created on because it grew too large to fit on that block with the rest of the rows.

INITRANS and MAXTRANS

Each block in a segment has a block header. Part of this block header is a transaction table. Entries will be make in the transaction table to describe which transactions have what rows/elements on the block locked. The initial size of this transaction table is specified by the INITRANS setting for the object. For table, this defaults to 1 (indexes default to 2). This transaction table will grow dynamically as needed up to MAXTRANS entries in size (given sufficient free space on the block, that is). Each allocated transaction entry consumes 23 to 24 bytes of storage
in the block header. Note that as of Oracle 10g, MAXTRANS is ignored—all segments have a
MAXTRANS of 255.

When to use IOT (Index Organized Tables)

• Anytime you have a table that you access via its primary key exclusively, it is a candidate for an IOT.
• When you want to enforce co-location of data or you want data to be physically stored in a specific order, the IOT is the structure for you. For users of Sybase and SQL Server, this is where you would have used a clustered index, but IOTs go one better.