Oracle Architecture and Concepts

An extent is a logical unit of database storage space allocation made up of a number of contiguous data blocks. One or more extents in turn make up a segment. When the existing space in a segment is completely used, Oracle allocates a new extent for the segment. When you create a table, Oracle allocates to the table's data segment an initial extent of a specified number of data blocks. Although no rows have been inserted yet, the Oracle data blocks that correspond to the initial extent are reserved for that table's rows. If the data blocks of a segment's initial extent become full and more space is required to hold new data, Oracle automatically allocates an incremental extent for that segment. An incremental extent is a subsequent extent of the same or greater size than the previously allocated extent in that segment. (The next section explains the factors controlling the size of incremental extents.) For maintenance purposes, the header block of each segment contains a directory of the extents in that segment.

Hash Clusters cluster table data in a manner similar to normal, index clusters. To find or store a row in a hash cluster, Oracle applies a hash function to the row's cluster key value. The resulting hash value corresponds to a data block in the cluster.All rows with the same key value are stored together on disk.Hash clusters are a better choice than using an indexed table or index cluster when a table is often queried with equality queries (for example, WHERE product_id=123). For such queries, the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the rows.This reduces the amount of I/Os that must be performed to locate and read/write a row of data.

A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace. For example, for each table, Oracle allocates one or more extents to form that table's data segment; for each index, Oracle allocates one or more extents to form its index segment. Oracle databases use four types of segments, these are:Data SegmentsIndex SegmentsTemporary SegmentsRolback SegmentsData Segments: A single data segment in an Oracle database holds all of the data for one of the following: a table that is not partitioned or clustered a partition of a partitioned table a cluster of tables Oracle creates this data segment when you create the table or cluster with the CREATE command. The storage parameters for a table or cluster determine how its data segment's extents are allocated. You can set these storage parameters directly with the appropriate CREATE or ALTER command. These storage parameters affect the efficiency of data retrieval and storage for the data segment associated with the object. Index Segments: Every nonpartitioned index in an Oracle database has a single index segment to hold all of its data. For a partitioned index, every partition has a single index segment to hold its data. Oracle creates the index segment for an index or an index partition when you issue the CREATE INDEX command. In this command, you can specify storage parameters for the extents of the index segment and a tablespace in which to create the index segment. (The segments of a table and an index associated with it do not have to occupy the same tablespace.) Setting the storage parameters directly affects the efficiency of data retrieval and storage.Temporary Segments: When processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement parsing and execution. Oracle automatically allocates this disk space called a temporary segment. Typically, Oracle requires a temporary segment as a work area for sorting. Oracle does not create a segment if the sorting operation can be done in memory or if Oracle finds some other way to perform the operation using indexesRollback Segments: Each database contains one or more rollback segments. A rollback segment records the old values of data that was changed by each transaction (whether or not committed). Rollback segments are used to provide read consistency, to roll back transactions, and to recover the database

Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. This improves disk access time.The related columns of the tables in a cluster are called the cluster key. The cluster key is indexed so that rows of the cluster can be retrieved with a minimum amount of I/O.No matter how many tables within the cluster contain the cluster key value, it is stored only once each in the cluster and the cluster index. Therefore, less storage is required.Whether or not a table is part of a cluster is transparent to users and to applications. Data stored in a clustered table is accessed by SQL in the same way as data stored in a nonclustered table.