Clusters Wizard (Oracle)

Go Up to Oracle Object Wizards

The Cluster Wizard lets you create a cluster. A cluster is a schema object that has one or more tables that all have one or more common columns. Rows of one or more tables that share the same value in these common columns are stored together in the database. The related columns of tables stored in a cluster are known as the cluster key.

Note: The account creating the cluster must have the CREATE CLUSTER or CREATE ANY CLUSTER system privilege.

To Open the Cluster Wizard

1. On the Navigator/Explorer, find the schema where you want to add the new cluster.
2. On the Cluster branch, right-click, and then select New.

OR

1. On the main toolbar, click Datasource, and then scroll to Objects.
2. Click Clusters, and then click New from the toolbar.

The tables below describe the fields you may encounter as you complete the wizard.

Required Information	Description
Who owns the cluster?	Pick an owner
What is the name of the cluster?	Type a unique name
On which tablespace do you want to create the cluster?	Self-explanatory
Add columns that are in this cluster	These are the columns that are common between the tables you are clustering. Use the Add or Edit button. For more information, see Adding or modifying a cluster column. Click Drop to drop the column.
What is the size of this cluster?	This is the estimated number of bytes/KB/MB required by an average cluster key and its associated rows. Do not exceed the size of a data block.
What is the cluster type?	Index. Rows having the same cluster key value are stored together. Each separate cluster key is stored only once in each data block. An indexed cluster is helpful if your clustered tables might grow unpredictably. Hash. Rows with the same hash key value are stored together. This is helpful if the tables are static.
If this is a hash cluster, what is the number of hash keys?	Type the number of hash keys. Oracle rounds up the value to the nearest prime number.
If this is a hash cluster, what is the hash function?	Oracle uses a hash function to generate a distribution of numeric values, called hash values, which are based on specific cluster key values. The key of a hash cluster, like the key of an index cluster, can be a single column or composite key (multiple column key). To find or store a row in a hash cluster, Oracle applies the hash function to the row's cluster key value. The resulting hash value corresponds to a data block in the cluster, which Oracle then reads or writes on behalf of the issued statement. The default is the Oracle internal hash function, otherwise specify the hash expression you want to use.
How many transaction entries are allowed for each data block in the cluster?	Each transaction that updates a data block requires a transaction entry. Initial (1-255). The initial parameter ensures that a minimum number of concurrent transactions can update a data block, avoiding the overhead of allocating a transaction entry dynamically. Maximum (1-255). The maximum parameter limits concurrency on a data block.
What is the percent of space reserved for future updates?	Percent Free (0-99). This sets the percentage of a data block to be reserved for possible row updates that are included in the block. The value you set is the percent kept free.
What is the minimum percentage of used space that Oracle maintains for each data block?	Percent Used (1-99). The storage parameter lets you tune performance by minimizing the occurrence of row migration and chaining caused by update operations that extend the length of rows stored on the data block.
How large are the cluster’s extents?	The unit of space allocated to an object whenever the object needs more space. Initial Extent. The initial space extent (in bytes) allocated to the object. Next Extent. The next extent (in bytes) that the object attempts to allocate when you require more space for the object.
Specify the number of free lists	Free lists are lists of data blocks that have space available for inserting rows. Identifying multiple free lists can reduce contention for free lists when concurrent inserts take place and potentially improve the performance of the cluster. Free Lists. The default and minimum value is 1. Set this option higher if multiple processes access the same data block.
Specify the number of free list groups	(Available only when using the parallel server option.) The number of groups of free lists for the database objects being created.
Define a default buffer pool for this cluster	Default. Select to retain the default. Keep. Select to retain the object in memory to avoid I/O conflicts.
Oracle's parallel query option	The parallel server query option lets you process queries using many query server processes running against multiple CPUs, which provides substantial performance gains such as reducing the query completion time.
Choosing Cache	Cache. Keeps the data block in memory by placing it at the most recently used end. This option is useful for small lookup tables. No Cache.

Adding or modifying a cluster column

The Add or Modify Cluster Column dialog lets you manage cluster columns. You can open the dialog in the Oracle Cluster Wizard.

The table below describes the options and functionality on the Add or Modify Cluster Column dialog.

Option	Description
Column Name	Lets you type the column name.
Datatype	Lets you select the datatype for the cluster. If you select CHAR, RAW, or VARCHAR2, type the width value in the Width field. If you select NUMBER, type the width value in the Width field, and then type the scale value in the Scale field.

Completing the Add or Modify Cluster Column Dialog Box

To complete the Add or Modify Cluster Column dialog box

1. In the Add Cluster Column dialog, type the column name in the Column Name field.
2. Click the Datatype list, and then select the datatype for the cluster.
   • If you clicked CHAR, RAW, or VARCHAR2, type the width value in the Width field.
   • If you clicked NUMBER, type the width value in the Width field, and then type the scale value in the Scale field.
3. Click Add.
4. To continue adding columns to the cluster, repeat steps 1-3.
5. When you finish adding columns, click Close.

For more information, see Adding or modifying a cluster column.