SQL语句如下:
SELECT COUNT(*) COUNT,
TO_CHAR(SUM(T.CHARGE) / 100, 'FM999999999990.00') CHARGE
FROM (SELECT B.SERV_ID, B.CHARGE
FROM DW.BILL_ACCT_ITEM_TOTAL_591 B, DW.PROD_INFO_ZQ P
WHERE B.SERV_ID = P.PROD_ID
AND P.AREA_CODE = '591'
AND P.STATE = 'A'
AND P.PLACE_NODE4 = '968749702'
AND B.BILLING_CYCLE_MONTH = '201201') T;--------------------------------------------------------------------------
以上语句DW.BILL_ACCT_ITEM_TOTAL_591表示账单表按BILLING_CYCLE_MONTH月分期,数据量是千万或亿 ,DW.PROD_INFO_ZQ表示档案表按AREA_CODE地方分区,数据量也是千万。 以上表B.SERV_ID,P.PROD_ID,P.PLACE_NODE4 都有索引。
以上语句统计需要50秒左右,求高手优化。
SELECT COUNT(*) COUNT,
TO_CHAR(SUM(T.CHARGE) / 100, 'FM999999999990.00') CHARGE
FROM (SELECT B.SERV_ID, B.CHARGE
FROM DW.BILL_ACCT_ITEM_TOTAL_591 B, DW.PROD_INFO_ZQ P
WHERE B.SERV_ID = P.PROD_ID
AND P.AREA_CODE = '591'
AND P.STATE = 'A'
AND P.PLACE_NODE4 = '968749702'
AND B.BILLING_CYCLE_MONTH = '201201') T;--------------------------------------------------------------------------
以上语句DW.BILL_ACCT_ITEM_TOTAL_591表示账单表按BILLING_CYCLE_MONTH月分期,数据量是千万或亿 ,DW.PROD_INFO_ZQ表示档案表按AREA_CODE地方分区,数据量也是千万。 以上表B.SERV_ID,P.PROD_ID,P.PLACE_NODE4 都有索引。
以上语句统计需要50秒左右,求高手优化。
SELECT COUNT(T.SERV_ID) COUNT,
TO_CHAR(SUM(T.CHARGE) / 100, 'FM999999999990.00') CHARGE
FROM (SELECT B.SERV_ID, B.CHARGE
FROM (
SELECT B.SERV_ID, B.CHARGE
FROM DW.BILL_ACCT_ITEM_TOTAL_591 B
WHERE AND B.BILLING_CYCLE_MONTH = '201201'
)
INNER JOIN (
SELECT P.PROD_ID WHERE DW.PROD_INFO_ZQ P
WHERE AND P.AREA_CODE = '591'
AND P.STATE = 'A'
AND P.PLACE_NODE4 = '968749702'
) P ON B.SERV_ID = P.PROD_ID
) T;
TO_CHAR(SUM(T.CHARGE) / 100, 'FM999999999990.00') CHARGE
FROM (SELECT B.SERV_ID, B.CHARGE
FROM (
SELECT B.SERV_ID, B.CHARGE
FROM DW.BILL_ACCT_ITEM_TOTAL_591 B
WHERE AND B.BILLING_CYCLE_MONTH = '201201'
)
INNER JOIN (
SELECT P.PROD_ID FROM DW.PROD_INFO_ZQ P
WHERE AND P.AREA_CODE = '591'
AND P.STATE = 'A'
AND P.PLACE_NODE4 = '968749702'
) P ON B.SERV_ID = P.PROD_ID
) T;
再说写成这样就可以吧, 还需要子查询吗?SELECT COUNT(*) COUNT,
TO_CHAR(SUM(B.CHARGE
) / 100, 'FM999999999990.00') CHARG FROM DW.BILL_ACCT_ITEM_TOTAL_591 B, DW.PROD_INFO_ZQ P
WHERE B.SERV_ID = P.PROD_ID
AND P.AREA_CODE = '591'
AND P.STATE = 'A'
AND P.PLACE_NODE4 = '968749702'
AND B.BILLING_CYCLE_MONTH = '201201'