oracle-tom-table-iot
目的：使用IOT表
索引組織表---就是儲存在一個索引結構中的表。我們一般的的表都是堆表，它的資料存放是無組織的。IOT表也就是索引組織表中的資料是按照主鍵儲存和排序
使用場景：當一個表的所有列都是索引時，程式碼查詢表。
總原則：如果你想讓資料儲存在某個位置上，或者希望資料以某種特定的順序物理儲存，IOT可以滿足這樣的需求。
如：以父/子表為來說明使用IOT將子表資訊物理的儲存在同一個位置上有什麼作用。
會話1：
SQL> show user;
USER is "SYS"
SQL> create table emp-------------建立父表
2 as
3 select object_id empno,
4 object_name ename,
5 created hiredate,
6 owner job
7 from all_objects
8 /
Table created.
SQL> alter table emp add constraint emp_pk primary key(empno)
2 /
Table altered.
SQL> begin--收集父表統計資訊
2 dbms_stats.gather_table_stats(user,'emp',cascade=>true);
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> create table heap_addresses---建立普通HEAP型別子表。也就是堆表。
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 /
Table created.
SQL> create table iot_addresses---建立IOT型別子表
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.
SQL>
向子表插入資料
SQL> insert into heap_addresses
2 select empno,'work','123 main street','washiongton','dc',20213
3 from emp;
50200 rows created.
SQL> insert into iot_addresses
2 select empno,'work','123 main street','washiongton','dc',20123
3 from emp;
50200 rows created.
SQL> insert into heap_addresses
2 select empno,'home','123 main street','washington','dc',20123
3 from emp;
50200 rows created.
SQL> insert into iot_addresses
2 select empno,'home','123 main street','washington','dc',20123
3 from emp;
50200 rows created.
SQL> insert into heap_addresses
2 select empno,'prev','123 main street','washington','dc',20123
3 from emp;
50200 rows created.
SQL> insert into iot_addresses
2 select empno,'prev','123 main street','washington','dc',20123
3 from emp;
50200 rows created.
SQL> insert into heap_addresses
2 select empno,'school','123 main street','washington','dc',20123
3 from emp;
50200 rows created.
SQL> insert into iot_addresses
2 select empno,'school','123 main street','washington','dc',20123
3 from emp;
50200 rows created.
SQL>
採集兩個子表的統計資訊
SQL> exec dbms_stats.gather_table_stats(user,'heap_addresses');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'iot_addresses');
PL/SQL procedure successfully completed.
通過查詢來比對執行計劃
SQL> set autotrace traceonly
SQL>
SQL>
SQL> select *
2 from emp,heap_addresses
3 where emp.empno=heap_addresses.empno
4 and emp.empno=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 2701700395
--------------------------------------------------------------------------------
---------------
| Id | Operation | Name | Rows | Bytes | Cost (%C
PU)| Time |
--------------------------------------------------------------------------------
---------------
| 0 | SELECT STATEMENT | | 4 | 348 | 9
(0)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 348 | 9
(0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 42 | 2
(0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1
(0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| HEAP_ADDRESSES | 4 | 180 | 7
(0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SYS_C006067 | 4 | | 2
(0)| 00:00:01 |
--------------------------------------------------------------------------------
---------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."EMPNO"=42)
5 - access("HEAP_ADDRESSES"."EMPNO"=42)
Statistics
----------------------------------------------------------
352 recursive calls
0 db block gets
68 consistent gets
0 physical reads
0 redo size
1052 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
8 sorts (memory)
0 sorts (disk)
4 rows processed
SQL>
SQL> select *
2 from emp,iot_addresses
3 where emp.empno=iot_addresses.empno
4 and emp.empno=42;
Execution Plan
----------------------------------------------------------
Plan hash value: 1739670451
--------------------------------------------------------------------------------
------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
------------------
| 0 | SELECT STATEMENT | | 4 | 348 | 4
(0)| 00:00:01 |
| 1 | NESTED LOOPS | | 4 | 348 | 4
(0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 42 | 2
(0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 1
(0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | SYS_IOT_TOP_53487 | 4 | 180 | 2
(0)| 00:00:01 |
--------------------------------------------------------------------------------
------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."EMPNO"=42)
4 - access("IOT_ADDRESSES"."EMPNO"=42)
Statistics
----------------------------------------------------------
84 recursive calls
0 db block gets
19 consistent gets
0 physical reads
0 redo size
1047 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
SQL>
這種情況下從執行計劃上看使用IOT表比使用普通表能獲取更高的執行速度。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15720542/viewspace-722196/，如需轉載，請註明出處，否則將追究法律責任。