05 July, 2015

Oracle Storage Structures : Pointers


There are different storage structures available in Oracle. The choice depends upon the purpose and usage of the table.

 There are the following types :

  1. Heap Tables (used by default)
  2. Clusters (master-detail type tables)
  3. Index Organized Tables (data stored acc to its key value)

Now adding few basic detials about each of these types.

HEAP STRUCTURED TABLE

  • Got its name from the way data is added/removed from it. When inserting, the first free storage block is used by oracle.
  • And when any data is deleted from heap table, it can be used by new records.
  • Default structure for oracle.
  • Each row has its unique ROWID associated with it and if the record position changes, this ROWID is also changed.

CLUSTER

  • Clusters are further divided into 2 types - hash cluster and index cluster.
  • Index cluster uses index to maintain the records in table.
  • Hash cluster uses a hash algorithm to locate a row in the table and are better suited for searches where equality operator is used.

These are few points of difference between hash and index based clusters.


INDEX ORGANIZED TABLES

  • Normally the index and the rowdata are stored separately; it increases the storage space . But IOTs store the rowdata in the index itself; which makes it fast and low on memory.
  • Uses a B-Tree index structure to store index.
  • The PK is a must for using IOTs. Data is stored in the PK itself.

Honorable mention for a storage structure used by oracle internally.

To minimize the loss of data, oracle uses redo log files. The size of the files can be configured. 
The lesser the size, the smaller the loss of data be.
And to create even more safeguard for failure, we can use redo log groups.
redo log files can be multiplexed as well and in this, multiple copies of the updates are saved.These multiple copies create a group.
The process of archiving the redo log files is called archiving.  
 But server has to be run in the archivelog mode.
Log Writer process(LGWR) is not allowed to reuse or overwrite redo log files until its archived.

 Oracle can start multiple archived processes and this can be initiated multiple times. At a single time maximum of 10 archival processes can be started.
SELECT * FROM v$log;
If you want to see the information on redo log files.