2022.11.02
SQLチューニング（第38回）「カラム変形によるSQL性能問題の理解する」（2／2回）
「カラム変形によるSQL性能問題の理解する」の第2回目は、カラムとOracle関数の関係についてです。では、さっそく内容に入りましょう。
10.２ カラムに不要なORACLE提供関数を使用しない
カラム変形によって発生する性能問題の代表的な二種類の類型を挙げるとすると、最初には照会カラムと変数のデータ タイプが違う場合であり、二番目はSQLの照会カラムにOracleで提供する関数を使用する場合です。
前回私たちはSQLで問い合わせるカラムと変数のデータ タイプが違う場合に対してテストを通じて調べて見ました。
これからはSQL作成時Where節の照会カラムに提供関数を使用することで、インデックスを使用できない性能問題に対して調べてみることにします。
10.2.1 不適切にFUNCTIONをカラムに使う場合
以下のCASE[1]とCASE[2]のWhere節はSQL作成時、カラムに関数を実行した一般的なパターンです。 CASE[1],[2]のWhere節で変更前と変更後は同じデータを抽出しますが、SQLの性能には多くの差が発生する可能性があります。
なぜなら、変更前のようにカラムにOracle提供関数を使用して加工することになれば、効率的なインデックスが存在しても使用できないためです。 したがってカラム(CUSTNO,INSERT_DATE)に効率的なインデックスが存在するならば、変更後のように照会カラムに提供関数を使用しないようにSQLを再作成して性能を改善することができるのです。
（１）CASE[1]. CUSTNOデータ タイプがCHARである場合
変更前:WHERE SUBSTR(custno、1,7) = '1234567'
↓
変更後:WHERE custno LIKE '1234567'||'%'
（２）CASE[2]. INSERT_DATEデータ タイプがDATEである場合
変更前:WHERE TO_CHAR(insert_date,'yyyymmdd') = '20110125'
↓
変更後:WHERE insert_date >= TO_DATE('20110125','yyyymmdd')
AND insert_date < TO_DATE ('20110125','yyyymmdd') + 1
CASE[1],[2]のWhere節条件で使われたCUSTNO,INSERT_DATEカラムはそれぞれインデックスがすでに存在します。 したがって変更前条件を変更後のように変えれば、インデックス使用が可能になります。 しかし、Where節条件(変更前)を変更できない環境(プログラム変更が不可)ならば、次善策でFunction Based Indexを生成して性能を改善することができます。
Function Based Indexはカラムに対してFunctionを実行した結果で生成したB*Treeインデックスであり、Where節の条件がインデックスを生成する時使った提供関数を使用した場合にだけインデックス使用が可能です。 CASE[1]とCASE[2]にSQL変更なしでFunction Based Indexを生成して性能を改善するべきだとすれば、インデックス生成構文は下記のように作成することができます。
CREATE INDEX idx_t1_01 ON t1 ( SUBSTR(custno,1,7) ) ;
CREATE INDEX idx_t1_01 ON t1 ( TO_CHAR(insert_date, 'yyyymmdd') )
Function Based IndexはCASE[1],[2]とともに主にカラム変形によってインデックスを使用できない場合に性能を改善するために生成します。 しかし、時にはしばしば使うオンライン プログラムでOrder By節に使われる整列と同じインデックスを生成する目的で生成したりもします。 なぜなら、Order By節とインデックスが同一に整列しているならば、部分範囲処理に誘導することができるためです。
例えば、Order By節がA asc,B desc,C ascとともに各カラムの整列基準が各自（それぞれ）違うならば、一般インデックスを使っては部分範囲処理を誘導することはできないでしょう。 したがってこのような場合にはA asc,B desc,C ascで整列したFunction Based Indexを生成すれば、Order By節と同じカラム順序でインデックスが整列しているので、インデックスを活用して部分範囲処理に誘導できることになります。
Note. Function Based Indexの構成情報はDBA_IND_EXPRESSIONSで確認することができます。10.2.２ カラム変形によりインデックスのMIN/MAXを活用できない場合
SQLに使われるカラムが(Where節、Select節含む)全部インデックス構成に含まれて、MAX値を抽出するカラムを除いたすべてのカラムがEQUALで照会される時、インデックス スキャンがINDEX RANGE SCAN (MIN/MAX)で実行されて、少ないリソースを使ってMIN/MAXデータを抽出することができます。
INDEX RANGE SCAN (MIN/MAX)はインデックスの整列したデータ最初と最後の値を利用してMINとMAX値を抽出する方式で、MIN値を照会をする場合、インデックスのデータは基本的にアセンディングで整列しているので最初の値を抽出してMAX価格は最後の値を抽出します。
しかしMIN,MAX関数を実行する抽出カラムに変形が加えられればINDEX RANGE SCAN (MIN/MAX)で実行されることができないので性能問題が発生する可能性があります。 したがってMIN,MAX値を抽出するカラムにカラム変形が加えられないように必ず注意しなければなりません。
以下のテストを通じてMIN,MAX関数に使われたカラム変形による性能問題を調べてみます。
Script. テスト データ生成用
DROP TABLE T1 PURGE;
■生成要件
- T1テーブルの全体件数は100,000ロー
- CUST_ID価格はUNIQUEする。
- CUSTNAME値の種類は5種類である。
■テーブル生成
CREATE TABLE T1 ( CUSTID NUMBER,
CUSTNAME VARCHAR2(50) );
■データ生成
BEGIN
FOR I IN 1 .. 100000
LOOP
IF (MOD(I,5)=0)
THEN INSERT INTO T1 VALUES (I、'A');
END IF;
IF (MOD(I,5)=1)
THEN INSERT INTO T1 VALUES (I、'B');
END IF;
IF (MOD(I,5)=2)
THEN INSERT INTO T1 VALUES (I, 'C') ;
END IF;
IF (MOD(I,5)=3)
THEN INSERT INTO T1 VALUES (I, 'D') ;
END IF;
IF (MOD(I,5)=4)
THEN INSERT INTO T1 VALUES (I, 'E') ;
END IF;
END LOOP;
COMMIT ;
END;
/
■各カラムにインデックス生成および統計情報収集
CREATE INDEX IDX01_T1_CUSTID ON T1(CUSTNAME , CUSTID);
BEGIN
dbms_stats.gather_table_stats(OWNNAME=>'SCOTT',
TABNAME=>'T1',
ESTIMATE_PERCENT=>99,
METHOD_OPT=>'FOR ALL INDEXED COLUMNS',
GRANULARITY=>'ALL',
CASCADE=>TRUE,
NO_INVALIDATE=>FALSE) ;
END;
/
（１）テスト[1]. カラム変形による非効率的なMAX()抽出
SELECT MAX(TO_CHAR(custid)) AS custid ---> TO_CHAR()に変更後MAX値を抽出
FROM T1
WHERE custname='A' ;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.01 0 189 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.01 0 191 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=189 pr=0 pw=0 time=12728 us)
20000 TABLE ACCESS FULL T1 (cr=189 pr=0 pw=0 time=80025 us)
（２）テスト[2]. カラム変形なしでインデックスを利用した効率的なMAX()抽出
SELECT TO_CHAR(MAX(custid)) AS custid ---> MAX値を抽出した後TO_CHAR()に変更
FROM T1
WHERE custname='a';
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 1 2 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 1 4 0 1
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=2 pr=1 pw=0 time=821 us)
1 FIRST ROW (cr=2 pr=1 pw=0 time=795 us)
1 INDEX RANGE SCAN (MIN/MAX) IDX01_T1_CUSTID (cr=2 pr=1 pw=0 time=776 us)
テスト[1]の場合MAX値を抽出するカラムをTO_CHAR(CUSTID)でカラム変形をした後にMAX値を抽出してFull Table Scanで実行されて非効率が発生しました。 反面、テスト[2]の場合は先にMAX(CUSTID)を実行した後TO_CHAR関数で型変換処理をしてINDEX RANGE SCAN (MIN/MAX)で処理されて効果的にインデックスを使うことになりました。
10.2.３ パーティションキー列の変形は、すべてのパーティションを読む
パーテーション テーブルを問い合わせる時体験する性能問題のうちで、パーテーション キーカラムのデータ タイプと照会変数のデータ タイプが違って発生する場合があります。 しばしば目撃されることはないですが、性能問題が発生するならばDBサーバー全体に大きい負荷を与えることがあるので注意しなければなりません。
日(月)カラムを基準として月別パーテーションを生成したテーブルがあると仮定しましょう。 データ特性上保管周期が長いならば、時間が過ぎるほどパーテーションの個数はますます多くなるでしょう。
多くのパーテーション中で特定月データに対して照会を行いましたが、意図とは違うように全体パーテーションを問い合わせるならばWhere節条件のうちパーテーション キーカラムの型変換が発生したのか疑わなければなりません。 なぜなら、パーテーション キーカラムが変形されれば、全体パーテーションを問い合わせなければならないたためです。
では、パーテーション テーブルを生成後パーテーション キーカラム変形による性能問題をテストを通じて確認してみます。
Script. テスト データ生成用
DROP TABLE PART_T1 PURGE;
■生成要件
- REGDATEテーブルに対してLIST PARTTITIONテーブル生成
■パーテーション テーブル生成
CREATE TABLE PART_T1 ( ID NUMBER NOT NULL,
MEM VARCHAR2(2) NOT NULL,
REGDATE VARCHAR2(8) NOT NULL )
PARTITION BY RANGE (REGDATE)
(
PARTITION PART_T1_201001 VALUES LESS THAN ('20100201'),
PARTITION PART_T1_201002 VALUES LESS THAN ('20100301'),
PARTITION PART_T1_201003 VALUES LESS THAN ('20100401'),
PARTITION PART_T1_201004 VALUES LESS THAN ('20100501'),
PARTITION PART_T1_201005 VALUES LESS THAN ('20100601'),
PARTITION PART_T1_201006 VALUES LESS THAN ('20100701'),
PARTITION PART_T1_201007 VALUES LESS THAN ('20100801'),
PARTITION PART_T1_201008 VALUES LESS THAN ('20100901'),
PARTITION PART_T1_201009 VALUES LESS THAN ('20101001'),
PARTITION PART_T1_201010 VALUES LESS THAN ('20101101'),
PARTITION PART_T1_201011 VALUES LESS THAN ('20101201'),
PARTITION PART_T1_201012 VALUES LESS THAN ('20110101')
) ;
■データ入力
INSERT INTO PART_T1 VALUES ( 1,'A1','20100101') ;
INSERT INTO PART_T1 VALUES ( 2,'B1','20100201') ;
INSERT INTO PART_T1 VALUES ( 3,'C1','20100301') ;
INSERT INTO PART_T1 VALUES ( 4,'D1','20100401') ;
INSERT INTO PART_T1 VALUES ( 5,'E1','20100501') ;
INSERT INTO PART_T1 VALUES ( 6,'A1','20100601') ;
INSERT INTO PART_T1 VALUES ( 7,'B1','20100701') ;
INSERT INTO PART_T1 VALUES ( 8,'C1','20100801') ;
INSERT INTO PART_T1 VALUES ( 9,'D1','20100901') ;
INSERT INTO PART_T1 VALUES ( 10,'E1','20101001') ;
INSERT INTO PART_T1 VALUES ( 11,'D1','20101101') ;
INSERT INTO PART_T1 VALUES ( 12,'E1','20101201') ;
INSERT INTO PART_T1 VALUES (101,'A1','20100102') ;
INSERT INTO PART_T1 VALUES (102,'B1','20100202') ;
INSERT INTO PART_T1 VALUES (103,'C1','20100302') ;
INSERT INTO PART_T1 VALUES (104,'D1','20100402') ;
INSERT INTO PART_T1 VALUES (105,'E1','20100502') ;
INSERT INTO PART_T1 VALUES (106,'A1','20100602') ;
INSERT INTO PART_T1 VALUES (107,'B1','20100702') ;
INSERT INTO PART_T1 VALUES (108,'C1','20100802') ;
INSERT INTO PART_T1 VALUES (109,'D1','20100902') ;
INSERT INTO PART_T1 VALUES (110,'E1','20101002') ;
INSERT INTO PART_T1 VALUES (111,'D1','20101102') ;
INSERT INTO PART_T1 VALUES (112,'E1','20101202') ;
COMMIT ;
■各カラムにインデックス生成および統計情報収集
CREATE INDEX IDX_PART_T1_01 ON PART_T1(REGDATE) LOCAL ;
（１）テスト[1]. カラム変形による非効率パーテーション テーブル照会
SELECT /*+ FULL(PART_T1) */
*
FROM PART_T1
WHERE regdate BETWEEN TO_DATE('20101101','yyyymmdd')
AND TO_DATE('20101131','yyyymmdd');--->カラム データ タイプと条件データ タイプ不一致
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Time | Pstart| Pstop |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 00:00:01 | | |
|* 1 | FILTER | | | | | | |
| 2 | PARTITION RANGE ALL| | 2 | 44 | 00:00:01 | 1 | 12 |
|* 3 | TABLE ACCESS FULL | PART_T1 | 2 | 44 | 00:00:01 | 1 | 12 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_DATE(' 2010-11-01 00:00:00', 'syyyy-mm-dd
hh24:mi:ss')<=TO_DATE('20101131','yyyymmdd'))
3 - filter(INTERNAL_FUNCTION("REGDATE")>=TO_DATE(' 2010-11-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss')
AND INTERNAL_FUNCTION("REGDATE")<=TO_DATE('20101131','yyyymmdd'))
PART_T1テーブルは2010年1月から12月までのデータを月単位パーテーションで構成して合計12個のパーテーションを有しています。 テスト[1]のSQLはパーテーション キー条件であるREGDATEカラムに照会条件を付与して、2010年11月パーテーション テーブルだけ読んでデータを抽出しようとしましたが、トレース結果を見ればPARTITION RANGE ALLオペレーションが発生して、すべてのパーテーションを問い合わせたということがわかります。 それならは、2010年11月に該当するパーテーション キー照会条件があるのにもかかわらず、なぜ全体パーテーションを問い合わせたのでしょうか?
理由はREGDATEカラムはVARCHAR2(8)でもWhere節照会時TO_DATE関数を使用して、パーテーション キーカラムと照会条件のデータ タイプがそれぞれ違ってOracle型変換優先順位によって内部的に型変換が発生したためです
パーテーション キーカラムに内部型変換が成り立つ場合、単一パーテーションだけ照会できなくて全体パーテーションを読まなければならないので、ともするとDBサーバーに性能問題を引き起こすことがあるので格別に注意しなければならないことになります。
それではパーテーション キーカラムに対して内部的な型変換が発生しないようにWhere節条件を変更して、SQLの実行計画を確認してみるようにします。
（２）テスト[2]. カラム変形なしで効率的なパーテーション テーブル照会
SELECT /*+ FULL(PART_T1) */
*
FROM PART_T1
WHERE regdate BETWEEN '20101101' AND '20101131';-->カラムと同じデータ
タイプで照会
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 2 | 44 | 2 (0)| 00:00:01 | 11 | 11 |
|* 2 | TABLE ACCESS FULL | PART_T1 | 2 | 44 | 2 (0)| 00:00:01 | 11 | 11 |
--------------------------------------------------------------------------------------------------実行計画でPstartとPstopの値を確認すれば、単一パーテーション テーブルだけ問い合わせたとのことがわかります。
今までカラム変形により発生しうる性能問題に対して調べてみました。 カラム変形による性能問題はほとんどのSQL作成時カラムのデータ タイプを確認しなかったり失敗によって発生する場合が多くあります。
カラムの型変換によって発生する性能問題はテーブル/カラム/インデックスの情報と照会条件の変数値だけで確認するならば比較的簡単に改善できる問題が多いので、ここで調べてみた内容をよく活用するように願います。
2回シリーズでお送りしました「カラム変形によるSQL性能問題の理解する」は、いかがでしたでしょうか？次回からは、「照会パターンに合うようにSQL実行計画を分岐すること」と題しまして、2回のシリーズでお届けいたします、ご期待ください。では、See You ^^
