Oracle Index Cluster Storage

A Cluster Index is an object oriented to gain performance when frequent master/detail relation ships are found between two tables. This is the fastest way to perform a join between two tables, but there are storage issues when this storage structure is used. The purpose of this article is to analyze the way Oracle reserves storage for a Cluster Index

Create cluster and verify Cluster storage

SQL> l
1 create cluster cluster_emp
2 (department_id NUMBER(4))
3* index


SQL> create index i_cluster_emp on cluster cluster_emp;

Index created.

SQL> r
1 create table emp cluster cluster_emp(department_id)
2 as
3* select employee_id, first_name, last_name, hire_date, salary, department_id from employees

Table created.

SQL> create table dept
2 cluster cluster_emp(department_id)
3 as
4 select * from departments;

Table created.


Verify where data is stored

SQL> r
1 select owner, segment_name, file_id, block_id, blocks, bytes
2 from dba_extents
3 where segment_name in ('I_CLUSTER_EMP','CLUSTER_EMP')
4* order by segment_name, extent_id

OWNER SEGMENT_NAME FILE_ID BLOCK_ID BLOCKS BYTES
-------- ------------- ---------- ---------- ---------- ----------
HR CLUSTER_EMP 4 3825 8 65536
HR CLUSTER_EMP 4 1817 8 65536
HR CLUSTER_EMP 4 3817 8 65536
HR CLUSTER_EMP 4 3809 8 65536
HR I_CLUSTER_EMP 4 3833 8 65536


Determine how many rows are retrieved for each department Id when join is performed

SQL> R
1 SELECT D.DEPARTMENT_ID, COUNT(*)
2 FROM EMP E, DEPT D
3 WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
4 GROUP BY D.DEPARTMENT_ID
5* ORDER BY D.DEPARTMENT_ID

DEPARTMENT_ID COUNT(*)
------------- ----------
10 1
20 2
30 6
40 1
50 45
60 5
70 1
80 34
90 3
100 6
110 2

11 rows selected.

Display where specific rows are stored.

SQL> R
1 SELECT D.DEPARTMENT_ID,
2 DBMS_ROWID.ROWID_RELATIVE_FNO(D.ROWID)||':'||DBMS_ROWID.ROWID_BLOCK_NUMBER(D.ROWID) DEPT_ROW,
3 DBMS_ROWID.ROWID_RELATIVE_FNO(E.ROWID)||':'||DBMS_ROWID.ROWID_BLOCK_NUMBER(E.ROWID) EMP_ROW,
4 COUNT(*)
5 FROM DEPT D, EMP E
6 WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID
7 GROUP BY
8 D.DEPARTMENT_ID,
9 DBMS_ROWID.ROWID_RELATIVE_FNO(D.ROWID)||':'||DBMS_ROWID.ROWID_BLOCK_NUMBER(D.ROWID),
10 DBMS_ROWID.ROWID_RELATIVE_FNO(E.ROWID)||':'||DBMS_ROWID.ROWID_BLOCK_NUMBER(E.ROWID)
11* ORDER BY DEPARTMENT_ID

DEPARTMENT_ID DEPT_ROW EMP_ROW COUNT(*)
------------- -------- -------- ----------
10 4:1821 4:1821 1
20 4:1822 4:1822 2
30 4:3829 4:3829 6
40 4:1823 4:1823 1
50 4:3830 4:3830 45
60 4:3832 4:3832 5
70 4:1824 4:1824 1
80 4:1819 4:1819 34
90 4:3831 4:3831 3
100 4:3828 4:3828 6
110 4:1817 4:1817 2

11 rows selected.

There it can be seen that for each department Id one block is assigned, this will make Oracle cluster to require more storage than that required to store individual independent tables. On the other hand if few employee rows are assigned to a department, a lot of block space will be wasted. Transactional operations over the individual cluster participant tables will make rows to be physically reallocated, thus making Oracle clusters suitable for fast join operations, but slow for transactional tables.

Block dumped to verify physical row storage from the first two cluster rows.

C:\Oracle\product\10.1.0\admin\alpha\udump>more alpha_ora_6088.trc
Dump file c:\oracle\product\10.1.0\admin\alpha\udump\alpha_ora_6088.trc
Wed Apr 02 01:12:30 2008
ORACLE V10.1.0.5.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1077M/2046M, Ph+PgF:3110M/3938M, VA:1701M/2047M
Instance name: alpha

Redo thread mounted by this instance: 1

Oracle process number: 13

Windows thread id: 6088, image: ORACLE.EXE (SHAD)


