Search notes:
Oracle: Clusters
A cluster is an object that stores data from one or more
tables that belongs together.
Create the cluster
create cluster tq84_cluster(
id integer
);
Index
A cluster requires an
index (before
data is inserted into the clustered tables).
create index tq84_cluster_ix on cluster tq84_cluster;
Clustered tables
Create the tables «in» the cluster:
create table tq84_cluster_parent (
id integer primary key,
dt date
)
cluster tq84_cluster(id);
create table tq84_cluster_child (
id integer references tq84_cluster_parent,
tx varchar2(20)
)
cluster tq84_cluster(id);
Inserts
Inserting a few records into the tables
insert into tq84_cluster_parent values (100, trunc(sysdate-100));
insert into tq84_cluster_parent values (200, trunc(sysdate-200));
insert into tq84_cluster_parent values (300, trunc(sysdate-300));
insert into tq84_cluster_child values (200, 'zweihundert' );
insert into tq84_cluster_child values (100, 'hundert' );
insert into tq84_cluster_child values (200, 'two hundred' );
insert into tq84_cluster_child values (300, 'three hundred');
insert into tq84_cluster_child values (100, 'cent' );
insert into tq84_cluster_child values (100, 'hundred' );
insert into tq84_cluster_child values (300, 'dreihundert' );
commit;
Data dictionary
Two views in the
data dictionary that are related to clusters are
dba_clu_columns and
dba_clusters:
select * from user_clusters where cluster_name = 'TQ84_CLUSTER';
select * from user_clu_columns where cluster_name = 'TQ84_CLUSTER';
Same block for related rows
Using
dbms_rowid, it can be shown that records with the same cluster-id (cluster index) are stored in the same
database block
select
-- rowid,
dbms_rowid.rowid_block_number (rowid) block_no,
dbms_rowid.rowid_row_number (rowid) row_no,
dbms_rowid.rowid_relative_fno (rowid) rel_fno,
case dbms_rowid.rowid_type (rowid)
when 0 then 'restricted'
else 'extended' end type_,
r.*
from
tq84_cluster_child r;
select
-- rowid,
dbms_rowid.rowid_block_number (rowid) block_no,
dbms_rowid.rowid_row_number (rowid) row_no,
dbms_rowid.rowid_relative_fno (rowid) rel_fno,
case dbms_rowid.rowid_type (rowid)
when 0 then 'restricted'
else 'extended' end type_,
r.*
from
tq84_cluster_parent r;
SQL execution plan
When joining two clustered tables, the
execution plan does not seem to show that they're clustered:
explain plan for
select
p.id,
p.dt,
c.tx
from
tq84_cluster_parent p left join
tq84_cluster_child c on p.id = c.id;
select * from table(dbms_xplan.display);
--
-- ---------------------------------------------------------------------------------------------
-- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-- ---------------------------------------------------------------------------------------------
-- | 0 | SELECT STATEMENT | | 6 | 462 | 6 (0)| 00:00:01 |
-- | 1 | NESTED LOOPS OUTER | | 6 | 462 | 6 (0)| 00:00:01 |
-- | 2 | TABLE ACCESS FULL | TQ84_CLUSTER_PARENT | 3 | 66 | 3 (0)| 00:00:01 |
-- |* 3 | TABLE ACCESS CLUSTER| TQ84_CLUSTER_CHILD | 2 | 110 | 1 (0)| 00:00:01 |
-- ------------------------------------------------------------------------------------------