載入資料順序對HASH SORT CLUSTER效能影響

HASH SORT CLUSTER表由於資料是根據順序儲存的，因此載入或插入資料的順序對影響影響很大。

 

 

看一個最簡單的例子：

SQL> SET TIMING ON
SQL> CREATE CLUSTER C_HASH_SORT
  2  (ID NUMBER, CREATED DATE SORT)
  3  HASHKEYS 1000 SIZE 31500;

Cluster created.

Elapsed: 00:00:00.12
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 MOD(ROWNUM, 1000) ID,
  3  OWNER,
  4  OBJECT_NAME,
  5  OBJECT_TYPE,
  6  CREATED
  7  FROM DBA_OBJECTS;

70767 rows created.

Elapsed: 00:00:01.69
SQL> DROP CLUSTER C_HASH_SORT INCLUDING TABLES;

Cluster dropped.

Elapsed: 00:00:00.39
SQL> CREATE CLUSTER C_HASH_SORT
  2  (ID NUMBER, CREATED DATE SORT)
  3  HASHKEYS 1000 SIZE 31500;

Cluster created.

Elapsed: 00:00:00.13
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, 1000) ID,
  6     OWNER,
  7     OBJECT_NAME,
  8     OBJECT_TYPE,
  9     CREATED
 10     FROM DBA_OBJECTS
 11  )
 12  ORDER BY ID, CREATED;

70767 rows created.

Elapsed: 00:00:00.58

對於無序資料的載入，載入7W條記錄花了將近1.7秒的時間，而如果對資料進行排序後載入，僅用了不到0.6秒的時間。

對於普通表而言，兩種方式的載入速度是一樣的：

SQL> CREATE TABLE T_NORMAL
  2  (ID NUMBER,
  3  OWNER VARCHAR2(30),
  4  OBJECT_NAME VARCHAR2(30),
  5  OBJECT_TYPE VARCHAR2(30),
  6  CREATED DATE);

Table created.

Elapsed: 00:00:00.01
SQL> INSERT INTO T_NORMAL
  2  SELECT MOD(ROWNUM, 1000) ID,
  3  OWNER,
  4  OBJECT_NAME,
  5  OBJECT_TYPE,
  6  CREATED
  7  FROM DBA_OBJECTS;

70768 rows created.

Elapsed: 00:00:00.35
SQL> DROP TABLE T_NORMAL PURGE;

Table dropped.

Elapsed: 00:00:00.04
SQL> CREATE TABLE T_NORMAL
  2  (ID NUMBER,
  3  OWNER VARCHAR2(30),
  4  OBJECT_NAME VARCHAR2(30),
  5  OBJECT_TYPE VARCHAR2(30),
  6  CREATED DATE);

Table created.

Elapsed: 00:00:00.01
SQL> INSERT INTO T_NORMAL
  2  SELECT *
  3  FROM
  4  (
  5     SELECT MOD(ROWNUM, 1000) ID,
  6     OWNER,
  7     OBJECT_NAME,
  8     OBJECT_TYPE,
  9     CREATED
 10     FROM DBA_OBJECTS
 11  )
 12  ORDER BY ID, CREATED;

70768 rows created.

Elapsed: 00:00:00.35

顯然普通表載入要比HASH SORT CLUSTER表載入速度快，因為後者要進行更多的處理。

除了批次載入需要排序，對於HASH SORT CLUSTER表，隨後插入的記錄也應該滿足排序列的遞增順序，否則就會影響效率，並導致表中的資料不斷的變動。

SQL> DROP CLUSTER C_HASH_SORT INCLUDING TABLES;

Cluster dropped.

Elapsed: 00:00:00.27
SQL> CREATE CLUSTER C_HASH_SORT
  2  (ID NUMBER, CREATED DATE SORT)
  3  HASHKEYS 1000 SIZE 1100;

Cluster created.

Elapsed: 00:00:00.02
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.00
SQL> BEGIN
  2     FOR I IN 1..100 LOOP
  3             FOR J IN 1..1000 LOOP
  4                     INSERT INTO T_HASH_SORT
  5                     VALUES (J, 'A', 'A', 'A', SYSDATE + I);
  6             END LOOP;
  7     END LOOP;
  8     COMMIT;
  9  END;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.80
SQL> DROP CLUSTER C_HASH_SORT INCLUDING TABLES;

Cluster dropped.

Elapsed: 00:00:00.27
SQL> CREATE CLUSTER C_HASH_SORT
  2  (ID NUMBER, CREATED DATE SORT)
  3  HASHKEYS 1000 SIZE 1100;

Cluster created.

Elapsed: 00:00:00.03
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> BEGIN
  2     FOR I IN 1..100 LOOP
  3             FOR J IN 1..1000 LOOP
  4                     INSERT INTO T_HASH_SORT
  5                     VALUES (J, 'A', 'A', 'A', SYSDATE - I);
  6             END LOOP;
  7     END LOOP;
  8     COMMIT;
  9  END;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.03

對於HASH SORT CLUSTER表而言，應該滿足插入資料的遞增性，否則的話可能並不合適使用這個結構。