ORDER BY の項目を SELECT する効果

ORDER BY の項目を SELECT する項目に含めるか否かで、速度や
メモリ消費の点で何らかの差がでるでしょうか？どちらが望ましい
という点も含めてプロフェッショナルの方の回答をお待ちしています。

No.3 ベストアンサー 回答者： chukenkenkou 回答日時： 2007/12/09 00:31

長い間、標準SQL及び多くのRDBMSでは、「ORDER BYで指定する項目は、SELECTの選択リストで指定した項目でなければならない」という制限がありました。

「SELECTの選択リストで指定していない項目でも、ORDER BY指定が可能」となったのは、比較的、最近のことです。
現在でも、「ORDER BYで式を指定」した場合などで、RDBMSにより、最適なアクセス計画を生成できないといった問題があります。

記述したSQLで、性能が出せるかどうかは、使用しているRDBMS、バージョンにより、大きな違いが生じます。
ORDER BYに関しては、メモリ消費よりは、インデクスを有効活用し、ソートを抑止できるかどうかが、利用者側にはっきり見えるところです。

自分で使うRDBMS及びバージョンで、どういうアクセス計画が生成されるかという、分析は必須です。

この回答へのお礼

かなり専門的なレスがつきました。ありがとうございます。

>「SELECTの選択リストで指定していない項目でも、ORDER BY指定が可能」となったのは、比較的、最近
この部分を具体的に教えてください。10年前からRDBを使用していますが
そのような制限に出会った記憶が無いので…。例えばOracle7ではどうだったか
SQLServerではいつ頃から可能になったか、等の情報があると嬉しいです。

>ORDER BYに関しては、メモリ消費よりは、インデクスを有効活用し、
仰るとおりです。ただ、取得件数が少ないと問題なく、逆に多いと
システム・エラーとなっている事象が発生している以上、メモリ上の
問題が発生しているのでは？と疑っているわけです。かならずしも
インデックスの付いた列をソートできるとは限りませんし…。

例えばSELECTに記述した方が、ソート領域を節約できる可能性があるとか
そういう情報に興味があります。個人的には「そんなはずは…」と
思っています。そういうSQLチューニングもあまり聞きませんし。

お礼日時：2007/12/09 15:02

No.5 回答者： chukenkenkou 回答日時： 2007/12/11 20:31

RDBMSは、DB2でしょうか？

ここでは、DB2利用者の書込み＆閲覧はそれ程多くなく、内容的にも有用な情報を答えられる人はいないように感じられます。

IBMとのサポート契約は、どうなっているのでしょうか？SEやRDBMS開発部署に、確認できないのでしょうか？

以下のようなサイトで、情報を入手すれば、あるいは有用な情報を得られるかも知れません。

http://www-06.ibm.com/jp/software/data/developer …
http://www-128.ibm.com/developerworks/forums/ind …

この回答へのお礼

回答ありがとうございます。IBMへの情報提供を求める動きは既にありますが
一般論として、ORDER BYの項目をSELECT句に書くのがメモリ効率上、あるいは
パフォーマンス上、何らかのメリットがあるという見解が存在するか？
ということを知りたいと思って質問させていただきました。
個人的な見解でも結構ですのでお聞かせ願えれば幸いです。

お礼日時：2007/12/11 22:00

No.4 回答者： chukenkenkou 回答日時： 2007/12/11 06:13

#3です。

各RDBMSにより、標準SQLになっていなくても、独自機能として早い時期から実装している場合もあるので、各RDBMSでの実装時期は、把握していません。
逆に特定のRDBMSの仕様が、のちに標準SQLに取り入れられるということも、少なくありません。

>取得件数が少ないと問題なく、逆に多いとシステム・エラーとなっている事象が発生している

インデクスによるソート抑止ができない場合、作業用のメモリやファイルを使うことになるので、多量件数のソートが発生すると、RDBMSやOSでエラーになってしまうことは何ら不思議な話ではありません。
ちなみに、ソートは「ORDER BY」だけでなく、「GROUP BY」や「DISTINCT」、インデクスの定義（または、再作成）といった操作でも発生します。

>インデックスの付いた列をソートできるとは限りませんし…。

大規模なシステムでは、検索条件での絞り込みに加え、「ソート抑止」できるようにインデクスを設計します。

この回答へのお礼

回答ありがとうございます。

> 各RDBMSでの実装時期は、把握していません。
わかりました。

>インデクスによるソート抑止ができない場合、作業用のメモリやファイルを使うことになるので
仰る通りです。しかしSELECT句に書くとエラーを回避できる現象に遭遇しています。
その現象は理解できる現象なのか、それとも単なるバグに近い現象なのかを
知りたいと思っています。

>大規模なシステムでは、検索条件での絞り込みに加え、「ソート抑止」できるようにインデクスを設計します。
仰るとおりです。しかし理想と現実にかなりギャップがあるのは致し方ない事実です。

お礼日時：2007/12/11 20:04

No.2 回答者： wolf03 回答日時： 2007/12/08 17:57

エラーになるのと速度が出ないは全く関係ないでしょう。

取得件数が多くなった際にエラーになるのはSQLが悪いのではなく、別の不具合なのでは？

この回答へのお礼

でもSELECT句に含めるだけで問題なく取得できるようになります。
No.１では、SELECT句に含めるメリットは無いという回答がつきましたが
そうでない可能性があるわけですよね。そこのシビアな部分をご存じの
プロフェッショナルの方の回答が欲しいです。

お礼日時：2007/12/08 19:06

No.1 回答者： wolf03 回答日時： 2007/12/08 13:22

基本的にはindexが物を言う世界です。

適切なindexが設定されて使われれば速くなるでしょう。

使わないのにselectに含めればメモリの無駄、さらに無駄な転送が増える分遅くなるでしょう。

この回答へのお礼

DB2で取得レコード数が多いとき、SELECT句に含めると正常に取得するのに
SELECT句に含めないとシステム・エラーとなる現象が発生しています。
レコード数が少ないと、いずれの書き方でも正常取得します。
この現象は上記回答と矛盾しないでしょうか？

お礼日時：2007/12/08 13:46