DATABASE CLUSTER NOTES:

Owner - Owner of the table/cluster

Tablespace - Name of the tablespace containing the cluster

Cluster Name - Name of the cluster

Table Name - Clustered table name

Table Column - Key column in the table

Cluster Column - Key column in the cluster

A cluster can be used when several tables store a row that is of the same data type and size in the same location. This reduces storage requirements and in some cases can speed access to data. The major drawback is that in operations involving updates, inserts and deletes there can be performance degradation. The DBA should look at the expected mix of transaction types on the tables to be clustered and only cluster those that are frequently joined and don't have numerous updates, inserts and deletes.

Clusters store shared data values in the same physical blocks (the cluster key values). For tables that are frequently joined this can speed access, for tables frequently accessed separately joining is not the answer. An exception is when a single table is clustered. A single table cluster forces the key values for that table into a single set of blocks thus access can be sped up for accesses of that table. Usually this single table clustering also uses a HASH structure to further improve access times.

Oracle also has the ability to specify a HASH cluster. A HASH cluster uses a HASH form of storage and no index. Hash structures should only be used for static tables. Hashing is the process where a value, either of a unique or non-unique row, is used to generate a hash value. This hash value is used to place the row into the hashed table. To retrieve the row, the value is simply recalculated. Hashes can only by used for equality operations.