Oracle® 非公式 SQL, PL/SQL & DBA's リファレンス

遅延セグメント作成 (deferred segment creation)

表を作成したときに同時に セグメント を作成せずにデータが初めて挿入されたときにセグメントを実体化（マテリアライズ）する機能である。
そして、この振る舞いが 11gR2 からデフォルトになっている。

※ この機能はエンタープライズ版(Oracle11g R2時点) の機能であるため SE1 や SE ではデフォルト無効であるかのようにみえる。

セグメント確保が遅延されるのは ローカル管理表領域 上の ヒープ表 が対象となる。
但し、パーティション表は 11.2.0.2 からの対応となっている。

セグメントがマテリアライズ（実体）化してるか調べる

テーブルの場合 USER_TABLES 系の SEGMENT_CREATED を確認する

			カラム名		データタイプ	説明
○	○	×	TABLE_OWNER	-	VARCHAR2(30)	表の所有者
○			TABLE_NAME		VARCHAR2(30)	表の名前
○			SEGMENT_CREATED		VARCHAR2(3)	実体化されている（YES) か 否 (NO)か

パーティション・テーブルの場合 USER_TAB_PARTITIONS 系の SEGMENT_CREATED を確認する

			カラム名		データタイプ	説明
○	○	×	TABLE_OWNER	-	VARCHAR2(30)	表の所有者
○			TABLE_NAME		VARCHAR2(30)	表の名前
○			PARTITION_NAME		VARCHAR2(30)	パーティション名
○			SEGMENT_CREATED		VARCHAR2(3) / 11.2.0.1 VARCHAR2(4) / 11.2.0.2	実体化されている（YES) か 否 (NO)か 11.2.0.1でも存在して設定されている

遅延セグメント作成 を発生させない方法

(1) CREATE TABLE 文に 遅延セグメント作成 に関する構文が追加されているので テーブルの DDL レベルで無効化する。
CREATE TABLE 〜 SEGMENT CREATION { IMMEDIATE | DEFERRED }

SQL> CREATE TABLE my_table (id number) SEGMENT CREATION IMMEDIATE;
表が作成されました。
 
SQL> select TABLE_NAME, SEGMENT_CREATED from user_tables
      where table_name = 'MY_TABLE';
TABLE_NAME                     SEGMENT_CREATED
------------------------------ ----------------
MY_TABLE                       YES

(2) 初期化パラメーター DEFERRED_SEGMENT_CREATION を使ってインスタンスレベルで無効化する。

SQL> show parameter DEFERRED_SEGMENT_CREATION
 
NAME                            TYPE        VALUE
------------------------------- ----------- ------
deferred_segment_creation       boolean     TRUE

SQL> alter system set DEFERRED_SEGMENT_CREATION=false scope=both;
 
システムが変更されました。

alter session でも変更可

指定オブジェクトの手動マテリアライズ（実体化）

• 任意をオブジェクトのセグメントを実体化するには

BEGIN
  DBMS_SPACE_ADMIN.MATERIALIZE_DEFERRED_SEGMENTS(
    schema_name  => 'my_name',         -- 
    table_name   => 'my_table',        -- 全パラメータが省略可
    partition_name   => 'my_partition' -- 
  );
END;

スキーマ名を省略した場合にはすべてのスキーマが対象となる。但し、実体化する前に検査プロセスがあるので非効率的。

• 表が空でセグメントを解放できる状態のときセグメントを解放する。

BEGIN
  DBMS_SPACE_ADMIN.DROP_EMPTY_SEGMENTS(
    schema_name  => 'my_name',         -- 
    table_name   => 'my_table',        -- 全パラメータが省略可
    partition_name   => 'my_partition' -- 
  );
END;

※ MATERIALIZE_DEFERRED_SEGMENTS / DROP_EMPTY_SEGMENTS のプロシージャは 11.2.0.2 からの実装となっているので 11.2.0.1 では実行できない。

遅延セグメント作成とシーケンス

セグメント実体化されるときにの INSERT 文中でシーケンスを使った場合にそこで取得された番号は欠番となる。

SQL> create table hoge1 (id number);
表が作成されました。
 
SQL> create table hoge2 (id number);
表が作成されました。
 
SQL> create sequence seq_hoge;
順序が作成されました。
 
SQL> insert into hoge1 values (seq_hoge.nextval);
1行が作成されました。
 
SQL> insert into hoge1 values (seq_hoge.nextval);
1行が作成されました。
 
SQL> insert into hoge2 values (seq_hoge.nextval);
1行が作成されました。
 
SQL> insert into hoge2 values (seq_hoge.nextval);
1行が作成されました。
 
SQL> select * from hoge1;
        ID
----------
         2
         3
 
SQL> select * from hoge2;
        ID
----------
         5
         6

以上のように 1 と 4 がスキップされてしまう。(※ この機能はエンタープライズ版の機能なので SE1 や SE では再現しないはず。未検証）
参考 ⇒ 様々なシーケンスの欠番理由

関連事項

• データブロック・エクステント・セグメント
• ALTER TABLE ＜エクステント割り当てと解放＞