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。

---------------表上存在两个索引

flush buffer cache

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表空间的使用一直在增加，产生了两个临时段：

sys@SMART>@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 sqlplus@dba-service- 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 sqlplus@dba-service- 141(s)
                                                                       12 (TNS V1-V3)
IDX MAINTENANCE (SOR                     629(MB)                                      7dy0qdzzmq7k4
insert /*+ append */ into test1 select * from test

表数据加载完成后，开始读取临时段，创建索引

我个人的猜想第二种方式慢，主要是因为在加载表数据的过程中，需要对索引字段存入临时段，并且需要排序，并行的这两个操作：加载数据+写入临时段. 影响最终的加载速度。

ITPub博客

索引对直接路径加载的影响

最新文章