ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 索引聚簇表ICT测试(Index Cluster Table)

索引聚簇表ICT测试(Index Cluster Table)

原创 Linux操作系统 作者:david3389 时间:2009-07-22 22:34:59 0 删除 编辑

ICT(Index Cluster Table)其实是很好用的,按索引顺序排列数据,简单测试了一下.

scott@PRIMARY> create cluster heap_no_cluster 
  2  (empno number)
  3  size 500
  4  /

簇已创建。

scott@PRIMARY> desc heap_no;
 名称                                                  是否为空? 类型
 ----------------------------------------------------- -------- ----------------
--------------------
 EMPNO                                                          NUMBER
 ENAME                                                          NUMBER

scott@PRIMARY> create table heapno
  2  (empno number primary key,
  3  ename number)
  4  cluster heap_no_cluster(empno)
  5  /

表已创建。

scott@PRIMARY> desc table heap_addresses
用法: DESCRIBE [schema.]object[@db_link]
scott@PRIMARY>
scott@PRIMARY> desc heap_addresses
 名称                                                  是否为空? 类型
 ----------------------------------------------------- -------- ----------------
--------------------
 EMPNO                                                 NOT NULL NUMBER
 ADDR_TYPE                                             NOT NULL VARCHAR2(10)
 STREET                                                         VARCHAR2(20)
 CITY                                                           VARCHAR2(20)
 STATE                                                          VARCHAR2(2)
 ZIP                                                            NUMBER
 TESTNO                                                         NUMBER

scott@PRIMARY> create table heap
  2  (empno number references heapno(empno)
  3  addr_type varchar2(10),
  4  street varchar2(20),
  5  city varchar2(20),
  6  state varchar2(2),
  7  zip number
  8  ,testno number)
  9  cluster heap_no_cluster(empno)
 10  /
addr_type varchar2(10),
*
第 3 行出现错误:
ORA-00907: missing right parenthesis


scott@PRIMARY> edi
已写入 file afiedt.buf

  1  create table heap
  2  (empno number references heapno(empno),
  3  addr_type varchar2(10),
  4  street varchar2(20),
  5  city varchar2(20),
  6  state varchar2(2),
  7  zip number
  8  ,testno number)
  9* cluster heap_no_cluster(empno)
scott@PRIMARY> /

表已创建。

scott@PRIMARY> edi
已写入 file afiedt.buf

  1  begin
  2  for x in (select * from heap_no)
  3  loop
  4  insert into heapno
  5* values(x.empno,x.ename)
scott@PRIMARY> desc heap_no;
 名称                                                  是否为空? 类型
 ----------------------------------------------------- -------- ----------------
--------------------
 EMPNO                                                          NUMBER
 ENAME                                                          NUMBER

scott@PRIMARY> edit
已写入 file afiedt.buf

  1  begin
  2  for x in (select * from heap_no)
  3  loop
  4  insert into heapno
  5  values(x.empno,x.ename);
  6  insert into heap
  7  select * from heap_addresses
  8  where empno=x.empno;
  9  end loop;
 10* end;
scott@PRIMARY> /
begin
*
第 1 行出现错误:
ORA-02032: clustered tables cannot be used before the cluster index is built
ORA-06512: at line 4


scott@PRIMARY> edi
已写入 file afiedt.buf

  1  begin
  2  for x in (select * from heap_no)
  3  loop
  4  insert into heapno
  5  values(x.empno,x.ename);
  6  insert into heap
  7  select * from heap_addresses
  8  where empno=x.empno;
  9  end loop;
 10* end;
scott@PRIMARY> create index heap_no_cluster_idx on cluster heap_no_cluster;

索引已创建。

scott@PRIMARY> edi
已写入 file afiedt.buf

  1  begin
  2  for x in (select * from heap_no)
  3  loop
  4  insert into heapno
  5  values(x.empno,x.ename);
  6  insert into heap
  7  select * from heap_addresses
  8  where empno=x.empno;
  9  end loop;
 10* end;
scott@PRIMARY> /

PL/SQL 过程已成功完成。

scott@PRIMARY> analyze table heap_no compute statistics;

表已分析。

scott@PRIMARY> analyze table heap compute statistics;

表已分析。

