DBA 常需建立大量測試資料進行效能測試、分頁查詢驗證等。本文整理 Tom Kyte 經典 big_table 腳本,分別提供 Oracle 10g 與 11g 版本,利用 all_objects 為種子資料,透過迴圈快速產生指定筆數資料。
big_table(含 id 主鍵 + all_objects 全欄位)。NOLOGGING 減少 Redo 產生。all_objects 插入種子資料。INSERT /*+ APPEND */ + WHILE 迴圈自我複製,直到達到目標筆數。big_table_pk 並收集統計資訊。-- cr_big_tb_10g.sql
CREATE TABLE big_table AS SELECT ROWNUM id, a.* FROM all_objects a WHERE 1=0;
ALTER TABLE big_table NOLOGGING;
DECLARE
l_cnt NUMBER;
l_rows NUMBER := &1; -- 執行時輸入目標筆數
BEGIN
INSERT /*+ APPEND */ INTO big_table SELECT rownum, a.* FROM all_objects a;
l_cnt := SQL%ROWCOUNT;
COMMIT;
WHILE (l_cnt < l_rows) LOOP
INSERT /*+ APPEND */ INTO big_table
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 big_table WHERE rownum <= l_rows - l_cnt;
l_cnt := l_cnt + SQL%ROWCOUNT;
COMMIT;
END LOOP;
END;
/
ALTER TABLE big_table ADD CONSTRAINT big_table_pk PRIMARY KEY (id);
BEGIN
dbms_stats.gather_table_stats(ownname => USER, tabname => 'BIG_TABLE',
method_opt => 'for all indexed columns', cascade => TRUE);
END;
/
SELECT COUNT(*) FROM big_table;
11g all_objects 新增 namespace、edition_name 兩欄,INSERT 清單需同步增加:
INSERT /*+ APPEND */ INTO big_table
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, namespace, edition_name
FROM big_table WHERE rownum <= l_rows - l_cnt;
SQL> @cr_big_tb_10g.sql Enter value for 1: 1000000 -- 產生 100 萬筆
DBMS_RANDOM 打散欄位值。