Oracle 11g検証 隠れた新機能検証 その４
＜Oracle 11g検証 隠れた新機能検証 その４＞
ペンネーム： オレンジみかん
前回に引き続きOracle11gの隠れた新機能のひとつの仮想列について検証を
行っていきます。
はじめに仮想列について少しおさらいをしておきましょう。
■前回のおさらい
仮想列とは、テーブルにあるカラムに対して直接計算結果を表示専用のカラ
ムとして利用出来る機能です（従来のVIEWに計算式を埋め込むことに相当）。
さらに、仮想列は対象カラムに対してINDEXを作成したり、仮想列をパーティ
ション化することも可能です。
今回は仮想列でパーティション化する場合(以降:仮想列パーティションといい
ます)に注目してメリットと注意事項について検証して行きたいと思います。
■検証環境
Red Hat Enterprise Linux Server release 5.3
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit
それでは、仮想列パーティションを作成して「通常の表＋VIEW」の利用と
「仮想列パーティション表」のレスポンスの違いについて確認してみましょう。
■レスポンスの比較
SQL実行時のレスポンスを比較するために前回より利用している通常の表お
よびビュー(T_ADDRESS + V_ADDRESS)と仮想列パーティション表
(VTP_ADDRESS）を使って940,000件のデータ(20,000件×47都道府県)をINSERT
およびSELECTしてみます。
・通常の表およびビュー(T_ADDRESS + V_ADDRESS)
SQL> DESC T_ADDRESS; 名前 NULL? 型 ------------- -------------- ------------------------------------ KEN_NAME VARCHAR2(16) KEN_CODE VARCHAR2(8) CREATE VIEW V_ADDRESS( KEN_NAME ,KEN_CODE ,AREA_CODE ) AS SELECT KEN_NAME ,KEN_CODE ,CASE WHEN KEN_CODE BETWEEN '01' AND '07' THEN '北海東北' WHEN KEN_CODE BETWEEN '08' AND '14' THEN '関東' WHEN KEN_CODE BETWEEN '15' AND '19' THEN '甲信北陸' WHEN KEN_CODE BETWEEN '20' AND '24' THEN '東海' WHEN KEN_CODE BETWEEN '23' AND '30' THEN '近畿' WHEN KEN_CODE BETWEEN '31' AND '35' THEN '中国' WHEN KEN_CODE BETWEEN '36' AND '39' THEN '四国' WHEN KEN_CODE BETWEEN '40' AND '47' THEN '九州沖縄' END AREA_CODE FROM T_ADDRESS;
・仮想列パーティション表(VTP_ADDRESS)
CREATE TABLE VTP_ADDRESS (
KEN_NAME VARCHAR2(16),
KEN_CODE VARCHAR2(8),
AREA_CODE AS (
CASE
WHEN KEN_CODE BETWEEN '01' AND '07' THEN '北海東北'
WHEN KEN_CODE BETWEEN '08' AND '14' THEN '関東'
WHEN KEN_CODE BETWEEN '15' AND '19' THEN '甲信北陸'
WHEN KEN_CODE BETWEEN '20' AND '24' THEN '東海'
WHEN KEN_CODE BETWEEN '23' AND '30' THEN '近畿'
WHEN KEN_CODE BETWEEN '31' AND '35' THEN '中国'
WHEN KEN_CODE BETWEEN '36' AND '39' THEN '四国'
WHEN KEN_CODE BETWEEN '40' AND '47' THEN '九州沖縄'
END) VIRTUAL)
PARTITION BY LIST (AREA_CODE)
(
PARTITION P_1 VALUES ('北海東北'),
PARTITION P_2 VALUES ('関東'),
PARTITION P_3 VALUES ('甲信北陸'),
PARTITION P_4 VALUES ('東海'),
PARTITION P_5 VALUES ('近畿'),
PARTITION P_6 VALUES ('中国'),
PARTITION P_7 VALUES ('四国'),
PARTITION P_8 VALUES ('九州沖縄')
);
・通常の表およびビュー(T_ADDRESS + V_ADDRESS)
SQL> SET TIMING ON
SQL> BEGIN
SQL> FOR I IN 1..20000 LOOP
SQL> INSERT INTO T_ADDRESS (KEN_NAME,KEN_CODE)VALUES('北海道','01');
・
(中略)
・
SQL> INSERT INTO T_ADDRESS (KEN_NAME,KEN_CODE)VALUES('沖縄県','47');
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /
PL/SQLプロシージャが正常に完了しました。
経過: 00:02:11.53
SQL> SELECT COUNT(AREA_CODE) FROM V_ADDRESS WHERE AREA_CODE='九州沖縄';
COUNT(AREA_CODE)
----------------
160000
経過: 00:00:00.83
実行計画
---------------------------------------------------------------------
| Id |Operation |Name |Rows |Bytes|Cost(%CPU)|Time |
---------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| 6| 675 (20)|00:00:09 |
| 1| SORT AGGREGATE | | 1| 6| | |
|* 2| TABLE ACCESS FULL|T_ADDRESS| 140K| 824K| 675 (20)|00:00:09 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(CASE WHEN
("KEN_CODE">='01' AND "KEN_CODE"='08' AND "KEN_CODE"='15' AND "KEN_CODE"='20' AND "KEN_CODE"='25' AND "KEN_CODE"='31' AND "KEN_CODE"='36' AND "KEN_CODE"='40' AND "KEN_CODE" SET TIMING ON
SQL> BEGIN
SQL> FOR I IN 1..20000 LOOP
SQL> INSERT INTO VTP_ADDRESS (KEN_NAME,KEN_CODE)VALUES('北海道','01');
・
(中略)
・
SQL> INSERT INTO VTP_ADDRESS (KEN_NAME,KEN_CODE)VALUES('沖縄県','47');
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /
PL/SQLプロシージャが正常に完了しました。
経過: 00:02:43.92
SQL> select count(area_code) from vtp_address
2 where area_code='九州沖縄';
COUNT(AREA_CODE)
----------------
160000
経過: 00:00:00.79
実行計画
-----------------------------------------------------------------------
|Id|Operation |Name |Rows|Bytes|Cost(%CPU)|Time |
-----------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| 12| 135 (25)|00:00:02|
| 1| SORT AGGREGATE | | 1| 12| | |
| 2| PARTITION LIST SINGLE| |9285| 108K| 135 (25)|00:00:02|
| 3| TABLE ACCESS FULL |VTP_ADDRESS|9285| 108K| 135 (25)|00:00:02|
-----------------------------------------------------------------------
「通常の表およびビュー」と「仮想列パーティション」を比較してみると、
SELECT処理は仮想パーティションの方が実行計画のコストが1/5になりました。
これは、ビュー列を検索キーにしたSELECT処理の場合、ビュー列がフルスキャ
ンになったためであり、予想通りの結果です。
一方、INSERT処理の場合では仮想列パーティションの方が通常の表と比べると
より多くの仮想列実行時間が必要になるようです。
仮想列パーティションに対するINSERT処理の実行時間が長くなる原因は何でし
ょうか？SQLTraceを実行して確認してみましょう。
SQLTraceの実行にはSQLチューニングでおなじみの’EVENTS 10046’を使用しま
す。
======================================================
・通常の表およびビュー(T_ADDRESS + V_ADDRESS)
SQL> SET TIMING ON
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER
2 ,LEVEL 12';
SQL> --対象SQL
SQL> BEGIN
SQL> FOR I IN 1..20000 LOOP
SQL> INSERT INTO T_ADDRESS (KEN_NAME,KEN_CODE)
2 VALUES('北海道','01');
・
(中略)
・
SQL> INSERT INTO T_ADDRESS (KEN_NAME,KEN_CODE)
2 VALUES('沖縄県','47');
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /
SQL> SELECT COUNT(AREA_CODE) FROM V_ADDRESS WHERE AREA_CODE='九州沖縄';
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
・仮想列パーティション表(VTP_ADDRESS)
SQL> SET TIMING ON
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER
2 ,LEVEL 12';
SQL> --対象SQL
SQL> BEGIN
SQL> FOR I IN 1..20000 LOOP
SQL> INSERT INTO VTP_ADDRESS (KEN_NAME,KEN_CODE)
2 VALUES('北海道','01');
・
(中略)
・
SQL> INSERT INTO VTPT_ADDRESS (KEN_NAME,KEN_CODE)
2 VALUES('沖縄県','47');
SQL> END LOOP;
SQL> COMMIT;
SQL> END;
SQL> /
SQL> SELECT COUNT(AREA_CODE) FROM VTP_ADDRESS
2 WHERE AREA_CODE='九州沖縄';
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
・INSERT文を抜粋して比較==============================================
(TKPROF整形済み）
SQL ID : 371p0zwr7w0s3
INSERT INTO T_ADDRESS (KEN_NAME,KEN_CODE)
VALUES
('沖縄県','47')
call count cpu elapsed disk query current rows
------- ------ ----- ------- ------ ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 20000 0.94 0.90 0 1983 26472 20000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ ----- ------- ------ ---------- ---------- ----------
total 20001 0.94 0.90 0 1983 26472 20000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=0 us)
(TKPROF整形済み）
SQL ID : awu31zmt6xs2w
INSERT INTO VTP_ADDRESS (KEN_NAME,KEN_CODE)
VALUES
('沖縄県','47')
call count cpu elapsed disk query current rows
------- ------ ----- ------- ------ ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 20000 1.47 1.44 0 20000 60543 20000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ ----- ------- ------ ---------- ---------- ----------
total 20001 1.47 1.44 0 20000 60543 20000
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=0 us)
SQLTraceのquery項目に注目して比較すると、仮想パーティションでのINSERT
文の実行ではquery項目の数値が多いことから、フェッチ・コールが頻繁に実行
されていることが推察されます。従って、仮想パーティションでは通常の表の
場合と比べると解析・実行回数が増加していたため、実行時間が多くかかって
しまうようです。
■まとめ
仮想列パーティションは今までVIEWで定義したカラムに対して、パーティショ
ンを作成することができるため、レスポンス対策の手段として効果があること
を改めて確認出来ました。
ただし、仮想パーティションに対してインサートを実行すると、解析・実行
処理が増加するため、検索処理と挿入処理を高速に行いたい場合のトランザク
ション系処理では利用には注意が必要です。
この様に仮想列パーティションの特徴を踏まえると、「頻繁に書き込みは無い
が大量データの中から高速にデータを検索したいというシステム」に生かせる
機能として活用出来る機能です。仮想列パーティションの具体的な活用例とし
てはDWHなどのデータ分析などに有効活用出来そうです。
今回はここまで
次回は、adaptive cursor sharing の機能について検証します。