Oracle 11g検証 隠れた新機能検証 その3

<Oracle 11g検証 隠れた新機能検証 その3>
ペンネーム: オレンジみかん

前回に引き続きOracle11gの隠れた新機能について検証を行います。
今回から2回にわたって仮想列について検証して行きたいと思います。

■仮想列とは
仮想列とは、テーブルにあるカラムに対して直接計算式を定義し
表示専用のカラムとして表示させることが出来る機能です。
従来のVIEWを利用しても計算式を定義して計算することができましたが
仮想列を利用することで幾つかのメリットが得られます。

今回の検証では仮想列とVIEWの違いを明らかにし、仮想列の基本的な特徴を
見て行きたいと思います。

■検証環境
Red Hat Enterprise Linux Server release 5.3
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit

■VIEWと仮想列の実装方法の違い
都道府県名を東北、関東というような地域に変換表示するケースを例に
VIEWと仮想列での実装方法の違いを見てみます。

1)VIEWの場合
地域表のVIEWを作成する基のテーブルとして、以下に示すような
都道府県名と県コードを持つテーブルを用います。

SQL> SELECT * FROM T_ADDRESS;

KEN_NAME KEN_CODE
-------- --------
北海道   01
青森県   02
岩手県   03
・
(途中省略)
・
宮崎県   45
鹿児島県 46
沖縄県   47


SQL> DESC T_ADDRESS
 名前                                      NULL?    型
 ----------------------------------------- -------- -----------------
 KEN_NAME                                           VARCHAR2(12)
 KEN_CODE                                           VARCHAR2(12)

VIEWで都道府県の情報を地域に変換する場合のSQLを以下に示します。

SQL> CREATE VIEW V_ADDRESS(
  2   KEN_NAME
  3  ,KEN_CODE
  4  ,AREA_CODE
  5  ) AS
  6  SELECT
  7   KEN_NAME
  8  ,KEN_CODE
  9  ,CASE
 10    WHEN KEN_CODE BETWEEN '01' AND '07' THEN '北海東北'
 11    WHEN KEN_CODE BETWEEN '08' AND '14' THEN '関東'
 12    WHEN KEN_CODE BETWEEN '15' AND '19' THEN '甲信北陸'
 13    WHEN KEN_CODE BETWEEN '20' AND '24' THEN '東海'
 14    WHEN KEN_CODE BETWEEN '23' AND '30' THEN '近畿'
 15    WHEN KEN_CODE BETWEEN '31' AND '35' THEN '中国'
 16    WHEN KEN_CODE BETWEEN '36' AND '39' THEN '四国'
 17    WHEN KEN_CODE BETWEEN '40' AND '47' THEN '九州沖縄'
 18  END AREA_CODE
 19  FROM T_ADDRESS;

ビューが作成されました。

2)仮想列の場合
仮想列はVIEWと異なり、計算式をテーブルのカラムとして直接定義する
ことが出来ます。具体的には、テーブル作成・変更時にVIRTUAL句を用いる
ことにより表示専用のカラムとして利用することが出来ます。

SQL> CREATE TABLE VT_ADDRESS (
  2            KEN_NAME            VARCHAR2(12),
  3            KEN_CODE            VARCHAR2(12),
  4            AREA_CODE AS (
  5  CASE
  6    WHEN KEN_CODE BETWEEN '01' AND '07' THEN '北海東北'
  7    WHEN KEN_CODE BETWEEN '08' AND '14' THEN '関東'
  8    WHEN KEN_CODE BETWEEN '15' AND '19' THEN '甲信北陸'
  9    WHEN KEN_CODE BETWEEN '20' AND '24' THEN '東海'
 10    WHEN KEN_CODE BETWEEN '23' AND '30' THEN '近畿'
 11    WHEN KEN_CODE BETWEEN '31' AND '35' THEN '中国'
 12    WHEN KEN_CODE BETWEEN '36' AND '39' THEN '四国'
 13    WHEN KEN_CODE BETWEEN '40' AND '47' THEN '九州沖縄'
 14  END) VIRTUAL);

表が作成されました。

参考として、仮想列はテーブルに計算式を定義しているため、データを直接
挿入することはできません。

この例の様に仮想列であるAREA_CODEを挿入対象カラムから外す必要があり
ます。

SQL> INSERT INTO VT_ADDRESS (KEN_NAME,KEN_CODE)VALUES('北海道','01');

1行が作成されました。

続いて仮想列の特徴について見て行きましょう。
■仮想列の特徴

仮想列はテーブル定義として扱える他に、仮想列に対してインデックス、
パーティションを作成することが出来ます。

それでは、実際に仮想列に対してインデックス、パーティションを作成して
実行計画の違いについて確認してみましょう。

1)VIEWの場合

SQL> SELECT COUNT(*) FROM V_ADDRESS WHERE AREA_CODE='北海東北';

  COUNT(*)
----------
         7


----------------------------------------------------------------------
Id |Operation              |Name       |Row|Bytes|Cost(%CPU)|Time    |
---------------------------------------------------------------------|
 0 |SELECT STATEMENT       |           |  1|    6|     3 (0)|00:00:01|
 1 |  SORT AGGREGATE       |           |  1|    6|          |        |
*2 |  TABLE ACCESS FULL    |T_ADDRESS  |  7|   42|     3 (0)|00:00:01|
----------------------------------------------------------------------

