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是索引的第一列,请问上面这个查询为什么不走索引???
SELECT STATEMENT, GOAL = ALL_ROWS 18 1 50
HASH GROUP BY 18 1 50
FILTER
PX COORDINATOR
PX SEND QC (RANDOM) SYS :TQ10000 2 1 50
PX BLOCK ITERATOR 2 1 50
TABLE ACCESS FULL CLAS FACT_WAPNEW_CDR2 2 1 50
FILTER
COUNT STOPKEY
VIEW CLAS 15 4344 56472
SORT ORDER BY STOPKEY 15 4344 60816
SORT GROUP BY 15 4344 60816
TABLE ACCESS FULL CLAS TPC_BIG_TRAF_MSISDN 12 23410 327740