Oracle 11g検証 隠れた新機能検証 その３
＜Oracle 11g検証 隠れた新機能検証 その３＞
ペンネーム： オレンジみかん
前回に引き続きOracle11gの隠れた新機能について検証を行います。
今回から２回にわたって仮想列について検証して行きたいと思います。
■仮想列とは
仮想列とは、テーブルにあるカラムに対して直接計算式を定義し
表示専用のカラムとして表示させることが出来る機能です。
従来のVIEWを利用しても計算式を定義して計算することができましたが
仮想列を利用することで幾つかのメリットが得られます。
今回の検証では仮想列とVIEWの違いを明らかにし、仮想列の基本的な特徴を
見て行きたいと思います。
■検証環境
Red Hat Enterprise Linux Server release 5.3
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit
■VIEWと仮想列の実装方法の違い
都道府県名を東北、関東というような地域に変換表示するケースを例に
VIEWと仮想列での実装方法の違いを見てみます。
１）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; ビューが作成されました。
２）仮想列の場合
仮想列は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行が作成されました。
続いて仮想列の特徴について見て行きましょう。
■仮想列の特徴
仮想列はテーブル定義として扱える他に、仮想列に対してインデックス、
パーティションを作成することが出来ます。
それでは、実際に仮想列に対してインデックス、パーティションを作成して
実行計画の違いについて確認してみましょう。
１）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
に対してフルスキャンとなってしまいます。
２）仮想列の場合（インデックス利用）
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に変わりました。今回は検証出来ませんで
したが、これによってパフォーマンスに対して効果がありそうです。
また、仮想列にはパーティションも作成することが出来ます。
以下の例はパーティションを地域ごとに作成した例です。
３）仮想列の場合（パーティション利用）
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つのリストパーティションを利用した実行計画となっています。
仮想列に対してはリストパーティションしか作成出来ず、ハッシュパーテ
ィション、レンジパーティションは作られないため注意が必要です。
今回は仮想列の基本的な特徴を見てきました。次回は仮想列のメリットと
制限事項を検証し活用方法を探ります。