VIEWの場合は検索キーとなるAREA_CODEはSELECTを使った計算式で定義し
ていることからINDEXは作成出来ません。そのため、実行計画はT_ADDRESS
に対してフルスキャンとなってしまいます。

2)仮想列の場合(インデックス利用)

SQL> CREATE TABLE VT_ADDRESS (
  2            KEN_NAME            VARCHAR2(12),
  3            KEN_CODE            VARCHAR2(12),
  4            AREA_CODE AS (
  5  CASE
  6    WHEN KEN_CODE BETWEEN '01' AND '07' THEN '北海東北'
  7    WHEN KEN_CODE BETWEEN '08' AND '14' THEN '関東'
  8    WHEN KEN_CODE BETWEEN '15' AND '19' THEN '甲信北陸'
  9    WHEN KEN_CODE BETWEEN '20' AND '24' THEN '東海'
 10    WHEN KEN_CODE BETWEEN '23' AND '30' THEN '近畿'
 11    WHEN KEN_CODE BETWEEN '31' AND '35' THEN '中国'
 12    WHEN KEN_CODE BETWEEN '36' AND '39' THEN '四国'
 13    WHEN KEN_CODE BETWEEN '40' AND '47' THEN '九州沖縄'
 14  END) VIRTUAL);

表が作成されました。

仮想列にはインデックスを作成することが出来ます。
検索キーのAREA_CODEに対してインデックスを作成してみましょう。

SQL> CREATE INDEX VT_AREA_INDEX ON VT_ADDRESS(AREA_CODE);

索引が作成されました。

実行計画を確認すると…

SQL>   SELECT COUNT(*) FROM VT_ADDRESS WHERE AREA_CODE='北海東北';

  COUNT(*)
----------
         7


----------------------------------------------------------------------
Id |Operation            |Name         |Row|Bytes|Cost(%CPU)|Time    |
---------------------------------------------------------------------|
 0 |SELECT STATEMENT     |             |  1|   12|    1  (0)|00:00:01|
 1 | SORT AGGREGATE      |             |  1|   12|          |        |
*2 |  INDEX RANGE SCAN   |VT_AREA_INDEX|  1|   12|    1  (0)|00:00:01|
---------------------------------------------------------------------

INDEXを作成することでINDEX RANGE SCANに変更されました。CostもVIEWの
時と比較してCost=3 から Cost=1に変わりました。今回は検証出来ませんで
したが、これによってパフォーマンスに対して効果がありそうです。

また、仮想列にはパーティションも作成することが出来ます。
以下の例はパーティションを地域ごとに作成した例です。

3)仮想列の場合(パーティション利用)

SQL> CREATE TABLE VTP_ADDRESS (
  2            KEN_NAME            VARCHAR2(12),
  3            KEN_CODE            VARCHAR2(12),
  4            AREA_CODE AS (
  5  CASE
  6    WHEN KEN_CODE BETWEEN '01' AND '07' THEN '北海東北'
  7    WHEN KEN_CODE BETWEEN '08' AND '14' THEN '関東'
  8    WHEN KEN_CODE BETWEEN '15' AND '19' THEN '甲信北陸'
  9    WHEN KEN_CODE BETWEEN '20' AND '24' THEN '東海'
 10    WHEN KEN_CODE BETWEEN '23' AND '30' THEN '近畿'
 11    WHEN KEN_CODE BETWEEN '31' AND '35' THEN '中国'
 12    WHEN KEN_CODE BETWEEN '36' AND '39' THEN '四国'
 13    WHEN KEN_CODE BETWEEN '40' AND '47' THEN '九州沖縄'
 14  END) VIRTUAL)
 15  PARTITION BY LIST (AREA_CODE)
 16  (
 17     PARTITION P_1 VALUES ('北海東北'),
 18     PARTITION P_2 VALUES ('関東' ),
 19     PARTITION P_3 VALUES ('甲信北陸'),
 20     PARTITION P_4 VALUES ('東海'),
 21     PARTITION P_5 VALUES ('近畿'),
 22     PARTITION P_6 VALUES ('中国'),
 23     PARTITION P_7 VALUES ('九州沖縄')
 24  );

表が作成されました。

それではパーティションでの実行計画を確認してみましょう。
実行計画の結果はどうなっているでしょうか?

SQL> SELECT COUNT(*) FROM VTP_ADDRESS PARTITION (P_1);

  COUNT(*)
----------
         7


----------------------------------------------------------------------
Id |Operation              |Name       |Row|Bytes|Cost(%CPU)|Time    |
---------------------------------------------------------------------|
 0 |SELECT STATEMENT       |           |  1|   6 |     3 (0)|00:00:01|
 1 | SORT AGGREGA          |           |  1|   6 |          |        |
 2 |  PARTITION LIST SINGLE|           |  7|   42|     3 (0)|00:00:01|
 3 |   TABLE ACCESS FULL   |VTP_ADDRESS|  7|   42|     3 (0)|00:00:01|
----------------------------------------------------------------------

実行計画を確認するとOperation=PARTITION LIST SINGLE になっていますの
で、1つのリストパーティションを利用した実行計画となっています。
仮想列に対してはリストパーティションしか作成出来ず、ハッシュパーテ
ィション、レンジパーティションは作られないため注意が必要です。

今回は仮想列の基本的な特徴を見てきました。次回は仮想列のメリットと
制限事項を検証し活用方法を探ります。