ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10g新增排序哈希聚集表

10g新增排序哈希聚集表

原创 Linux操作系统 作者:yangtingkun 时间:2009-07-11 23:22:57 0 删除 编辑

Oracle10gHASH CLUSTER表增加了新功能,允许HASH CLUSTER表对于指定HASH键值的记录按照指定列的顺序存放。而这个功能有点像是HASH CLUSTER表和索引组织表的结合体。

 

 

首先看看如何建立一个HASH SORT CLUSTER

SQL> CREATE CLUSTER C_HASH_SORT
  2  (ID NUMBER, CREATED DATE SORT)
  3  HASHKEYS 5000 SIZE 100;

Cluster created.

SQL> CREATE TABLE T_HASH_SORT
  2  (ID NUMBER, CREATED DATE SORT, NAME VARCHAR2(30))
  3  CLUSTER C_HASH_SORT (ID, CREATED);

Table created.

SQL> BEGIN
  2     FOR I IN 1..1000 LOOP
  3             FOR J IN 1..50 LOOP
  4                     INSERT INTO T_HASH_SORT
  5                     VALUES (I, SYSDATE - 51 + J, 'OBJECT' || I ||J);
  6             END LOOP;
  7     END LOOP;
  8     COMMIT;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> SELECT COUNT(*) FROM T_HASH_SORT;

  COUNT(*)
----------
     50000

接下来看看什么样的查询可以从这种结构的表中获得性能优势:

SQL> SET AUTOT ON
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

SQL> SELECT *
  2  FROM T_HASH_SORT
  3  WHERE ID = 352
  4  ORDER BY CREATED;

        ID CREATED             NAME
---------- ------------------- ------------------------------
       352 2009-05-22 16:30:07 OBJECT3521
       352 2009-05-23 16:30:07 OBJECT3522
       352 2009-05-24 16:30:07 OBJECT3523
       352 2009-05-25 16:30:07 OBJECT3524
       352 2009-05-26 16:30:07 OBJECT3525
       352 2009-05-27 16:30:07 OBJECT3526
       352 2009-05-28 16:30:07 OBJECT3527
.
.
.
       352 2009-07-09 16:30:07 OBJECT35249
       352 2009-07-10 16:30:07 OBJECT35250

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1422057106

----------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |    23 |   897 |     0   (0)|
|*  1 |  TABLE ACCESS HASH| T_HASH_SORT |    23 |   897 |            |
----------------------------------------------------------------------

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

   1 - access("ID"=352)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       2469  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

可以看到,上面的查询只执行了一个TABLE ACCESS HASH,根本没有执行排序的操作,这是因为数据本身就是根据CREATED字段的顺序存储的。

SQL> CREATE TABLE T_NORMAL
  2  (ID NUMBER, CREATED DATE, NAME VARCHAR2(30));

Table created.

SQL> BEGIN
  2     FOR I IN 1..1000 LOOP
  3             FOR J IN 1..50 LOOP
  4                     INSERT INTO T_NORMAL
  5                     VALUES (I, SYSDATE - 51 + J, 'OBJECT' || I ||J);
  6             END LOOP;
  7     END LOOP;
  8     COMMIT;
  9  END;
 10  /

PL/SQL procedure successfully completed.

SQL> COMMIT;

Commit complete.

SQL> SELECT *
  2  FROM T_NORMAL
  3  WHERE ID = 352
  4  ORDER BY CREATED;

        ID CREATED             NAME
---------- ------------------- ------------------------------
       352 2009-05-22 22:19:30 OBJECT3521
       352 2009-05-23 22:19:30 OBJECT3522
       352 2009-05-24 22:19:30 OBJECT3523
       352 2009-05-25 22:19:30 OBJECT3524
       352 2009-05-26 22:19:30 OBJECT3525
.
.
.
       352 2009-07-09 22:19:30 OBJECT35249
       352 2009-07-10 22:19:30 OBJECT35250

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1781898017

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |    96 |  3744 |    44   (5)| 00:00:01 |
|   1 |  SORT ORDER BY     |          |    96 |  3744 |    44   (5)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T_NORMAL |    96 |  3744 |    43   (3)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - filter("ID"=352)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        125  consistent gets
          0  physical reads
          0  redo size
       2469  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         50  rows processed

对于普通表来说,不但需要扫描表,还需要排序操作,代价就要大得多。

当然普通表可以添加索引改善情况:

SQL> CREATE INDEX IND_T_NORMAL ON T_NORMAL (ID, CREATED);

Index created.

SQL> SELECT *
  2  FROM T_NORMAL
  3  WHERE ID = 352
  4  ORDER BY CREATED;

        ID CREATED             NAME
---------- ------------------- ------------------------------
       352 2009-05-22 22:19:30 OBJECT3521
       352 2009-05-23 22:19:30 OBJECT3522
       352 2009-05-24 22:19:30 OBJECT3523
       352 2009-05-25 22:19:30 OBJECT3524
       352 2009-05-26 22:19:30 OBJECT3525
.
.
.
       352 2009-07-09 22:19:30 OBJECT35249
       352 2009-07-10 22:19:30 OBJECT35250

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3493887494

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |    50 |  1950 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_NORMAL     |    50 |  1950 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IND_T_NORMAL |    50 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("ID"=352)
       filter("ID"=352)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       2469  bytes sent via SQL*Net to client
        525  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

即使添加了索引,所需要的物理读也要比HASH SORT CLUSTER要多,因为Oracle先要访问索引,在索引找到对应的键值,然后根据索引的ROWID去读取表。这个操作代价要比通过HASH算法直接找到表的行记录的代价高得多。

当然普通表建立索引有更好的灵活性,使用更多更灵活的情况,而HASH SORT CLUSTER只对特定的查询有效,查询必须提供HASH键值列的值,而且必须对指定的列进行排序。

 

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

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10405181