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 CLUSTER LOC_ENTRIES (COUNTRY_ID) AS
   SELECT L.*, CAST(‹*› AS CHAR(1000)) AS FOO_DATA
   FROM LOCATIONS L;
   

6. Verify the database blocks where the data of the two tables is stored:

   select
     cl_countries.country_id,
     dbms_rowid.rowid_block_number(cl_countries...