Oracle 測試專用大表 BIG_TABLE 建立腳本：10g/11g 版本差異與統計資訊收集

DBA 測試效能、備份恢復、分區表操作常需大量測試資料。基於 Tom Kyte 經典 BIG_TABLE 腳本，本文提供 Oracle 10g 與 11g 兩版本建立腳本 (含 EDITION_NAME、NAMESPACE 欄位差異)、NOLOGGING 加速載入、主鍵建立、統計資訊收集完整流程。

核心設計邏輯

1. 以 ALL_OBJECTS 為種子資料 (約 5-9 萬行)
2. INSERT /*+ APPEND */ 直寫資料區塊，產生最少 REDO
3. 迴圈倍增至目標行數 (指定替換變數 &1)
4. 建立主鍵 BIG_TABLE_PK 於 ID 欄位
5. DBMS_STATS.GATHER_TABLE_STATS 收集完整統計資訊

Oracle 10g 版本腳本 (cr_big_tb_10g.sql)

--==============================================
-- Create a test table for Oracle 10g
-- File : cr_big_tb_10g.sql
--==============================================
PROMPT Create a big table from all_objects
CREATE TABLE big_table AS SELECT ROWNUM id, a.* FROM all_objects a WHERE 1=0;

PROMPT Modify table to nologging mode
ALTER TABLE big_table NOLOGGING;

PROMPT Please input rows number to fill into big_table
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;
/

PROMPT Add primary key for big table
ALTER TABLE big_table ADD CONSTRAINT big_table_pk PRIMARY KEY (id);

PROMPT Gather statistics for big_table
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER, tabname => 'BIG_TABLE',
    method_opt => 'FOR ALL INDEXED COLUMNS', cascade => TRUE);
END;
/

PROMPT Check total rows for big_table
SELECT COUNT(*) FROM big_table;

Oracle 11g 版本腳本 (cr_big_tb_11g.sql) - 新增欄位

--==============================================
-- Create a test table for Oracle 11g
-- File : cr_big_tb_11g.sql
--==============================================
PROMPT Create a big table from all_objects
CREATE TABLE big_table AS SELECT ROWNUM id, a.* FROM all_objects a WHERE 1=0;

PROMPT Modify table to nologging mode
ALTER TABLE big_table NOLOGGING;

PROMPT Please input rows number to fill into big_table
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,
           namespace, edition_name          -- 11g 新增兩欄
    FROM big_table WHERE ROWNUM <= l_rows - l_cnt;
    l_cnt := l_cnt + SQL%ROWCOUNT;
    COMMIT;
  END LOOP;
END;
/

PROMPT Add primary key for big table
ALTER TABLE big_table ADD CONSTRAINT big_table_pk PRIMARY KEY (id);

PROMPT Gather statistics for big_table
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER, tabname => 'BIG_TABLE',
    method_opt => 'FOR ALL INDEXED COLUMNS', cascade => TRUE);
END;
/

PROMPT Check total rows for big_table
SELECT COUNT(*) FROM big_table;

執行方式

SQL> @cr_big_tb_11g.sql 1000000
-- 參數 1000000 為目標行數，將產生 100 萬行測試資料

關鍵效能參數說明

擴充應用：建立分區大表

-- 範例：按 CREATED 日期範圍分區
CREATE TABLE big_table_part (
  id NUMBER, owner VARCHAR2(128), object_name VARCHAR2(128), ...
) PARTITION BY RANGE (created) (
  PARTITION p_2020 VALUES LESS THAN (TO_DATE('2021-01-01','YYYY-MM-DD')),
  PARTITION p_2021 VALUES LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD')),
  PARTITION p_max VALUES LESS THAN (MAXVALUE)
) NOLOGGING;

-- 載入資料同樣使用 APPEND 迴圈

使用提醒：1) 目標表空間需有足夠空間 (預估 100萬行約 200-500MB) 2) NOLOGGING 操作後需立即備份，否則媒體恢復不可用 3) 統計資訊收集大表耗時較久，可加 ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE 4) 12c+ 可用 DBMS_STATS.SET_GLOBAL_PREFS 設定偏好。