Overview of Indexed Clusters

An index cluster is a table cluster that uses an index to locate data. The cluster index is a B-tree index on the cluster key. A cluster index must be created before any rows can be inserted into clustered tables.

oracle e business suite training courses malaysia

Example 2-6 Creating a Table Cluster and Associated Index

Assume that you create the cluster employees_departments_cluster with the cluster key department_id, as shown in the following example:

CopyCREATE CLUSTER employees_departments_cluster
   (department_id NUMBER(4))
SIZE 512;

CREATE INDEX idx_emp_dept_cluster 
   ON CLUSTER employees_departments_cluster;

Because the HASHKEYS clause is not specified, employees_departments_cluster is an indexed cluster. The preceding example creates an index named idx_emp_dept_cluster on the cluster key department_id.

Example 2-7 Creating Tables in an Indexed Cluster

oracle database training courses malaysia

You create the employees and departments tables in the cluster, specifying the department_id column as the cluster key, as follows (the ellipses mark the place where the column specification goes):

CopyCREATE TABLE employees ( ... )
   CLUSTER employees_departments_cluster (department_id);
 
CREATE TABLE departments ( ... )
   CLUSTER employees_departments_cluster (department_id);

Assume that you add rows to the employees and departments tables. The database physically stores all rows for each department from the employees and departments tables in the same data blocks. The database stores the rows in a heap and locates them with the index.

oracle cloud infrastructure training courses malaysia

Figure 2-5 shows the employees_departments_cluster table cluster, which contains employees and departments. The database stores rows for employees in department 20 together, department 110 together, and so on. If the tables are not clustered, then the database does not ensure that the related rows are stored together.

Figure 2-5 Clustered Table Data
Description of “Figure 2-5 Clustered Table Data”

The B-tree cluster index associates the cluster key value with the database block address (DBA) of the block containing the data. For example, the index entry for key 20 shows the address of the block that contains data for employees in department 20:

Copy20,AADAAAA9d

The cluster index is separately managed, just like an index on a nonclustered table, and can exist in a separate tablespace from the table cluster.

nodejs training courses malaysia