RelationalDBDesign RelationalDBDesign 


Partitioned Tuning   «Prev 

Advantages to Reorganizing Selected Index Partitions

Table Partitions

  1. Can be reorganized faster than the whole table
  2. Can be segregated into separate tablespaces independently of the other partitions
  3. Can be made read only, independently of other table partitions. This is a great feature for a data warehouse.

Question: What Are Index-Organized Tables?
An index-organized table, in contrast to an ordinary table, has its own way of structuring, storing, and indexing data. If you compare this concept to an ordinary table it may help to explain its uniqueness.

Index-Organized Tables Versus Ordinary Tables

A row in an ordinary table has a stable physical location. Once this location is established, the row never completely moves. Even if it is partially moved with the addition of new data, there is always a row piece at the original physical address which is identified by the original physical rowid from which the system can find the rest of the row.
As long as the row exists, its physical rowid does not change. An index in an ordinary table stores both the column data and the rowid.
A row in an index-organized table does not have a stable physical location. It keeps data in sorted order, in the leaves of a B*-tree index built on the table's primary key. These rows can move around to preserve the sorted order. For example, an insertion can cause an existing row to move to a different slot, or even to a different block.
The leaves of the B*-tree index hold the primary key and the actual row data. Changes to the table data. For example, adding new rows, or updating or deleting existing rows, result only in updating the index.