1.创建父表
sys@TEST>create table p as select object_id empno,
2 object_name ename,
3 created hiredate,
4 owner job from all_objects;
Table created.
sys@TEST>desc p
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER
ENAME NOT NULL VARCHAR2(30)
HIREDATE NOT NULL DATE
JOB NOT NULL VARCHAR2(30)
sys@TEST>alter table p add constraint emp_pk primary key(empno);
Table altered.
sys@TEST>begin
2 dbms_stats.gather_table_stats(user,'P',cascade=>true);
3 end;
4 /
PL/SQL procedure successfully completed.
2. 创建2个子表,一个为堆表,另一个为索引组织表
sys@TEST>create table heap_address
2 (
3 empno references emp(empno) on delete cascade,
4 addr_type varchar2(10),
5 street varchar2(20),
6 city varchar2(20),
7 state varchar2(2),
8 zip number,
9 primary key(empno,addr_type)
10 )
11 /
Table created.
sys@TEST>create table iot_address
2 (empno references emp(empno) on delete cascade,
3 addr_type varchar2(10),
4 street varchar2(20),
5 city varchar2(20),
6 state varchar2(2),
7 zip number,
8 primary key(empno,addr_type)
9 )
10 organization index
11
sys@TEST>create table iot_address
2 (empno references emp(empno) on delete cascade,
3 addr_type varchar2(10),
4 street varchar2(20),
5 city varchar2(20),
6 state varchar2(2),
7 zip number,
8 primary key(empno,addr_type)
9 )
10 organization index
11 /
Table created.
3. 向两个子表中插入数据
sys@TEST>insert into heap_address select empno,'WORK','123 main street','Washington','DC',20123 from emp;
14 rows created.
sys@TEST>insert into iot_address select empno,'WORK','123 main street','Washington','DC',20123 from emp;
14 rows created.
sys@TEST>c/WORK/HOME
1* insert into iot_address select empno,'HOME','123 main street','Washington','DC',20123 from emp
sys@TEST>/
14 rows created.
sys@TEST>C/iot/heap
1* insert into heap_address select empno,'HOME','123 main street','Washington','DC',20123 from emp
sys@TEST>/
14 rows created.
sys@TEST>c/HOME/PREV
1* insert into heap_address select empno,'PREV','123 main street','Washington','DC',20123 from emp
sys@TEST>/
14 rows created.
sys@TEST>c/heap/iot
1* insert into iot_address select empno,'PREV','123 main street','Washington','DC',20123 from emp
sys@TEST>/
14 rows created.
sys@TEST>c/PREV/SCHOOL
1* insert into iot_address select empno,'SCHOOL','123 main street','Washington','DC',20123 from emp
sys@TEST>/
14 rows created.
sys@TEST>c/iot/heap
1* insert into heap_address select empno,'SCHOOL','123 main street','Washington','DC',20123 from emp
sys@TEST>/
14 rows created.
sys@TEST>exec dbms_stats.gather_table_stats(user,'HEAP_ADDRESS');
PL/SQL procedure successfully completed.
sys@TEST>c/HEAP/IOT
1* insert into IOT_address select empno,'SCHOOL','123 main street','Washington','DC',20123 from emp
sys@TEST>exec dbms_stats.gather_table_stats(user,'IOT_ADDRESS');
PL/SQL procedure successfully completed.
4. 分别对两个子表进行相同的查询
sys@TEST>set autotrace traceonly;
sys@TEST>select * from emp,heap_address where emp
2 .empno=heap_address.empno and emp.empno=42;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=4 Bytes=320)
1 0 NESTED LOOPS (Cost=4 Card=4 Bytes=320)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 By
tes=36)
3 2 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) (Cost=1 Card=
14)
4 1 TABLE ACCESS (FULL) OF 'HEAP_ADDRESS' (Cost=2 Card=4 Byt
es=176)
Statistics
----------------------------------------------------------
149 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
909 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
sys@TEST>l
1 select * from emp,heap_address where emp
2* .empno=heap_address.empno and emp.empno=42
sys@TEST>1
1* select * from emp,heap_address where emp
sys@TEST>c/heap/iot
1* select * from emp,iot_address where emp
sys@TEST>2
2* .empno=heap_address.empno and emp.empno=42
sys@TEST>c/heap/iot
2* .empno=iot_address.empno and emp.empno=42
sys@TEST>l
1 select * from emp,iot_address where emp
2* .empno=iot_address.empno and emp.empno=42
sys@TEST>/
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=4 Bytes=320)
1 0 NESTED LOOPS (Cost=3 Card=4 Bytes=320)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 By
tes=36)
3 2 INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE) (Cost=1 Card=
14)
4 1 INDEX (RANGE SCAN) OF 'SYS_IOT_TOP_6599' (UNIQUE) (Cost=
1 Card=4 Bytes=176)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
909 bytes sent via SQL*Net to client
372 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
很明显,索引组织表的IO明显降低
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10972173/viewspace-246152/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10972173/viewspace-246152/