Databases Reference
In-Depth Information
In step 7, we dropped the newly created cluster and tables, and recreated them without
inserting data in the tables.
This time, the data—which in step 4 and step 5 was inserted one table at a time—was inserted
in step 11 using a PL/SQL block to practice a particular trick, useful when loading data in a
cluster (the first time). We load together the correlated data of both the
CL_COUNTRIES
and
CL_LOCATIONS
tables, proceeding "one country at a time".
In step 12, we executed the same query as in step 6, to compare the results of the
two approaches.
There's more...
The major benefit of using clusters is in reduced I/O for accessing data from different tables
joined together, reducing even the space occupied by the cluster key that is stored once for
all the rows of all the tables participating in the cluster, which have the same key value.
This organization is called Multi-Table Index Cluster Tables; we can also create Single-Table
Index Cluster Tables, where rows from a single table are stored in sequence, thus enhancing
index range scan performance.
An index cluster works as an ordinary index, speeding up access to the rows with a specific
cluster key value.
In the example presented in this recipe, we can see that by loading the cluster one table
at a time, the rows which share the same cluster key value are stored in different database
blocks, reducing the performance gain, which can be obtained by the introduction of
clustered tables—we must read multiple database blocks to answer a query.
Loading the clustered tables involved inserting the rows which share the same cluster key
value in all the tables; we try to pack the records of the clustered tables in the same database
block. In our example, CN, JP, and UK countries are stored in different database blocks, thus
reducing the I/O needed when accessing our tables.
The benefits of using clusters are reduced when we regularly access a single table of the
cluster or when we perform more DML operations—insert, update, and delete—than select.
The reason for this performance delay is that storing rows from more than one table in
the same database block, forces the engine to read a greater block than in the case of a
standard table; full table scan performance is affected by the same issue, which causes poor
performance in DML operations. This is because by updating an (eventually) small row in a
table of the cluster, all the blocks containing the rows for the particular cluster key affected
will be read and manipulated.
Introducing an index cluster must be a long-meditated decision: even if the rows for a cluster
key value cannot be stored in few (1-2) database blocks, clusters are not a good choice.
