SQL質疑応答スレ 17問目 [無断転載禁止]©2ch.net
■ このスレッドは過去ログ倉庫に格納されています
このスレは
「こういうことをやりたいんだけどSQLでどう書くの？」
「こういうSQLを書いたんだけどうまく動きません＞＜」
などの質問を受け付けるスレです。
SQLという言語はISOによって標準化されていますが
この標準を100%実装したDBMSは存在せず、
また、DBMSによっては標準でない独自の構文が
追加されていることもあります。
質問するときはDBMS名を必ず付記してください。
【質問テンプレ】
・DBMS名とバージョン
・テーブルデータ
・欲しい結果
・説明
前スレ：
SQL質疑応答スレ 16問目
http://echo.2ch.net/test/read.cgi/db/1447160858/ >>581
ありがとう。
1) は動作結果からそう考えていたのですが、仕様的確認をとれて助かりました。
2) はそうですよね。SSMS の実行計画は見ていたのですが、そういう差があること以上のことは分かりませんでした。
教えていただいた方法でも調べてみます。 サブクエリでのORDER BYが、メインクエリでも有効なのかどうか、
ご存知の方教えていただけないでしょうか。
例えば、学生別点数テーブルから点数上位10名を取得したい場合、
SELECT
*,
ROWNUM as num
FROM (
SELECT *
FROM 学生別点数テーブル
ORDER BY 点数 DESC
)
WHERE num <= 10;
というソースだと、
サブクエリでは学生別点数テーブルが点数の降順でソートされるはずですが、
メインクエリでROWNUMが各レコードに通番(num)を振る際にもその並びが保持されているのか。
「サブクエリの並び順って、メインクエリでは保持されないんじゃなかったか？」と質問を受け、
回答に困っている状態です。
oracle 11gのリファレンスには、ROWNUMを利用した上記SQLで、点数上位10名が取得可能と読み取れます。
「ORDER BY句を副問合せに埋め込んでROWNUM条件をトップレベル問合せに置いた場合、行の順序付けの後でROWNUM条件を強制的に適用させることができます。たとえば、次の問合せは、小さい順に10個の従業員番号を持つ従業員を戻します。」
https://docs.oracle.com/cd/E16338_01/server.112/b56299/pseudocolumns009.htm
別の箇所で質問させてもらった際は、
「サブクエリーでソートされた結果を出力するだけ（joinやwhere条件など索引に関係するものが無ければ）なら、並びを変更される要因がないので、同じ結果となる」
との回答をもらっていますが、
もしよろしければこちらの有識者の方のご意見も頂けると幸いです。
よろしくお願い致します。 その回答した人は、ROWNUMがOracle固有の疑似列だということを知らなかったんじゃないかね。
ちゃんとそれがわかるように質問した？
あとOracleの場合でも、順序はROWNUMで得られるけれども出現順が保持されるとは言っていないと思う。 >>583
保証されないって思っておいた方がいいと思う
> たとえば、次の問合せは、小さい順に10個の従業員番号を持つ従業員を戻します。
これは
「小さい順に10個の従業員番号を持つ従業員」を戻します
であって
小さい順に「10個の従業員番号を持つ従業員」を戻します
じゃない
> 「サブクエリーでソートされた結果を出力するだけ（joinやwhere条件など索引に関係するものが無ければ）なら、並びを変更される要因がないので、同じ結果となる」
これOracleサポートの正式回答ならとりあえずは信じていいと思うけど、バージョン変わった時も保証されるのかを聞いておいた方がいいと思う
そもそも普通に
order by num asc
を追加しとけばいいだけじゃないの？ でたらめアドバイスだらけだが、OracleのFROM句の副問い合わせは、インラインビューと呼ばれ、並び替えも含めてインラインビューの結果そのものを、再度、検索する。
rownomをSELECT句に書いて、別名を付けるのは一般的ではない。 >>584,585
ご回答ありがとうございます。
別場所で質問した際は、oracle11gで、と前置きしていましたが、回答者の方がoracle固有の疑似列と知ってらっしゃったかは分かりません…
お二人に指摘頂いてるリファレンスの解釈については、確かにそのとおりですね。
公式の見解を聞きたいですが、サポート契約が無いのが辛いorz >>586の方もご指摘ありがとうございます。
確かに、リファレンスにもROWNUMにasで別名を付けるソースではないんですよね…
ただ、既に稼働しているシステムのソースがROWNUM別名形式でして、弄くる箇所は最小限にしたいなあ、と。 >>588
そもそもrownumをSELECT句に挙げる慣習はOracleにはない。
最近、SELECT句で選択していない項目は条件で使えないと思っていたベテランがいたがw 既に稼働しているシステムのソースをなるべく弄らない方針にすると
負債がどんどん貯まっていって見るのも嫌になるのでおすすめ >>583
外側のクエリでサブクエリの並び順を変更する要素がなければ、サブクエリの並び順のまま出力されるけど、それは現状の実装がそうなってるだけ
外側にもサブクエリと同じorder byを付けとけばオプティマイザが2回目のソートは不要って判断してくれるから心配なら付けといて損はない >>592
それはおかしい。
間違いは素直に認めろ。 インラインビューをサブクエリとわざわざ曖昧にしてるあたり分かっているとは思えない。 >>590
row_numberは記述が冗長になるのと、機能を拡張しすぎていてかえって分かりにくくなってしまう。 >>592
あなたはrownumはフェッチ時に番号が振られるのを理解してないのだと思う。
番号を付けるときにorder by rownumしても何の意味もない。 >>597
アウターにorder byがなくてもオラクルが順序を保証してくれると主張してるの？
最低限、何を主張したいのか分かるように書いてくれる？ >>596
Oracle方言よりANSI SQL使うに越したことはない >>598
rownumという列があるわけではない。
並び順はorder byを指定しないかぎりは保証しないというSQLの規格とは関係ない。
Oracleは内部的なことをSQLに書いているだけで、SQL ServerのTOP関数も内部では二度SELECTしていることが多い。 >>600
で？
保証してくれるの？してくれないの？ rownum絡みの仕様変更はずっとアナウンスされていない。 order byがなければ順序は保証しないとマニュアルに明記されているにも関わらず
rownum使ったtop-N最適化のケースは例外的に順序が保証されると言うならその根拠が必要だよね どういう思い込みなのか、rownumの変な使い方にこだわって、やってもかまわないが、変な人を貫きたいというので答えたのにな。
さらにオラクル社のいうこと、伝統、実装も突っぱねて、さらにオラクルでは、リソース食いのrow_numberを使用しようともしている。
どのRDBMSも標準化SQLが先にあって作られているわけではないから、標準SQLの方が性能も動きも読めないうえに、バグにあたる可能性がある。
さらに標準化SQLの構文を拡張してるから、どんどんわかりにくくなる。
なんで調べないのかな。
rownumは列じゃないんだよ。
インラインビューのorder byは意味があり、入れ子のrownum同士は別物。
オラクルではインラインビューのorder byは意味があり、インラインビューをSELECTするSQLではrownumをorder byをするのはかまわないが滑稽。
使い方は自由だからそう書きたいならそう書けばよい。 >>604
どういう初心者なのか知らないが、ソートしたものの中から、初めの何件かを取得するというSELECT文は、Oracle Databaseではインラインビューでソートしてrownumで絞り込む構文になっているだけ。
他のRDBMSでもOracleと同じものもあれば、ひとつのSELECT文で表現するものもある。
SQLの見た目で判断しているようでは、データベースをやめた方がいいよ。
どうしてソートしてないのに先頭何件かが取れるのか考えろよw 諦めろお前らはとっくに ID:oX0yCwR9 にマウント取られてんだよw >>604
Oracle Databaseは保証しないことは書いてあっても、保証するとは明言していないのが特徴。
こんな実績があって有名な話題で、さらにorder byを特殊なOracle構文にも摘要させたがるのは、世界初のリレーショナルデータベースのオラクルが憎くて、標準SQL側の攻撃のようだw 内部実装についていくら書いても
オラクルが順序を保証する根拠にならないよ
ただの無意味な長文 >>610
じゃ、保証してないんでしょ？
保証してんの？ >>586
一般的な書き方かどうかはどうでもいいけど
>並び替えも含めてインラインビューの結果そのものを、再度、検索する。
のソース教えてくれ
とくに、並び替えも含めてってとこな
これが正確で保障された動作なら、ORACLEにおいては保障されてるって話になるな >>613
なんねーよw
再度検索した後の結果セットの並び順が
その理屈でなんで保証されるんだよ Oracle初心者が調べもせずに教えろと騒ぐスレになったのか。 例の目的を達するにはこう書くと決まっているのに。rownumは値が固定のカラムではないと何度言ったらわかるのかw >>614
保障されないならrownumでトップnとか取れないって事になるぞ
あれは単に行のフェッチ順のはずだから
でも実際にはあちこちでORACLEのトップnのクエリの例としてあがってるからな
>>616
理屈も解らずにただこう書くと言われても納得できない方が普通じゃないかね だから保証されていることを保証しているのかとアホみたいに聞くからおかしなことになる。
オラクルはインラインビューにあとからorder byを導入して、レコードの並び順を固定している。
だからインラインビューをSELECTする場合にはorder byがいらない。
だいたいマニュアルでも書籍でもネット上でもさんざん説明されているのに、オラクルスレでもないここで聞いている行為がおかしい。 Oracleスレでやれよ
ここSQL全般スレなんだから方言とか実装の話なんかいらん >>617
top-Nを取得することと、取得した結果セットの並び順がどうなるかは別の問題だってのが分からない？ 下のが質問者が引用してたリファレンス原文だけど結果セットの並び順については何も言及されてない
the 10 smallest employee numbersがreturnされると書いてるだけ
For example, the following query returns the employees with the 10 smallest employee numbers.
This is sometimes referred to as top-N reporting:
https://docs.oracle.com/cloud/latest/db112/SQLRF/pseudocolumns009.htm#SQLRF00255 でもって>>593リンクの中に並び順が保証されてるわけじゃないと書いてある
so, while I cannot imagine
select rownum, x.* from (select ... order by ... ) x
not being sorted by rownum (by the order by), it is permitted to not be sorted.
... Why would it be permitted not to be sorted? ...
because there is no order by on the outer query. that is why.
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1137689100346245972 >>619
保証されてるというソースがあればみんな納得するよ >>619
> だからインラインビューをSELECTする場合にはorder byがいらない。
> だいたいマニュアルでも書籍でもネット上でもさんざん説明されている
ネットや書籍はどうでもいいからどのマニュアルのどこに載ってるか示してくれ >>625
マニュアルは外国に丸投げしたせいか、劣化が激しく、間違いや変な説明が多いので、マニュアルが正ともかぎらないし、あえて言及してないことも多い。 https://docs.oracle.com/cd/E16338_01/server.112/b56299/pseudocolumns009.htm
「SELECT * FROM (SELECT * FROM employees ORDER BY employee_id) WHERE ROWNUM < 11;
前述の例では、ROWNUM値はトップレベルのSELECT文の値です。これらの値は、副問合せ内のemployee_idによって行が順序付けられた後で生成されます。」 そのトップレベルのSELECTの結果の出力順が
インラインのORDER BYの順序と同一であることが
保証されてるかどうかって話じゃなかったの？知らんけど。 「ORDER BY句を副問合せに埋め込んでROWNUM条件をトップレベル問合せに置いた場合、行の順序付けの後でROWNUM条件を強制的に適用させることができます。
たとえば、次の問合せは、小さい順に10個の従業員番号を持つ従業員を戻します。
これは、上位N番のレポートと呼ばれることがあります。
SELECT * FROM
(SELECT * FROM employees ORDER BY employee_id)
WHERE ROWNUM < 11;
前述の例では、ROWNUM値はトップレベルのSELECT文の値です。
これらの値は、副問合せ内のemployee_idによって行が順序付けられた後で生成されます。」 >>628
rownumは主問い合わせの列で、その値は副問い合わせの結果と書いてある。
これを根底からくつがえすなら、あらゆるシステムで問題になっている。
これは内部的にソートされるから結果的に並び順が同じたぐいの話ではない。 うんだからROWNUMの値がインラインのORDER BYに紐付いてるのはわかったよ
そのROWNUM < 11なトップレベルの問合せはROWNUM順になるのが保証されてるのか
ってのが上の人らの指摘なんじゃないの？
個人的には保証されてるかどうかはどうでもいいんだけどズレた内容でドヤ顔されるのはうざいし インラインビューのorder byは無視されているのではなくて、order byの結果セットを返す仕様で、rownumは結果セットのフェッチ順。
構文が気持ち悪くみえる初心者がいるのは理解できるが、SQL ServerのTOPの方がよほど気持ち悪い。
レコードに位置の概念があったり、位置の概念がないと思いながらもソートがいきなりできると思い込むITオンチと話すときりがない。 誰でも知ってることをドヤ顔で永遠と書いた挙句に最後は八つ当たりww >>631
それは構文がそう見えるだけだろうが。
rownumの値は副問い合わせの結果で、かつrownumは主問い合わせの取得順。
Oracleの構文になんでそんなに文句があるのか。
外国人はデタラメが多いから気をつけろよ。 実際にこの話題は仕事でも若い人ほど、標準SQLが先にあって、OracleがそのSQLの仕様に沿って作られていると勘違いしているのか、引っかかって面倒なんだよな。
最近もSQLはこう書くとこうなるみたいな話をデータベースに詳しくない人間が言い始めると、面倒だからそれに従うしかない。 批判されていると思い込む恐怖心はわかるが、ネットとはこういうところです。 >rownumは主問い合わせの取得順
これは間違いないんだが、その肝心の主問い合わせの取得順が
副問い合わせのORDER BY順にならなければトップn取れないわけだが
（最終的な出力順はその通りとは限らんでも良いけど）
これがORACLEでは保障されてるってなら
マニュアルのどこに書いてあるか言えばいいだけなんだがな
ORACLEには仕様ではないが過去の実装からそうだと決めつけられてることが結構あって
ORACLE自身も影響範囲がでかすぎて、おいそれと変更ができなくなってる
これもその一つだと思うけどね やりとり見てないまま横から失礼だが
普通は row_number() over 〜 使うんじゃないの >>640
top-N集合の取得は保証されてるよ
それはリファレンスのrownumのところ読めばわかるじゃん >>641
そうね
今はfetch first/next使う どうでも良いからOracleのスレでやれって アフォどもが ソート条件のあるカーソルでフェッチ順がソート条件通りにならないという主張はただの知識不足としか思えない。 >>640
なんでオラクル社の説明をねじ曲げて解釈するのか？
例のrownumの件は、ドキュメントでも主問い合わせのrownumは、副問い合わせの結果が値で、主問い合わせの疑似列であると説明している。 >>645
もうやめたら？言えば言うだけバカがよけいにムキになるだけだぞw >>647
SQLは仕事上、間違いを主張しまくる人間が多いから、ここでもとにかく誤りを正さないと生活がめちゃめちゃになる。 >>646
>>623でオラクルのエンジニアが並び順は保証されてないとハッキリ言ってるじゃん
彼が間違ってて君が正しいと言える客観的根拠が全く提示されないから説得力ゼロ
今のところ”ソースは俺の頭の中(ｷﾘｯ”な状態 >>645
君以外誰一人としてカーソルのフェッチについての話なんてしてないよ >>650
SQLが何なのかわかってない。いい加減にしろ。 >>649
その人が間違ってるのに、なぜオラクルエンジニアの私が間違ってると決めつけられなきゃいけないのか？ >>646
主問い合わせのrownumは主問い合わせのフェッチ順のはずで
>副問い合わせの結果が値
なのは、「主問い合わせのフェッチ順が副問い合わせのorder by順」な結果に過ぎないんじゃね
で、今問題なのは
「主問い合わせのフェッチ順が副問い合わせのorder by順」
なのは実装なのか仕様なのかって話なんだが
（主問い合わせの出力順が主問い合わせのフェッチ順通りかどうかは別の問題）
ま、どっちにしても個別DBMSの話だから続きはオラクルスレでやってくれればいいけど 不思議なのは、Oracleがインラインビューにorder byの仕様をあとから追加して並び順を付けたのに、なぜ並び順が不定と言い張っているのか？
最近のマニュアルはどんどん表現が変わって、Oracle Databaseの歴史まで間違いを埋め込んでいる。
いまでも使われているバージョンなのに誤情報が書かれているから、仕方がない側面もある。
オラクル社がわけのわからない外国に仕事を投げてたり、データベースに詳しくない人間に仕事をさせるからおかしくなる。 >>655
それはOracleの構文が実装に近いから、標準SQLや他のRDBMSのSQLと比べるとそう感じるだけだと思うよ。
ソートはSELECTがネストするのに、構文ではネストしてないように見せているから初心者はそう思ってしまう。
結合もそうだけど、結合するとループのネストが発生することも知らないプロも多い。
あまりにしつこいから、時間があったら、実行計画を書くよ。 サブクエリでorderby使えるようにしているオラクルがバカなんだよ
オラクルなんて使うなよ >>654
別に決め付けてはないだろ
オラクル社のサポートチームの主張が間違ってて
自称オラクルエンジニアの主張が正しいことも可能性としてはゼロではないと思ってるよ
だからきちんとした根拠を提示しろって言ってんの
でも散々的はずれなことを書いてきて
やっと提示されたソースが>>627みたいなレベルだから
今のところ誰も信用しない サポートチームという言葉は間違ってたか
どちらにしろThomas Kyteが間違ってて君が正しいという根拠があるんなら出してみれば >>655←わかったけど意地はってる人
>>659←まだわかってないバカw Oracleスレに行かないのはOracleスレにもっと詳しい人がいるからやろなぁ >>661
はあww
馬鹿が追加されたか…
パッケージソフトにおける外部仕様と内部実装の違いの意味を理解してないから
内部実装について延々と無駄レスしてんだろ >>657
まさかと思うけど、一般的なRDBMSの実行計画で結合はネステッドループしかないと思ってるわけじゃないよな
で、オラクルがサブクエリのデータを扱う方法が、サブクエリの順序を保障した方法しか選択しない仕様なのかどうかの問題だと思うんだが
>>658
多くのRDBMSでサブクエリのOrder Byは意味を持つようになってると思うけどな
ただしそのサブクエリ内でトップなりオフセットなり取るっていう前提で
rownumみたいにそのサブクエリ内ではOrder Byに影響されない変な疑似列使うのが悪いだけで
昔ならともかく今どき書くべきクエリじゃないなと
ORACLEにはそんな過去のバッドノウハウがいっぱいあるけどな
>>661
別に意地張ってるわけでもなんでもなくて、初めから仕様か実装かって話をしてたはずなんだがね
その区別すらついてないから実装をグダグダと説明してたのかね マニュアルは信用ならん！
AskTOMの回答は間違ってる！！
俺が正しい！！！
またすごいのが来たね
ジャイアンも真っ青 残念、やっぱり>>664も本質的にはバカだったかw あえて最初の質問>>583に改めて答えてみる。
まず、>>583は構文が間違っているので以下のように直す。
（SELECT句でつける別名はWHERE句では使えない。）
SELECT t.*, ROWNUM as num FROM ( SELECT * FROM 学生別点数テーブル ORDER BY 点数 DESC ) t WHERE ROWNUM <= 10;
このとき、WHERE句のROWNUMは副問合せ内のORDER BYの順序で振られる。
これはマニュアルに明確に書かれている。
しかし、このSELECT文全体の結果が副問合せ内のORDER BYの順序で
返されることは保証されていない。
もっと言えばSELECT句のROWNUMがWHERE句のROWNUMと同じになるとも限らない。
WHERE句を評価した後、SELECT句を評価するまでの間に
順序を変えてはいけないと決まっているわけではなく、
順序が変わればROWNUMも変わる可能性がある。
（副問合せを評価した後、主問合せのWHERE句を評価するまでの間にも
同じことが言えるが、これはOracleが自分でマニュアルで規定している。） >>667
あなたの考えだと、インラインビューの並び順指定をわざわざ付けたのは、ROWNUMのためという主張になるが自分でもおかしいと思わないのか？
どうもSQLがどう処理されているのかをあまり知らないからそんなヘンテコな発想になる。
副問い合わせの結果セットの並び順が不定なら、結合もグループ化もできないことになる。 >>670
SQLがどう処理されてるかと、仕様として保証する動作かどうかは関係ない
それすら分からわないから一人でヘンテコりんな珍回答を繰り返す まあしかし、メーカーに「保障された実装」とメーカーが発表した「仕様」でどれほどの違いがあるのかという
保障された実装というものを考えると実装の区別もどうでもよくね
つうことでこの話終わりにしようぜ >>670
>副問い合わせの結果セットの並び順が不定なら、結合もグループ化もできないことになる。
ここでいう不定って、もとのORDER BY以外の順序の可能性があるってことで
たとえばハッシュジョインならハッシュキー順に、ソートマージならその結合キー順にならんでる可能性があるんじゃないかね >>672
仕様として約束された動作でなければ断りなく変わりうるから
明確にアナウンスされる仕様変更とはベンダー側もユーザー側も対応が違ってくる
その違いが重要じゃないと言い切れる場合を除いて区別は必須 >>673
もともとの質問者はリレーショナルデータベースでは、ORDER BYしないかぎりはレコードの並び順は保証しないという仕様から疑問に思っただけだと思われる。
内部的にソートが発生するSQLだったり、並び順があるインデックスが使用されたり、たまたま実際のデータ格納順が同じで、ORDER BYがあってもなくても結果が同じなのと、RDBMSの仕様はまったく異なる話。
そもそもORDER BYは、結果に対する並び替え指示だから、イメージとしてはSELECTを二度行っているようなもの。 >>583の元の質問をさせてもらった者ですが、話が大きくなり申し訳ありません。
議論を止める権利などは無いと思いますが、
私個人の問題としては(一旦は)解決しているので報告させて頂きます。 律儀やね
ORDER BY ROWNUM じゃなく
ORDER BY 点数 DESC って書いておくといいよ
サブクエリ内と同じ表現ね >>678
ありがとうございます。今後、修正する際は気をつけます。
また、幾つかのレスで「Row_number()を使えば」とアドバイス頂いています。
仰るとおりで、Row_number()であれば心配しなくて済みそうですが、
何年も前に客先に納品しているシステムでして勝手に修正は難しい状況です。
作るときに気が付いて欲しかったなあ・・・ いい加減オラクルスレでやれっつてんだ、このクソボケ こんな過疎板でキレることじゃねーだろww
約1名を除いてwww ■ このスレッドは過去ログ倉庫に格納されています
