Database
技術記事
Ask Tom
テクノロジー： Ask Tom
文字、ピボット、最終行についてTom Kyte文字を置き換え、疑わしいデータを見て、REDOを避ける技術者達 次のようなSQL文で、表に値を挿入しようとしています。 create table test (name varchar2(35)); しかし、これを実行すると、置換変数の値の入力を求められます。 どうすればアンパサンド（&）を挿入できますか。 アンパサンド（&）の挿入方法は、AskTom （asktom.oracle.com）に限らずどこのOracle Databaseフォーラムでも、よく質問されます。 答えを示す前に、この問題の全体像を説明しましょう。 SQL*Plusのデフォルトでは、入力を1行ずつスキャンして&文字を探します。 &文字が見つかれば、アンパサンドに続く文字列をスキャンして、その文字列を変数名として使用します（この例の変数名はVivekです）。 その後、ユーザーにVivekの値の入力を求めます。一連の流れは次のようになります。
SQL> insert into test (name)
values ('&Vivek');
Enter value for vivek: Hello World
old 1: insert into test (name)
values ('&Vivek')
new 1: insert into test (name)
values ('Hello World')
1 row created.
この例により、SQL*Plusで&VivekがどのようにHello Worldに変換されるかが分かります。 では、SQL*Plusにこの変換を止めさせるにはどうすれば良いでしょうか。 もっとも簡単な方法は、SQL*Plus set define offコマンドを発行することです。
SQL> set define off
SQL> insert into test (name)
values ('&Vivek');
1 row created.
SQL> select * from test;
NAME
———————————————————————
Hello World
&Vivek
このコマンドにより、SQL*Plusで置換文字を探すための入力のスキャンが行われなくなります。 また、別の置換文字を使用するというアプローチもあります。 SQL> set define @ SQL> insert into test (name) values ( '&Vivek @X' ); Enter value for x: this was x old 1: insert into test (name) values ( '&Vivek @X' ) new 1: insert into test (name) values ( '&Vivek this was x' ) 1 row created. この場合、以前の&の機能を、@文字が代行しています。 ほかにもアプローチはあります。たとえば、SQL内で&文字の使用を避けることも可能です。 SQL> insert into test values (chr(38)||'Vivek xxx'); 1 row created. SQL> select * from test where name like '% xxx'; NAME ————————————————————————— &Vivek xxx このアプローチも機能しますが、SQL文を変更する必要があるため、私は好きではありません。 さらに別のアプローチとして、長さゼロの置換変数名を使用するという方法もあります。SQL*Plusでは、&文字だけの場合はそのままにします。
SQL> insert into test
values ('&'||'Vivek yyy');
1 row created.
SQL> select * from test
where name like '% yyy';
NAME
—————————————————————————
&Vivek yyy
このアプローチはおそらく、SQL内で&文字を避けてchr(38)を使用する方法よりも優れていますが、やはりお勧めはしません。 話はそれますが、私は25年もの間SQL*Plusを使用しているのに、最後の解決策（'&' ||'Vivek'の利用）がこのような結果となると知りませんでした。 これはAskTomの読者から教えてもらった新しい知識です。 動的ピボット次のような表があります。 create table fish ( insert into fish values (1,'COD',20); この表を次のように表示しようと思っています。 COD HAD HKE LIN CTY ....... さまざまな魚の種類があるため、列の数や名前は固定されません。 このように表示する方法について教えてください。 SQLでは、解析時にすべての列の番号、名前、データ型を把握しておく必要があります。そのため、ここでは動的SQLを少し使用する必要があります。 この動的SQLをお見せする前に、まずは既存のデータに対して動作する静的SQL文を作成しましょう。 リスト1には、すべてのOracle Databaseリリースで動作する問合せが含まれています （Oracle Database 11g以降のリリースでは、組込みのPIVOT構文を使用できますが、PIVOT構文でも動的SQLが必要です）。 コード・リスト1：既存のデータに対する静的SQL
SQL> select fish_id,
2 sum(decode(fish_type,'COD',fish_weight)) cod,
3 sum(decode(fish_type,'HAD',fish_weight)) had,
4 sum(decode(fish_type,'HKE',fish_weight)) hke,
5 sum(decode(fish_type,'LIN',fish_weight)) lin,
6 sum(decode(fish_type,'CTY',fish_weight)) cty
7 from fish
8 group by fish_id
9 order by fish_id
10 /
FISH_ID COD HAD HKE LIN CTY
—————————— ————————— ————————— —————————— ————————— —————————
1 20 30
2 45 10 55
3 52 60 90
次に、リスト1のSQLを動的SQLに変えるために、リスト2のようなストアド・プロシージャを作成します。このストアド・プロシージャは、列の名前を特定するために問合せを実行し、その情報を使用して動的にピボット問合せを組み立てます。 コード・リスト2：列名を特定するためのストアド・プロシージャ SQL> create or replace procedure go_fishing( p_cursor in out sys_refcursor ) 2 as 3 l_query long := 'select fish_id'; 4 begin 5 for x in (select distinct fish_type from fish order by 1 ) 6 loop 7 l_query := l_query || 8 replace( q'|, sum(decode(fish_type,'$X$',fish_weight)) $X$|', 9 '$X$', 10 dbms_assert.simple_sql_name(x.fish_type) ); 11 end loop; 12 13 l_query := l_query || ' from fish group by fish_id order by fish_id'; 14 15 open p_cursor for l_query; 16 end; 17 / Procedure created. 注：リスト2では、fish_type列が、fish_typeを主キーとする別の表の外部キーであることは明白です。 fish_typeを主キーとする表は、有効な魚の種類を取得するための参照表です。 そのため、リスト2のSELECT DISTINCT...の部分は、その参照表に対する単純なSELECTに置き換えることができます。 リスト2のストアド・プロシージャでは、重複のない魚の種類のリストを生成し、それぞれの魚の種類ごとに問合せの列を追加しています。 次のような文字列を使用しています。 q'|, sum(decode(fish_type,'$X$', fish_weight)) $X$|' これは、元々の静的SQLの次の部分に対するテンプレートです。 sum(decode(fish_type,'COD', fish_weight)) cod, このテンプレートを使用して行うべきことは、$X$（COD、HADなどを表すためにランダムに選択した文字列）をx.fish_typeの値に置き換えることだけです。 ただし、ただむやみに$X$をx.fish_typeの値に置き換えているわけではありません。 DBMS_ASSERTパッケージを使用して、SQL文に連結しようとしているデータが"安全"であること、すなわち単純なSQL名であり、SQL文の意味を変えるような何らかのSQLでないことを検証しています。 要するに、DBMS_ASSERTコールによってSQLインジェクションから保護しています。 SQLインジェクションにあまり詳しくない場合、あるいは実に興味深いSQLインジェクションのテクニックを確認する場合は、bit.ly/IgU3YQとbit.ly/K7aAKWを確認することをお勧めします。 問合せを1つの文字列として作成した後は、リスト3のように参照カーソルを使用して、カーソルを開き、このカーソルをクライアント・アプリケーションに送り返すことができます。 コード・リスト3：参照カーソル、カーソル、フェッチ
SQL> variable x refcursor
SQL> exec go_fishing( :x )
PL/SQL procedure successfully completed.
SQL> print x
FISH_ID COD CTY HAD HKE LIN
—————————— ————————— ————————— —————————— ————————— —————————
1 20 30
2 45 10 55
3 52 90 60
最終行の特定Oracle Databaseで複数の表を基に1つのビューを作成しました。SELECTにより、そのビュー内部のレコードをフェッチできる状態です。 質問は次のとおりです。 新しいレコードが追加された場合に、ビュー内部の新規追加レコードのみをフェッチするためには、どのようなSQL問合せを使用できますか。 ROWIDを試しましたが、期待どおりの結果が得られませんでした。 "新しい行"を紙に印刷するとした場合に、それらの"新しい行"はどのように特定できますか。 どの行が"新しい"のか、あるいはどれが"最終行"なのかを答えられなければ、私にもそれは分かりません。 ROWIDは、ファイル内部のブロック上の場所を示す行のアドレスであり、単調に増加する値ではありません。 単純に表への挿入しか行っていない場合でも、行が"挿入順"に並んでいない可能性があります。挿入順に並ぶこともあれば、そうでない場合もあるのです。 つまり、行が何らかの順序で並んでいることは期待できません。 たとえば、リスト4のように、ブロック・サイズが8Kの自動セグメント領域管理（ASSM）の表領域を使用してデータを挿入したとします。 コード・リスト4："最終行"テストのための表の作成
SQL> create table t ( x int, y varchar2(4000), z varchar2(4000) );
Table created.
SQL> insert into t values ( 1, rpad('*',1,'*'), rpad('*',1,'*') );
1 row created.
SQL> connect /
Connected.
SQL> insert into t values ( 2, rpad('*',3000,'*'), rpad('*',3000,'*') );
1 row created.
SQL> connect /
Connected.
SQL> insert into t values ( 3, rpad('*',3000,'*'), rpad('*',3000,'*') );
1 row created.
SQL> connect /
Connected.
SQL> insert into t values ( 4, rpad('*',1,'*'), rpad('*',1,'*') );
1 row created.
ここではほぼ間違いなく、行"4"がT表の"最終"行であり最新の行です。しかし、リスト5のように、これらの行について、保存されているデータベース・ブロックを調査すると、状況が異なることが分かります。 コード・リスト5：ROWIDの順序を確認する問合せ
SQL> connect /
Connected.
SQL> select dbms_rowid.rowid_block_number(rowid), x from t;
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) X
———————————————————————————————————— —————————
18948 1
18948 2
18948 4
18949 3
SQL> select rowid, x from t order by rowid desc;
ROWID X
———————————————————— ———————
AAAaxGAAEAAAEoFAAA 3
AAAaxGAAEAAAEoEAAC 4
AAAaxGAAEAAAEoEAAB 2
AAAaxGAAEAAAEoEAAA 1
ROWIDを使用して"最終"行、つまり最新の行を検索した場合、この時点で期待を裏切られます。 行の挿入時の処理は次のとおりです。小さな行1を最初のブロックに配置し、大きめの行2を同じ最初のブロックに配置しています。しかし、次に行3を挿入しようとした場合に、データが大きすぎて行1と行2のある最初のブロックには収まりません。そのため、行3は表の2番目のブロックに配置されます。 ところが、行4の挿入時には、また小さなデータであるため、最初のブロックに十分な空き領域があります。 行は、保存するのに十分な空き領域のあるブロックに保存されます。 表の"最終ブロック"に配置されるわけではありません。最終ブロックに配置した場合は、表のサイズが拡大し続けることになります。 削除後に残された領域を再利用できなくなります。 表の"最新の行"を検索する必要がある場合、新しい行を特定するための何らかのデータ（たとえばタイムスタンプ）を、それぞれの行に関連付けておく必要があります。 ORA_ROWSCNが解決策になると言う人もいるかもしれませんが、"新しい行"の検索のためにORA_ROWSCNを使用するにはオーバーヘッドが大きすぎます。毎回、1行1行を調べる必要があるためです。 REDOとグローバル一時表標準的なDML（データ操作言語）で演算を行う場合に、一時表によってREDOは生成されますか。 すべてのDML演算でUNDOが生成されるため、グローバル一時表に対するすべてのDML演算でもREDOが生成されると思っているのですが。 一言で言えば、一時表ブロックに対してREDOは生成されません。 ただし、一時表ブロックに対してUNDOが生成される場合は、REDOも生成されます。 そのため、グローバル一時表に対する多くの演算で、UNDO生成の副作用としてREDOが生成されます。 リスト6の例は、REDOが生成される演算とREDOが生成されない演算をそれぞれ示しています。 コード・リスト6：ダイレクト・パスINSERTではREDOは作成されない
SQL> create global temporary table gtt
2 on commit preserve rows
3 as
4 select * from all_objects where 1=0;
Table created.
SQL> set autotrace on statistics
SQL> insert into gtt select * from all_objects;
72259 rows created.
Statistics
———————————————————————————————
…
412112 redo size
…
72259 rows processed
SQL> insert /*+ append */ into gtt select * from all_objects;
72259 rows created.
Statistics
———————————————————————————————
…
0 redo size
…
72259 rows processed
SQL> set autotrace off
リスト6では、ダイレクト・パスINSERTを使用した場合のREDOのサイズは0です（ただし、挿入後のデータを読み取るには、INSERTをコミットしておく必要があります）。これは、ダイレクト・パスINSERTによってUNDO生成が省略されるためです。 従来型パス・ロードでは、412,112バイトのREDOが生成されていますが、これはUNDO情報を保護する目的のみで生成されたものであり、ロードされたデータを保護する目的ではありません。 このことは、従来型パスINSERTを"通常の"表に使用した場合に明らかです。
SQL> create table t
2 as
3 select * from all_objects where 1=0;
Table created.
SQL> set autotrace traceonly statistics;
SQL> insert into t select * from
all_objects;
72862 rows created.
Statistics
——————————————————————————————
…
8546004 redo size
…
72259 rows processed
グローバル一時表によって、通常は生成されるREDOのサイズが大幅に削減されますが、まったく生成されないということは一般的にはありません。 複雑な一意制約ある表について、あるフィールドが特定の値の場合に、他の特定の2つの列を一意にするというデータ・ルールがあります。 具体的には、t_resource_type列の値が100000、1000001、1000002のいずれかである場合、t_resource_address1とt_resource_hst_idの値を一意とする必要があります。 これを実現する方法を教えてください。 ファンクション索引か、仮想列（Oracle Database 11g以降のみ）を使用して簡単に実現できます。 この両方について説明します。 まずは、表を作成します。 SQL> create table test_data 2 ( 3 t_resource_type number(8) 4 ,t_resource_address1 varchar2(50) 5 ,t_resource_hst_id number(11) 6 ); Table created. 次に、一意索引、具体的に言えば一意のファンクション索引を追加します。
SQL> create unique index t_idx
on test_data (
2 case when t_resource_type in
(100000, 1000001, 1000002)
then t_resource_address1
end,
3 case when t_resource_type in
(100000, 1000001, 1000002) then
t_resource_hst_id
end
4 );
Index created.
2つのCASE文は次のいずれかの値を返します。 Null, Null – t_resource_type列の値が100000、1000001、1000002のいずれにも当てはまらない場合 または t_resource_address1, t_resource_hst_id – t_resource_type列の値がこれらの値のいずれかに当てはまる場合 Null, Nullの値は常に一意であるとみなされるため、索引内には登場しません。この索引では、リソース・タイプが指定した値セットのいずれかに当てはまる場合のみ、表内の行に索引を設定します。 これで、表内の一部の行のみに対する一意索引が作成されます。 Oracle Database 11g以降では、リスト7のように仮想列を使用することで、実際の制約を適用できます。 コード・リスト7：仮想列による実際の制約
SQL> alter table test_data
2 add (
3 t_resource_address1_unq varchar2(50)
4 generated always as
5 (case when t_resource_type in (100000, 1000001, 1000002) then
t_resource_address1 end)
6 )
7 /
Table altered.
SQL> alter table test_data
2 add (
3 t_resource_hst_id_unq number(11)
4 generated always as
5 (case when t_resource_type in (100000, 1000001, 1000002) then
t_resource_hst_id end)
6 )
7 /
Table altered.
SQL> alter table test_data
2 add constraint address_hst_id_unique
3 unique (t_resource_address1_unq,t_resource_hst_id_unq);
Table altered.
このアプローチでは、表に2つの列が追加され、リソース・タイプの値が指定した値セットのいずれかに当てはまる場合のみ、これらの列に値が表示されます。それ以外の場合はNullです。 これらは表の"実際の"列であるため、従来型の制約を適用できます。 仮想列であるため記憶域は消費されませんが、"通常の"列とほぼ同様の操作を実行できます。 最後になりますが、これらの2つのアプローチは、両方ともファンクション索引を作成しているという点でほぼ同じものです。
Tom KyteはオラクルのServer Technologies部門に籍を置くデータベース・エバンジェリストで、1993年からオラクルに勤務しています。Expert Oracle Database Architecture（Apress、2005年/2010年）、Effective Oracle by Design（Oracle Press、2003年）などの著書があります。 |
Printer View