Databases Reference
In-Depth Information
21 (SUBPARTITION JOB10 VALUES ('10')
22 ,SUBPARTITION JOB20 VALUES ('20')
23 ,SUBPARTITION JOB30 VALUES ('30')
24 ,SUBPARTITION JOB40 VALUES ('40')
25 ,SUBPARTITION JOB50 VALUES ('50')
26 ,SUBPARTITION JOB60 VALUES ('60')
27 ,SUBPARTITION JOB70 VALUES ('70')
28 ,SUBPARTITION JOB80 VALUES ('80')
29 ,SUBPARTITION JOB90 VALUES ('90')
30 ,SUBPARTITION JOB100 VALUES ('100')
31 ,SUBPARTITION JOB110 VALUES ('110')
32 (
33 partition p1990 values less than ('1991-01-01'),
...
45 );
subpartition template
*
ERROR at line 20:
ORA-25198: only range, list, and hash partitioning are supported for
index-organized table
This error clearly indicates that composite partitioning is not supported. For more information on the
features of IOT's and their limitations, see the
Oracle Database Administrator's Guide
for your release of
the database.
Adding an Overflow Segment
For index-organized tables, it is common, and even recommended, to create an overflow area for row
data as part of the overall index-organized table structure. The typical index-organized table that
includes an overflow area is structured as follows:
•
B-tree index entry, which includes the following:
•
Primary key columns
•
Some non-key columns depending on PCTTHRESHOLD and
INCLUDING clause values specified
•
Physical ROWID pointer to overflow segment
Overflow segment, which contains the remaining non-key column values
In a normal B-tree index, the leaf node contains the index column key value, and then the
ROWID
for the
row in the data segment. With index-organized tables, all the non-key column values are stored within
the leaf blocks of the index by default. If the row data becomes very wide, the B-tree entries can become
very large. This can slow data retrieval simply because the index must traverse more index blocks.
The overflow segment can aid in the efficiency of the overall B-tree index of an index-organized
table by storing some of the non-key column values in an overflow data segment of the IOT, which is
used solely to store these non-key column values. Associated with the overflow area is the
PCTTHRESHOLD
parameter, which specifies how column data goes to the overflow segment. If the length of a row is
•
