大表變小表，小表再串連————記一次PL/SQL最佳化過程

     公司的業務系統中存在一個大的日誌表，表大約是這樣：
create table log
(
    logtime date,  -- PK
    username varchar2(20)
);
    現有需求如下：統計日誌表中，兩小時內使用過系統的使用者在三天內的日誌數。
    最初編寫的查詢如下：WITH
    result1 AS
    (
        SELECT DISTINCT username
        FROM log
        WHERE logtime>=sysdate - 1/24*2
    )
SELECT log.username, count(1) as times
FROM log INNER JOIN result1 ON log.username=result1.username
WHERE logtime>=sysdate - 3
GROUP BY log.username;

    後來發現，log表記錄達到300萬後，查詢非常慢。測試後發現是因為log表和臨時視圖result1串連的時候，採用NESTED LOOPS，於是增加提示，採用HASH串連：WITH
    result1 AS
    (
        SELECT DISTINCT username
        FROM log
        WHERE logtime>=sysdate - 1/24*2
    )
SELECT /**//*+USE_HASH(log)*/ log.username, count(1) as times
FROM log INNER JOIN result1 ON log.username=result1.username
WHERE logtime>=sysdate - 3
GROUP BY log.username;

     效率果然提高了很多，但是還是不令人滿意。

    測試的過程中發現，如果不先選取兩小時內的使用者，而直接選取三天內的所有使用者來統計，效率非常高。看來，效能的瓶頸還是在表串連上。在已經選用HASH串連的情況下，只有想辦法減少串連的記錄數了。於是嘗試寫了如下查詢：WITH
    result1 AS
    (
        SELECT DISTINCT username
        FROM log
        WHERE logtime>=sysdate - 1/24*2
    ),
    result2 AS
    (
        SELECT username, count(1) AS times
        FROM log
        WHERE logtime>=sysdate - 3
        GROUP BY username
    )
SELECT result2.username, result1.times
FROM result1 INNER JOIN result2 ON result1.username=result2.username;

    效能高了好多好多！！！
    哦，還忘記了點東西：WITH
    result1 AS
    (
        SELECT DISTINCT username
        FROM log
        WHERE logtime>=sysdate - 1/24*2
    ),
    result2 AS
    (
        SELECT username, count(1) AS times
        FROM log
        WHERE logtime>=sysdate - 3
        GROUP BY username
    )
SELECT /**//*+USE_HASH(result1)*/ result2.username, result1.times
FROM result1 INNER JOIN result2 ON result1.username=result2.username;

     測試一下，效能又高了一點點。

      3天內的日誌數是300萬，第一個查詢執行了1小時以上，最佳化後的最後一個查詢只花了112秒。

    總結下來，這個查詢最佳化的思路為：大表變小表，小表再串連。
    希望有高手能夠提出更好的想法，謝謝！