Thursday, January 14, 2010



Types of segments:

1. Table

 A table segment stores that data for a table that is neither clustered nor partitioned.
 All the data in a table segment must be stored in one tablespace.

2. Table Partition

 Scalability and availability are major concerns, In such cases, data within a table may be stored in several partitions, each of which resides in a different tablespace.
 The Oracle server currently supports partitioning by a range of key values or by a hashing algorithm.

3. Cluster

 A cluster may contain one or more tables.
 Tables in a cluster belong to the same segment and share the same storage Characteristics.
 The rows in a clustered table can be accessed with an index or hashing algorithm.

4. Index

 All the entries for a particular index are stored within one index segment.
 If a table has three indexes, three index segments are used.
5. Index-Organized Table

 In an index-organized table, data is stored within the index based on the key value.
 An index organized table does not need a table lookup, because all the data can be retrieved directly from the index tree.
6. Index Partition

 An index can be partitioned and spread across several tablespaces.
 In this case, each partition in the index corresponds to a segment and cannot span multiple tablespaces.
 The primary use of a partitioned index is to minimize contention by spreading index I/O.

7. Undo Segment

 An undo segment is used by a transaction that is making changes to a database.
 Before changing the data or index blocks, the old value is stored in the undo segment. This allows a user to undo changes made.

8. Temporary Segment

 When a user executes commands such as CREATE INDEX, SELECT DISTINCT, and SELECT GROUP BY, the Oracle server tries to perform sorts in memory.
 When a sort needs more space than the space available in memory, intermediate results are written to the disk.
 Temporary segments are used to store these intermediate results.

9. LOB Segment

 It can be used to store large objects (LOBs) such as text documents, images, or videos.
 The table contains only a locator or a pointer to the location of the corresponding LOB data.

10. Nested Table

 A column in a table may be made up of a user-defined table as in the case of items within an order.
 In such cases, the inner table, which is known as a nested table, is stored as a separate segment.

11. Bootstrap Segment

 A bootstrap segment, also known as a cache segment, is created by the sql.bsq script when a database is created.
 This segment helps to initialize the data dictionary cache when the database is opened by an instance.
 The bootstrap segment cannot be queried or updated and does not require any maintenance by the database administrator.

No comments:

Post a Comment