Oracle Database 11gR2 Performance Tuning Cookbook

Using index clusters

A cluster is a group of tables that share common columns and are stored in the same data blocks; this organization is useful when we access this data using joins in queries.

How to do it...

In this recipe, we will see how and when to use index clusters, and some tricks to adopt when using this kind of storage. Follow these steps:

1. Connect to the HR schema of TESTDB database:

   CONNECT hr@TESTDB/hr
   

2. Create a cluster:

   CREATE CLUSTER LOC_ENTRIES (COUNTRY_ID CHAR(2)) SIZE 100;
   

3. Create the cluster index:

   CREATE INDEX IDX_LOC_ENTRIES ON CLUSTER LOC_ENTRIES;
   

4. Create and populate the first table:

   CREATE TABLE CL_COUNTRIES CLUSTER LOC_ENTRIES (COUNTRY_ID) AS
   SELECT * FROM COUNTRIES;
   

5. Create and populate the second table:

   CREATE TABLE CL_LOCATIONS ...