scott@PRIMARY> analyze table heap_no_cluster compute statistics;
analyze table heap_no_cluster compute statistics
              *
第 1 行出现错误:
ORA-00942: table or view does not exist


scott@PRIMARY> analyze cluster heap_no_cluster compute statistics;

簇已分析。

scott@PRIMARY> select a.empno,b.ename
  2  from heap a,heapno b
  3  where a.empno=15238
  4  and a.empno=b.empno
  5
scott@PRIMARY> ecit
SP2-0042: 未知命令 "ecit" - 其余行忽略。
scott@PRIMARY> edi
已写入 file afiedt.buf

  1  select a.empno,b.ename
  2  from heap a,heapno b
  3  where a.empno=15238
  4* and a.empno=b.empno

已经准备好数据,并且分析过表及聚簇了,现在看一下具体的执行计划,首先对聚簇进行查询,再查询普通表.

scott@PRIMARY> set autotrace traceonly;
scott@PRIMARY> edi
已写入 file afiedt.buf

  1  select a.empno,b.ename
  2  from heap a,heapno b
  3  where a.empno=15238
  4* and a.empno=b.empno
scott@PRIMARY> /


执行计划
----------------------------------------------------------
Plan hash value: 2212161699

--------------------------------------------------------------------------------
------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)
| Time     |
--------------------------------------------------------------------------------
------------
|   0 | SELECT STATEMENT             |             |     5 |    60 |     3   (0)
| 00:00:01 |
|   1 |  NESTED LOOPS                |             |     5 |    60 |     3   (0)
| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| HEAPNO      |     1 |     8 |     2   (0)
| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | SYS_C005395 |     1 |       |     1   (0)
| 00:00:01 |
|*  4 |   TABLE ACCESS CLUSTER       | HEAP        |     5 |    20 |     1   (0)
| 00:00:01 |
--------------------------------------------------------------------------------
------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("B"."EMPNO"=15238)
   4 - filter("A"."EMPNO"=15238)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
        450  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

scott@PRIMARY> edi
已写入 file afiedt.buf

  1  select a.empno,b.ename
  2  from heap_addresses a,heap_no b
  3  where a.empno=15238
  4* and a.empno=b.empno
scott@PRIMARY> /


执行计划
----------------------------------------------------------
Plan hash value: 275802435

--------------------------------------------------------------------------------
--
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time
 |
--------------------------------------------------------------------------------
--
|   0 | SELECT STATEMENT   |             |     5 |    65 |    30   (7)| 00:00:01
 |
|   1 |  NESTED LOOPS      |             |     5 |    65 |    30   (7)| 00:00:01
 |
|*  2 |   TABLE ACCESS FULL| HEAP_NO     |     1 |     8 |    28   (8)| 00:00:01
 |
|*  3 |   INDEX RANGE SCAN | SYS_C005363 |     5 |    25 |     2   (0)| 00:00:01
 |
--------------------------------------------------------------------------------
--

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("B"."EMPNO"=15238)
   3 - access("A"."EMPNO"=15238)


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        109  consistent gets
          0  physical reads
          0  redo size
        450  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

scott@PRIMARY>

从执行计划上来看,对聚簇的查询产生的COST非常低,且一致性读非常低,还是值得做的措施,对于聚簇表的使用环境网上有很多介绍,这里不再赘述,只是想通过例子来实验一下,得出结论究竟ICT可以做到什么样的优化,要用好ICT最重要的就是充分计算好SIZE的大小.
SIZE该如何计算呢?
很简单,想象一下HEAPNO等于DEPT,HEAP等于EMP,那么表的关系就好理解了,首先analyze一下所有的表,这里最好不要用dbms_stats包,后者不计算行头的3个字节,我们用来计算的数据尽量做的冗余一些.
然后来看它们的行长度:
scott@PRIMARY> edit
已写入 file afiedt.buf

  1  select table_name,avg_row_len from user_tables
  2  where table_name='HEAP'
  3  union
  4  select table_name,avg_row_len from user_tables
  5* where table_name='HEAPNO'
scott@PRIMARY> /

TABLE_NAME                                                   AVG_ROW_LEN
------------------------------------------------------------ -----------
HEAP                                                                  73
HEAPNO                                                                33

