C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[2]: ACCESS FULL にならないSQLの書き方について

分類:[.NET 全般]　

ひろしと申します。

データベースはOracle 11.2.0.2.0 を使用しています。

ID CHAR(10)　(PKEY)
NAME VARCHAR(20)

上記のようなテーブルがあったとします。

画面から ID の指定があった場合は、ID 条件を指定して、指定がない場合は全件取得という仕様があります。
（言語はVBです）

そこで、プログラム的には、IF文で分岐すると、SQLエディタのSQLをそのまま貼り付ける事が出来なくなってしまうため

select * from emp
where id > :id_From or :id_From is null

というSQLで分岐なしにしようと考えたのですが、実行計画を見てみると、プライマリーキーで検索しているのにもかかわらず
TABLE ACCESS FULL
になってしまいます。

VBのIf文で分岐することなく、インデックスが使われるSQLの書き方はあるでしょうか。

If分で分岐すると以下のようになると思います。
sb.AppendLine("select * from emp")
if Not String.IsNullOrEmpty(id_From) Then
sb.AppendLine("where id > :id_From")
End If

分岐なしにすると以下のようになり、エディタのSQLをそのまま貼り付けることが出来ます。
sb.AppendLine("select * from emp")
sb.AppendLine("where id > :id or :id is null")

よろしくお願いします。

※質問用にSQLをシンプルにしていますが、実際にはもっと複雑なSQLとなります。

■No97213 (ひろしさん) に返信

> 画面から ID の指定があった場合は、ID 条件を指定して、指定がない場合は全件取得という仕様があります。

SQL 文を例えば以下のようにパラメータ化して（@ID というのは SQL Server, MySQL の場合。ODP.NET で @ID の
ようにできるのかは自分は分かりません）、

... WHERE (@ID='ALL' OR ID=@ID)

指定がない場合はパラメータに "ALL" を代入するというのはいかがですか？

＃SQL 文を StringBuilder で組み立てるのは止めた方が良いのでは？　可動性が落ちて間違いが見つけにくくなる
だけで何のメリットもないと思いますけど。

×: 可動性
〇: 可読性

でした。

2021/04/16(Fri) 12:13:36 編集(投稿者)

■No97213 (ひろしさん) に返信
> ※質問用にSQLをシンプルにしていますが、実際にはもっと複雑なSQLとなります。
とはいえ今回は、提示されたシンプルな構造の場合の話に限定して良いのですよね。

データ量が多いテーブルから少量のデータのみを取り出すようなケースでは
TABLE ACCESS FULL は非効率的ですが、総件数が多いテーブルの場合や
読みだすデータが大量の場合は、あえて TABLE ACCESS FULL に誘導した方が適切なケースもあります。

> 実行計画を見てみると、プライマリーキーで検索しているのにもかかわらず
> TABLE ACCESS FULL
> になってしまいます。
少なくとも、今回の SQL においては :id_From が NULL の時には全件走査以外の選択肢は無いわけで、
それが OR で繋がっている以上、事前の実行計画は TABLE ACCESS FULL にならざるを得ないかと。

