提高Oracle性能--经验实谈

qwiehcoqfy

　　本人初出茅庐，从事对日软件开发已一年时间了，之前一直在JAVAEYE上面看大家的博客，不敢随意发表任何言论。目前我正好维护一个J2SE的项目，刚好碰到一个障害要求提高Oracle的执行效率问题。本人觉得很有意思，供大家分享！希望大家能够给点鼓励……
　　
　　原来的SQL文需要执行15万条左右的数据记录，执行一个查询条件至少要15分钟以上。原SQL文是：

SELECT
A.SHHN_CD,
A.KKR_KB,
A.DN_KB,
A.DN_GYBN_KB,
SUM(A.CS_SRY),
SUM(A.BR_SRY),
SUM(A.BR_KNZN_SRY)
FROM TRNSCTN A,
SHHN_MST
WHERE A.HNSHTN_CD='001'
AND ((A.ZK_SK_CD='1'
AND A.ZK_KSHN_DY=TO_DATE('99991231','YYYYMMDD')
AND DN_DY<TO_DATE('20080601',
'YYYYMMDD'))
OR (A.ZK_SK_CD='1'
AND A.DN_DY BETWEEN TO_DATE('20080601','YYYYMMDD')
AND TO_DATE('20080623','YYYYMMDD')))
AND DN_GYBN_KB IN('1',
'2',
'5',
'7',
'C',
'D',
'G',
'H')
AND DN_KB IN('1',
'2',
'3',
'4')
AND GY_NB<>'0'
AND (A.HNSHTN_CD=SHHN_MST.HNSHTN_CD
AND A.SHHN_CD=SHHN_MST.SHHN_CD
AND SHHN_MST.BMN_CD BETWEEN '000'
AND '999' )
GROUP BY A.SHHN_CD,
A.KKR_KB,
A.DN_KB,
A.DN_GYBN_KB
ORDER BY A.KKR_KB,
A.DN_KB,
A.DN_GYBN_KB

　　
　　当我把这段SQL文拷贝到SQLPLUS中，按F5键执行时，我才发现这段SQL文没有用到索引。具体索引如下：

写道

Owner   Name             Type    Columns
KMJ       TRNSCTNFK1   Normal HNSHTN_CD, DN_NB
KMJ       TRNSCTNFK10 Normal HNSHTN_CD, INJZM_KB
KMJ       TRNSCTNFK11 Normal HNSHTN_CD, STI_INJZM_KB
KMJ       TRNSCTNFK12 Normal HNSHTN_CD, GY_NB, AT_DN_NB
KMJ      TRNSCTNFK13  Normal HNSHTN_CD, SHHN_CD, ZK_KSHN_DY, DN_DY
KMJ      TRNSCTNFK14  Normal HNSHTN_CD, TRHKSK_CD, TRHKSK_KB, DN_KB, GY_NB, STI_INJZM_KB
KMJ      TRNSCTNFK15  Normal HNSHTN_CD, TRHKSK_CD, DN_KB, SKY_DY, CHH_DY
KMJ      TRNSCTNFK2    Normal HNSHTN_CD, TRHKSK_CD, DN_DY
KMJ      TRNSCTNFK3    Normal HNSHTN_CD, SHHN_CD, DN_DY
KMJ      TRNSCTNFK4    Normal HNSHTN_CD, ZK_KSHN_DY
KMJ      TRNSCTNFK5    Normal HNSHTN_CD, SYS_SHR_DY, DN_KB
KMJ      TRNSCTNFK6    Normal HNSHTN_CD, SKY_DY
KMJ      TRNSCTNFK7    Normal HNSHTN_CD, TGT_DY, DN_KB
KMJ      TRNSCTNFK8    Normal HNSHTN_CD, DN_DY, DN_KB
KMJ      TRNSCTNFK9    Normal HNSHTN_CD, TRHKSK_CD, SKY_DY, CHH_DY
KMJ      TRNSCTNPKI    Unique HNSHTN_CD, DN_DY, DN_KB, DN_NB, DN_D, KKR_KB, GY_NB, NY_TN_NB

