读书笔记之-堆表与索引组织表

1.创建父表

sys@TEST>createtable p as select object_id empno,
  2     object_name ename,
  3   created hiredate,
  4   owner job from all_objects;
 
Table created.
 
sys@TEST>descp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER
 ENAME                                     NOT NULL VARCHAR2(30)
 HIREDATE                                  NOT NULL DATE
 JOB                                       NOT NULL VARCHAR2(30)
 
sys@TEST>altertable 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>createtable 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>createtable 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>createtable 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>insertinto heap_address select empno,'WORK','123 main street','Washington','DC',20123 from emp;
 
14 rows created.
 
sys@TEST>insertinto 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>execdbms_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>execdbms_stats.gather_table_stats(user,'IOT_ADDRESS');
 
PL/SQL procedure successfully completed.
 

4. 分别对两个子表进行相同的查询

sys@TEST>setautotrace 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明显降低