CREATE TABLE test NOLOGGING AS
SELECT ROWNUM AS id, a.*
FROM dba_objects a
WHERE 1=2; -- 僅複製結構,不複製資料
NOLOGGING 減少 Redo 生成,加速後續大量載入。
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 建立測試大表的標準範式,可在分鐘級產生百萬級測試數據,支援效能基準測試、分頁優化、分區裁剪等驗證場景。