10g新增排序哈希聚集表

最新推荐文章于 2024-10-02 00:27:17 发布

ckawt40802

最新推荐文章于 2024-10-02 00:27:17 发布

阅读量76

点赞数

文章标签：数据库

Oracle的10g对HASH 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;

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-608907/