When dealing with the storage properties of objects like tables and
indexes, the only tweakable items for a long time were the PCTFREE and
PCTUSED parameters. Such is not the case any longer, as the Oracle DBA
has many more options at his or her disposal.
When planning the storage and placement of your object structures, keep
the following points in mind:
Use LMTs - Almost all objects should now be placed into
locally-managed tablespaces to avoid the headaches that used to be
associated with object-based fragmentation (reaching maximum extent
limits, performance degradation due to many extents, etc.).
Use multiple blocksizes - If Oracle9i is being used, then indexes,
large tables that are scanned often, tables whose rows have the
capability to exceed a small database block size, and LOB tables should
be placed into tablespaces with large block sizes (16K 32K).
Separate tables and child indexes - If distinct, physical drives are
being used on the database server, then tables and their child indexes
should be physically separated onto different drives.
Use partitioning - Tables and indexes that have the potential to grow
large and that are scanned frequently should be intelligently
partitioned in hopes that only certain partitions, and not the entire
object, will be scanned.
Use automatic segment management - Tables and indexes that are the
likely targets of much INSERT activity should be placed into tablespaces
that use automatic segment management instead of traditional free list
management (available only in Oracle9i and above). Note that LOBs cannot
be stored in such tablespaces.
Use read only tablespaces - Tables that will be read only should be
placed into read only tablespaces.
Use automatic UNDO - If Oracle9i is being used, the automatic UNDO
management should be employed to alleviate the need for sizing and
creating individual rollback segments
