数据分布影响exp条件导出变慢的问题处理

oracle10.2.0.4

AIX5.3

夜间跑批的一个存储过程，逻辑大致为数据库内部先进行数据处理后插入空的导出表，数据处理全部完成后，按照每个表的字段A1进行where条件过滤导出。

在数据处理过程中，由于无意义的嵌套循环，导致对一张表的数据重复的进行I/O读取。数据量大后，处理变慢，优化需求就提上来了。

例如：

CURSOR cur_a1

is

select code from cura1;

LOOP

select A1 into x_a1 from testa where ta1=cur_a1;

begin

--判断sequence重置

select count(*) into v_count from sys.dba_objects where object_type='SEQUENCE' AND object_name = 'SEQUENCEname';

IF (v_icount > 0)

THEN

EXECUTE IMMEDIATE 'DROP SEQUENCE SEQUENCEname';

END IF;

EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQUENCEname INCREMENT BY 1 START WITH 1 MINVALUE 1 NOCYCLE NOCACHE NOORDER';

--执行数据插入

insert into table （a1,a2,a3） select a1,SEQUENCEname.NEXTVAL,a3 from A1 where a1=x_a1;

为何说是无意义循环，在我们的这套环境中，是根据表A中的字段A1中的非重复值数量进行循环，A1字段相等的时候，A2字段取递增序列SEQ作为A2字段的值，在每次循环初始，都会重置SEQ。唯一约束为A1+A2。而A2字段值在程序处理过程中毫无用处。因此计划取消循环，一次SEQ递增直到处理完A表，确保A表A2字段值不会重复。

根据这思路，完成了优化，大大提高了数据处理过程的耗时。

但是奇怪的是，就在优化变更做完当天，导出的过程却离奇的变慢了许多，以至于提高的时间被抹平。

经过分析，发现在优化前和优化后，表的数据分布情况发生了变化，原先是根据A1字段排序插入A表，而优化后一次生成数据插入A表，忽略了排序，因此导致A1字段索引聚族因子变差。从而影响[exptable=Aquery='whereA1like'1002%']语句的导出效率。

直接加上orderbya1发现报错：

insert into testa

(a1, a2, a3)

select a1, SEQUENCEname.NEXTVAL, a3 from A order by a1;

ORA - 02287 :sequence number not allowed here

ORACLE有如下限制：

RestrictionsonSequenceValuesYoucannotuseCURRVALandNEXTVALinthe

followingconstructs:

AsubqueryinaDELETE,SELECT,orUPDATEstatement

Aqueryofavieworofamaterializedview

ASELECTstatementwiththeDISTINCToperator

ASELECTstatementwithaGROUPBYclauseorORDERBYclause

ASELECTstatementthatiscombinedwithanotherSELECTstatementwiththeUNION,INTERSECT,orMINUSsetoperator

TheWHEREclauseofaSELECTstatement

TheDEFAULTvalueofacolumninaCREATETABLEorALTERTABLEstatement

TheconditionofaCHECKconstrain

调整语句结构如下：

insert into testa

(a1, a2, a3)

select a1, SEQUENCEname.NEXTVAL, a3

from (select a1, a3 from A order by a1);

顺利完成。

©著作权归作者所有：来自51CTO博客作者kylefree的原创作品，如需转载，请注明出处，否则将追究法律责任

oracle数据库sequenceORACLEDB