2018.01.24
Add column
目次
Add columnについて
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値に置き換えなければならないので不必要なブロックの変更を行わずにメタデータを使用するようにしています。