　　我通过一天的测试，发现将原SQL文进行如下优化之后，效率最高，执行时间提高到161.875s。

SELECT
/*+ index(SHHN_MST TRNSCTNFK4)*/ /*+ index(SHHN_MST TRNSCTNFK8)*/
A.SHHN_CD,
A.KKR_KB,
A.DN_KB,
A.DN_GYBN_KB,
SUM(A.CS_SRY),
SUM(A.BR_SRY),
SUM(A.BR_KNZN_SRY)
FROM TRNSCTN A,
SHHN_MST
WHERE A.HNSHTN_CD='001'
AND ((A.ZK_SK_CD='1'
AND A.ZK_KSHN_DY=TO_DATE('99991231','YYYYMMDD')
AND DN_DY<TO_DATE('20080601',
'YYYYMMDD'))
OR (A.ZK_SK_CD='1'
AND A.DN_DY BETWEEN TO_DATE('20080601','YYYYMMDD')
AND TO_DATE('20080623','YYYYMMDD')))
AND DN_GYBN_KB IN('1',
'2',
'5',
'7',
'C',
'D',
'G',
'H')
AND DN_KB IN('1',
'2',
'3',
'4')
AND GY_NB<>'0'
AND (A.HNSHTN_CD=SHHN_MST.HNSHTN_CD
AND A.SHHN_CD=SHHN_MST.SHHN_CD
AND SHHN_MST.BMN_CD BETWEEN '000'
AND '999' )
GROUP BY A.SHHN_CD,
A.KKR_KB,
A.DN_KB,
A.DN_GYBN_KB
ORDER BY A.KKR_KB,
A.DN_KB,
A.DN_GYBN_KB

　　
　　日方对这样的效率已基本满意，但是这仅仅时SQL文执行的时间，要是加上JAVA执行的时间的话也得要5分钟。作为天生追求完美的程序员来说似乎有点不甘休。于是我将上述SQL采取了一个最原始的优化，性能倍增，执行时间只需要8.912s。

SELECT  
A.SHHN_CD,
A.KKR_KB,
A.DN_KB,
A.DN_GYBN_KB,
SUM(A.CS_SRY),
SUM(A.BR_SRY),
SUM(A.BR_KNZN_SRY)
FROM
(
SELECT
*
FROM TRNSCTN
WHERE ZK_SK_CD='1'
AND ZK_KSHN_DY=TO_DATE('99991231','YYYYMMDD')
AND DN_DY<TO_DATE('20080601',
'YYYYMMDD')
UNION ALL
SELECT
*
FROM TRNSCTN
WHERE ZK_SK_CD='1'
AND DN_DY BETWEEN TO_DATE('20080601','YYYYMMDD')
AND TO_DATE('20080623','YYYYMMDD')
) A,
SHHN_MST
WHERE A.HNSHTN_CD='001'
AND DN_GYBN_KB IN('1',
'2',
'5',
'7',
'C',
'D',
'G',
'H')
AND DN_KB IN('1',
'2',
'3',
'4')
AND GY_NB<>'0'
AND (A.HNSHTN_CD=SHHN_MST.HNSHTN_CD
AND A.SHHN_CD=SHHN_MST.SHHN_CD
AND SHHN_MST.BMN_CD BETWEEN '000'
AND '999' )
GROUP BY A.SHHN_CD,
A.KKR_KB,
A.DN_KB,
A.DN_GYBN_KB
ORDER BY A.KKR_KB,
A.DN_KB,
A.DN_GYBN_KB
　　其实我对ORACLE并没有开发经验，只是出于个人爱好，研究点皮毛。为什么这样的执行效率最高，我并不是特别清楚，希望大家给点建议！！！！！！！
　　
　　本人已将次文的详细叙述过程放到http://rdqwhr.iyunv.com/admin/blogs/208382，敬请查阅！！！