对于HASH CLUSTER表而言，HASHKEYS和SIZE是最重要的存储参数设置，而对于HASH SORT CLUSTER表也是一样的。
对于一个正常设置参数的HASH SORT CLUSTER表：
SQL> CREATE CLUSTER C_HASH_SORT
2 (ID NUMBER, CREATED DATE SORT)
3 HASHKEYS 100000 SIZE 1125;
Cluster created.
SQL> CREATE TABLE T_HASH_SORT
2 (ID NUMBER,
3 OWNER VARCHAR2(30),
4 OBJECT_NAME VARCHAR2(30),
5 OBJECT_TYPE VARCHAR2(30),
6 CREATED DATE SORT)
7 CLUSTER C_HASH_SORT (ID, CREATED);
Table created.
SQL> SET TIMING ON
SQL> INSERT INTO T_HASH_SORT
2 SELECT *
3 FROM
4 (
5 SELECT MOD(ROWNUM, 100000) ID,
6 A.OWNER,
7 OBJECT_NAME,
8 OBJECT_TYPE,
9 A.CREATED
10 FROM DBA_OBJECTS A, DBA_DB_LINKS
11 )
12 ORDER BY ID, CREATED;
2477265 rows created.
Elapsed: 00:00:35.32
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
SQL> SET AUTOT ON
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT ID, OWNER, OBJECT_TYPE, CREATED
8 FROM T_HASH_SORT
9 WHERE ID = 11232
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM <= 20
13 )
14 WHERE RN > 10;
RN ID OWNER OBJECT_TYPE CREATED
---------- ---------- -------------------- ------------------------------ --------------
11 11232 SYS JAVA CLASS 11-6月 -08
12 11232 SYS JAVA CLASS 11-6月 -08
13 11232 PUBLIC SYNONYM 11-6月 -08
14 11232 PUBLIC SYNONYM 11-6月 -08
15 11232 CTXSYS TABLE 11-6月 -08
16 11232 ORDSYS PROCEDURE 11-6月 -08
17 11232 MDSYS TYPE 11-6月 -08
18 11232 SYS JAVA CLASS 11-6月 -08
19 11232 SYSMAN TRIGGER 11-6月 -08
20 11232 JIANGSU15 INDEX 12-6月 -08
10 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 156907859
------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------------
0 SELECT STATEMENT 20 1380 5 (100) 00:00:01
* 1 VIEW 20 1380 5 (100) 00:00:01
* 2 COUNT STOPKEY
3 VIEW 2293 125K 5 (100) 00:00:01
* 4 TABLE ACCESS HASH T_HASH_SORT 2293 125K
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">10)
2 - filter(ROWNUM<=20)
4 - access("ID"=11232)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1087 bytes sent via SQL*Net to client
491 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
如果参数设置合理，无论是批量加载数据，还是查询获取数据性能都会相对高效。需要注意的是，HASHKEYS计算的结果并不包含SORT列，而是CLUSTER中除去SORT列以外的列的键值数，而SIZE对应的是每个HASHKEYS的所有记录的和。
在上面的例子中，ID列中不相同的键值的列数就是HASHKEYS的值，这里是100000。而每个ID对应将近25条记录，每条记录的平均长度是45，因此得到SIZE为1125。
而如果认为HASH SORT CLUSTER中的所有列的不同值来计算HASHKEYS的大小，就会错误的设置存储参数：
SQL> DROP CLUSTER C_HASH_SORT INCLUDING TABLES;
Cluster dropped.
Elapsed: 00:00:00.11
SQL> SELECT COUNT(*)
2 FROM
3 (
4 SELECT DISTINCT ID, CREATED
5 FROM
6 (
7 SELECT MOD(ROWNUM, 100000) ID,
8 A.OWNER,
9 OBJECT_NAME,
10 OBJECT_TYPE,
11 A.CREATED
12 FROM DBA_OBJECTS A, DBA_DB_LINKS
13 )
14 );
COUNT(*)
----------
2477059
Elapsed: 00:00:03.54
SQL> CREATE CLUSTER C_HASH_SORT
2 (ID NUMBER, CREATED DATE SORT)
3 HASHKEYS 2477059 SIZE 70;
Cluster created.
Elapsed: 00:00:01.77
SQL> CREATE TABLE T_HASH_SORT
2 (ID NUMBER,
3 OWNER VARCHAR2(30),
4 OBJECT_NAME VARCHAR2(30),
5 OBJECT_TYPE VARCHAR2(30),
6 CREATED DATE SORT)
7 CLUSTER C_HASH_SORT (ID, CREATED);
Table created.
Elapsed: 00:00:00.01
SQL> INSERT INTO T_HASH_SORT
2 SELECT *
3 FROM
4 (
5 SELECT MOD(ROWNUM, 100000) ID,
6 A.OWNER,
7 OBJECT_NAME,
8 OBJECT_TYPE,
9 A.CREATED
10 FROM DBA_OBJECTS A, DBA_DB_LINKS
11 )
12 ORDER BY ID, CREATED;
2477265 rows created.
Elapsed: 00:00:41.71
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.00
SQL> SET AUTOT ON
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT ID, OWNER, OBJECT_TYPE, CREATED
8 FROM T_NORMAL
9 WHERE ID = 11232
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM <= 20
13 )
14 WHERE RN > 10;
RN ID OWNER OBJECT_TYPE CREATED
---------- ---------- -------------------- ------------------------------ --------------
11 11232 SYS JAVA CLASS 11-6月 -08
12 11232 SYS JAVA CLASS 11-6月 -08
13 11232 PUBLIC SYNONYM 11-6月 -08
14 11232 SYS JAVA CLASS 11-6月 -08
15 11232 CTXSYS TABLE 11-6月 -08
16 11232 ORDSYS JAVA RESOURCE 11-6月 -08
17 11232 MDSYS PACKAGE BODY 11-6月 -08
18 11232 PUBLIC SYNONYM 11-6月 -08
19 11232 PUBLIC SYNONYM 11-6月 -08
20 11232 JIANGSU15 INDEX 12-6月 -08
10 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1590327436
------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes Cost (%CPU) Time
------------------------------------------------------------------------------------------
0 SELECT STATEMENT 20 1380 4 (0) 00:00:01
* 1 VIEW 20 1380 4 (0) 00:00:01
* 2 COUNT STOPKEY
3 VIEW 21 1176 4 (0) 00:00:01
4 TABLE ACCESS BY INDEX ROWID T_NORMAL 21 609 4 (0) 00:00:01
* 5 INDEX RANGE SCAN IND_T_NORMAL 25 3 (0) 00:00:01
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">10)
2 - filter(ROWNUM<=20)
5 - access("ID"=11232)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1099 bytes sent via SQL*Net to client
491 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
可以看到，如果错误的设置HASHKEYS和SIZE参数，无论是批量插入所虚时间，还是查询语句所需的IO数，都要超过正常设置的值。
而且设置太大的HASHKEYS，会导致Oracle预分配大量的空间，造成空间的浪费；而设置HASHKEYS太小，就可能出现HASH冲突，从而影响性能。因此对于HASH类型的CLUSTER，设置HASHKEYS和SIZE参数，应该经过仔细的设计和计算。
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2011-8-25 18:07:32 |
赞(
操作
