SQL:
SELECT /*+ PARALLEL(FACT_WAPNEW_CDR2 8) */
201108060000, SP_DOMAIN, SERVICE_TYPE, CODE_ID, COUNT(1)
FROM FACT_WAPNEW_CDR2 SUBPARTITION(PART_20110806_1) A
WHERE EXISTS (SELECT MSISDN
FROM (SELECT MSISDN, SUM(TRAFFIC)
FROM TPC_BIG_TRAF_MSISDN
GROUP BY MSISDN
ORDER BY SUM(TRAFFIC) DESC) B
WHERE ROWNUM <= 100
AND A.MSISDN = B.MSISDN)
GROUP BY 201108060000, SP_DOMAIN, SERVICE_TYPE, CODE_ID;执行计划:
SELECT STATEMENT, GOAL = ALL_ROWS 15 1 50
HASH GROUP BY 15 1 50
FILTER
PARTITION COMBINED ITERATOR 2 1 50
TABLE ACCESS FULL CLAS FACT_WAPNEW_CDR2 2 1 50
COUNT STOPKEY
VIEW CLAS 12 5 65
SORT GROUP BY STOPKEY 12 5 70
TABLE ACCESS FULL CLAS TPC_BIG_TRAF_MSISDN 12 5 70
TPC_BIG_TRAF_MSISDN这个表只有几W数据,没有索引。
FACT_WAPNEW_CDR2 SUBPARTITION(PART_20110806_1)一个子分区的数据有5到7千万,
建了组合索引,MSISDN是索引的第一列,请问上面这个查询为什么不走索引???
SELECT /*+ PARALLEL(FACT_WAPNEW_CDR2 8) */
201108060000, SP_DOMAIN, SERVICE_TYPE, CODE_ID, COUNT(1)
FROM FACT_WAPNEW_CDR2 SUBPARTITION(PART_20110806_1) A
WHERE EXISTS (SELECT MSISDN
FROM (SELECT MSISDN, SUM(TRAFFIC)
FROM TPC_BIG_TRAF_MSISDN
GROUP BY MSISDN
ORDER BY SUM(TRAFFIC) DESC) B
WHERE ROWNUM <= 100
AND A.MSISDN = B.MSISDN)
GROUP BY 201108060000, SP_DOMAIN, SERVICE_TYPE, CODE_ID;执行计划:
SELECT STATEMENT, GOAL = ALL_ROWS 15 1 50
HASH GROUP BY 15 1 50
FILTER
PARTITION COMBINED ITERATOR 2 1 50
TABLE ACCESS FULL CLAS FACT_WAPNEW_CDR2 2 1 50
COUNT STOPKEY
VIEW CLAS 12 5 65
SORT GROUP BY STOPKEY 12 5 70
TABLE ACCESS FULL CLAS TPC_BIG_TRAF_MSISDN 12 5 70
TPC_BIG_TRAF_MSISDN这个表只有几W数据,没有索引。
FACT_WAPNEW_CDR2 SUBPARTITION(PART_20110806_1)一个子分区的数据有5到7千万,
建了组合索引,MSISDN是索引的第一列,请问上面这个查询为什么不走索引???
201108060000, SP_DOMAIN, SERVICE_TYPE, CODE_ID, COUNT(1)
FROM FACT_WAPNEW_CDR2 SUBPARTITION(PART_20110806_1) A,
TPC_BIG_TRAF_MSISDN b where A.MSISDN = B.MSISDN
GROUP BY 201108060000, A.SP_DOMAIN, A.SERVICE_TYPE, A.CODE_ID;
201108060000, SP_DOMAIN, SERVICE_TYPE, CODE_ID, COUNT(1)
FROM FACT_WAPNEW_CDR2 SUBPARTITION(PART_20110806_1) A,
TPC_BIG_TRAF_MSISDN b where A.MSISDN = B.MSISDN
GROUP BY 201108060000, A.SP_DOMAIN, A.SERVICE_TYPE, A.CODE_ID;
201108060000, SP_DOMAIN, SERVICE_TYPE, CODE_ID, COUNT(1)
FROM FACT_WAPNEW_CDR2 SUBPARTITION(PART_20110806_1) A,
(select distinct MSISDN from TPC_BIG_TRAF_MSISDN) b where A.MSISDN = B.MSISDN
GROUP BY 201108060000, A.SP_DOMAIN, A.SERVICE_TYPE, A.CODE_ID;
这样更合理些,你是在通信行业吧,msisdn 应该是手机号吧