Database
技術記事
Ask Tom
2013年9/10月 |
テクノロジー：Ask Tom
Oracle Database 12cについて：パート1
Tom Kyte著
オラクルの技術者が、改善されたデフォルト値、大きなデータ型の処理、FETCHについて初めて説明します。
Ask Tomの各コラムでは通常、過去2か月の間にユーザーが投稿した質問を3つか4つ取り上げて、それらの質問と回答を紹介しています。しかし、以後4回のコラムでは、Oracle Database 12cの重要な機能について説明します。これらの重要な機能はすべて、2012年にサンフランシスコで開催されたOracle OpenWorldで筆者が行ったプレゼンテーション"12 Things About Oracle Database 12c"で取り上げたものです（このプレゼンテーションのスライドは、asktom.oracle.comのFilesタブで公開しています）。このコラムではまず、次の3つのOracle Database 12c機能について説明します。
-
改善されたデフォルト値
-
大きなデータ型
-
上位Nの問合せ
改善されたデフォルト値
デフォルトの列値の作成機能は、以前からSQLに存在しています。しかし、このSQLの機能にはさまざまな制限があり、やや限界がありました。たとえば、SEQUENCEオブジェクトを使用してデフォルト値を指定することはできませんでした。また、デフォルト値を表に挿入するか表を更新する場合に、SQL文内でDEFAULTキーワードを使用するか、INSERT文からその列を完全になくす必要がありました。さらに、デフォルト値とともに、NULL値を許可する新規の列を追加する操作は、オフラインで行う必要がありました。しかし、Oracle Database 12cでは、これらの制限や機能上の制約が取り除かれました。
取り除かれた制約：SEQUENCEからのデフォルト値の生成。Oracle Database 12cでは、順序の.NEXTVAL属性を使用して、デフォルトの列値を作成できるようになりました。たとえば、次のコードを見てください。
SQL> create sequence s;
Sequence created.
SQL> create table t
2 ( x int
3 default s.nextval
4 primary key,
5 y varchar2(30)
6 );
Table created.
SQL> insert into t (x,y)
2 values ( default, 'hello' );
1 row created.
SQL> insert into t (y)
2 values ( 'world' );
1 row created.
SQL> select * from t;
X Y
———————— ————————
1 hello
2 world このコードは、主キーのデフォルトの列値を順序値から作成できることを示しています。従来使用していたトリガーは使用していません。つまり、Oracle Database 12cでは、次のような手続き型のコードに置き換わって、上の例のCREATE TABLE文にあるDEFAULT S.NEXTVALを使用します。
SQL> create trigger t 2 before insert on t 3 for each row 4 begin 5 if (:new.x is null) 6 then 7 :new.x := s.nextval; 8 end if; 9 end; 10 / Trigger created.
順序の参照を使用したデフォルトの列値の作成の代わりに、IDENTITY型を使用することもできます。IDENTITY型により、順序が生成され、その順序が表に関連付けられます。たとえば、次のCREATE TABLE文を見てください。
SQL> create table t 2 ( x int 3 generated as identity 4 primary key, 5 y varchar2(30) 6 ) 7 / Table created.
この文を実行すると、（前述のCREATE TABLE文でDEFAULT S.NEXTVALを明示的に呼び出したように）順序を明示的に作成しなくても、表Tには同じデータが読み込まれます。スキーマを調査すれば、この順序の存在を確認できます。
SQL> select object_name, object_type 2 from user_objects 3 / OBJECT_NAME OBJECT_TYPE —————————————————— ————————————— T TABLE ISEQ$$_90241 SEQUENCE SYS_C0010233 INDEX
ただし、次のように、この表を削除して、リサイクル・ビンからも削除した場合、この順序も削除されることに注意してください。
SQL> drop table t purge; Table dropped. SQL> select object_name, object_type 2 from user_objects 3 / no rows selected
IDENTITYでは内部的に順序が使用されるため、その内部的な順序のあらゆる設定についても制御できます。たとえば、次のCREATE TABLE文を見てください。
SQL> create table t 2 ( x int 3 generated by default 4 as identity 5 ( start with 42 6 increment by 1000 ) 7 primary key, 8 y varchar2(30) 9 ) 10 / Table created.
この文は、START WITHとINCREMENT BYの値を制御できることを示しています。さらに、GENERATEDのみの代わりにGENERATED BY DEFAULT文を使用することで、デフォルトのIDENTITY値をオーバーライドできます。次のSQLでこの点を示します。このSQLでは、値として1を挿入した後にさらに2行を挿入した結果、IDENTITYによりデフォルト値を生成できています。
SQL> insert into t (x,y)
2 values ( 1, 'override' );
1 row created.
SQL> insert into t (x,y)
2 values ( default, 'hello' );
1 row created.
SQL> insert into t (y)
2 values ( 'world' );
1 row created.
SQL> select * from t;
X Y
—————————— ———————————
1 override
42 hello
1042 world
改善された機能：NULL列に対するデフォルト値の作成。Oracle Database 12cでは、DEFAULTキーワードを使用した場合やINSERT文から列を完全になくした場合だけでなく、列の値をNULLに明示的に設定した場合にも、デフォルトの列値を作成できるようになりました。
過去のバージョンでは、列にデフォルト値を使用する場合、INSERT/UPDATE文内でDEFAULTキーワードを使用するか、INSERT/UPDATE文からその列を完全になくす必要がありました。つまり、特定の状況でのみデフォルト値を使用するには、2つ以上のINSERT/UPDATE文を、複雑なif/then/else構文とともに使用する必要がありました。たとえば、列Xにデフォルト値が設定されている状態で、状況によってオーバーライド値を挿入したりデフォルト値を使用したりするには、次のようなコードを記述する必要がありました。
if (x is_to_be_defaulted) then insert into t (x, … ) values ( DEFAULT, … ); else insert into t (x, … ) values ( :x, … ); end if;
1つの列に対して、状況に応じてデフォルト値を作成する必要がある場合には、このコードでも問題ないのですが、2つ、3つ、あるいはさらに多くの列がある場合はどうなるでしょうか。これを実現するために、どれほど多くのINSERTやUPDATEの組み合わせが複雑なif/then/elseブロックとともに必要になるかを考えてみてください。Oracle Database 12cでは、列にNULL値を明示的に挿入した場合にデフォルトの列値を作成できるようになりました。次に例を示します。
SQL> create table t 2 ( x number 3 generated as identity 4 primary key, 5 y varchar2(30), 6 z number default ON NULL 42 7 ) 8 / Table created.
z number default ON NULL 42を使用することで、列Zに明示的にDEFAULTと設定した場合や、INSERT文からこの列をなくした場合だけではなく、明示的にNULLを挿入した場合にも、列Zにデフォルト値が設定されるようになります。次に例を挙げます。
SQL> insert into t (y) 2 values ( 'just y' ); 1 row created. SQL> insert into t (y,z) 2 values ( 'y with z set to null', null ); 1 row created. SQL> insert into t (y,z) 2 values ( 'y and z', 100 ); 1 row created. SQL> select * from t; X Y Z ———— —————————————————————— ———— 1 just y 42 2 y with z set to null 42 3 y and z 100
この例から分かるように、列Zの値は、両方のケースでデフォルト値の42として作成されています。また、Zに対してこのように宣言することで、明示していなくても、ZをNOT NULLとして定義する効果が得られました。
SQL> select column_name, nullable 2 from user_tab_columns 3 where table_name = 'T' 4 order by column_id 5 / COLUMN_NAME N ——————————— — X N Y Y Z N
オンライン操作の追加：列の追加に関する改善機能。Oracle Database 11gでは、列にデフォルト値が設定され、かつ列がNOT NULLと定義されている場合に、高速な列の追加を実行できました（高速な列の追加については、Arup Nandaがbit.ly/16tQNChで説明しています）。しかし、デフォルト値が設定され、NULL値を許可する列を追加しようとした場合は、ADD COLUMN操作で非常に長い時間がかかり、大量のUNDOとREDOが生成され、その操作の実行中は表全体がロックされることがありました。Oracle Database 12cでは、列の追加プロセスでそのように、長い時間がかかったり、大量のファイルが発生したり、ロックされたりすることはありません。
このことを示すために、次のようにALL_OBJECTSをある表にコピーし、asktom.oracle.comに投稿されているshow_spaceユーティリティを使用して、その領域のブロック数およびバイト数を測定します。
SQL> create table t
2 as
3 select *
4 from all_objects;
Table created.
SQL> exec show_space('T')
…
Full Blocks .... 1,437
Total Blocks........... 1,536
Total Bytes............ 12,582,912
Total MBytes........... 12
…
PL/SQL procedure successfully completed.
次に、この表Tに1つの列を追加し、この列にサイズの大きなデフォルト値を設定します。追加する列はCHAR(2000)であるため、常に2,000バイトが消費されます。CHAR型は常に固定長で空白部分がパディングされるためです。表Tには87,000件を超えるレコードが存在するため、列の追加操作には通常は非常に長い時間がかかります。しかし、この結果から分かるように、Oracle Database 12cでのこの追加操作は、ほぼ一瞬で完了します。
SQL> set timing on SQL> alter table t add (data char(2000) default 'x'); Table altered. Elapsed:00:00:00.07
Oracle Database 11gで同じ操作を実行すると、実行時間が次のように測定されます。
SQL> set timing on SQL> alter table t add (data char(2000) default 'x'); Table altered. Elapsed:00:00:28.59
明らかに、実行時間に大きな差が見られます。さらに、列を追加した状態で表のサイズを確認すると、Oracle Database 12cでは次のように表示されます。
SQL> exec show_space('T')
…
Full Blocks .... 1,437
Total Blocks........... 1,536
Total Bytes............ 12,582,912
Total MBytes........... 12
…
PL/SQL procedure successfully completed.
この結果より、表のサイズがまったく増加していないことが分かります。しかし、Oracle Database 11gで同じテストを実行すると、表のサイズが約9MBから192MBに増加します。さらに、Oracle Database 11gでは、表内にあるほぼすべての行が数桁の単位で拡大しているため、行移行が発生しました。そのような表はおそらく、以前のリリースでは再編成の対象となりますが、Oracle Database 12cでは再編成の対象となりません。
大きなデータ型
Oracle8 Databaseでは、VARCHAR型のサイズが255バイト（Oracle7）から4,000バイトまで大幅に拡大しました。今回のOracle Database 12cリリースでは、このサイズが4,000バイトから32KBまで拡大し、SQL VARCHAR2、NVARCHAR2、RAWデータ型のサイズがそれぞれ、PL/SQLの対応するデータ型と揃えられました。
デフォルトでは、この新機能は無効であり、DBAが有効にする必要があります。有効にするには、init.oraの新しいパラメータであるMAX_STRING_SIZEをEXTENDEDに設定します。このパラメータを設定すれば、次のような文を実行できるようになります。
SQL> create table t ( x varchar(32767) ); Table created.
次に、RPAD、LPAD、TRIMなどの文字列関数を使用します。
SQL> insert into
t values ( rpad('*',32000,'*') );
1 row created.
SQL> select length(x) from t;
LENGTH(X)
——————————————
32000
以前は、RPADなどの組込みの文字列関数では、4,000バイトしか返すことができませんでしたが、現在は、戻り型がVARCHAR2の場合は最大32KBを返すことができます。
内部的には、Oracle Database 12cはラージ・オブジェクト（LOB）を使用して、これらの大きな文字列型やRAW型を格納しています。挿入された文字列が4,000バイト以内の場合は、従来のVARCHAR2型と同様に、データベースによって表のデータベース・ブロックにデータが保管されます。一方、文字列が4,000バイトを超える場合は、通常の方法からは外れて、データベースによってLOBセグメントおよび索引内に透過的に格納されます。
上位Nの問合せとページ区切り
Ask Tom（asktom.oracle.com）に寄せられる多数の質問のうち、非常によくあるのは、"M件の結果セットからN行を取得する方法（結果セットのページ区切りを行う方法）"と、"結果セットの最初のNレコードを取得する方法"についてです。実は、過去数年にわたるOracle Magazineの複数の記事で、これらの質問にすでに回答しています（"On Top-n and Pagination Queries"と"On ROWNUM and Limiting Results"）。これらの記事では、質問の内容を実現する方法について説明していますが、説明した方法は面倒で分かりづらく、必ずしも移植性が高いとも言えません。
Oracle Database 12cでは、ANSI規格のFETCH FIRST/NEXT句とOFFSET句をサポートしています。これらの句は、行制限句と呼ばれます。行制限句を使用することで、結果セットから最初のNレコードを容易に取得できます。または、あるレコード・セットをスキップした後の最初のNレコードを容易に取得することもできます。そのため、結果セット内のページ区切りを簡単に実行できます。図1に、行制限句の構文を示します。
図1：行制限句の構文
行制限句をSQL SELECT文の末尾に追加するだけで、特定のレコード・セットをフェッチできます。複数のインライン・ビュー・レイヤーやWHERE句を使用する必要はありません。従来は、ROWNUMやROW_NUMBER()を使用していたため、インライン・ビューやWHERE句は慎重に配置する必要がありました。
たとえば、次の表Tがあるとします。
SQL> create table t 2 as 3 select * from all_objects; Table created. SQL> create index t_idx on t(owner,object_name); Index created.
ここで、OWNERとOBJECT_NAMEでソートした後の最初の5行を取得するとします。この場合、リスト1のように、このSQL問合せにFETCH FIRST N ROWSを追加するだけで実現できます。
コード・リスト1：FETCH FIRSTを追加した単純なSELECT問合せ
SQL> select owner, object_name, object_id
2 from t
3 order by owner, object_name
4 FETCH FIRST 5 ROWS ONLY;
…
——————————————————————————————————————————————————————————————————————————————
| Id |Operation | Name|Rows |Bytes |Cost (%CPU)|Time |
——————————————————————————————————————————————————————————————————————————————
| 0|SELECT STATEMENT | | 5 | 1450 | 7 (0)|00:00:01|
|* 1| VIEW | | 5 | 1450 | 7 (0)|00:00:01|
|* 2| WINDOW NOSORT STOPKEY | | 5 | 180 | 7 (0)|00:00:01|
| 3| TABLE ACCESS BY INDEX ROWID|T |87310 | 3069K| 7 (0)|00:00:01|
| 4| INDEX FULL SCAN |T_IDX| 5 | | 3 (0)|00:00:01|
——————————————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
—————————————————————————————————————————————————————————————————
1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=5)
リスト1の条件情報から分かるように、行制限句は内部で透過的にROW_NUMBER()を使用しており、分析を使用するように問合せを書き換えています。つまり、行制限句により、従来は手作業で実行していたことを非常に容易に実行できるようになります。
結果セット内のページ区切りを行うには（つまり結果セット内の特定のページから一度にN行を取得するには）、OFFSET句を追加します。リスト2では、ある結果セットの最初の5行をスキップして、その次の5行を取得します。
コード・リスト2：OFFSET FETCHを追加した単純なSELECT問合せ
SQL> select owner, object_name, object_id
2 from t
3 order by owner, object_name
4 OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
…
—————————————————————————————————————————————————————————————————————————————
| Id |Operation |Name |Rows |Bytes |Cost (%CPU)|Time |
—————————————————————————————————————————————————————————————————————————————
| 0|SELECT STATEMENT | | 5| 1450 | 7 (0)|00:00:01|
|* 1| VIEW | | 5| 1450 | 7 (0)|00:00:01|
|* 2| WINDOW NOSORT STOPKEY | | 5| 180 | 7 (0)|00:00:01|
| 3| TABLE ACCESS BY INDEX ROWID|T |87310| 3069K| 7 (0)|00:00:01|
| 4| INDEX FULL SCAN |T_IDX| 5| | 3 (0)|00:00:01|
—————————————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
———————————————————————————————————————————————————————————————————————
1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE WHEN (5>=0)
THEN 5 ELSE 0 END +5 AND "from$_subquery$_003"."rowlimit_$$_rownumber">5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OWNER","OBJECT_NAME")<=CASE WHEN
(5>=0) THEN 5 ELSE 0 END +5)
リスト2から分かるように、データベースは前述と同様に内部的に、インライン・ビューおよび分析を使用するように問合せを書き換えており、以前は分かりづらく複雑であった操作を自動化しています。
なお、実際には、ハードコードされたリテラルではなくバインド変数を使用することになります。つまりこの例の場合、数値5を使用した部分で、数値5をバインドすることになります。
次のステップ
|
Tom Kyteは、オラクルのServer Technologies部門に籍を置くデータベース・エバンジェリストで、1993年からオラクルに勤務しています。Expert Oracle Database Architecture（Apress、2005年/2010年）、Effective Oracle by Design（Oracle Press、2003）などの著書があります。
ご意見ご感想をお寄せください。
Printer View