手元に環境が無いので試せませんが、ひとまず OR を外して
　WHERE id > CAST(NVL(:id_From, RPAD(CHR(0), 10, CHR(0)) AS CHAR(10))
のような問い合わせにすれば、Oracle 11.2 環境では
TABLE ACCESS BY INDEX ROWID になるものと予想します。12.1 以降であれば
TABLE ACCESS BY INDEX ROWID BATCHED になるでしょう。

ただし、データ量が極端に多い場合の全件取得が目的の場合は要注意。
TABLE ACCESS FULL であればマルチブロックリードが行われますが、
TABLE ACCESS BY INDEX ROWID だとシングルブロックリードとなってしまうため、
キャッシュに載っていないブロックの物理 I/O が数倍に増えることがあるためです。

TABLE ACCESS BY INDEX ROWID BATCHED が使えるバージョンであれば、
パラレルリードされる見込みがあるものの、11.2 では非サポート…。

それが速度低下につながるかは、実環境で試さないと分からないですけれどね。
(大量のメモリを積んでキャッシュに載せている場合は、物理 I/O が無くなります)

> そこで、プログラム的には、IF文で分岐すると、SQLエディタのSQLをそのまま貼り付ける事が出来なくなってしまうため
一回の問い合わせ中に、WHERE の異なる 2 パターンの問い合わせを用意しても良いのであれば、
PL/SQL の REF CURSOR で受け渡しするようにすれば、PL/SQL の IF で分岐できるかと思います。

■No97213 (ひろしさん) に返信
> 分岐なしにすると以下のようになり、エディタのSQLをそのまま貼り付けることが出来ます。
> sb.AppendLine("select * from emp")
> sb.AppendLine("where id > :id or :id is null")

案１
select * from emp where id > :id

指定がない場合は:idに最小値を入れる。
インデックスを使用することになるので
全件取得が遅くなるかも。

案２
select * from emp where id > :id union all
select * from emp where :id is null

ORを無くしてみました。

■No97214 (WebSurfer さん) に返信

ひろしです。

コメントありがとうございます。

> 指定がない場合はパラメータに "ALL" を代入するというのはいかがですか？
こちらを試してみましたが、やはり、TABLE ACCESS FULL になってしまいました。
ORACLEとMySqlでの違いなのでしょうか・・・

> ＃SQL 文を StringBuilder で組み立てるのは止めた方が良いのでは？　可動性が落ちて間違いが見つけにくくなる

確かにその通りだと思います
ただ、最新バージョンの Visual Studio では

cmd.CommandText = "
select *
from xxxxx
"

の様な書き方ができますが、会社で使っているのは Visual Studio 2010 なので
文字列の途中で改行が出来なくてとても不便です。

ありがとうございました。

■No97216 (魔界の仮面弁士さん) に返信

ひろしです。

コメントありがとうございます。

>>実行計画を見てみると、プライマリーキーで検索しているのにもかかわらず
>>TABLE ACCESS FULL
>>になってしまいます。
> 少なくとも、今回の SQL においては :id_From が NULL の時には全件走査以外の選択肢は無いわけで、
> それが OR で繋がっている以上、事前の実行計画は TABLE ACCESS FULL にならざるを得ないかと。
私はここの認識が不十分でした。
実行計画というのは、パラメータを考慮していない状態での計画と言う事ですね。
私は、OracleCommand のパラメータがどのように設定されているかによって Oracleが解析してくれるものと思っておりました。

ちょっとサンプルが悪かったのですが、よくあるパターンとして
例えば　売上伝票　というテーブルがあって、数年分で数百万件のデータが入っています。

その中から、画面では　

入力日　 FROM～TO
伝票日付 FROM～TO

入力日付を条件とするのか、伝票日付を条件とするのか２択あります。
どちらかの From を入力した場合は、To も必須の条件となります。
実際に抽出される件数は 100万件以上のうちの数百件程度になるでしょうか。

そうした場合

WHERE (入力日 >= :入力日FROM 　OR :入力日FROM IS NULL)
AND (入力日 <= :入力日TO 　OR :入力日TO IS NULL)
AND (伝票日付 >= :伝票日付FROM OR :伝票日付FROM IS NULL)
AND (伝票日付 <= :伝票日付TO OR :伝票日付TO IS NULL)

と記述できれば、一番シンプルで見やすいSQLになると考えています。
さらに、エディタでＳＱＬをゴリゴリ直したあと、ＶＢに反映させるのも簡単です。

IF分で書くと

IF 入力日付FROM 条件がある場合
And 入力日 >= :入力日FROM
End if
If 入力日TO 条件がある場合
And 入力日 <= :入力日TO
End if
If 伝票日付FROM の条件がある場合
And 伝票日付 >= :伝票日付FROM
End if
If 伝票日付TO の条件がある場合
And 伝票日付 <= :伝票日付TO
End If

となって、エディタで編集したあと、ＶＢに反映させようとしたときに
非常に苦労します。

> 手元に環境が無いので試せませんが、ひとまず OR を外して
> 　WHERE id > CAST(NVL(:id_From, RPAD(CHR(0), 10, CHR(0)) AS CHAR(10))

確かに、以前以下のような条件を試してみたことがあります。

日付FROM の指定がない時は "00000000" ※Date型ではなくCHAR8桁を使用しています。
日付TO の指定がない時は "99999999"
に変換して

伝票日付 BETWEEN 伝票日付FROM AND 伝票日付TO

という条件にすると、確かにインデックスが使われ、全てOKと思ったのですが・・・

例えば、伝票日付がNULLで入力日付が設定されている伝票があったとして
入力日付を条件にした場合、

入力日付 BETWEEN '20210401' AND '20210430'
And 伝票日付 BETWEEN '00000000' AND '99999999'

となると思うのですが、その場合、伝票日付が Null のデータが除外されてしまう事がわかりました。
（これは拾えないとNGです）

結局はやはり、条件が指定されていない場合
WHERE (入力日 >= :入力日FROM 　OR :入力日FROM IS NULL)
上記の書き方で、インデックスが使われるようにする方法はないのかと考えておりました。

ただ、実行計画が、OracleParameter の設定値を考慮してくれないのであれば不可能っぽいですね。。。

> PL/SQL の REF CURSOR で受け渡しするようにすれば、PL/SQL の IF で分岐できるかと思います。
本番環境へのリリース等もあるので、外注の身の私としては
その手の改良はちょっと厳しいです。

大変ためになりました。
ありがとうございました。

■No97222 (ひろしさん) に返信

> こちらを試してみましたが、やはり、TABLE ACCESS FULL になってしまいました。
> ORACLEとMySqlでの違いなのでしょうか・・・

具体例としては以下の記事のようにして期待通りになっているのですが・・・

DropDownList を使って絞込み
http://surferonwww.info/BlogEngine/post/2011/07/17/Showing-records-selected-by-DropDownLists-into-GridView.aspx

質問者さんのケースで何故うまくいかないのかは情報が少なすぎて分かりません。

■No97217 (furu さん) に返信

ひろしです。
コメントありがとうございます。

> 案１
> select * from emp where id > :id
> 指定がない場合は:idに最小値を入れる。
こちらは、魔界の仮面弁士さんのコメントにも書いたのですが
id がNullの場合もあって、最小値だと取得できなくなってしまうのでNGでした。

サンプルをもっとしっかり書いておくべきでした。
申し訳ないです。

> 案２
> select * from emp where id > :id union all
> select * from emp where :id is null
こちらは、今試してみましたが TABLE ACCESS FULL になってしまうようです・・・
あと、実際には結構複雑なSQLなので、UNION ALL だとちょっときついです。

ありがとうございました。

■No97224 (WebSurfer さん) に返信

ひろしです。
コメントありがとうございます。

> 具体例としては以下の記事のようにして期待通りになっているのですが・・・
>
> DropDownList を使って絞込み
> http://surferonwww.info/BlogEngine/post/2011/07/17/Showing-records-selected-by-DropDownLists-into-GridView.aspx
>
> 質問者さんのケースで何故うまくいかないのかは情報が少なすぎて分かりません。

こちらの記事を拝見しました。
もちろん、抽出という意味では問題なく動作しました。

ただ、やはり検索的には全件検索が動いてしまいます。

実際には EXPLAIN PLAN FOR を使って、以下のSQLを実行しました。L

select *
from emp a
where (:id = 'ALL' or a.ID >= :id)

その結果が

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 59 | 4130 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 59 | 4130 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"."ID">=:ID OR :ID='ALL')

Note
-----
- dynamic sampling used for this statement (level=2)

となりました。

EMPテーブルの ID が CHAR(3) で、001 ～ 999 までのデータが更新されており
パラメータを IDパラメータを 100 にしたところ、100以上のデータが正しく取得され、
ALL をセットすると全件取得出来たので、動作的には問題なしです。

一つ分かったのですが、OracleParameterを使用せず
:ID を '100' にリプレースかけて

select *
from emp a
where ('100' = 'ALL' or a.ID >= '100')

で実行計画を見てみると

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 900 | 63000 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 900 | 63000 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_C0012163 | 9 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"."ID">='100')

Note
-----
- dynamic sampling used for this statement (level=2)

という結果となり、インデックスが使われました。

OracleParameter より、リプレースした方がいいような気がしてきました・・・

ありがとうございました。

C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[2]: ACCESS FULL にならないSQLの書き方について

過去ログには書き込み不可