有关大表建索引的效率问题

看来网上有不少文章对大表建索引的处理。就做个测试。
环境是linux el5+oracle10.2.0.1
内存4g  cpu是两个4核  测试的表有将近4千万行数据

测试结果如下
                         时间            sort（disk）
没有并行     3分22秒（3分24秒）   1（1）
parallel 2   2分16（1分09秒）     2（0）
parallel 4   1分53 （1分）        4（0）
parallel 6   1分58 （58秒）       6（0）
parallel 8   2分（1分4秒）        8（0）   

括号外的值是在
workarea_size_policy=auto
pga_aggregate_target=385M
的情况下取得的
括号里的值是在
workarea_size_policy=manual
sort_area_size=1024M
的情况取得
从结果可以看出：
1.。并行确实可以提高效率，但并行数为多少合适，需要考证，这里并不明显；
2.。加大排序内存也能提高效率，尤其是在并行的情况下。估计主要是没有使用temp空间就好一些。在不使用temp的情况下，在加大内存应该不会有效率，反而会造成系统内存紧张。
 

测试时的脚本：
declare
  v_str varchar2(200);
begin
  select to_char(sysdate,'hh24:mi:ss') into v_str from dual;
  dbms_output.put_line(v_str);
  execute   immediate 'create index idx_owner on test(owner)';
  select to_char(sysdate,'hh24:mi:ss') into v_str from dual;
  dbms_output.put_line(v_str);
end;

declare
  v_str varchar2(200);
begin
  select to_char(sysdate,'hh24:mi:ss') into v_str from dual;
  dbms_output.put_line(v_str);
  execute   immediate 'create index idx_owner on test(owner) online nologging parallel 8 compute statistics';
  select to_char(sysdate,'hh24:mi:ss') into v_str from dual;
  dbms_output.put_line(v_str);
end;

用过的命令
alter session set sort_area_size=1073741824;
alter session set workarea_size_policy=***;


在测试过程中还了解到在10g中
workarea_size_policy自动管理时sort_area_size参数是无效的。只有在手动管理pga的情况下才有效。但在9i下就不同了（没有验证）。下面是参考资料的描述：
if ( 数据库版本 >= 10gR1 )
then
   if (workarea_size_policy=auto)
   then
       sort area size 无效
   else
       sort area size 有效
   end if
else -- 数据库版本 < 10gr1
   if ( shared server 连接方式 )
   then
       sort area size 有效
   else -- dedicated server连接方式
       if (workarea_size_policy=auto)
       then
           sort area size 无效
       else
           sort area size 有效
       end if
    end if
end if

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

转载于:http://blog.itpub.net/32939/viewspace-573232/