Oracle database index cluster and Hachizu cluster use guide

Use of index clusters:

Create clusters of tables that are often accessed in connection statements.

Do not cluster tables if the tables are only occasionally connected or their public columns are often modified. (It takes more time to modify the record's clustered key than to modify it in a nonclustered table, because Oracle must migrate the modified records to other blocks to maintain the cluster).

If you often need a full search on a table, do not cluster the table (a full search of a clustered table is longer than a full search on a nonclustered table), and Oracle may want to read more chunks because the tables are stored together.

If you frequently query records from a parent table and corresponding child tables, consider creating a clustered table of 1 pairs (1:*) relationships. (child table records are stored in the same block of data as the parent table, so they can be in memory at the same time as they are retrieved, requiring Oracle to do less I/O).

If you frequently query multiple child records in the same parent table, consider clustering the child tables individually. (This increases the performance of querying child table records from the same parent table and does not reduce the performance of full search for the parent table).

Do not cluster tables if the data queried from all tables that have the same clustered key value is more than one or two oracle blocks. (To access records in a clustered table, Oracle reads all blocks of data that contain that record value, and if the record occupies more than one block of data, the number of accesses to one record is more than the number of accesses to the same record in a nonclustered table).

Use of Hachizu clusters:

Consider using Hachizu clusters to store tables when you frequently use query clauses with the same columns that contain equality criteria to access the table. Use these columns as a clustered key.

If you can determine the space (both present and future) that is required to hold all the records with a given clustered key value, this table is stored in Hachizu clusters.

If there is not enough space and you cannot allocate additional space for the new record you want to insert, do not use the Hachizu cluster.

If it is impractical to create a new, large Hachizu cluster occasionally to save such a table, do not use Hachizu clusters to store frequently-growing tables.

Do not store this table in Hachizu clusters if you frequently need a full table search, and you must allocate enough space for the Hachizu cluster in the expected growth of the table. (Such a full search must read all the blocks assigned to the Hachizu cluster, even though some blocks may contain only a small number of records.) Storing the table individually reduces the number of blocks read by the full table retrieval. ）

If your application frequently modifies the value of a clustered key, do not store the table in Hachizu clusters.

Whether or not the table is often connected to other tables, it is useful to store a table in a Hachizu cluster as long as the hash is appropriate for a table based on the previous guide.