Database Reference
In-Depth Information
8 dbms_rowid.rowid_block_number(emp.rowid) emp_blk,
9 dept.deptno
10 from emp, dept
11 where emp.deptno = dept.deptno
12 )
13 )
14 where flag = '*'
15 order by deptno
16 /
DEPT_BLK EMP_BLK F DEPTNO
---------- ---------- - ----------
24845 22362 * 12
24845 22362 * 12
24845 22362 * 12
...
24844 22362 * 39
24844 22362 * 39
24844 22362 * 39
46 rows selected.
We can see there are 46 out of 882
EMP
rows on a block separate and distinct from the block their corresponding
DEPTNO
is on in the
DEPT
table. Given that we undersized the cluster (the
SIZE
parameter was too small given our real
life data), we could re-create it with a cluster
SIZE
of 1200, and then we would discover the following:
EODA@ORA12CR1> select min(count(*)), max(count(*)), avg(count(*))
2 from dept
3 group by dbms_rowid.rowid_block_number(rowid)
4 /
MIN(COUNT(*)) MAX(COUNT(*)) AVG(COUNT(*))
------------- ------------- -------------
6 6 6
EODA@ORA12CR1> select *
2 from (
3 select dept_blk, emp_blk,
4 case when dept_blk <> emp_blk then '*' end flag,
5 deptno
6 from (
7 select dbms_rowid.rowid_block_number(dept.rowid) dept_blk,
8 dbms_rowid.rowid_block_number(emp.rowid) emp_blk,
9 dept.deptno
10 from emp, dept
11 where emp.deptno = dept.deptno
12 )
13 )
14 where flag = '*'
15 order by deptno
16 /
no rows selected
