- PR -

SELECT結果をINSERTした時ORDER BY が無視される

1
投稿者投稿内容
ハルシオン
常連さん
会議室デビュー日: 2005/03/29
投稿数: 24
投稿日時: 2008-06-09 23:07
いつもお世話になっております。
Oracle9iのSQL文について質問させていただきます。

あるテーブル(TABLE_A)に別のテーブル(TABLE_B)から取得した結果をINSERTしたいのですが
その際、取得結果をソートする必要があります。

以下のようなSQLを作成し、実行しました。

コード:

--SQL①↓

INSERT INTO TABLE_A
SELECT 
　　　　aaa
	,bbb　…
FROM
	TABLE_B
WHERE
	aaa = '1'
ORDER BY
	aaa ASC


結果、取得結果は表に格納されるのですが、表をSELECTすると、
ORDER BY で指定したソート条件でデータが格納されていないことが判明しました。
テーブルが空の状態で上記SQL(SQL①)を発行し、以下SQL(SQL②)で
確認しました。
結果はソートされていない状態でした。
(元表の並び方とも異なる状態でした)

コード:

--SQL②↓

SELECT * FROM TABLE_A


※SQL文は全てSQLPlusで実行して確認しました。
　SQL文にヒントは使用していません。
　TABLE_Aの一部の項目にINDEXが存在します。
　上記SQL①の１行目の「INSERT INTO TABLE_A」をコメントにして
　SQLを実行すると、ソートされた結果が取得できているので、
　SELECT文に間違いは無いと思われます。

質問させていただきたい事は、以下の３点です。

１）SQL①のようにSQLの取得結果をソートしてINSERTする際
　　このような事が起きる原因として考えられることは何かあるのでしょうか。
　　気をつけるべき事等あればご指摘ください。

２）何度かSQL②を実行してからSQL①を実行すると、結果の並び順が
　　若干変わることがありました。
　　このことから、キャッシュがソート結果に影響しているのではないかと考えましたが
　　この推察は間違っているでしょうか。
　　また、これを確認する方法はありますか。

３）そもそもSQL①のINSERT方法、またSQL②の確認方法が間違っているのでしょうか。

ヒントになりそうなことでも結構です。
何か思い当たることがある方がいらっしゃいましたらぜひご教示頂けたらと思います。
何か不足している情報があればご指摘ください。

宜しくお願いいたします。


Java僧
ぬし
会議室デビュー日: 2003/11/06
投稿数: 261
投稿日時: 2008-06-09 23:14
ORDER BY 無しのSELECTは出力順は不定です。(RDBMSが一番都合のいい順で出します)
INSERT結果がどの順で保存されるかは不定です。(RDBMSが一番都合のいい順で保存します)
RDBMSは結果の順を不定にすることで性能を維持しています。
＃昔、商用RDBMSの開発に携わったことがありますが、0.01秒でも速くなるよう内部にはさまざまな工夫がありました。

[ メッセージ編集済み 編集者: Java僧 編集日時 2008-06-09 23:33 ]
末記人
大ベテラン
会議室デビュー日: 2005/12/05
投稿数: 233
お住まい・勤務地: あわにこ
投稿日時: 2008-06-09 23:23
こんばんは

一般的なRDBにおいて、格納順という概念はありません。
格納しやすい場所に格納したり、インデックス値によって格納したり
実装によってさまざまです。
逆に順列指定されていないときの取り出し順も未定義です。
取り出しやすい場所から取り出したり、キャッシュされている部分を
先にとりだしたりこちらも実装次第です。

想定した順序で取り出したければ、選択時に順列をかける必要があります。

ORDERをかけた列も INSERTしているなら取り出し時にも ORDERをかけて
あげればいいだけだと思います。
あまり既定の動作というのは期待しないほうがいいと思いますよ。

後から見たときに、ORDERが書いてあるのとないのでは、コードの解析時に
かかる時間も短縮できると思うし、テストでは動いたけど本番では動かない
などという原因不明なエラーも防げると思います。
ハルシオン
常連さん
会議室デビュー日: 2005/03/29
投稿数: 24
投稿日時: 2008-06-09 23:42
>Java僧さん

そうなんですね。
知りませんでした。
SELECT結果でCREATE TABLE した際に ORDER BY した結果どおりにデータが並んでいた気がしたので、INSERT でも同じように出来るのだと思ってしまいました。
CREATE TABLE の際ももしかしたら正しくソートされていなかったのかもしれません。
明日確認してみます。
ありがとうございます。

>末記人さん
やはりそうですか。
INSERT時にINDEXを無視した形で(ぐちゃぐちゃに)データが挿入されるのであればINDEXが無駄かなあと思ってINDEX通りにソートしてみたのですが、そもそもその考えが間違っていたということになるのでしょうか。
ともかく、INSERT時のORDER BYは無視される挙動はRDBであれば当たり前の動作だったということですね。

不勉強でした。今日一日悩んでいたのでとてもスッキリです。
ありがとうございます。
末記人
大ベテラン
会議室デビュー日: 2005/12/05
投稿数: 233
お住まい・勤務地: あわにこ
投稿日時: 2008-06-10 00:23
引用:

ともかく、INSERT時のORDER+BYは無視される挙動はRDBであれば当たり前の動作だっ
たということですね

INSERTはORDER BY の順に行なわれている場合もあると思います。
シリアル値を付加した場合、意図した順序でシリアル値が付加されていたので。。。
(MySQLでですが)

※※領域や順序が順になっているという意味ではないです。あくまでインサートが
ORDER の順で行なわれたという意味で書いています。

ただ先述の通り格納場所の問題、取り出し順序の問題があるので取り出し時には
追加した順で取り出されない、順列を指定しなかった場合の既定の取り出し順序
は保証されていないということです。



[ メッセージ編集済み 編集者: 末記人 編集日時 2008-06-10 15:20 ]
未記入
会議室デビュー日: 2006/03/22
投稿数: 19
投稿日時: 2008-06-10 13:43
オラクルではデフォルトの表はヒープ表です。
ヒープ表は大きな領域を割り当てて格納場所はオラクルが勝手に決定します。
格納場所はキャッシュ、空きリスト、パラレル処理やダイレクパスインサートなどの要因で最適化されます。
ですから順番が保証されません。
格納場所を限定？指定？する表がないわけでもありません。
索引構成表、クラスタ化、パーティション化といったものがあります。
それぞれメリット、デメリット（パーティション化は有料）がありますが、
書ききれないので省略します。
deanna
大ベテラン
会議室デビュー日: 2004/08/23
投稿数: 118
投稿日時: 2008-06-10 23:47
SELECTとINSERTで混乱してませんか？
INSERT ... SELECT ... ORDER BY ...
ではINSERTはORDER BYの指定順に挿入されます。
ですが、物理的に挿入順に格納されるとは限りません。

SELECTでORDER BYなしでは挿入順にとれないのは当然です。
どんなRDBMSも保証してません。て、言うか、ORDER BYの記述のないSELECTの順序はわかりませんと書いてあるはず。
ありがちなのは、偶然、ある順序で読めたケースで、順序が保証されていると思い込む人が多いことかな。
1

スキルアップ／キャリアアップ（JOB@IT）