Explanation of Permanent Segments, UNDO Segments, and Temporary Segments

In an Oracle database, segments are essential components for storing data, managing transactions, and performing temporary processing. This article provides a detailed explanation of Permanent Segments, UNDO Segments, and Temporary Segments, covering their respective roles, uses, and advantages/disadvantages. Additionally, it provides an easy-to-understand supplement on the concept of UNDO, which is the foundation of UNDO segments.

1.Permanent Segment
2.UNDO Segment
3.Temporary Segment
4.Comparison Table: Permanent Segments, UNDO Segments, and Temporary Segments
5.Selection Points by Use Case
6.Conclusion

1.Permanent Segment

A permanent segment is a segment used to store data that is held persistently within the database. It corresponds to objects such as tables, indexes, and clusters, and it securely stores the information saved by users in the database.

Features

Persistence: Data stored in the segment remains permanently until it is explicitly deleted.
Usage: Used for objects such as tables, indexes, clusters, and partitioned tables.
Storage Location: Data Tablespace.
Automatic Management: The Oracle database automatically allocates space.

Example of Permanent Segment

The following SQL statement creates a permanent segment for the employees table.

CREATE TABLE employees (
    id NUMBER,
    name VARCHAR2(100),
    department_id NUMBER
);

This data becomes visible to other sessions once the transaction is committed.

Advantages and Disadvantages

Advantages:
- High reliability and availability due to persistent data storage.
- Can leverage advanced storage options (compression, partitioning).
Disadvantages:
- Storage utilization can become inefficient in some cases (e.g., when unnecessary data is left behind).
- Table growth (bloat) can impact performance.

2.UNDO Segment

An UNDO segment is a segment used to store the change history of transactions, ensuring data consistency and recoverability. The UNDO mechanism streamlines transaction management.

What is UNDO?

UNDO is a mechanism for recording the state of data before a transaction modified it. This record provides the following important functions:

Rollback: Cancels a transaction, returning the data to its previous state.
Consistent Read: Allows other sessions to read data that is currently being modified, in a consistent state (as of a point in time).
Recovery: Restores transactions in the event of a system failure.

UNDO is particularly important in environments with many concurrent transactions and plays a role in maintaining the overall stability of the database.

Features

Data Temporariness: UNDO data is reused after a transaction completes.
Usage:
- Transaction rollback.
- Ensuring consistent reads.
- Providing past snapshots for queries.
Storage Location: UNDO Tablespace.
Automatic Management: Oracle automatically controls the creation, use, and reuse of UNDO segments.

Example of UNDO Segment

In the following SQL, the ROLLBACK statement reverts the data to its original state. The UNDO segment is utilized at this time.

BEGIN TRANSACTION;

UPDATE employees
SET name = 'Jane Doe'
WHERE id = 1;
-- The data before the change is stored in the UNDO segment

ROLLBACK;

Advantages and Disadvantages

Advantages:
- Enables transaction rollbacks.
- Provides high data consistency.
- Guarantees integrity when other sessions access data being modified.
Disadvantages:
- An error (ORA-01555, etc.) can occur if the UNDO tablespace is insufficient.
- Requires proper size management in high-transaction environments.

3.Temporary Segment

A temporary segment is a segment used to store temporary data or intermediate data for processing. It is automatically released when the temporary data processing is finished.

Features

Data Temporariness: Deleted when the data processing is complete.
Usage:
- Sort operations.
- Hash joins.
- Data storage for Temporary Tables.
Storage Location: Temporary Tablespace.
Automatic Management: Oracle automatically creates and releases segments as needed.

Example of Temporary Segment

In the following SQL, a temporary segment is used during the sort operation.

SELECT * FROM employees
ORDER BY name;

When this processing is finished, the temporary segment is released.

Advantages and Disadvantages

Advantages:
- Efficiently utilizes resources, as storage is automatically released after processing.
- Supports large-scale data processing.
Disadvantages:
- An error can occur if the temporary tablespace is insufficient for large sorts or hash joins.
- Storage consumption temporarily increases if excessive temporary data is created.

4.Comparison Table: Permanent Segments, UNDO Segments, and Temporary Segments

Segment Type	Usage	Data Persistence	Storage Location	Main Purpose
Permanent Segment	Tables, indexes, clusters, etc.	Persistent	Data Tablespace	Persistent data storage
UNDO Segment	Transaction change history	Temporary	UNDO Tablespace	Rollback, consistent reads
Temporary Segment	Sorts, temporary data processing	Temporary	Temporary Tablespace	Sorts, hash joins, temporary tables

5.Selection Points by Use Case

When emphasizing data persistence: Design tables and indexes using permanent segments, and consider partitioning or compression.
When ensuring transaction safety and consistency: Appropriately size the UNDO tablespace to guarantee the efficient use of UNDO segments.
When aiming to optimize temporary data processing: Secure sufficient capacity in the temporary tablespace to reduce unnecessary load.

6.Conclusion

Permanent segments, UNDO segments, and temporary segments in the Oracle database each fulfill different roles:

Permanent Segments: For persistent data storage.
UNDO Segments: To ensure transaction safety and data consistency.
Temporary Segments: To support temporary data processing.

By utilizing these segments appropriately, you can streamline database design and operation, making performance optimization possible.

[reference]
Oracle Database Database Performance Tuning Guide, 19c