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
从执行计划上来看,对聚簇的查询产生的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;
提交完成。
这时候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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/16628454/viewspace-609951/
530
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
