朋友在500w的表上建索引,半个小时都没有结束。所以就讨论如何提速。
一.先来看一下创建索引要做哪些操作:
1.把indexkey的data读到内存
==>如果data没在db_cache中,这时候很容易有大量的dbfilescatterreadwait
2.对indexkey的data作排序
==>sort_area_size或者pga_aggregate_target不够大的情况下,需要做disksort,会有大量的driectpathread/write,另外,消耗大量CPUTime
3.创建新的indexsegment,把排过序的indexdata写到所创建的indexsegment里面
==>如果index很大,那么,有时也会有redolog相关等待,如:
logbufferspace,logfilesync,logfileparallelwrite等
所以,在建大表索引时,可以增大pga,增大temptablepace,并且用nologging或并行选项。
如:
createindexidx_logsonlogs(time)nologgingparallel4;
并行度一般看CPU个数。当然在CPU比较空闲的情况下可以多并行几个。对于单CPU不建议用并行,这样反而会增加创建时间。也可以根据v$session_wait的资料,做针对性的tuning,这样可以降低点时间。
补充知识:
查看cpu信息:more/proc/cpuinfo
查看内存信息:more/proc/meminfo
查看操作系统信息:more/etc/issue
有关索引概念性的东西,请参考我的Blog:
Oracle索引详解
http://blog.csdn.net/tianlesoftware/archive/2010/03/05/5347098.aspx
二.测试
自己也测试了下。测试环境:Oracle11gR2,win764bit,CPUT66702.2G双核,内存:4G。
1.查看表的数据量:
SQL>selectcount(*)fromcustaddr;
COUNT(*)
----------
7230464
2.查看现有索引:
SQL>selectindex_name,index_typefromuser_indexeswheretable_name='CUSTADDR';
INDEX_NAMEINDEX_TYPE
---------------------------------------------------------
PK_CUSTADDR_TP_723NORMAL
IX_CUSTADDR_ADDRABB_TPNORMAL
IX_CUSTADDR_TEAMID_TPNORMAL
IX_CUSTADDR_CUSTID_TPNORMAL
IX_CUSTADDR_COMPABB_TPNORMAL
IX_CUSTADDR_AREACODENORMAL
IX_CUSTADDR_ADDR_TPNORMAL
已选择7行。
3.删除索引:IX_CUSTADDR_CUSTID_TP
SQL>dropindexIX_CUSTADDR_CUSTID_TP;
索引已删除。
4.默认方式创建索引:
SQL>SETtimingon;
SQL>CREATEINDEXIX_CUSTADDR_CUSTID_TPONCUSTADDR(CUSTID);
索引已创建。
已用时间:00:00:48.37
单位:s
5.用nologging模式:
SQL>dropindexIX_CUSTADDR_CUSTID_TP;
索引已删除。
已用时间:00:00:00.09
SQL>CREATEINDEXIX_CUSTADDR_CUSTID_TPONCUSTADDR(CUSTID)NOLOGGING;
索引已创建。
已用时间:00:00:34.46
6.Nologging+parallel模式
SQL>dropindexIX_CUSTADDR_CUSTID_TP;
索引已删除。
已用时间:00:00:00.17
SQL>CREATEINDEXIX_CUSTADDR_CUSTID_TPONCUSTADDR(CUSTID)NOLOGGINGPARALLEL2;
索引已创建。
已用时间:00:00:52.56
SQL>dropindexIX_CUSTADDR_CUSTID_TP;
索引已删除。
已用时间:00:00:00.07
SQL>CREATEINDEXIX_CUSTADDR_CUSTID_TPONCUSTADDR(CUSTID)NOLOGGINGPARALLEL4;
索引已创建。
已用时间:00:00:53.44
看来在单CPU上,并行效果还不好.
7.Parallel模式
SQL>dropindexIX_CUSTADDR_CUSTID_TP;
索引已删除。
已用时间:00:00:00.02
SQL>CREATEINDEXIX_CUSTADDR_CUSTID_TPONCUSTADDR(CUSTID)PARALLEL2;
索引已创建。
已用时间:00:00:49.97
SQL>dropindexIX_CUSTADDR_CUSTID_TP;
索引已删除。
已用时间:00:00:00.02
SQL>CREATEINDEXIX_CUSTADDR_CUSTID_TPONCUSTADDR(CUSTID)PARALLEL4;
索引已创建。
已用时间:00:00:50.25
从上面的测试数据可以看出,700万的数据,建索引,也在1分钟以内。但是并行在单CPU上效果不明显,而且比光使用NOLOGGING还要慢,因为出现资源争用了,可能是CPU的争用,也可能是I/O的争用。
<!--EndFragment-->
------------------------------------------------------------------------------
Blog:http://blog.csdn.net/tianlesoftware
网上资源:http://tianlesoftware.download.csdn.net
相关视频:http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
DBA1群:62697716(满);DBA2群:62697977
<!--EndFragment-->