- PR -

Oracle10g グループごとに連番を振りたい

参照元記事 | 同じ記事を参照しているスレッド一覧
1|2 次のページへ»
投稿者投稿内容
ぺどら
会議室デビュー日: 2006/02/17
投稿数: 14
お住まい・勤務地: かごしま
投稿日時: 2006-12-07 10:02
いつもお世話になってます。

以下のような構造のデータがあるとき
コード:
+------+------+
| COL1 | COL2 |
+------+------+
| AAAA | 2    |
| AAAA | 5    |
| AAAA | 8    |
+------+------+
| BBBB | 0    |
| BBBB | 3    |
| BBBB | 4    |
+------+------+
| CCCC | 9    |
+------+------+

COL1列ごとにグループ化させて、COL2列に以下のように連番を振り直そうと考えてます。
コード:
+------+------+
| COL1 | COL2 |
+------+------+
| AAAA | 1    |
| AAAA | 2    |
| AAAA | 3    |
+------+------+
| BBBB | 1    |
| BBBB | 2    |
| BBBB | 3    |
+------+------+
| CCCC | 1    |
+------+------+

そこで、『おらくる虎の穴掲示板』に書いてあったSQLを参考に、
コード:
UPDATE TBL T1 SET T1.COL2 = (
  SELECT SEQ.連番
  FROM (
    SELECT T1.COL1, COUNT(*) AS 連番, T1.ROWID AS キー
    FROM TBL T1, TBL T2
    WHERE T1.COL1   = T2.COL1
      AND T1.ROWID >= T2.ROWID
    GROUP BY T1.COL1, T1.ROWID
    ORDER BY T1.COL1) SEQ
  WHERE T1.ROWID = SEQ.キー
  )
;

このようなSQLを作成したのですが、
このSQLで、役10万件のデータの更新をしようとすると、応答がなくなってしまいました。

更新しようとするデータは最大で100万件程度になることもあるようで、
現状のSQLよりも、もっとレスポンスの良い書き方がないものかと思い
色々と調べてみたのですが、先の参照元URLよりも良さそうなSQLが見つかりません。

現状のSQLよりも、レスポンスの良いSQLの書き方
もしくは、その参考になりそうなURLをご存知であれば、
ご教授願いたく投稿させていただきました。

皆様のお知恵を拝借させていただけると、さいわいです。
よろしくお願いいたします。
七味唐辛子
ぬし
会議室デビュー日: 2001/12/25
投稿数: 660
投稿日時: 2006-12-07 10:29
とりあえず　実行計画を取ってみて　そこから判断しましょう。

PoH
常連さん
会議室デビュー日: 2003/09/09
投稿数: 48
投稿日時: 2006-12-07 10:31
RANK関数を使えばどうでしょうか？

select
COL1 , COL2 ,
rank() over (partition by COL1 order by COL2) rank
from TABLE1

みたいな感じです


ぺどら
会議室デビュー日: 2006/02/17
投稿数: 14
お住まい・勤務地: かごしま
投稿日時: 2006-12-07 11:13
七味唐辛子さん＞
引用:

七味唐辛子さんの書き込み (2006-12-07 10:29) より:
とりあえず　実行計画を取ってみて　そこから判断しましょう。


さっそくの返信ありがとうございます。
実行計画とは、オブジェクトブラウザの『SQL 実行』ウィンドウにある
[実行計画]で見られる情報のことでしょうか？

表示される情報の意味が分からないので、調べつつ調べ（微妙な言い回しですね・・）て
みようと思います。


[ メッセージ編集済み 編集者: ぺどら 編集日時 2006-12-07 11:14 ]
ぺどら
会議室デビュー日: 2006/02/17
投稿数: 14
お住まい・勤務地: かごしま
投稿日時: 2006-12-07 11:24
PoHさん＞
引用:

PoHさんの書き込み (2006-12-07 10:31) より:
RANK関数を使えばどうでしょうか？


さっそくの返信ありがとうございます。
例文まで書いていただき、非常に感謝しています。

RANK関数というのを初めて目にしたので、使い方が正しいかわかりませんが
コード:
UPDATE TBL T1 SET T1.COL2 = (
  SELECT SEQ.連番
  FROM (
    SELECT T1.COL1, T1.ROWID AS キー,
      RANK() OVER (PARTITION BY T1.COL1 ORDER BY T1.ROWID) AS 連番
    FROM TBL T1) SEQ
  WHERE T1.ROWID = SEQ.キー
  )
;

というSQLを作成し、実験したところ
以前のSQL（『おらくる虎の穴掲示板』バージョン）よりも遅くなってしまいました・・

オブジェクトブラウザの『SQL 実行』ウィンドウより実行した場合の処理時間ですが、
以前のSQLの場合（データ件数：500件）、00:00:00
今回のSQLの場合（データ件数：500件）、00:00:26
という結果でした。

RANK関数の使い方はこれで正しいのでしょうか・・？
連番だけを表示させた場合（UPDATEを外して実験）、
以前のSQLより、RANK関数を使ったものの方が早かったのですが。
ぺどら
会議室デビュー日: 2006/02/17
投稿数: 14
お住まい・勤務地: かごしま
投稿日時: 2006-12-07 11:55
自分の作ったSQLと、PoHさんから教えて頂いたRANK関数を使ったSQLを
七味唐辛子さんから教えて頂いた、実行計画を表示させて比較してみました。
コード:
以前のSQL
recursive calls                        |     85
db block gets                          |   1036
consistent gets                        | 102363
physical reads                         |    316
redo size                              | 227840
bytes sent via SQL*Net to client       |    141
bytes received via SQL*Net from client |    506
SQL*Net roundtrips to/from client      |      1
sorts (memory)                         |   1012
sorts (disk)                           |      0
Rows processed                         |    506

RANK関数使用
recursive calls                        |      1
db block gets                          |   1022
consistent gets                        |1493656
physical reads                         |      0
redo size                              | 121828
bytes sent via SQL*Net to client       |    141
bytes received via SQL*Net from client |    387
SQL*Net roundtrips to/from client      |      1
sorts (memory)                         |    506
sorts (disk)                           |      0
Rows processed                         |    506


結果、以前のものとRANK関数使用バージョンでは
『consistent gets』が後者の方が10倍程度多い以外は、
後者の方が全体的な数値は低かったです。

なのに、更新時は前者の方がレスポンスが速い・・
もうちょっと実行計画というものの見方を調べてみようと思います。
明智重蔵
大ベテラン
会議室デビュー日: 2005/09/05
投稿数: 127
投稿日時: 2006-12-07 12:41
こうすれば多少は早くなるかも

UPDATE TBL T1 SET T1.COL2 = (
SELECT SEQ.連番
FROM (
SELECT /*T1.COL1,*/ T1.ROWID AS キー,
/*RANK()*/row_number() OVER (PARTITION BY T1.COL1 ORDER BY /*T1.ROWID*/1) AS 連番
FROM TBL T1) SEQ
WHERE T1.ROWID = SEQ.キー
)
;
PoH
常連さん
会議室デビュー日: 2003/09/09
投稿数: 48
投稿日時: 2006-12-07 13:08
SQL文を見直しても変わらないなら
CREATE INSERTでWORKテーブルに一旦入れて
そこから本番にDELETE、INSERTという手もありますが。。。

一時的な更新や夜間バッチであればいいですが
通常運用で使うならNGですねぇ

SQL文だけでなくインデックスなどを
見直す必要もあるかもしれないので
お力になりきれないですが、がんばってください
1|2 次のページへ»

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