Oracle Database Redo Logs and Index Optimization

Redo Log File Management

The file system provides warnings when additional redo log groups are required. When a group is filled, a LOG FILE SWITCH event occurs. Control passes to the next group until the end of the sequence is reached, at which point the system returns to the first group and overwrites the data (acting as a circular buffer), potentially losing track of transactions.

Redo Log Files

The set of redo log files is known as the Redo Log of the database. Its primary function is to record all changes made to data. These files are critical for protecting the database against failures and are essential for recovery.

Groups and Members

• Groups: Redo log files operate in a sequence; one is active while the others wait. Each group has a unique number and must contain members with identical configurations.
• Members: Each redo log record is written to all active members within a group. Members must:
  • Have the same size and sequence number.
  • Use similar naming conventions and reside on different physical disks to ensure effective failure protection.

Database Indexing and Performance

Indices are objects that enable faster data access. They can be created on one or more fields to accelerate searches, particularly for fields frequently used in WHERE clauses.

Avoiding Full Table Scans (FTS)

Without indexes, the DBMS must perform a Full Table Scan (FTS), traversing the entire table to retrieve information, which fills the Buffer Cache with unnecessary data. To avoid FTS, indexes should be implemented.

Index Structure and Maintenance

Indexes are stored in tree structures. Oracle utilizes threaded binary trees where child nodes are linked by a doubly linked list to facilitate range searches. In these trees, nodes to the left are smaller than the parent, while nodes to the right are larger.

Accessing Data via ROWID

When accessing an index, the system retrieves two fields:

1. The value sought.
2. The ROWID, which acts as a pointer to the exact physical location of the data within the table.

Note that for very small tables, indexes may be inefficient as they require accessing both the index and the table. However, as the number of records increases, indexes become vital. If indexes are not maintained, the trees can degrade into linked lists, causing performance to revert to FTS speeds. In such cases, indexes must be rebuilt.