Add Column - 日本エクセム株式会社 Oracle 技術情報

2025-05-03 02:03Oracle技術情報

1.基本情報
2.テスト

基本情報

　Oracle11gでは、テーブルにカラムを追加しながらDefault Valueを設定できます。この場合、一般的に考えて、多くのデータを持つテーブルにカラムを追加してDefault valueまでを指定をすることで、列を追加するoverheadとdefault valueを入れながら発生するupdateのoverheadを考えることができます。しかし、11gでは、既存のデータの更新ではなく、メタデータに格納されているdefault value値を嫁にとるようになりました。

テスト

テストを通じてその機能について調べてみましょう。

＜テスト環境＞
ノートPC: Asianux2.0（2.6.9-42.7AXsmp）+ Oracle11g11.1.0.6

＜テストシナリオ＞
1. Table生成
2. データinsert
3. Block dump
4. Add Column with default value
5. Block dump
6. SELECT statements
7. Insert New Data

テストに使用したスクリプト

＜テーブルの作成＞
CREATE TABLE ADD_COL_TEST
　(id number, name varchar2(1000), sal number);

＜データインサート＞
BEGIN
  FOR i IN1..100000 LOOP
     INSERT INTO ADD_COL_TEST VALUES (i, 'add_test', 100);
     COMMIT;
  END LOOP;
END;
/

＜列を追加＞
SQL> alter table ADD_COL_TEST add add_col varchar2(10) default 'ADD COLUMN' NOT NULL;

＜テストを開始＞
テーブルを作成した後、データを入れてブロックダンプを実行します。（以降のブロックの内容と比較）
ブロック全体の代わりにid値が1番のデータが含まれているブロックに対してのみダンプを実行して見ます。

SQL> SELECT dbms_rowid.rowid_relative_fno (ROWID),
            dbms_rowid.rowid_block_number (ROWID)
     FROM add_col_test
     WHERE id=1;

 DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER(
  ------------------------------ ------------------------------
                                 4                          17743
  1 row

＜ダンプを実行＞
SQL> alter system dump datafile4 block17743;

次は、ブロックのダンプ内容です。

block_row_dump：
  tab0、row0、@0x1f86
  tl：18 fb：--H-FL-- lb：0x0 cc：3
  col0：[2] c102
  col1：[8]6164645f74657374
  col2：[2] c202
  tab0、row1、@0x1f74
  tl：18 fb：--H-FL-- lb：0x0 cc：3
  col0：[2] c103
  col1：[8]6164645f74657374
  col2：[2] c202
  tab0、row2、@0x1f62
  tl：18 fb：--H-FL-- lb：0x0 cc：3
  col0：[2] c104
  col1：[8]6164645f74657374
  col2：[2] c202
  ........省略

　ここで簡単に説明をすると、ccという値はカラム数を示します。これにより、現在のすべてのrowがカラムカウントが3であることを知ることができます。

　テーブルにカラムを追加した後、同じブロックのダンプを実行すると、次のような結果を確認することができます。

block_row_dump：
  tab0、row0、@0x1f86
  tl：18 fb：--H-FL-- lb：0x0 cc：3
  col0：[2] c102
  col1：[8]6164645f74657374
  col2：[2] c202
  tab0、row1、@0x1f74
  tl：18 fb：--H-FL-- lb：0x0 cc：3
  col0：[2] c103
  col1：[8]6164645f74657374
  col2：[2] c202
  tab0、row2、@0x1f62
  tl：18 fb：--H-FL-- lb：0x0 cc：3
  col0：[2] c104
  col1：[8]6164645f74657374
  col2：[2] c202
  .......省略

カラムを追加する前、または追加した後やカラム数は変化がありません。

SQL> SELECT table_name、column_name、NULLABLE、DATA_DEFAULT FROM dba_tab_columns
          WHERE owner= 'SCOTT'
           AND table_name= 'ADD_COL_TEST';
  TABLE_NAME COLUMN_NAME NULLABLE DATA_DEFAULT
  -----------------------------------------------------------------------------------
  ADD_COL_TEST ID Y
  ADD_COL_TEST NAME Y
  ADD_COL_TEST SAL Y
  ADD_COL_TEST ADD_COL N'ADD COLUMN」
  4 rows

　上記のクエリを介して、メタデータを照会してみると、カラムが4であることを確認することができます。

SQL> SELECT*
     FROM add_col_test
     WHERE id<3

                 ID NAME                                                      SAL ADD_COL   
  -------------------- ---------------------------------------- -------------------- ----------
                  1 add_test                                                  100 ADD COLUMN
                  2 add_test                                                  100 ADD COLUMN
  2 rows

テーブルを参照すると、通常のデータが表示されます。

　つまり、追加されたカラムにDefault値を与えることができるので、実際の物理的なスペースを割り当てた後、データを入れる方法ではなく、Disk I/Oのoverheadを減らすために、メタデータのみが変更された後、SELECTクエリの値をリターンしていることがわかります。

　その後、rowを更新すると、どのようにされていることを確認してましょう。

SQL> update add_col_test set add_col='update' where id = 1 ;

ブロックのダンプを実行した後の内容を確認してみると、物理的な空間に変化があることを確認することができます。

  block_row_dump：
  tab0、row0、@0x5eb
  tl：25 fb：--H-FL-- lb：0x1 cc：4
  col0：[2] c102
  col1：[8]6164645f74657374
  col2：[2] c202
  col3：[6]757064617465
  tab0、row1、@0x1f74
  tl：18 fb：--H-FL-- lb：0x0 cc：3
  col0：[2] c103
  col1：[8]6164645f74657374
  col2：[2] c202
  tab0、row2、@0x1f62
  tl：18 fb：--H-FL-- lb：0x0 cc：3
  col0：[2] c104
  col1：[8]6164645f74657374
  col2：[2] c202
  ...省略

物理的なスペースの変化が発生しました。

　新しいデータをinsertし、最後に追加されたカラムにDefault valueを使用するようにしてみましょう。

SQL> insert into add_col_test (id、name、sal) values(999999、「insert」、999);

SQL>SELECT dbms_rowid.rowid_relative_fno (ROWID ),
           dbms_rowid.rowid_block_number( ROWID )
    FROM   add_col_test
    WHERE  id = 999999 ;

DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER(
  ------------------------------ ------------------------------
                               7                            211
  1 row

SQL> alter system dump datafile 7 block 211 ;

tab 0, row 203, @0xfac
  tl: 18 fb: --H-FL-- lb: 0x2  cc: 3
  col  0: [ 2]  c3 0b
  col  1: [ 8]  61 64 64 5f 74 65 73 74
  col  2: [ 2]  c2 02
  end_of_block_dump
  End dump data blocks tsn: 4 file#: 7 minblk 211 maxblk 211

　そのブロックの最後のrowの内容を確認してみると、やはりカラム数が3であることを確認することができます。Nullが入る場合Default値に置き換えなければならないので不必要なブロックの変更を行わずにメタデータを使用するようにしています。

Add Column - 日本エクセム株式会社 Oracle 技術情報

基本情報

テスト

前の記事

次の記事

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします

人気記事ランキング

タグ一覧

Add Column - 日本エクセム株式会社 Oracle 技術情報

基本情報

テスト

前の記事

次の記事

他社に頼らず自社でデータベースを監視・運用をしませんか？MaxGaugeがサポートします

人気記事ランキング

タグ一覧

他社に頼らず自社でデータベースを監視・運用をしませんか？
MaxGaugeがサポートします