Database Reference
In-Depth Information
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);
126 rows created.
■
I used a sQl trick to generate data in this example. I wanted more than seven departments to demonstrate that
Oracle will limit the number of department keys per block based on my
SIZE
parameter. therefore, I needed more than
the four department rows found in
SCOTT.DEPT
. I generated nine rows using the “connect by level” trick against
DUAL
and
performed a Cartesian join of those nine rows with the four in
DEPT
resulting in 36 unique rows. I did a similar trick with
EMP
to fabricate data for these departments.
Note
Now that the data is loaded, let's look at the organization of it on disk. We'll use the
DBMS_ROWID
package to peek
into the rowid and see what blocks data is stored on. Let's first look at the
DEPT
table and see how many
DEPT
rows per
block we have:
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, even though we loaded
DEPT
first—and the
DEPT
rows are very small (hundreds of them could fit on an 8k
block normally)—we find that the maximum number of
DEPT
rows on a block in this table is only seven. That fits in
with what we anticipated when we set the
SIZE
to 1024. We estimated that with an 8k block and 1024 bytes of data per
cluster key for the combined
EMP
and
DEPT
records, we would see approximately seven unique cluster key values per
block, and that is exactly what we are seeing here. Next, let's look at the
EMP
and
DEPT
tables together. We'll look at the
rowids of each and compare the block numbers after joining by
DEPTNO
. If the block numbers are the same, we'll know
that the
EMP
row and the
DEPT
row are stored on the same physical database block together if they differ we'll know
they are not. In this case, we observe that all of our data is perfectly stored. There are no cases where a record for the
EMP
table is stored on a block separate from its corresponding
DEPT
record:
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
