萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 快速建立測試大表:DBMS_OBJECTS 循環插入 500 萬筆範例

一、建立目標表結構

CREATE TABLE test NOLOGGING AS
SELECT ROWNUM AS id, a.*
FROM dba_objects a
WHERE 1=2;  -- 僅複製結構,不複製資料

NOLOGGING 減少 Redo 生成,加速後續大量載入。

二、PL/SQL 迴圈插入 500 萬筆 (含 APPEND 提示)

SET SERVEROUTPUT ON
DECLARE
  l_cnt   NUMBER := 0;
  l_rows  NUMBER := 5000000;  -- 目標筆數
BEGIN
  -- 首批直載
  INSERT /*+ APPEND */ INTO test
  SELECT ROWNUM, owner, object_name, subobject_name,
         object_id, data_object_id, object_type,
         created, last_ddl_time, timestamp,
         status, temporary, generated, secondary
  FROM dba_objects;
  l_cnt := SQL%ROWCOUNT;
  COMMIT;

  -- 迴圈補足至目標筆數
  WHILE (l_cnt < l_rows) LOOP
    INSERT /*+ APPEND */ INTO test
    SELECT ROWNUM + l_cnt,
           owner, object_name, subobject_name,
           object_id, data_object_id, object_type,
           created, last_ddl_time, timestamp,
           status, temporary, generated, secondary
    FROM dba_objects
    WHERE ROWNUM <= l_rows - l_cnt;
    l_cnt := l_cnt + SQL%ROWCOUNT;
    COMMIT;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Total inserted: ' || l_cnt);
END;
/
```

三、關鍵技術點

技巧作用
NOLOGGING 建表初始載入不產生 Redo (需資料庫啟用 FORCE LOGGING=NO)
/*+ APPEND */Direct-path INSERT,繞過緩衝區、高水位線直接寫新區塊,速度最快
ROWNUM + l_cnt生成連續不重複主鍵
WHILE 迴圈分批避免單次巨大事務撐爆 Undo/Redo,每批提交釋放資源
COMMIT 每批防止長事務導致 Snapshot too old / Undo 保留壓力

四、進階:並行載入 (PDML) 更快

ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(test, 8) APPEND */ INTO test
SELECT ... FROM dba_objects ...;

配合 PARALLEL 提示,利用多 CPU 核心並行載入,大表可再提升 3-5 倍速度。

五、驗證與統計

SELECT COUNT(*) FROM test;           -- 確認筆數
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TEST');  -- 收集統計供優化器使用
SELECT table_name, num_rows, blocks, avg_row_len FROM user_tables WHERE table_name='TEST';

六、其他大表生成來源

  • ALL_OBJECTS / DBA_OBJECTS:約 7-9 萬行,適合循環擴充。
  • CONNECT BY LEVEL <= N:純生成數字序列,SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 1000000。
  • DBMS_RANDOM:生成隨機字串/數值/日期。
  • 外部表讀取 CSV/Parquet 測試資料集。

此模式「結構複製 → 首批直載 → 迴圈補足 → 並行優化」是 Oracle 建立測試大表的標準範式,可在分鐘級產生百萬級測試數據,支援效能基準測試、分頁優化、分區裁剪等驗證場景。

copyright © 萬盛學電腦網 all rights reserved