....
Or object based tables can be nested etc...
....
when objects are been thought as storage technique, things goes crazy like this.
rajesh@ORA11G> create or replace type emp_typ is object
2 ( empno number ,
3 ename varchar2(30) ,
4 job varchar2(19) ,
5 hiredate date ,
6 deptno number ,
7 sal number ,
8 comm number );
9 /
Type created.
rajesh@ORA11G> create or replace type emp_tab
2 is table of emp_typ ;
3 /
Type created.
rajesh@ORA11G> create table dept2
2 ( deptno number,
3 dname varchar2(30),
4 location varchar2(19),
5 employees emp_tab )
6 nested table employees store as employees_tab;
Table created.
rajesh@ORA11G> set timing on
rajesh@ORA11G> insert /*+ append */ into dept2(deptno,dname,location,employees)
2 select d.deptno,d.dname,d.location,
3 cast( multiset( select emp_typ(e.empno,e.ename,e.job,
4 e.hiredate,e.deptno,e.sal,e.comm)
5 from emp e
6 where e.deptno = d.deptno ) as emp_tab)
7 from dept d ;
1000 rows created.
Elapsed: 00:00:09.09
rajesh@ORA11G> set timing off
rajesh@ORA11G> commit;
Commit complete.
rajesh@ORA11G>
rajesh@ORA11G> alter table dept2 add constraint dept2_pk
2 primary key(deptno);
Table altered.
rajesh@ORA11G>
select *
from dept2 d, table(d.employees) t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 4 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 668 4.99 16.99 0 12575 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 4.99 16.99 0 12579 0 100000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
100000 100000 100000 TABLE ACCESS BY INDEX ROWID EMPLOYEES_TAB (cr=5846 pr=0 pw=0 time=101380 us cost=1 size=108702 card=1098)
100000 100000 100000 INDEX RANGE SCAN SYS_FK0000092518N00004$ (cr=3798 pr=0 pw=0 time=40873 us cost=1 size=0 card=439)(object id 92520)
100000 100000 100000 NESTED LOOPS (cr=6729 pr=0 pw=0 time=313505 us cost=7 size=15478416 card=109776)
100000 100000 100000 NESTED LOOPS (cr=4835 pr=0 pw=0 time=105938 us cost=7 size=15478416 card=930000)
1000 1000 1000 TABLE ACCESS FULL DEPT2 (cr=673 pr=0 pw=0 time=8644 us cost=7 size=42000 card=1000)
100000 100000 100000 INDEX RANGE SCAN SYS_FK0000092518N00004$ (cr=4162 pr=0 pw=0 time=93657 us cost=0 size=0 card=930)(object id 92520)
100000 100000 100000 TABLE ACCESS BY INDEX ROWID EMPLOYEES_TAB (cr=1894 pr=0 pw=0 time=124292 us cost=0 size=10890 card=110)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 668 0.00 0.00
SQL*Net message from client 668 0.32 135.33
SQL*Net more data to client 74495 0.00 13.93
With Relational model it goes like this.
select *
from emp e, dept d
where e.deptno = d.deptno
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 668 0.18 0.14 0 1499 0 100000
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 670 0.18 0.14 0 1499 0 100000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 90
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
100000 100000 100000 HASH JOIN (cr=1499 pr=0 pw=0 time=81767 us cost=449 size=8100000 card=100000)
1000 1000 1000 TABLE ACCESS FULL DEPT (cr=6 pr=0 pw=0 time=150 us cost=5 size=25000 card=1000)
100000 100000 100000 TABLE ACCESS FULL EMP (cr=1493 pr=0 pw=0 time=18851 us cost=439 size=5600000 card=100000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 668 0.00 0.00
SQL*Net message from client 668 0.00 0.52
SQL*Net more data to client 653 0.00 0.04