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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15720542/viewspace-722196/