Home　>　Oracleのワナとワザ

Oracle　ひとくちメモ
Oracleとの格闘の中で学んだ、ちょっとした事柄をまとめました


　まとめて実行したいときは　@

　毎日行う処理とか評価で毎回実行するSQLとかを、毎回SQL*Plusで打ち込むのはとっても大変。
テキストかなんかに保存しておいて、都度こぴぺしてもいいけれど、SQL*Plusにペーストできる行数(文字数?)には限界があるらしく、 長大なSQL文の場合はやってらんない。小分けしてコピペなんかしていると、どっかで必ず間違えます。
　そこで、SQL文を､どっかのファイルにテキストとして保存しておいて(いってみりゃバッチファイルみたいなもんですかね) SQL*Plusからそのファイルを呼び出すことができます。下記のような具合です。
SQL> @c:\1.txt
これで、1.txtの中に記述されたSQL文が一気に実行されます。ファイル名の前にはフルパスを指定します。
このワザと呼ぶには程遠い手法は、PL/SQLの入門書とかにもなかなか載っていません。 本屋さんでPL/SQLの解説書を探すなら、「索引」に「@」が出ているかを一つの基準にするとよいかも知れません。


　Nullも数える　COUNT(*)

　下のようなテーブル TAB1 があるとします。
ID_TYPE　　　　　形状
---------- ---------------------------------
1　　　　　 　　デスクトップ
2　　　　　　　 モノクロレーザービームプリンタ
3　　　　　　　 カラーレーザービームプリンタ
4　　　　　　　 カラーインクジェットプリンタ
5　　　　　　　 ノートPC
6　　　　　　　
7　　　　　　　 プリントサーバー
8　　　　　　　 ミドルタワー
9　　　　　　　 ルーター
10　　　　　　 液晶ディスプレイ
11　　　　　　 モノクロインクジェットプリンタ
ここで、2通りの行数カウントをしてみます。
①SELECT COUNT(*) FROM TAKE.TAB1;
②SELECT COUNT(形状) FROM TAKE.TAB1;
①
SQL> SELECT COUNT(*) FROM TAKE.TAB1;

COUNT(*)
----------
11
②
SQL> SELECT COUNT(形状) FROM TAKE.TAB1;

COUNT(形状)
----------
10
結果は以上の通り。 COUNT(*)　は、NULLもカウントするようです。


　SET TIMING ON　で､処理時間を計測

　　処理時間に大変時間がかかる処理があります。その場合、 インデックスの有無で評価したり、実行順序で評価したりして、速度を上げる努力をすることがあり、 正確な処理時間が知りたい場合があります。そんなときに使うのがSET TIMING ONです。 「SET TIMING ON」に続けて記述されたSQL文の実行開始から処理終了までの時間をミリ秒単位で計測できます。
SQL> SET TIMING ON
SQL> SELECT COUNT(*) FROM SKIP_MAIN.Z_NSTEC;

COUNT(*)
----------
13968

経過: 00:00:00.01
上の例ではただのCOUNTですので一瞬で終わっていますが、UPDATEなどで効果を発揮します。


　役に立たない　DISTINCT

　データベースの世界で最も嫌われるのが、重複とNULLです。 ここでは重複行を削除する方法について考えます。

結合を使って値を更新しようとした場合、以下のようなSQL文がひらめく人が多いと思います。

UPDATE 更新先
SET 更新先.更新列 = (SELECT 参照先.参照列 FROM 参照元
WHERE 更新先.結合列 = 参照先.結合列 );

Accessの場合､上のSQL文は 参照元.結合列 に値の重複があっても問題なく通りますが、 Oracleではエラーになります。
「単一行副問合せにより2つ以上の行が戻されます」
このメッセージは、 それまでAccessユーザーだった人を大いに混乱させます。
実はAccessでは、参照先の結合列に重複があっても、最初に見つかった行以外の行を無視しているのです。 そして何事もなかったかのようにクエリが終了するので、何も考えていないユーザーは何も気付きません。
しかしOracleは違います。「単一行副問合せにより2つ以上の行が戻されます」というエラーメッセージを吐いて終了します。 もちろんUPDATEは行われません。
ですから、Oracleで結合して参照更新をする場合、参照先の結合列に重複をなくすという前処理をする必要があります。 重複を削除したいニーズがある人が真っ先に飛びつくのが、DISTINCTコマンドですが、 これが役に立たないコマンドであることに気付くのに、そう時間はかかりません。 このコマンドは「ある列についてだけの重複を省いたリストを出す」だけで、 重複が省かれた結果の他の列の値は求めてくれないからです。 最初に見つかった行以外の重複行を削除するには、どうしたらよいのでしょうか?

DISTINCTを使って重複行を消す場合、次のようなSELECT文が思い浮かびます。
　　　SELECT DISTINCT テーブル.列1 FROM 参照先
 これだと重複を省かれた列1の値が表示されるだけで他の列の値は表示されません。そこで次に考えるのが、
　　　SELECT DISTINCT テーブル.列1,列2,列3,列4 FROM 参照先
 ですが、これだと列1,列2,列3,列4のすべての列において重複な行を1つにして表示するだけなので、やはりNGです。 欲しいのは　「列1において重複な行」　を省いた　「全表」　なのです。

ORACLEにはROWIDというのがあります。取得はできても更新はできない擬似的な列で、ユニークな値が振られています。 これを利用した重複行を削除するSQL文を2パターン紹介します。

　　　　　　　　　　　　　　①
DELETE FROM 表名 別名1
　　　 WHERE ROWID < (SELECT MAX(ROWID) FROM 表名 別名2
　　　 WHERE 別名1.重複調査列 = 別名2.重複調査列);



　　　　　　　　　　　　　　②
DELETE FROM 表名
　　　 WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM 表名
　　　 GROUP BY 別名2.重複調査列);


①は、一つの表に違う別名を与えて自己参照し、値が同じ行のうち1行を残してそれ以外を削除しています。
②は、重複を調査する列の値をグループ化して、そのうち1行を残してそれ以外を削除しています。
やっていることは似たことのように思えますが、処理対象のテーブルのいい加減差によっては削除される列数が異なります。 ①の場合はスカラー値の比較をしているので、重複調査列にNullが含まれていた場合は、その行は比較対照外となります。 これに対して、②はNullの行もグループ化されます。
つまり、重複調査列にNullがあった場合、①では重複調査列がNullである行はそっくり残り、②では重複調査列がNullである行は 1行を除きすべて削除されます。

この処理をした後で結合参照した更新をすると、エラーになりません。


　最後に

　Oracleとの格闘の中で難問にぶち当たる度、こちらに 非常にお世話になりました。　その割には私の成果があまりにお粗末なので、ここのご主人様にはリンク貼りのご挨拶をしていません。
でも、Oracleを使う羽目になった人は、是非こちらをご覧になると良いと思います。



dicdicのホームページ

感想をメールください感想、ご意見をメールください。

Home　>　Oracleのワナとワザ
inserted by FC2 system