优化下面的sql

[lizi351120311]

SELECT CHL.CHANNEL_NAME "channelName",
APK.APK_CODE,
--拉新数量
(SELECT COUNT(DISTINCT CPT.PERSONAL_TERM_ID)
FROM COMM_TERM_UPDATE_PATH CTUP, COMM_PERSONAL_TERM CPT
WHERE CTUP.PERSONAL_TERM_ID = CPT.PERSONAL_TERM_ID
AND CTUP.UPDATE_NUM = 1
AND CTUP.APP_ID > 0
AND CTUP.APK_ID = APK.APK_ID),
--激活数量
(SELECT COUNT(DISTINCT CPT.PERSONAL_TERM_ID)
FROM COMM_TERM_UPDATE_PATH CTUP, COMM_PERSONAL_TERM CPT
WHERE CTUP.PERSONAL_TERM_ID = CPT.PERSONAL_TERM_ID
AND CTUP.UPDATE_NUM = 1
AND CTUP.APP_ID > 0
AND CTUP.APK_ID = APK.APK_ID
AND EXISTS
(SELECT 1
FROM USER_LOGIN_INFO ULI
WHERE ULI.PERSONAL_TERM_ID = CPT.PERSONAL_TERM_ID
AND ULI.LOGIN_NAME IS NOT NULL)),

--累计购买
(SELECT COUNT(DISTINCT CPT.PERSONAL_TERM_ID)
FROM COMM_TERM_UPDATE_PATH CTUP, COMM_PERSONAL_TERM CPT
WHERE CTUP.PERSONAL_TERM_ID = CPT.PERSONAL_TERM_ID
AND CTUP.UPDATE_NUM = 1
AND CTUP.APP_ID > 0
AND CTUP.APK_ID = APK.APK_ID
AND EXISTS
(SELECT 1
FROM SALE_EXCHANGE_ORDER SEO
WHERE SEO.PERSONAL_TERM_ID = CPT.PERSONAL_TERM_ID
AND SEO.SEO_STS = 'F')),

--累计复购
(SELECT COUNT(DISTINCT CPT.PERSONAL_TERM_ID)
FROM COMM_TERM_UPDATE_PATH CTUP, COMM_PERSONAL_TERM CPT
WHERE CTUP.PERSONAL_TERM_ID = CPT.PERSONAL_TERM_ID
AND CTUP.UPDATE_NUM = 1
AND CTUP.APP_ID > 0
AND CTUP.APK_ID = APK.APK_ID
AND EXISTS
(SELECT 1
FROM (SELECT SEO.PERSONAL_TERM_ID,
COUNT(SEO.PERSONAL_TERM_ID) BUY_COUNT
FROM SALE_EXCHANGE_ORDER SEO
WHERE SEO.SEO_STS = 'F'
GROUP BY SEO.PERSONAL_TERM_ID) T1
WHERE T1.BUY_COUNT >= 2
AND T1.PERSONAL_TERM_ID = CPT.PERSONAL_TERM_ID)),

--累计充值
(SELECT COUNT(DISTINCT CPT.PERSONAL_TERM_ID)
FROM COMM_TERM_UPDATE_PATH CTUP, COMM_PERSONAL_TERM CPT
WHERE CTUP.PERSONAL_TERM_ID = CPT.PERSONAL_TERM_ID
AND CTUP.UPDATE_NUM = 1
AND CTUP.APP_ID > 0
AND CTUP.APK_ID = APK.APK_ID
AND EXISTS
(SELECT 1
FROM ACCT_RECHARGE_DETAIL ARD
WHERE ARD.PERSONAL_TERM_ID = CPT.PERSONAL_TERM_ID
AND ARD.EXPEND_STS = 'C')),
--活跃终端数
(SELECT COUNT(DISTINCT CPT.PERSONAL_TERM_ID)
FROM COMM_TERM_UPDATE_PATH CTUP, COMM_PERSONAL_TERM CPT
WHERE CTUP.PERSONAL_TERM_ID = CPT.PERSONAL_TERM_ID
AND CTUP.UPDATE_NUM = 1
AND CTUP.APP_ID > 0
AND CTUP.APK_ID = APK.APK_ID
AND EXISTS
(SELECT 1
FROM USER_BEHAVIOR_DATA UBD, USER_BEHAVIOR_BASIC_DATA UBBD
WHERE UBD.BASIC_DATA_ID = UBBD.BASIC_DATA_ID
AND UBD.PERSONAL_TERM_ID = CPT.PERSONAL_TERM_ID
AND UBBD.BASIC_DATA_ID = 2))
FROM COMM_CHANNEL CHL, APKCODE_INFO APK
WHERE CHL.CHANNEL_ID = APK.CHANNEL_ID
AND CHL.STS = 'A'
AND APK.STS = 'A'
AND APK.COMPONENT_TYPE = 'A'
ORDER BY CHL.CHANNEL_NAME


这几个子查询都是在同一条sql的基础上加上别的条件查出来的。请问有别的实现方式么。现在执行一次都要用到20秒，表SALE_EXCHANGE_ORDER ，ACCT_RECHARGE_DETAIL 都在PERSONAL_TERM_ID 列上建索引了，但是还是全表扫面，请问这是怎么回事。


请高手指点，万分感激啊。

[何来lo]

select中的表有很多都重复引用了。
你看下是否可以将其写在where条件中筛选，实在觉得麻烦的话，用临时表或者with存储多次关联查询的表。相信会比直接反复查询快上许多。

[a494910091]

一团挺乱的sql... 好歹也 上下 表结构和测试数据呀

[guo624587253]

我是农民，顶下.