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

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

掲示板トップ C# と VB.NET 入門 新規作成 利用方法 ツリー表示 トピック表示 ランキング 記事検索 過去ログ
ログ内検索
  • キーワードを複数指定する場合は 半角スペース で区切ってください。
  • 検索条件は、(AND)=[A かつ B] (OR)=[A または B] となっています。
  • [返信]をクリックすると返信ページへ移動します。
キーワード/ 検索条件 /
検索範囲/ 強調表示/ ON (自動リンクOFF)
結果表示件数/ 記事No検索/ ON
大文字と小文字を区別する

No.97213 の関連記事表示

<< 0 >>
■97213 　ACCESS FULL にならないSQLの書き方について
□投稿者/ ひろし ＠ -(2021/04/15(Thu) 21:32:22)

    分類:[.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となります。
親記事 /過去ログ168より / 関連記事表示
削除チェック/

■97214 　Re[1]: ACCESS FULL にならないSQLの書き方について
□投稿者/ WebSurfer -(2021/04/15(Thu) 23:23:20)
    ■No97213 (ひろし さん) に返信

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

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

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

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

    ＃SQL 文を StringBuilder で組み立てるのは止めた方が良いのでは？　可動性が落ちて間違いが見つけにくくなる
    だけで何のメリットもないと思いますけど。
記事No.97213 のレス /過去ログ168より / 関連記事表示
削除チェック/

■97215 　Re[2]: ACCESS FULL にならないSQLの書き方について
□投稿者/ WebSurfer -(2021/04/15(Thu) 23:25:26)

    ×: 可動性
    〇: 可読性

    でした。
記事No.97213 のレス /過去ログ168より / 関連記事表示
削除チェック/

■97222 　Re[2]: ACCESS FULL にならないSQLの書き方について
□投稿者/ ひろし ＠ -(2021/04/16(Fri) 21:11:47)
    ■No97214 (WebSurfer さん) に返信


    ひろしです。

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


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


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

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

    cmd.CommandText = "
    select *
    from xxxxx
    "

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


    ありがとうございました。
記事No.97213 のレス /過去ログ168より / 関連記事表示
削除チェック/

■97224 　Re[3]: ACCESS FULL にならないSQLの書き方について
□投稿者/ WebSurfer -(2021/04/16(Fri) 22:08:17)
記事No.97213 のレス /過去ログ168より / 関連記事表示
削除チェック/

■97226 　Re[4]: ACCESS FULL にならないSQLの書き方について
□投稿者/ ひろし ＠ -(2021/04/16(Fri) 22:52:26)
    ■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 より、リプレースした方がいいような気がしてきました・・・


    ありがとうございました。
記事No.97213 のレス /過去ログ168より / 関連記事表示
削除チェック/

■97216 　Re[1]: ACCESS FULL にならないSQLの書き方について
□投稿者/ 魔界の仮面弁士 -(2021/04/16(Fri) 09:44:46)
    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 で分岐できるかと思います。
記事No.97213 のレス /過去ログ168より / 関連記事表示
削除チェック/

■97223 　Re[2]: ACCESS FULL にならないSQLの書き方について
□投稿者/ ひろし ＠ -(2021/04/16(Fri) 22:02:09)
    ■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 で分岐できるかと思います。
    本番環境へのリリース等もあるので、外注の身の私としては
    その手の改良はちょっと厳しいです。


    大変ためになりました。
    ありがとうございました。
記事No.97213 のレス /過去ログ168より / 関連記事表示
削除チェック/

■97217 　Re[1]: ACCESS FULL にならないSQLの書き方について
□投稿者/ furu -(2021/04/16(Fri) 10:43:53)
    ■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を無くしてみました。
記事No.97213 のレス /過去ログ168より / 関連記事表示
削除チェック/

■97225 　Re[2]: ACCESS FULL にならないSQLの書き方について
□投稿者/ ひろし ＠ -(2021/04/16(Fri) 22:09:36)
    ■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 だとちょっときついです。


    ありがとうございました。
記事No.97213 のレス /過去ログ168より / 関連記事表示
削除チェック/



<< 0 >>
パスワード/

- Child Tree -