Database Reference
In-Depth Information
PCTTHRESHOLD 50 OVERFLOW
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
TABLESPACE "USERS"
EODA@ORA12CR1> select dbms_metadata.get_ddl( 'TABLE', 'T3' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','T3')
--------------------------------------------------------------------------------
CREATE TABLE "EODA"."T3"
( "X" NUMBER(*,0),
"Y" VARCHAR2(25),
"Z" DATE,
PRIMARY KEY ("X") ENABLE
) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
TABLESPACE "USERS"
PCTTHRESHOLD 50 INCLUDING "Y" OVERFLOW
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
TABLESPACE "USERS"
So, now we have
PCTTHRESHOLD
,
OVERFLOW
, and
INCLUDING
left to discuss. These three items are intertwined, and
their goal is to make the index leaf blocks (the blocks that hold the actual index data) able to efficiently store data. An
index is typically on a subset of columns. You will generally find many more times the number of row entries on an
index block than you would on a heap table block. An index counts on being able to get many rows per block. Oracle
would spend large amounts of time maintaining an index otherwise, as each
INSERT
or
UPDATE
would probably cause
an index block to split in order to accommodate the new data.
The
OVERFLOW
clause allows you to set up another segment (making an IOT a multisegment object, much like
having a
CLOB
column does) where the row data for the IOT can overflow onto when it gets too large.
■
Note
the columns making up the primary key cannot overflow—they must be placed on the leaf blocks directly.
Notice that an
OVERFLOW
reintroduces the
PCTUSED
clause to an IOT when using MSSM.
PCTFREE
and
PCTUSED
have the same meanings for an
OVERFLOW
segment as they do for a heap organized table. The conditions for using an
overflow segment can be specified in one of two ways:
PCTTHRESHOLD
: When the amount of data in the row exceeds that percentage of the block, the
trailing columns of that row will be stored in the overflow. So, if
PCTTHRESHOLD
was 10 percent
and your block size was 8KB, any row that was greater than about 800 bytes in length would
have part of it stored elsewhere, off the index block.
•
INCLUDING
: All of the columns in the row up to and including the one specified in the
INCLUDING
clause are stored on the index block, and the remaining columns are stored in the
overflow.
•
Given the following table with a 2KB block size:
EODA@ORA12CR1> create table iot
2 ( x int,
3 y date,
4 z varchar2(2000),
