Database Reference
In-Depth Information
12 )
13 )
14 where flag = '*'
15 order by deptno
16 /
no rows selected
That was exactly our goal—to get every row in the EMP table stored on the same block as the corresponding DEPT
row. But what would have happened if we estimated incorrectly, what if 1024 was insufficient? What if some of our
departments were close to 1024 and others exceeded that value? Then, obviously, the data could not fit on the same
block and we'd have to place some of the EMP records on a block separate from the DEPT record. We can see this easily
by resetting our prior example (I'm starting with the tables as they were before the load, right after creating them).
When I load this time, we'll load every EMP record eight times, to multiply the number of employee records per each
department:
EODA@ORA12CR1> insert into dept
2 ( deptno, dname, loc )
3 select deptno+r, dname, loc
4 from scott.dept,
5 (select level r from dual connect by level < 10);
36 rows created.
EODA@ORA12CR1> insert into emp
2 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
3 select rownum, ename, job, mgr, hiredate, sal, comm, deptno+r
4 from scott.emp,
5 (select level r from dual connect by level < 10),
6 (select level r2 from dual connect by level < 8);
882 rows created.
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(*))
------------- ------------- -------------
1 7 6
So far, it looks just like the prior example, but let's compare the blocks the EMP records are on to the blocks the
DEPT records are on:
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,
Search WWH ::




Custom Search