Q&A
ご解答ありがとうございます。
>挿入のみ行われるようなテーブルなのか、更新や削除も行われるテーブルなのか
更新・削除が行われるテーブルかによってSQLの実行時間が異なるのは始めて知りました！
また、恥ずかしながら実行計画が作成されるタイミングや、その後どうなるかなどあまり考えたことがありませんでした。
状況に応じてSQLをチューニングしていく必要があるということをしみじみと実感いたしました。
実行計画に関してもまだまだ薄知であり、勉強する必要を実感したしました。
ありがとうございます！
ISOかANSIか？
現在、業務で作成しているSQLが下記のように作成しているのですが、Inner Join等を書いてある場合と、書いていない場合があり、どちらが良いのか迷ったので質問させて頂きました。
恥ずかしながら、ISOかANSIという名称も先程知りました。
また、あまり詳しくないのですが、SQLはoracleから入ったので、自分はパターンAで書いてしまいます。
ポイントとしては、結合部と条件部の切り分け、可読性、可変性、メンテナンス性、作りやすさといったところでしょうか？そこに着眼してみたときに、どちらのパターンが優位なのでしょうか？
- Aパターン
sql
1select 2 テーブルA.カラム１ 3 ,テーブルB.カラム２ 4from 5 テーブルA 6 ,テーブルB 7where 8 テーブルA.カラム３ = テーブルB.カラム３
- Bパターン
sql
1select 2 テーブルA.カラム１ 3 ,テーブルB.カラム２ 4from 5 テーブルA 6 inner join テーブルB on テーブルA.カラム３ = テーブルB.カラム３
DB情報
私が普段使用するDBは下記の２つなので、その中ではどのようなものなのか、また、SQL ServerやMySqlでのパターンも知識として知れたらなと思っていますので、ご意見等があればお教えいただきたいです。
- Oracle
- postgres
追記
解答から発生した疑問について追記させて頂きます。
やはり「結合してから絞り込む記述」より「絞り込んでから結合の記述」の方が速度的にも早いのでしょうか？
深く考えたことは無かったのですが、どちらの記述を行ってもSQL解析の時点で最適な方法に変換されると思っているのですが、ど
うなのでしょう？
With句を用いたものや、InnerJoin句内のWhere句で絞り込んだ結果を結合する場合と、そこではWhere句は書かずに結合し、SQL全体として条件を指定して絞り込む場合とで、速度等に影響が及ぶのでしょうか？
漠然とですが、SQLの実行時に最適化されて、結果同じ速度になるかと思っていますが、この認識はあっていますか？
回答5件
0
FROMでテーブルを列記しても、INNERJOINしてもほぼ同じと考えてよいでしょう。
JOINの強みはJOIN後のデータに対してWHERE句をつかって絞込ができることです。
LEFT JOINした場合など、二重で条件設定させる場合がよくあります。
あとは効率というよりは認識の問題ですね
JOINの良さのひとつは主従関係がイメージしやすい
もう一つは複数連結する際の連結条件が見やすい
などがあげられると思います
投稿2017/07/20 03:34
総合スコア114821
0
追記に対する解答です
「結合してから絞り込む記述」より「絞り込んでから結合の記述」が早いかは一概に言えず
実行計画がない状態で全てオプティマイザまかせ（ヒント句などを利用しない場合）にした場合
駆動表がどのテーブルになるか、その時の索引の状況やレコード数、また
挿入のみ行われるようなテーブルなのか、更新や削除も行われるテーブルなのか
検索条件、ソート条件によってSQL実行時間が異なりますし
必ずしも最も高速で動作するようにSQLを解析してくれるとは限りません
またトランザクション系のテーブルに100件程度しかレコードがない状態で
10件抽出するSQLを実行、その時点で実行計画が作成され
そのままレコードが100万件超まで増えたとし、同じ検索条件で10件抽出するSQLが
実行されたとしても
レコード数が100件の時に作成された実行計画でSQLが実行されてしまうと
SQLの実行時間が遅くなってしまう可能性があります
あくまで私が初めにSQLを記述する方法として
「WITH句を利用してあらかじめ結合するテーブルを絞り込んでおき
その結果をINNER JOINで記述」の場合が多いだけです
※場合によって「結合してから絞り込む記述」SQLを記述する場合もあります
本当に速いSQLを求めるのであれば
想定されるレコード数や索引の状態、検索条件、ソート条件を考慮してSQLを記述し
想定されるレコード数をテーブルに格納した状態で性能試験を行って実行計画を取得
速度が遅い場合は実行計画からSQLが遅くしている原因の箇所を探し
SQLをチューニングするになると思います
投稿2017/07/21 05:26
総合スコア112
Oracleであれば
ALTER INDEX インデックス名 REBUILD
が何を行っているか？、何のために必要か？
統計情報とは？辺りを詳しく調べてみるといかがでしょう
お返事が遅くなってしまい、申し訳ございません。
その辺の知識が全く無く、お恥ずかしい限りです。
勉強するための取っ掛かりをお教えいただいたので、その部分からいろいろとDBについて勉強させて頂きます！
ありがとうございました！
0
最近のDBだとSQL解析が優秀でどちらで記述しても
あまり結果に差がなく、見た目と好みの問題になりつつあると思います
もちろん可読性・メンテナンス性も大事だと思いますが
取得元も同じ、取得結果も同じで記述方法のみ違うSQLが
ミリ秒単位で結果を返す場合と数分を要して結果を返す場合とを
何度も目にしてきています
もちろんコーディング規約等で可読性・メンテナンス性を考慮した
記述を強制した上で、速く結果を返すSQLが一番良いのでしょうが
何よりも速く結果が返却されるSQLが私としては正解なのかなと思います
何も絞り込み条件が無くテーブルを結合して利用すること少ないので
WITH句を利用してあらかじめ結合するテーブルを絞り込んでおき
その結果をINNER JOINで記述するのが個人的に最近は好みです
投稿2017/07/20 08:26
総合スコア112
ご解答ありがとうございます。
なるほど、やはり見た目や好みの問題になってくるんですね。
With句の使用に関しては確かに用いたほうが見やすくなると思います。
やはり「結合してから絞り込む記述」より「絞り込んでから結合の記述」の方が速度的にも早いのでしょうか？
深く考えたことは無かったのですが、どちらの記述を行ってもSQL解析の時点で最適な方法に変換されると思っているのですが、どうなのでしょう？
この件に関しては気になる部分でもあったので、質問に追記させて頂きます！
0
ベストアンサー
動けばパターンAでもパターンBでもどちらでも良いと思います。
私もOracle出身ですが、パターンBの方が好みです。
（理由は、yambejp様の記載内容と同じです。）
また、主従関係をよりイメージさせる場合、
INNER JOINではなくLEFT JOINにするくらいですかね。
パターンAを使うケース
ケース１）テーブルAとテーブルBの紐づけがない場合
（例）大容量のテストデータ作成時等、純粋に掛け合わせの件数をヒットさせたい場合
ケース２）テーブルAまたはテーブルBを抽出する結果が１件（または数件）だけで、JOINするよりも性能が良い場合
（例）抽出結果が結合するテーブルを抽出する条件になる場合等
ケース３）テーブルAとテーブルBが多対多の場合
※多対多のテーブルの結合は、
中間テーブルを作成して、
それぞれに対して、１対多にして、JOINした方が性能面は良いはずです。
追記に対する回答
やはり「結合してから絞り込む記述」より「絞り込んでから結合の記述」の方が速度的にも早いのでしょうか？
⇒テーブル設計や実際のデータによって異なります。
どちらの記述を行ってもSQL解析の時点で最適な方法に変換されると思っている
⇒SQL文、データベースやバージョンによって、
解析の仕方、最適な方法の算出方法は変わってきます。
データベースのSQL解析が間違えていたり、最適な方法が違う場合もあります。
SQLチューニングは奥が深く経験が必要なので、
追及すると本１冊じゃ足りません。
（大容量のデータを取り扱う現場でなければ、そこまで気にする必要はないかもしれません）
With句を用いたものや、InnerJoin句内のWhere句で絞り込んだ結果を結合する場合と、そこではWhere句は書かずに結合し、SQL全体として条件を指定して絞り込む場合とで、速度等に影響が及ぶのでしょうか？
⇒イメージが共有しきれてないかもしれませんが、
「Where句は書かずに結合」というのを、全件結合（掛け合わせ）してから、絞り込むという意味であれば、テーブルのINDEXが効かないため、遅くなります。
SQLの実行時に最適化されて、結果同じ速度になるかと思っていますが、この認識はあっていますか？
⇒検索結果が同じでも、結合方法・EXISTS句・副問合せ等でSQL文が違ければ、
速度も違う可能性は多々あります。
投稿2017/07/21 09:18
総合スコア760
ご解答ありがとうございます。
「動く」という観点からすれば、パターンA・パターンBに関してはやはり好みの問題となりそうですね。
また、主従関係を明確にするのであればLEFT JOINを使用するという考え方は私には無かったので、少し驚きました！そのような考え方があるのですね！
>⇒イメージが共有しきれてないかもしれませんが、
>「Where句は書かずに結合」というのを、全件結合（掛け合わせ）してから、絞り込むという意味であれば、テーブルのINDEXが効かないため、遅くなります。
「掛け合わせて絞り込む」という意味でしたので、あっているかと思います。Indexが効かなくなるため遅くなるというのは理解しました！ありがとうございます。
> ⇒検索結果が同じでも、結合方法・EXISTS句・副問合せ等でSQL文が違ければ、
> 速度も違う可能性は多々あります。
SQLのチューニングに関しては、まだまだ勉強不足であると実感いたしました。ありがとうございます。
ただ、
> パターンAを使うケース
に関して、何故この3つのケースはパターンAを使うのかな？と素人ながら思ってしまいました。単純にこのケースの場合はパターンAで記述するのが好みということでしょうか？
それとも何かしらメリットがあるからということでしょうか？
> 主従関係を明確にするのであればLEFT JOINを使用する
⇒LEFT JOINの時には、結合条件のみを記載して、
INNER のような条件文は WHERE句に書いたりしますね。
FROM句のテーブルに対して、何件のデータを抽出するか、パッと見で見やすくするためです。
（INNER JOINの場合でもなれれば分かるので、完全に好みの問題です。）
> 何故この3つのケースはパターンAを使うのかな？
⇒期待を裏切るようで申し訳ないですが、ご認識の通り「好み」です（笑）。
分かりやすく言うと、記載した３つのパターンには、
『主従関係』が存在しないケースになります。
お返事が遅くなってしまい、申し訳ありません。
>FROM句のテーブルに対して、何件のデータを抽出するか、パッと見で見やすくするためです。
（INNER JOINの場合でもなれれば分かるので、完全に好みの問題です。）
パッと見で分かりやすいのと、もし分離するときにメリットになりそうですね！
>『主従関係』が存在しないケースになります。
納得いたしました！
確かに、結合の時は必ずしも主従関係があるとは限りませんからそのように書き分けるのも、作成者の意図が判りやすいかも知れませんね！ありがとうございます！
他のかたがたも分かりやすいご返答をして頂きましたが、パターンA等を利用するケースなど、例を提示していただき、無知な私にも分かりやすく教えて頂いたのでベストアンサーに選ばせて頂きます。m(_ _)m
0
Oracleでjoinが使えるようになったのはOracle9iからでしたがバグがひどくて、parallelヒントと外部結合を組み合わせると結果から対象になるはずのデータが足りなくなって、サポートにクレームをつけてパッチの提供を約束してもらったこともあります。joinはOracle9.2の最終パッチでまともに使えるようになったかどうか？
Oracleの結合演算子よりも、FROM句のOUTER JOIN構文を使用することをお薦めします。
ということで (+) は過去の遺産を継承できるように残っているので、なるべくouter joinを使った方が良い、ということらしいです。
投稿2017/07/22 01:17
総合スコア16415
ご解答ありがとうございます！
お返事が遅くなってしまい、申し訳ございません。
Oracle9.2まではまともに使えなかったのですか！？今では想像できませんが、大変だったのですね...
>ということで (+) は過去の遺産を継承できるように残っているので、なるべくouter joinを使った方が良い、ということらしいです。
確かに、最近の記事か何かで(+)は非推奨と書いてあったので、使わないようにしています。（過去の遺産で残っているものはありますがｗ）
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/07/20 05:08