索引對直接路徑載入的影響

TEST表大小1,984M

 

-----------表上無索引，每次操作前flush buffer cache

flush buffer cache

insert /*+ append */ into test1 select * from test;

19999996 rows created.

Elapsed: 00:01:19.06

 

flush buffer cache

create index tt1 on test1(object_id);

Index created.

Elapsed: 00:00:51.82

 

flush buffer cache

create index tt2 on test1(object_type);

Index created.

Elapsed: 00:00:55.21

 

 

總共耗時：79+52+55=186。

 

 

---------------表上存在兩個索引

insert /*+ append */ into test1 select * from test;

19999996 rows created.

Elapsed: 00:04:12.60

 

總共耗時：252秒

 

 

第一種方式比第二種方式快了66秒。

 

 

11GR2版本（之前的版本沒有測試），是在資料載入完成後，建立的索引。

 

因為在建立的過程中，透過指令碼@size檢視段的大小：

1）表段的大小，一直在增大，增大到1,984M後，不再增大。整個過程中，兩個索引段的大小沒有變化。說明表的載入跟索引的載入是分開的。

2）表載入完成後，索引段的大小開始增大，先是TT2索引大小增大，然後是TT1索引。

 

用@showlong指令碼檢視，載入資料的整個過程：

1）先是Table Scan:  TEST.TEST: 36612 out of 382813 Blocks done

2）再是Sort/Merge:  : 10765 out of 78629 Blocks done

3）最後是Sort Output:  : 18293 out of 78601 Blocks done ，這個過程會出現兩次。因為是兩個索引。

SQL_ID             MESSAGE                                                                          TIME_REMAINING
------------------ -------------------------------------------------------------------------------- --------------
7dy0qdzzmq7k4      Sort Output:  : 13678 out of 68976 Blocks done                                               28

 

過程1是掃描表。過程2說不清楚。過程三是建立索引過程中的排序。

 

測試中發現_sort_multiblock_read_count不再有用。

alter session set "_sort_multiblock_read_count"=128;

Session altered.

 

後臺觀察到的P3引數direct path read temp一直是31

 

裡面有一點需要思考：

我們最終的目的是想要把資料載入到表裡，表包含兩個索引。

第一種方式先載入了表資料後建立的索引

第二種方式在存在兩索引表上直接載入資料，但是我們透過一些手段發現，ORACLE其實也是先載入了資料再建立的索引。跟第一種似乎沒什麼區別。但是時間上是有明顯差異的。第一種比第一種快了不少。

 

實驗過程中我發現，第二種方式，雖然載入資料過程中，索引的大小不變，但是temp表空間的使用一直在增加，產生了兩個臨時段：

    SID SPID       USERNAME     MACHINE            LOGON_TIME          PROGRAM              EL_TIME
------- ---------- ------------ ------------------ ------------------- -------------------- -------------------------------------------
OPERATION_TYPE                           MB                                           SQL_ID
---------------------------------------- -------------------------------------------- ------------------
SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
   2084 3959       TEST         dba-service-12     2011-12-31 14:58:01 - 141(s)
                                                                       12 (TNS V1-V3)
IDX MAINTENANCE (SOR                     540(MB)                                      7dy0qdzzmq7k4
insert /*+ append */ into test1 select * from test

   2084 3959       TEST         dba-service-12     2011-12-31 14:58:01 - 141(s)
                                                                       12 (TNS V1-V3)
IDX MAINTENANCE (SOR                     629(MB)                                      7dy0qdzzmq7k4
insert /*+ append */ into test1 select * from test

 

表資料載入完成後，開始讀取臨時段，建立索引

 

我個人的猜想第二種方式慢，主要是因為在載入表資料的過程中，需要對索引欄位存入臨時段，並且需要排序，並行的這兩個操作：載入資料+寫入臨時段. 影響最終的載入速度。