Databases Reference
In-Depth Information
The IOT can be stored in a different tablespace
to the overflow segment.
The
PCTTHRESHOLD
parameter indicates the percentage of the leaf database block
reserved for a row. If the row size exceeds the size indicated by this parameter, the fields
not indicated by the
INCLUDING
option are stored in the
OVERFLOW
—if indicated,
otherwise the row is not accepted.
Logical ROWID
Due to their nature, index organized tables don't have physical ROWIDs, because the rows
are stored in the index and not in a regular table segment.
To avoid problems related to IOT not having ROWIDs, LOGICAL ROWIDs were introduced
by Oracle. They give access to the rows in a IOT using two paths:
F
A direct access to the file and block where the row is placed (this is a database
engine guess)
F
An access to the row made by the primary key values, if the guess of the preceding
bullet fails
The guess in the first point is due to the fact that we have exact knowledge of the row position
when we create the row in the index. Later, when the leaf block has been eventually split, our
previous knowledge of the physical position is wrong, so the guess will fail.
We can use logical ROWIDs as if they were physical, so we can, for example, add secondary
indexes to an index organized table.
See also
F
In this chapter, see the recipe
Compressing indexes
for details about how to use the
COMPRESS option
Using partitioning
Tables (and indexes) may become very large in a database, driving performance and
maintenance problems.
Partitioning is the way to improve performance in large tables (Oracle suggests to partition
tables with more than 2 GB of data), to ease the configuration and care of these objects and
to reduce downtime in case of failures or scheduled maintenance such as move the tables or
take some data offline.
