demo05.sql

Expert Oracle Database Architecture 9i and 10g sql源码

create table emp
as
select object_id   empno,
       object_name ename,
       created     hiredate,
       owner       job
  from all_objects
/
alter table emp add constraint emp_pk primary key(empno)
/
begin
   dbms_stats.gather_table_stats( user, 'EMP', cascade=>true );
end;
/
create table heap_addresses
( empno     references emp(empno) on delete cascade,
  addr_type varchar2(10),
  street    varchar2(20),
  city      varchar2(20),
  state     varchar2(2),
  zip       number,
  primary key (empno,addr_type)
)
/
create table iot_addresses
( empno     references emp(empno) on delete cascade,
  addr_type varchar2(10),
  street    varchar2(20),
  city      varchar2(20),
  state     varchar2(2),
  zip       number,
  primary key (empno,addr_type)
)
ORGANIZATION INDEX
/
insert into heap_addresses
select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123
  from emp;
insert into iot_addresses
select empno, 'WORK', '123 main street', 'Washington', 'DC', 20123
  from emp;
exec dbms_stats.gather_table_stats( user, 'HEAP_ADDRESSES' );
exec dbms_stats.gather_table_stats( user, 'IOT_ADDRESSES' );
set autotrace traceonly
select *
  from emp, heap_addresses
 where emp.empno = heap_addresses.empno
   and emp.empno = 42;
select *
  from emp, iot_addresses
 where emp.empno = iot_addresses.empno
   and emp.empno = 42;
set autotrace off

alter session set sql_trace=true;
begin
    for x in ( select empno from emp )
    loop
        for y in ( select emp.ename, a.street, a.city, a.state, a.zip
                     from emp, heap_addresses a
                    where emp.empno = a.empno
                      and emp.empno = x.empno )
        loop
            null;
        end loop;
     end loop;
end;
/