scott@PRIMARY> select count(*) from heap where empno=15238;

  COUNT(*)
----------
         5

scott@PRIMARY> select count(*) from heapno where empno=15238;

  COUNT(*)
----------
         1

scott@PRIMARY>
也就是说,1条heapno对应5条heap记录,那么一个聚簇键的大小就应该计算为:(HEAPNO_ROW_LEN)+(HEAP_ROW_LEN)*5=33+73*5=398,我的SIZE设置为500是冗余大小.因为有SIZE设置,所以聚簇在使用时对与记录数量的扩展就显的要差一些,如果聚簇键记录数扩展太块,超过了SIZE值就会溢出存储到另外的块,但这并不是说不能DML,只不过要看这个DML要怎么样来插入,尽量避免过度的块串链,若是父子表组做聚簇的话,那么插入顺序尽量保证先插入1个父表记录然后插入对应的子表记录,尽量不要把父表记录都插入完了再插入子表.

聚簇不仅可以用于父子表中,也可以用于两个单表中,只要有共同的列,看测试:
lcbj@SUPPORT> create cluster t1_t2_cluster
  2  (id number)
  3  size 7000;

簇已创建。

lcbj@SUPPORT> create table t1
  2  (id number primary key,
  3  name varchar2(20))
  4  cluster t1_t2_cluster(id)
  5  /

表已创建。

lcbj@SUPPORT> create table t2
  2  (id number primary key,
  3  address varchar2
  4
lcbj@SUPPORT> drop table t1;

表已删除。

lcbj@SUPPORT> create table t1
  2  (id number,
  3  name varchar2(20))
  4  cluster t1_t2_cluster(id)
  5  /

表已创建。

lcbj@SUPPORT> create table t2
  2  (id number,
  3  addr varchar2(20))
  4  cluster t1_t2_cluster(id)
  5  /

表已创建。

lcbj@SUPPORT> insert into t1 values(1,'x');
insert into t1 values(1,'x')
            *
第 1 行出现错误:
ORA-02032: clustered tables cannot be used before the cluster index is built


lcbj@SUPPORT> create index t1_t2_cluster_idx on cluster t1_t2_cluster;

索引已创建。

lcbj@SUPPORT> insert into t1 values(1,'x');

已创建 1 行。

lcbj@SUPPORT> insert into t1 values(2,'x');

已创建 1 行。

lcbj@SUPPORT> insert into t1 values(3,'x');

已创建 1 行。

lcbj@SUPPORT> insert into t2 values(4,'x');

已创建 1 行。

lcbj@SUPPORT> insert into t2 values(5,'x');

已创建 1 行。

lcbj@SUPPORT> insert into t2 values(6,'x');

已创建 1 行。

lcbj@SUPPORT> commit;

提交完成。

lcbj@SUPPORT>

这时候t1里面有三条记录,分别是1,2,3,根据size的设置,一定是不在同一个块中,t2中的记录是4,5,6,也不在一个块中,验证一下:
lcbj@SUPPORT> select dbms_rowid.rowid_block_number(t1.rowid) t1_blk from t1;

    T1_BLK
----------
    108684
    108687
    108688

lcbj@SUPPORT> select dbms_rowid.rowid_block_number(t2.rowid) t1_blk from t2;

    T1_BLK
----------
    108685
    108686
    108702

lcbj@SUPPORT>
这说明,它们都保存数据在聚簇段了,我们再来看看插入1,2,3的记录到t2以后的情况:
lcbj@SUPPORT> select id,dbms_rowid.rowid_block_number(t2.rowid) t1_blk from t2;

        ID     T1_BLK
---------- ----------
         3     108684
         5     108685
         4     108686
         1     108687
         2     108688
         6     108702

已选择6行。

lcbj@SUPPORT> select id,dbms_rowid.rowid_block_number(t1.rowid) t1_blk from t1;

        ID     T1_BLK
---------- ----------
         3     108684
         1     108687
         2     108688

lcbj@SUPPORT>
可以看到,ID相等的都放在同一个块中,聚簇的管理已经起到作用,所以说不一定父子表才能做聚簇,只要是有相同的列,都可以做聚簇.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16628454/viewspace-609951/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-17

  • 博文量
    19
  • 访问量
    120524