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 列上建索引了,但是还是全表扫面,请问这是怎么回事。
请高手指点,万分感激啊。
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 列上建索引了,但是还是全表扫面,请问这是怎么回事。
请高手指点,万分感激啊。
解决方案 »
- 请教 进行冷备时,是否需要备份临时表空间文件?
- 看了半天的贴子,还是没解决--ORACLE10g 实例问题
- 请各位使用过Oracle9i全文检索功能的大虾,帮兄弟一个忙
- 我有一开发问题!请赐教!!!!
- 关于oracle9i的apache的启动问题,在线等
- 求一SQL语句
- 创建的函数状态是invalid,怎么使其变为valid的状态
- 为什么启动不了Enterprise Manager→Configuration Assistant?
- 想问一下,(+)是什么意思??
- 如何确定oracle 数据库是大、中、小数据库,根据数据库的数据量吗?比如说200GB以上的是大型数据库?和物理内存有关系吗?
- 用PL/SQL导出数据的问题
- PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
你看下是否可以将其写在where条件中筛选,实在觉得麻烦的话,用临时表或者with存储多次关联查询的表。相信会比直接反复查询快上许多。