善用 Oracle 外部表分析網站存取日誌實戰

外部表：日誌分析的利器

Oracle 外部表允許直接以 SQL 查詢作業系統上的文字檔案，無須先匯入資料庫。對於 Apache 等 Web 伺服器的存取日誌，這項功能極為方便，甚至比專用分析工具更靈活（唯讀取外部檔案，無法寫入）。

實作步驟：建立目錄物件與授權

-- OS 層建立日誌目錄
$ mkdir -p /opt/oracle/elog
$ cp access_log.20061016 /opt/oracle/elog/

-- 資料庫層建立 Directory Object 並授權
SQL> CREATE OR REPLACE DIRECTORY elog AS '/opt/oracle/elog';
SQL> GRANT READ, WRITE ON DIRECTORY elog TO eygle;

定義外部表結構與切分規則

日誌欄位以雙引號分隔，使用 FIELDS TERMINATED BY '\"' 切分：

CREATE TABLE eygle_access_log_20061016 (
    ip_address_date VARCHAR2(100),
    acc_file        VARCHAR2(400),
    acc_cdsz        VARCHAR2(20),
    acc_url         VARCHAR2(400),
    left_blank      VARCHAR2(10),
    acc_agent       VARCHAR2(400)
)
ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ELOG
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        NOBADFILE NODISCARDFILE NOLOGFILE
        FIELDS TERMINATED BY '\"'
        MISSING FIELD VALUES ARE NULL
    )
    LOCATION ('eygle_access_log.20061016')
)
REJECT LIMIT UNLIMITED;

實用分析查詢範例

擷取 IP 位址

SELECT SUBSTR(ip_address_date, 1, INSTR(ip_address_date, ' ')) AS ip_address
FROM   eygle_access_log_20061016
WHERE  ROWNUM < 11;

統計當日獨立訪客數

SELECT COUNT(DISTINCT SUBSTR(ip_address_date, 1, INSTR(ip_address_date, ' '))) AS uip
FROM   eygle_access_log_20061016;
-- 耗時約 7 秒，回傳 7,534 個唯一 IP

熱門頁面 Top 20

SELECT REPLACE(REPLACE(acc_file, 'GET ', 'www.eygle.com'), 'HTTP/1.1') AS accfile, ct
FROM (
    SELECT acc_file, COUNT(*) ct
    FROM   eygle_access_log_20061016
    WHERE  acc_file LIKE '%htm%'
    GROUP BY acc_file
    ORDER BY ct DESC
)
WHERE ROWNUM < 21;

效能比較：外部表 vs 一般表

將同樣資料載入一般表後，同樣查詢僅需 2 秒。外部表因需即時剖析文字檔，效能約慢 3 倍。若頻繁分析大量日誌，建議先載入暫存表或分區表再查詢。