ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle-tom-table-iot

oracle-tom-table-iot

原创 Linux操作系统 作者:oracle_db 时间:2012-04-25 13:21:45 0 删除 编辑
目的:使用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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 对象表
请登录后发表评论 登录
全部评论

注册时间:2008-11-13

  • 博文量
    158
  • 访问量
    306976