Database
技術記事
Ask Tom
2013年7/8月 |
テクノロジー：Ask Tom
ランダム性、構文、変更について
Tom Kyte著
決定性とデータ区切りとトリガーの話
UPDATE文が1つのデータに対して2つの異なる結果を返すので困っています。UPDATEで、変更される行の特定に副問合せを使用していますが、そのSELECT文はまず、ID が14152967の行を返します。しかし、UPDATEを実行すると別の行が更新されます。何が起こっているか、説明していただけますか。
[編集者のメモ：リスト1に、質問者の表設定、UPDATEの副問合せ、UPDATE自体、UPDATEの結果を示します]
コード・リスト1：最初の質問のサンプル表、副問合せ、UPDATE、結果情報
SQL> CREATE TABLE TEST 2 ( ID varchar2(10), 3 PROD_RID varchar2(10) NULL, 4 VERSIONS_RID varchar2(10) NULL, 5 HOSTID varchar2(10) NULL, 6 HIDDEN varchar2(10) NULL 7 ); Table created. SQL> insert into TEST 2 (id,prod_rid,versions_rid,hostid,hidden) 3 values(14152967, 10013252, 29300796, 44026, 0 ); 1 row created. SQL> insert into TEST 2 (id,prod_rid,versions_rid,hostid,hidden) 3 values(14152966, 10013252, 29300796, 44026, 0); 1 row created. SQL> create table PRI 2 (PROD_RID NUMBER, 3 VERSIONS_RID NUMBER, 4 PRI NUMBER 5 ); Table created. SQL> INSERT INTO PRI VALUES (10013252,29300796,13); 1 row created. SQL> SELECT id 2 FROM (SELECT row_number() over 3 (PARTITION BY hostid, prod_rid 4 order by pri DESC) row_n, 5 ID 6 FROM (SELECT prod.ID, 7 prod.HOSTID, 8 prod.PROD_RID, 9 mv.PRI 10 FROM TEST prod 11 JOIN PRI mv 12 ON (mv.VERSIONS_RID = prod.VERSIONS_RID 13 AND mv.PROD_RID = prod.PROD_RID) 14 WHERE HIDDEN = 0 15 ) x 16 ) y 17 WHERE y.row_n > 1; ID ———————— 14152967 SQL> UPDATE TEST 2 SET HIDDEN = 4 3 WHERE ID IN 4 ( 5 <<the 17 lines of the above SELECT id… query…>> 22 ); 1 row updated. SQL> select id,hidden from test; ID HIDDEN ———————— —————— 14152967 0 14152966 4
問題は、副問合せに決定性がない（実行するたびに異なる結果が出る）ことにあります。この例を実際に実行したところ、TEST表にある2つの行のどちらが更新されるかは、どちらの行が先に挿入されたかで変わってしまいました。そこで、パーティション化の基準になっているデータ値と、並べ替えの基準になっているデータ値を確認できるように、この問合せに列を追加しました。そして、WHERE ROW_N > 1である行を取得するためのWHERE句を削除しました。リスト2にその結果を示します。
コード・リスト2：決定性のない結果セットの詳細
SQL> SELECT *
2 FROM (SELECT row_number() over
3 (PARTITION BY hostid, prod_rid
4 order by pri DESC) row_n,
5 ID,
6 hostid, prod_rid, pri
7 FROM (SELECT prod.ID,
8 prod.HOSTID,
9 prod.PROD_RID,
10 mv.PRI
11 FROM TEST prod
12 JOIN PRI mv
13 ON (mv.VERSIONS_RID = prod.VERSIONS_RID
14 AND mv.PROD_RID = prod.PROD_RID)
15 WHERE HIDDEN = 0
16 ) x
17 ) y
18 /
ROW_N ID HOSTID PROD_RID PRI
—————————— ———————— —————— ———————— ———
1 14152966 44026 10013252 13
2 14152967 44026 10013252 13
リスト2のデータを見れば、この異常の原因は明らかです。結果セットに決定性がありません。いろいろな答えを"ランダムに"返しているのです。
このコードでは、HOSTIDとPROD_RIDでパーティション化し、PRIでソートした後で、ROW_NUMBERを割り当てています。HOSTIDとPROD_RIDの値が同じで、PRIの値も同じであるため、このデータをソートする決定的な方法はありません。この2つの行のどちらが先頭に来てもおかしくないのです。この問合せを何回も実行した場合、ROW_NUMBERの割当てが常に一定であるとは保証されません。実際、表を切り捨てて、データを逆順に挿入した場合、次のような結果となることがあります。
ROW_N ID HOSTID PROD_RID PRI
—————— ———————— —————— ———————— ———
1 14152967 44026 10013252 13
2 14152966 44026 10013252 13
ご覧のとおり、ROW_Nの値が入れ替わっています。これは、行を表に挿入する順序を変えたことが原因です。これを正確に再現できなくても気にしないでください。結果は、ソート用に割り当てられた作業領域のサイズ、ブロック・サイズ、使用しているセグメント領域割当ての種類など、さまざまな要因によって変わります。言い換えれば、実際の結果に規則性がないのです。
重複する値を持つ複数の属性を基準にパーティション化や並べ替えを行うと、決定性のある結果は得られません。結果セットのデータの順序と、特定の分析ウィンドウ関数の値は、問合せを実行するたびに変わります。
この例が抱える問合せの問題について、最終的な解決策を私が示すことはできません。この問合せを決定的なものにするには、並べ替えの基準を決める必要があります。たとえば、IDの大きい方を更新するのであれば、リスト3に示すように、ORDER BYリストにIDを追加します。
コード・リスト3：結果の決定性を解決する1つの手段としてORDER BYにIDを追加
SQL> SELECT *
2 FROM (SELECT row_number() over
3 (PARTITION BY hostid, prod_rid
4 order by pri DESC, ID ASC ) row_n,
5 ID,
6 hostid, prod_rid, pri
7 FROM (SELECT prod.ID,
8 prod.HOSTID,
9 prod.PROD_RID,
10 mv.PRI
11 FROM TEST prod
12 JOIN PRI mv
13 ON (mv.VERSIONS_RID = prod.VERSIONS_RID
14 AND mv.PROD_RID = prod.PROD_RID)
15 WHERE HIDDEN = 0
16 ) x
17 ) y
18 /
ROW_N ID HOSTID PROD_RID PRI
—————————— ———————— —————— ———————— ———
1 14152966 44026 10013252 13
2 14152967 44026 10013252 13
リスト3の結果セットでこのIDが一意であるとすると（この例には制約がまったくないので断言できませんが）、PRI DESCで並べ替えた場合、ID ASCにより、何回問合せを実行しても同じ結果が得られることが保証されます。
この話題については、数年前、"On Top-n and Pagination Queries"でも簡単に触れています。この決定性という特性は分析ウィンドウ関数を使用するときだけでなく、単なるORDER BY文を使用するときでも常に重要です。
Oracle SQL Developerのカンマ区切りデータ
Oracle SQL Developerを使って、カンマ区切りファイルを作成しなければなりません。現在、次の構文を使って、出力を生成しています。
select spriden_id||','||
spriden_first_name||','||
spriden_last_name
from spriden
where spriden_pidm = 1012;
このファイルの送信先の会社は、私が選択しているフィールド名をすべて、ファイルの先頭にヘッダー・レコードとして表示して欲しいと言っています。次のようなヘッダー・レコードを作成するにはどのような構文にすればいいでしょうか。
ID,First_Name,Last_Name
これはびっくりするほど簡単です。Oracle SQL Developerは、SQL問合せで、独自に設計した"ヒント"をいくつかサポートしています。（これらは、INDEX、PARALLEL、FULL、USE_NLなどのヒントとは形式が異なります）。この結果セットでは、シンプルに
select /* csv */ spriden_id ID, spriden_first_name FIRST_NAME, spriden_last_name LAST_NAME from spriden where spriden_pidm = 1012;
でうまくいきます。このほか、次のような"ヒント"もあります。
XML – タグ付きXML形式 HTML – マークアップ済みHTML表形式 DELIMITED – csvと同じ INSERT – SQL挿入形式 LOADER – SQL*Loader（sqlldr）に適したパイプ区切り形式 FIXED – 固定幅のフィールドで、指定された長さになるまで後ろに空白が追加される TEXT – プレーン・テキストの出力
これらそれぞれの例と、Oracle SQL Developerでの使用にについて、詳しくは、bit.ly/10AuN46とbit.ly/19dt2Orを参照してください。この情報が記載されているブログ、ThatJeffSmithの著者はOracle SQL DeveloperのProduct Manager、Jeff Smithです。
表の変更
現在、次のようなトリガーを使っています。
create or replace trigger TR_TABLE_X_AU after update on TABLE_X for each row declare cursor cursor_select_fk is select FK_FOR_ANOTHER_TABLE from TABLE_Y Y, TABLE_Z Z where :NEW.JOINING_COL = Y.JOINING_COL and Y.JOINING_COL = Z.JOINING_COL and :NEW.FILTER_CONDITION_1 = Y.FILTER_CONDITION_1 and :NEW.FILTER_CONDITION_2 = Y.FILTER_CONDITION_2 and :NEW.SOME_DATE_COL = (select max(SOME_DATE_COL) from TABLE_X where FILTER_CONDITION_1 = :NEW.FILTER_CONDITION_1 and FILTER_CONDITION_2 = :NEW.FILTER_CONDITION_2) begin for rec in cursor_select_fk loop PCK_SOME_PACKAGE.SOME_PROC (rec.FK_FOR_ANOTHER_TABLE); end loop; end TR_TABLE_X_AU;
トリガーを使っているのは、これが機能拡張だからです。今あるソフトウェアを変更したくないのです。問題の原因は、日付の最大値を選択しているネストした問合せだと思います。問合せを変更して、SYSDATEを使用すると、エラーや例外は出ません。TABLE_Xに対してこのトリガーを実行したときに、日付の最大値を取得できるようにするにはどうすればいいでしょうか。
トリガーする文が複数の行に作用する可能性がある場合（そして、UPDATEは一般に複数の行に作用します）、行トリガーはトリガーの対象となる表からの読取りはできません。トリガーの対象となる表を読み取ることができるタイプの行トリガーは、0行または1行だけに作用する、VALUES句を使用したINSERTのみです。
ですから、このエラー・メッセージは、“データベースのデータに整合性がなくなってしまう”という災難、つまり、ロジック上の重大なエラーからユーザーを救うために存在します。仮にこのトリガーが機能できた場合、つまりトリガー起動時にTABLE_Xの読取りを許可された場合の結果は、まったく同じデータに対して実行されたまったく同じSQLから得られた決定性のないものとなります（これは、このコラムの最初の質問と非常によく似ています）。理由については、後ほど説明します。
まず、その前に、「...トリガーを使っているのは、これが機能拡張だからです...」について一言。その論拠にはあまり感心しません。ソフトウェアに変更はつきものです。だから、ハードウェアではなくソフトウェアと呼ばれるのです。ハードウェアは私たちがそのまま受け入れなければならないもの、ソフトウェアは融通が効き、変更できるものです。トリガーは変更を魔法のごとく"自動的に"本番環境へもぐりこませようとする恐ろしいもので、たくさんの副作用や長期間にわたるマイナス面がつきまとっています。これについては、過去の記事"The Trouble with Triggers"で説明しています。
あなたがトリガーでやろうとしていることは、トリガーがなぜ悪者となりうるかということの典型的な例です。このトリガーが動作できたとしても、データは整合性のとれていないままで残されます。それどころか、最初はまったく同じデータの入っていた2つのデータベースが、異なる状態の異なる2つのデータベースになる可能性もあります。たとえば、SOME_DATE_COLに対してUPDATEを実行するとします。このUPDATEは複数の行に作用します。もし、このトリガーが動作した場合（UPDATEの実行中に、このトリガーがTABLE_Xを読み取ることができた場合）何が起こるか、考えてみてください。
このような状況では、行が更新されるたびに、トリガーは“max”値を読み取ろうとします。でも、思い出してください。あなたはSOME_DATE_COLを更新しています。つまり、UPDATEの途中で表を読み取っていますが、表のデータは更新の最中なのです（これはあくまでも仮定です。実際のOracle Databaseでは、このような不正な処理は許可されません）。それまでに一度もmax値になったことのないmax値が読み取られるかもしれません。表示されることのないデータが読み取られ、処理されるでしょう。
たとえば、まず、表のデータが
...... 01-JAN-2012 ...... .......02-JAN-2012 ........
で、UPDATEがこの順序で行をヒットしたとします。ここで、実行したUPDATEが
update table_x set some_date_col = some_date_col + 5 .......;
であったとすると、01-janが06-janに変更され、次に02-janが07-janに変更されます。実際、この表でmax(SOME_DATE_COL)だったことがある値は02-jan（UPDATE前）と07-jan（UPDATE後）だけです。しかし、このトリガーは何を見ているのでしょう。トリガーは01-janが06-janに変わったことを認識しています。そして、この値がある時点で日付の最大値であったかのように、この結果を読み取り、処理しています。しかし、そのようなことはまったくありませんでした。一度も。次に、このトリガーは2行目の日付の最大値として07-janを処理します。
しかし、この処理は、UPDATEがこの2行をこの順序でヒットした場合にのみ行われます。もし、これらの行が次の順序で処理されたらどうなるでしょう。
.......02-JAN-2012 ........ ...... 01-JAN-2012 ......
この場合、データのセットがまったく同じであるとすると、ディスクに行がどのように存在するかによって処理の順序が変わり、まったく同じデータからまったく異なる答えが導かれます。これは、このコラムの最初にあった、決定性のない分析関数に関する質問と奇妙なくらい良く似ています。ディスク上に実際に存在する行の順序が答えに影響します。
したがって、Oracle Databaseはトリガー内でこのような動作を許可しません。Oracle Databaseは、あなたがアプリケーションにこのような矛盾をプログラミングすることを認めませんし、あなたが表の編集中に、この表に問合せを実行することを許可しないでしょう。このような順序に依存する奇妙なバグがアプリケーションに入りこんだらたいへんなことになります。
このトリガーをあきらめて、コードをばっさりと変更し、必要なコード・ロジックを実装することを強く（力いっぱい強く）お勧めします。大切なことなので繰り返します。ソフトウェアに変更はつきものです。だから、ハードウェアではなくソフトウェアと呼ばれるのです。
この次のリンクを投稿するのはためらわれますが、検索すれば簡単に見つけられるでしょう。bit.ly/19hg5Dpには、表が"安定"しているときに、変更されたデータの処理をトリガーの後まで延期する方法が記載されています。
Oracle Database 11g以上では、このリンク先テキストで説明されている3つのトリガーの代わりに複合トリガーを使用できますが、論理は同じです。しかし、コード変更で対応すべきものについてはトリガーを使用しないということを真剣に検討してください。
プロシージャでは動作せず、無名ブロックで動作する問合せ
私のカーソル問合せは無名ブロックではうまく動作しますが、PL/SQLブロックではデータをまったく見つけられません。コードは次のとおりです。
declare cursor c1 is select object_name from all_objects where owner='IRIS_DATA' and object_type='SEQUENCE'; v_string varchar2(2000); begin for c2 in c1 loop v_string := 'DROP SEQUENCE IRIS_DATA.'||c2.object_name; execute immediate v_string; end loop; end;
これを無名ブロックとして実行すると、問題なく動作します。しかし、ストアド・プロシージャにすると、実行はされますが、処理するシーケンスを1つも見つけられません。私はこのプロシージャをCLONEDEVスキーマでコーディングし、SYSとして以下の特権をCLONEDEVユーザーに付与しました。
GRANT DBA TO CLONEDEV; GRANT SELECT ANY TABLE TO CLONEDEV; GRANT ANALYZE ANY TO CLONEDEV; GRANT DELETE ANY TABLE TO CLONEDEV; GRANT INSERT ANY TABLE TO CLONEDEV; GRANT CREATE ANY TABLE TO CLONEDEV; GRANT UPDATE ANY TABLE TO CLONEDEV; GRANT DROP ANY TABLE TO CLONEDEV; GRANT ALTER ANY TABLE TO CLONEDEV; GRANT ALTER ANY TRIGGER TO CLONEDEV; GRANT DROP ANY INDEX TO CLONEDEV; GRANT CREATE ANY INDEX TO CLONEDEV; GRANT ALTER ANY INDEX TO CLONEDEV; GRANT CREATE ANY SEQUENCE TO CLONEDEV; GRANT DROP ANY SEQUENCE TO CLONEDEV; GRANT SELECT ON ALL_TAB_COLUMNS TO CLONEDEV; GRANT SELECT ON ALL_CONSTRAINTS TO CLONEDEV; GRANT SELECT ON ALL_CONS_COLUMNS TO CLONEDEV; GRANT SELECT ON ALL_OBJECTS TO CLONEDEV; GRANT EXECUTE ON DBMS_STATS TO CLONEDEV; GRANT EXECUTE ON DBMS_FLASHBACK TO CLONEDEV; GRANT FLASHBACK ANY TABLE TO CLONEDEV;
を参照してください。ここでは、Definerの権限ルーチンの実行中、ロールは絶対に有効化されないということを解説しています。設計上、ALL_OBJECTSビューには、現在与えられている特権セットで表示できるものが表示されます。したがって、ロールを無効化した場合、ALL_OBJECTSに“表示”されるものの数はぐんと少なくなります。以下に例を挙げます。
SQL> select count(*) from all_objects;
COUNT(*)
—————————————
72946
SQL> set role none;
Role set.
SQL> select count(*) from all_objects;
COUNT(*)
—————————————
56414
というわけで、これが根本的な原因です。たとえどのようなシーケンスでも作成または削除できるとしても、すべてのシーケンスを表示することはできません。とはいうものの、私はこのANY特権が好きではないので、あなたのアプローチは好みではありません。実はこれにはInvokerの権限ルーチンが最適です。CLONEDEVにこのコードを“所有”させて、実行はIRIS_DATAに任せてください。これで、特別な権限はまったく必要なくなります。リスト4のコードを見てみましょう。
コード・リスト4：Invokerの権限と特別な権限なし
SQL> create user iris_data identified by iris_data;
User created.
SQL> grant create session, create sequence to iris_data;
Grant succeeded.
SQL> create user clonedev identified by clonedev;
User created.
SQL> grant create session, create procedure to clonedev;
Grant succeeded.
SQL> connect iris_data/iris_data;
Connected.
iris_data> create sequence iris_data.seq;
Sequence created.
iris_data> connect clonedev/clonedev;
Connected.
clonedev> create or replace procedure drop_sequences
2 AUTHID CURRENT_USER
3 is
4 begin
5 dbms_output.put_line('drop sequence started.');
6 for x in (select 'drop sequence ' || sequence_name sql_stmt
7 from user_sequences )
8 loop
9 dbms_output.put_line( x.sql_stmt );
10 execute immediate x.sql_stmt;
11 dbms_output.put_line( 'success:' || x.sql_stmt );
12 end loop;
13 dbms_output.put_line('drop_sequence:Dropping sequence completed.');
14 end drop_sequences;
15 /
Procedure created.
clonedev> grant execute on drop_sequences to iris_data;
Grant succeeded.
clonedev> connect iris_data/iris_data
Connected.
iris_data> exec clonedev.drop_sequences;
drop sequence started.
drop sequence SEQ
success: drop sequence SEQ
drop_sequence:Dropping sequence completed.
PL/SQL procedure successfully completed.
必要な権限は非常にわずかであることがわかりますか。あの非常にややこしいANY権限は1つも出てきません。パスワードのためにIRIS_DATAとして接続する必要があることを気にしているのなら、それも解決できます。その方法については、最近のコラム"他のユーザーへの成り代わり、制限、リストアについて"を参照してください。IRIS_DATAは、clonedevが、clonedevのパスワードを使用してIRIS_DATAに“なれる”ようにします。これにより、権限をほとんど必要とせずに目的を達成できるようになります。
インポート中のコミット
インポート中、表全体がインポートされたあとコミットされているようです。また、エラーが発生した場合は表全体がロールバックされているように見えます。無数の行を持つサイズの大きい表があるときに、一部の行だけがインポートされるようにするにはどうすればいいですか。インポートでデータを取得すると同時にUNDOを解放するように、行をコミットできますか。
インポート（imp）には確かにCOMMITパラメータがあります。これは一定数のレコードの後コミットするのではなく、配列の挿入を1回実行するたびにコミットします。この制御はBUFFERパラメータで行います。しかし、念のため言っておきますが、表にLONGまたはLOB列があるときは、いかなる場合でも配列のサイズは1になります。
INSERTはほとんどUNDOを生成しないため（UNDOではdelete+rowidエントリのみ）、COMMITパラメータの使用が必要になることはまれです。めったにありません。
また、Import（imp）ではなく、ダイレクト・パス挿入が可能なOracle Data Pump import（impdp）を使用するべきです。ダイレクト・パス操作はUNDO生成をバイパスするため、関係するUNDOはありません。詳細については、Oracle Database Utilities 11g Release 2（11.2）の"Oracle Data Pumpの概要"を参照してください。
一般に、インポートでCOMMIT = Yを使うのは非常にまずいことです。表のインポートを開始し、行の最初のバッチをコミットしたときに、何らかの理由でエラーが発生した場合、失敗したところからインポートを再開する方法がありません。表を切り捨てるか、破棄して、最初からやり直すしかないのです。
いずれにしても、Oracle Data Pump importを使用してください。そうすればUNDO生成を気にする必要はなくなります。
次のステップ
その他の記事、書籍 関連機能の詳細 |
Tom KyteはオラクルのServer Technologies部門に籍を置くデータベース・エバンジェリストで、1993年からオラクルに勤務しています。Expert Oracle Database Architecture（Apress、2005年/2010年）、Effective Oracle by Design（Oracle Press、2003）などの著書があります。
Printer View