萬盛學電腦網

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

Oracle 測試用大量資料表 BIG_TABLE 建立腳本 (10g/11g)

DBA 常需建立大量測試資料進行效能測試、分頁查詢驗證等。本文整理 Tom Kyte 經典 big_table 腳本,分別提供 Oracle 10g 與 11g 版本,利用 all_objects 為種子資料,透過迴圈快速產生指定筆數資料。

共同邏輯

  1. 建立空表 big_table(含 id 主鍵 + all_objects 全欄位)。
  2. 設定 NOLOGGING 減少 Redo 產生。
  3. 首次從 all_objects 插入種子資料。
  4. 以 INSERT /*+ APPEND */ + WHILE 迴圈自我複製,直到達到目標筆數。
  5. 建立主鍵 big_table_pk 並收集統計資訊。

Oracle 10g 版本

-- 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;

Oracle 11g 版本差異

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 萬筆

注意事項

  • 表空間需有足夠空間(含暫存表空間供排序)。
  • NOLOGGING 模式下備份策略需配合(不可靠此表進行時間點復原)。
  • 若需更真實分佈,可搭配 DBMS_RANDOM 打散欄位值。
copyright © 萬盛學電腦網 all rights reserved