Segments

Segments are space-occupying objects in the database's data files. This means that they are associated with things that take up space, such as tables, indexes, clusters, and other similar objects.

Table Segments

Tables are the most common means of storing data within a database. A table's segment stores the data for a table that is neither a cluster table nor a partitioned table. Data within the segment is stored in no particular order, a situation that the database administrator (DBA) has little control over. DBAs can't control the location of rows within the blocks within the segments in the table. All the data within a table segment must be stored within a single tablespace.

Table Partition Segment

Because scalability, maintainability, and availability are primary concerns in a database, particularly when a given table reaches either an extremely large number of rows, or an inordinate number of concurrent usage, a table can be partitioned, with data from that single table residing in several partitions, each in its own tablespace. If a table is partitioned, each partition is a segment. This means that storage parameters can be specified for each individual partition independent of each other.

Partitioning, and therefore this type of segment, is only available with the Oracle 9i Enterprise Edition.

Cluster Segments

A cluster, somewhat like a table, is also a type of data segment. Rows in a table that is a member of a cluster are based on a key column's values. All the tables in a cluster belong to the same segment and therefore share that segment's storage characteristics. To access the rows in a cluster, you need to use either an index or a hashing algorithm.

Index Segments

Each index on a table has its own single index segment. Its purpose (the index and the segment) is to look up the location of the referenced rows in the table based on a key value. It is important to remember that index segments can be, and usually should be, in different tablespaces than the table segments with which they are associated.

Index-Organized Table

An index-organized table (IOT) stores data within the index based on the key value. An IOT doesn't need a table against which to look up the values because all the data is retrieved directly from the index.

Index Partition

Indexes can be partitioned and spread across several tablespaces just like tables can. The primary reason to partition indexes is to distribute index I/O. Again, as with table partitioning, the option requires the use of Oracle 9i Enterprise Edition.

Undo Segment

Undo segments are used by transactions making changes to the database to store the information necessary to undo the changes that they are making. Before the change is made, the old values are stored to the undo segments allowing the changes to be undone.

Temporary Segment

Oracle wisely tries to perform sorting operations in memory if they can be processed there because memory is faster than disk access. Sorts are done any time a user creates an index, selects distinct values from tables, selects with an order by or group by clause, and many other times. But if a sort needs more space than is available in memory, the intermediate results of the sort are stored to disk temporarily. These results are stored in temporary segments.

LOB Segment

Large Objects Binary (LOBs) can be used in Oracle to store, in a table column, objects larger than could fit in any other kind of column. It can store text documents, video, audio, images, and so on. The Oracle Server stores these values not in the segment with the rest of the tabular data, but rather in separate segments known as LOB segments. The table, and therefore the table's segment, stores only the locator pointer to the location of the referenced LOB data. LOB segments can, and usually should, be located in different tablespaces than the table segment with which they are associated.

Nested Table Segment

A user-defined table can be stored as a single column in another table. The user-defined pseudo-table, or nested table, can store things such as items in an order, address lines in an address, or payments made on an insurance claim among other things. In this way, one row/column combination can be used to store several columns and several rows that correspond to the row's key all within that single outer row. The inner, nested table is stored within a separate segment from the outer table's segment.

Bootstrap Segment

The bootstrap, or cache, segment is created when you create the database when the Oracle Server runs the sql.bsq script. This segment initializes the data dictionary cache when the database is opened by the instance. This segment can neither be queried nor updated and requires no maintenance (my favorite kind of segment!).

Recall that a segment is made up of extents. You can specify a storage clause to specify at the segment level how to control the ways that the extents are allocated.

Storage parameters set at the segment level override any of those same parameters that have been set at the tablespace level except for the minimum extent size or uniform size tablespace parameter. If the parameters aren't set at the segment level, they default to the tablespace level parameter values, and if not set at that level, they default to the Oracle Server's system level default values.

It is important to remember that the default values are set extremely low. The storage parameters, therefore, should be set at least at the tablespace level or at the segment level.

If the storage parameters are altered after the creation of the segment, the new values apply only to those extents not yet allocated to the segment.

There are parameters that cannot be specified at the tablespace level and therefore should be specified at the segment level.