*** 2008-04-02 01:12:30.265
*** ACTION NAME:() 2008-04-02 01:12:30.250
*** MODULE NAME:(SQL*Plus) 2008-04-02 01:12:30.250
*** SERVICE NAME:(SYS$USERS) 2008-04-02 01:12:30.250
*** SESSION ID:(152.1209) 2008-04-02 01:12:30.250
Start dump data blocks tsn: 4 file#: 4 minblk 1821 maxblk 1822
buffer tsn: 4 rdba: 0x0100071d (4/1821)
scn: 0x0000.001deadc seq: 0x01 flg: 0x06 tail: 0xeadc0601
frmt: 0x02 chkval: 0x1b2c type: 0x06=trans data
Block header dump: 0x0100071d
Object id on Block? Y
seg/obj: 0xc312 csc: 0x00.1dea96 itc: 2 flg: E typ: 1 - DATA
brn: 1 bdba: 0x1000ef1 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.021.0000051a 0x008005bb.02be.34 C--- 0 scn 0x0000.001dea72
0x02 0x0003.00a.000008e7 0x00800526.04cd.12 --U- 1 fsc 0x0000.001deadc

data_block_dump,data header at 0x6051264
===============
tsiz: 0x1f98
hsiz: 0x20
pbl: 0x06051264
bdba: 0x0100071d
76543210
flag=--------
ntab=3
nrow=3
frre=-1
fsbo=0x20
fseo=0x1f47
avsp=0x1f27
tosp=0x1f27
0xe:pti[0] nrow=1 offs=0
0x12:pti[1] nrow=1 offs=1
0x16:pti[2] nrow=1 offs=2
0x1a:pri[0] offs=0x1f82
0x1c:pri[1] offs=0x1f60
0x1e:pri[2] offs=0x1f47
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 2 comc: 2 pk: 0x0100071d.0 nk: 0x0100071d.0
col 0: [ 2] c1 0b
tab 1, row 0, @0x1f60
tl: 34 fb: -CH-FL-- lb: 0x0 cc: 5 cki: 0
col 0: [ 2] c2 03
col 1: [ 8] 4a 65 6e 6e 69 66 65 72
col 2: [ 6] 57 68 61 6c 65 6e
col 3: [ 7] 77 bb 09 11 01 01 01
col 4: [ 2] c2 2d
tab 2, row 0, @0x1f47
tl: 25 fb: -CH-FL-- lb: 0x2 cc: 3 cki: 0
col 0: [14] 41 64 6d 69 6e 69 73 74 72 61 74 69 6f 6e
col 1: [ 2] c2 03
col 2: [ 2] c2 12
end_of_block_dump
buffer tsn: 4 rdba: 0x0100071e (4/1822)
scn: 0x0000.001deadc seq: 0x01 flg: 0x06 tail: 0xeadc0601
frmt: 0x02 chkval: 0x028c type: 0x06=trans data
Block header dump: 0x0100071e
Object id on Block? Y
seg/obj: 0xc312 csc: 0x00.1dea96 itc: 2 flg: E typ: 1 - DATA
brn: 1 bdba: 0x1000ef1 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0001.02a.0000051a 0x008005bb.02be.36 C--- 0 scn 0x0000.001dea73
0x02 0x0003.00a.000008e7 0x00800526.04cd.13 --U- 1 fsc 0x0000.001deadc

data_block_dump,data header at 0x6051264
===============
tsiz: 0x1f98
hsiz: 0x22
pbl: 0x06051264
bdba: 0x0100071e
76543210
flag=--------
ntab=3
nrow=4
frre=-1
fsbo=0x22
fseo=0x1f2c
avsp=0x1f0a
tosp=0x1f0a
0xe:pti[0] nrow=1 offs=0
0x12:pti[1] nrow=2 offs=1
0x16:pti[2] nrow=1 offs=3
0x1a:pri[0] offs=0x1f82
0x1c:pri[1] offs=0x1f5c
0x1e:pri[2] offs=0x1f41
0x20:pri[3] offs=0x1f2c
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 3 comc: 3 pk: 0x0100071e.0 nk: 0x0100071e.0
col 0: [ 2] c1 15
tab 1, row 0, @0x1f5c
tl: 38 fb: -CH-FL-- lb: 0x0 cc: 5 cki: 0
col 0: [ 3] c2 03 02
col 1: [ 7] 4d 69 63 68 61 65 6c
col 2: [ 9] 48 61 72 74 73 74 65 69 6e
col 3: [ 7] 77 c4 02 11 01 01 01
col 4: [ 3] c3 02 1f
tab 1, row 1, @0x1f41
tl: 27 fb: -CH-FL-- lb: 0x0 cc: 5 cki: 0
col 0: [ 3] c2 03 03
col 1: [ 3] 50 61 74
col 2: [ 3] 46 61 79
col 3: [ 7] 77 c5 08 11 01 01 01
col 4: [ 2] c2 3d
tab 2, row 0, @0x1f2c
tl: 21 fb: -CH-FL-- lb: 0x2 cc: 3 cki: 0
col 0: [ 9] 4d 61 72 6b 65 74 69 6e 67
col 1: [ 3] c2 03 02
col 2: [ 2] c2 13
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 1821 maxblk 1822

C:\Oracle\product\10.1.0\admin\alpha\udump>