CREATE CLUSTER
CREATE CLUSTER cluster_name (column_name Datatype) SIZE size_value storage_clause;
| CREATE CLUSTER | Required keywords. |
| cluster_name | The unique name for the cluster. |
| column_name | The name of the column or columns that will make up the cluster key. These names are unique to the cluster, but they do not have to match the names of the corresponding columns in the clustered tables. |
| Datatype | One of the standard Oracle column datatypes, except for LONG RAW and RAW. Although the names of the columns in the clustered table do not have to match the -column_name, the datatypes of the corresponding columns do have to match. |
| SIZE | A required keyword. |
| size_value | The size of the cluster needed to store all the rows with the same value for the cluster key. Sizing a cluster is discussed in more detail in the next lesson. |
| storage_clause | Contains the same types of storage options as the storage clause for a table, such as TABLESPACE and PCTFREE. |
Specify the Space Required by an Average Cluster Key and Its Associated Rows
The CREATE CLUSTER statement has an optional clause, SIZE, which is the estimated number of bytes required by an average cluster key and its associated rows.
The database uses the SIZE parameter when performing the following tasks:
SIZE does not limit the space that can be used by a given cluster key. For example, if SIZE is set such that two cluster keys can fit in one data block, any amount of the available data block space can still be used by either of the cluster keys.
By default, the database stores only one cluster key and its associated rows in each data block of the cluster data segment. Although block size can vary from one operating system to the next, the rule of one key for each block is maintained as clustered tables are imported to other databases on other machines. If all the rows for a given cluster key value cannot fit in one block, the blocks are chained together to speed access to all the values with the given key. The cluster index points to the beginning of the chain of blocks, each of which contains the cluster key value and associated rows. If the cluster SIZE is such that more than one key fits in a block, blocks can belong to more than one chain.
- Estimating the number of cluster keys (and associated rows) that can fit in a clustered data block
- Limiting the number of cluster keys placed in a clustered data block. This maximizes the storage efficiency of keys within a cluster.
SIZE does not limit the space that can be used by a given cluster key. For example, if SIZE is set such that two cluster keys can fit in one data block, any amount of the available data block space can still be used by either of the cluster keys.
By default, the database stores only one cluster key and its associated rows in each data block of the cluster data segment. Although block size can vary from one operating system to the next, the rule of one key for each block is maintained as clustered tables are imported to other databases on other machines. If all the rows for a given cluster key value cannot fit in one block, the blocks are chained together to speed access to all the values with the given key. The cluster index points to the beginning of the chain of blocks, each of which contains the cluster key value and associated rows. If the cluster SIZE is such that more than one key fits in a block, blocks can belong to more than one chain.