Oracle 隨機抽樣完全指南：DBMS_RANDOM 實戰與效能優化

快速隨機查詢：ORDER BY DBMS_RANDOM.VALUE

最簡單的隨機抽樣寫法，適合中小型資料表：

SELECT * FROM (
  SELECT * FROM busi.t_ar_userinfo 
  ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM < 500;  -- 隨機取 499 筆

原理：為每列生成隨機數排序，取前 N 筆。缺點：全表掃描 + 排序，大表效能差。

DBMS_RANDOM 核心函數參考

大量資料高效隨機抽樣：輔助表法

適用於百萬筆以上資料表，避免全表排序：

步驟詳解

-- 1. 建立流水號序列
CREATE SEQUENCE tmp_id INCREMENT BY 1 START WITH 1 MAXVALUE 9999999 NOCYCLE NOCACHE;

-- 2. 篩選符合條件資料至暫存表，帶上流水號
CREATE TABLE tmp_1 AS 
SELECT tmp_id.NEXTVAL AS id, email, mobileno 
FROM source_table 
WHERE 條件;

-- 3. 取得最大 ID
SELECT MAX(id) FROM tmp_1;  -- 假設 5000

-- 4. 設定隨機種子（可選，預設以系統時間為種子）
EXECUTE DBMS_RANDOM.SEED(12345678);
-- 或 EXECUTE DBMS_RANDOM.SEED(TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS'));

-- 5. 生成隨機 ID 暫存表（取 200 筆）
CREATE TABLE tmp_2 AS 
SELECT TRUNC(DBMS_RANDOM.VALUE(1, 5000)) AS id 
FROM tmp_1 
WHERE ROWNUM < 201;
-- 注意：TRUNC 去除小數，若 tmp_1 過大可改用較小表 tmp_3 生成 tmp_2

-- 6. 關聯取得最終樣本
SELECT t1.mobileno, t1.email 
FROM tmp_1 t1, tmp_2 t2 
WHERE t1.id = t2.id;

-- 7. 匯出文字檔（可選）
SET PAGESIZE 300;
SPOOL /tmp/200.txt;
SELECT t1.mobileno, t1.email 
FROM tmp_1 t1, tmp_2 t2 
WHERE t1.id = t2.id 
ORDER BY t1.mobileno;
SPOOL OFF;

-- 8. 清理暫存物件
DROP TABLE tmp_1; DROP TABLE tmp_2; DROP SEQUENCE tmp_id;

效能優化關鍵點

• 索引：tmp_1.id 必須建立索引，關聯查詢才會快
• 避免重複：上述方法可能產生重複 ID，嚴格不重複需用 SAMPLE 子句或 ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE)
• Oracle 10g+ 採樣子句：SELECT * FROM table SAMPLE (10) 區塊層級採樣；SAMPLE BLOCK (10) 區塊採樣
• 分區表：可針對特定分區抽樣

進階：無重複隨機抽樣

-- 使用 ROW_NUMBER + DBMS_RANDOM 確保不重複
SELECT * FROM (
  SELECT t.*, ROW_NUMBER() OVER (ORDER BY DBMS_RANDOM.VALUE) AS rn
  FROM large_table t
  WHERE conditions
)
WHERE rn <= 200;

廢棄警告

Oracle 文件標註 DBMS_RANDOM 已棄用，建議改用 DBMS_CRYPTO 套件產生加密級隨機數。但一般業務抽樣仍廣泛使用 DBMS_RANDOM。