Oracle cluster Table User Guide

 

Oracle supports two types of clustering: Index clustering and hash clustering.

 

 

Guide to index Clustering

 

L create clustering for tables frequently accessed in connection statements.

 

L if the table is occasionally connected or its public columns are often modified, do not cluster the table. (Modifying the record's clustered key value takes more time than modifying this value in a non-clustered table, because Oracle must port the modified records to other blocks to maintain clustering ).

 

L if you often need to perform a full search on a table, do not cluster the table (the full search for a cluster Table is longer than the full search on a non-cluster table, oracle may need to read more blocks because tables are stored together .)

 

L if records are frequently queried from a parent table and corresponding sub-tables, create a cluster table for a one-to-many (1: *) relationship. (Sub-table records are stored in the same data block as the parent table records. Therefore, when retrieving them, they can be stored in the memory at the same time. Therefore, less I/O needs to be performed in Oracle ).

 

L if you frequently query multiple sub-records in the same parent table, consider clustering the sub-Table separately. (This improves the performance of querying sub-table records from the same parent table, and does not reduce the full search performance for the parent table ).

 

L if more than one or two Oracle blocks are queried from all tables with the same cluster key value, do not create a cluster table. (To access records in a cluster Table, Oracle reads all data blocks containing the record value. If the record occupies multiple data blocks, the number of times that a record needs to be read is more than the number of times that the same record is accessed in a non-clustered table ).

 

 

Guide to Using hash Clustering

 

L when a query clause with the same columns containing the same conditions is frequently used to access the table, hash clustering is used to store the table. Use these columns as clustering keys.

 

L if you can determine the space required to store all records with the given cluster key value (including current and future records), store the table in a hash cluster.

 

L if you do not have enough space and cannot allocate additional space for new records to be inserted, do not use Hash clustering.

 

L if it is impractical to occasionally create a new, large hash Clustering Cluster to store such a table, do not use Hash clustering to store tables that frequently grow.

 

L if you often need to perform full table search and allocate enough space for the hash clustering in the expected growth of the table, do not store the table as a hash clustering. (Such a full search must read all the blocks allocated to the hash clustering, even if some blocks may only contain a small number of records. Storing a table separately reduces the number of blocks retrieved from the full table .)

 

L if your applications often modify the clustering key value, do not store the table in a hash clustering mode.

 

L no matter whether the table is frequently connected to other tables, it may be useful to store a table in a hash cluster as long as the hash is suitable for tables based on previous guidelines.

 

Author: kill522