Database Reference
In-Depth Information
5 )
6 organization index
7 OVERFLOW;
Table created.
EODA@ORA12CR1> create table t3
2 ( x int primary key,
3 y varchar2(25),
4 z date
5 )
6 organization index
7 overflow INCLUDING y;
Table created.
We'll get into what
OVERFLOW
and
INCLUDING
do for us, but first let's look at the detailed SQL required for the
first table:
EODA@ORA12CR1> select dbms_metadata.get_ddl( 'TABLE', 'T1' ) from dual;
DBMS_METADATA.GET_DDL('TABLE','T1')
--------------------------------------------------------------------------------
CREATE TABLE "EODA"."T1"
( "X" NUMBER(*,0),
"Y" VARCHAR2(25),
"Z" DATE,
PRIMARY KEY ("X") ENABLE
) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PCTTHRESHOLD 50
This table introduces a new option,
PCTTHRESHOLD
, which we'll look at in a moment. You might have noticed that
something is missing from the preceding
CREATE TABLE
syntax: there is no
PCTUSED
clause, but there is a
PCTFREE
. This is
because an index is a complex data structure that isn't randomly organized like a heap, so data must go where it belongs.
Unlike a heap, where blocks are sometimes available for inserts, blocks are always available for new entries in an index.
If the data belongs on a given block because of its values, it will go there regardless of how full or empty the block is.
Additionally,
PCTFREE
is used only when the object is created and populated with data in an index structure. It is not
used like it is in the heap organized table.
PCTFREE
will reserve space on a newly created index, but not for subsequent
operations on it. The same considerations for
FREELIST
s we had on heap organized tables apply in whole to IOTs.
First, let's look at the
NOCOMPRESS
option. This option is different in implementation from the table compression
discussed earlier. It works for any operation on the index organized table (as opposed to the table compression which
may or may not be in effect for conventional path operations). Using
NOCOMPRESS
, it tells Oracle to store each and
every value in an index entry (i.e., do not compress). If the primary key of the object were on columns
A
,
B
, and
C
,
every occurrence of
A
,
B
, and
C
would physically be stored. The converse to
NOCOMPRESS
is
COMPRESS N
, where
N
is an
