Oracle勉強していて何気に面倒なのが、テキトーな件数のレコードを持つテーブルを作ること。ので、いままでに自分の知ることが出来た範囲でやり方をメモしていく。
連番作成SQL
基本
下記はSQL Develoerのクエリ例からのコピペ。
insert into bonuses(employee_id) (select e.employee_id from employees e, orders o where e.employee_id = o.sales_rep_id group by e.employee_id);
create table emp as select * from employees;
↑は、selectの結果をそのままテーブルに入れられるので、色々と応用が利く。あるテーブルの特定列だけにしたり、joinした結果を入れたり、whereで絞った行だけにしたり。create table as selectは、テーブルコピーしたり、order byかインデックスヒントで実テーブル内の行順序を並び替えたり、など。また、insertを件数分発行するよりも、たいていは格段に高速。
オブジェクト直積
良く見かけるので、たぶんコレが一番メジャーでカンタンと思う。
select rownum from (select 0 from all_catalog where rownum <= 1000), (select 0 from all_catalog where rownum <= 1000);
上記のall_catalogは、1000行以上返すなら何でも良い。webで色んな人にそのままコピぺされても動くように、Oracleならまず間違いなく存在していて、かつ、1000行以上あるものの代表格としてall_catalogが使われるのだと思う。で、直積によって1000×1000で百万行を作っている。あとはrownumで順番な値を取り出すだけ。
フツウは直積は行数が爆発するためにまず使うことがないのだけど、その爆発する習性を上手く使っている。
LEVEL擬似列直積
しばちょう先生の試して納得！DBAへの道 第7回 表領域の管理方法を理解 から
select rownum from (select LEVEL from DUAL connect by LEVEL <= 1000), (select LEVEL from DUAL connect by LEVEL <= 1000);
まず、LEVEL擬似列ってなんだ？ ってハナシですが、Oracleの階層問い合わせ（1） （start with句、connect by句） （1/2）：CodeZineの記事がわかりやすかったです。
LEVELは本来は木構造アクセス時に使うもんなんですが。↑のSQLは木構造を表す条件が無いので、そうすると最後のノード以外は一つのノードが一つのノードをぶらさげる、数珠繋がりな構造になる。となると、それは既に木でなくリストなわけですが、しかし木構造用の構文は使える。で、木だけどもほぼリストの構造に対してLEVELを取ると、ノードには1からnの番号が振られ、事実上の連番になる、という仕組み。
ただし、LEVEL <= 100000 とか余りにデカい値を取るとメモリ不足のORA-30009が発生する。なので、上記は1000 * 1000の直積にしています。メモリが許すなら、直積にしなくても <= xに任意の値を取っても良い。
再帰With句直積
しばちょう先生の試して納得！DBAへの道 第3回 データ領域管理の理解～SQLチューニングにも挑戦～ から
select i+j from ( with data2(j) as ( select 0 from dual union all select j+1000 from data2 where j < 999000 ) select j from data2 ), ( with data1(i) as ( select 1 from dual union all select i+1 from data1 where i < 1000 ) select i from data1 );
再帰WITH句は 図でイメージするOracle DatabaseのSQL全集 第7回 再帰with句 がわかりやすい。
再帰WITH句は一見ややこしいが↑の解説ページをまず見てもらうとして。↑のクエリを理解するためには、union allの上側のクエリが出発点として一回だけ実行され、下側のクエリの条件が満たされるまで実行される、とだけ分かれば良い。で、with data1(i)は1〜1000を作り、with data2(j)は1000刻みで0〜999000を作る。あとはそれらを直積して足し算。
PL/SQL
CREATE OR REPLACE PROCEDURE CREATE_TEST_DATA AS BEGIN FOR I IN 1..1000000 LOOP INSERT INTO DEST(COLUMN1) VALUES(I); END LOOP; COMMIT; END CREATE_TEST_DATA;
泥臭いやり方ではあるものの、細かい条件指定があったり、SQLで表現するやり方わかんなかったときは手続き的にやってしまうのも手だと思う。上記はコミット間隔取らないのがアレだが、まぁ、面倒くさいし……
PL/SQL + sequence
CREATE OR REPLACE PROCEDURE CREATE_TEST_DATA2 AS BEGIN FOR I IN 1..1000000 LOOP INSERT INTO DEST(COLUMN1) VALUES(SEQ_DEST.nextval); END LOOP; COMMIT; END CREATE_TEST_DATA2;
その他メモ
ここからは下は連番以外についてとかデータ作るときに良くつかう関数のメモとか。
乱数ソート
ランダムに並び替えられたデータが欲しいとき。
select customer_id from customer order by dbms_random.value();
乱数でソートすることでランダムを表現する。ただし、乱数生成プラスorder byのコストはかなり高いので、場合と件数によってはかなり重くなる点は注意が必要。参考：ORDER BY RAND() 使うな
なお、Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス11g リリース2(11.2)DBMS_RANDOMによると、11gではDBMS_RANDOM.RANDOMは推奨されていない。使えないことは無いんだろうけど、推奨されていないものは使わないに越したことは無いと思われる。
Oracle Database 11gでは、次のサブプログラムの使用は推奨されていません。
INITIALIZEプロシージャ
RANDOMプロシージャ
TERMINATEプロシージャ
Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス11g リリース2(11.2)DBMS_RANDOM より抜粋
ランダムセレクション
1から最大値までの間で値を得るやり方。
初期データ付きでテーブル作る
図でイメージするOracle DatabaseのSQL全集 第7回 再帰with句 から
create table RowToRows(ID,FromVal,ToVal) as select 'AA',2,4 from dual union all select 'BB',8,9 from dual union all select 'CC',3,5 from dual;
ランダムな数値とか文字列とか日付とか
テーブルの列に適当な値を入れる時に良く使う関数とかプロシージャとか。
select --0以上1未満、小数点以下が38桁(精度38桁)の乱数 DBMS_RANDOM.VALUE() as a1, --1以上100以下の整数の乱数 FLOOR(DBMS_RANDOM.VALUE(1, 101)) as a2, --マイナス100以上100以下の整数の乱数 FLOOR(DBMS_RANDOM.VALUE(-100, 101)) as a3, --大文字のアルファベット文字と数字で文字列 ex 5KNX06FYFV DBMS_RANDOM.STRING('X', 10) as a4, --大/小文字が混在したアルファベット文字で文字列 ex psTZFnNGFe DBMS_RANDOM.STRING('A', 10) as a5, --印刷可能な任意の文字で文字列 ex S:d{6qUQ>m DBMS_RANDOM.STRING('P', 10) as a6, --文字列の左側をゼロ埋め ex 000000b{mqVr#K"7 lpad(DBMS_RANDOM.STRING('P', 10),16,'0'), --文字列の右側をゼロ埋め ex V~>5xI\&1M000000 rpad(DBMS_RANDOM.STRING('P', 10),16,'0'), --文字列の左側を空白埋め ex [ 31$^l[zHF'] '[' || lpad(DBMS_RANDOM.STRING('P', 10),16) || ']' from dual;
DBMS_RANDOM.VALUE(low, high)は、lowは含むがhighは含まない。DBMS_RANDOM.VALUE(1, 101)にすると、100.99999...が最大となるんでその値をFLOORで切り下げ。
FLOORに負の数入れた場合でも、n以下の最大整数を戻すことに変わりは無い。DBMS_RANDOM.VALUE(-100, 101)とした場合、最小は-100ピッタシで、FLOOR(-100)は-100を返す。-100の次に大きい（小数点でその次ってのもヘンな表現だが）数は、-99.9999..とかだが、FLOOR(-99.999...)は-100に切り下げられる。
Oracle Database SQL言語リファレンス11gリリース2(11.2) FLOOR
一定範囲内の日付からランダム。Code Tips から。
select --20130101 TO_DATE('20130101','YYYYMMDD') + 0 as d20130101, --20131231 TO_DATE('20130101','YYYYMMDD') + 364 as d20131231, --0以上364以下の乱数 FLOOR(DBMS_RANDOM.VALUE(0, 365)) as n_0to365, --2013年の日数=365 TO_DATE('20140101','YYYYMMDD') - TO_DATE('20130101','YYYYMMDD') as daysin2013, --20130101〜20131231のランダム その1 TO_DATE('20130101','YYYYMMDD') + FLOOR(DBMS_RANDOM.VALUE(0, 365)) as d2013xxxx_1, --20130101〜20131231のランダム その2 TO_DATE('20130101','YYYYMMDD') + FLOOR(DBMS_RANDOM.VALUE(0, TO_DATE('20140101','YYYYMMDD') - TO_DATE('20130101','YYYYMMDD'))) as d2013xxxx_2 from dual;
上記のランダムその1は、一年の日数が365だと固定してやっている。一月一日＋ゼロ日から＋364日までの範囲。その2は、一年の日数を計算している。
ダイレクト・パス・インサート、nologging
Oracle Database SQL言語リファレンス11gリリース2(11.2) APPENDヒント
Oracle Database SQL言語リファレンス11gリリース2(11.2) logging_clause
insert /*+ APPEND */ into dest nologging select i from ...
一応、再帰With句直積の場合で速度計測。
| 種類 | 1 | 2 | 3 |
|---|---|---|---|
| 再帰With句直積 | 1.266 | 1.344 | 1.437 |
| 再帰With句直積（append） | 1.282 | 1.187 | 1.219 |
ダイレクトパスインサートの詳細はOracle SQLチューニング講座（12）：更新／挿入／削除のSQLを高速化する3つの技とは？ (2/3) - ＠ITなどテキトーにぐぐって参照。速くなるからつって何も知らずに本番環境で使うのは極めて危険だが、使い捨てのテーブルになら積極的に使っても危険性は低いと思われる。
Oracleへのデータ投入
以下は、データの作成ではなく外部ツールなどを利用しての投入手段について。
Accessのリンクテーブルにエクセルなどからコピペ
やり方は、Accessむかむか - ODBCの設定(Access+Oracle)とか「oracle access リンクテーブル」とかでぐぐる。エクセルからコピペでOracleのテーブルにデータを放り込めるので、場合によっては手っ取り早い。
エクセルの文字列結合で複数のINSERT作る
こんな感じ。
上記キャプチャのE2セルの内容は↓な感じ。
="insert into "&$A$1&"("&$B$1&", "&$C$1&")"&" values ("&$B2&",'"&$C2&"');"
SQL*Loader
Oracle Databaseユーティリティ11gリリース2 (11.2) SQL*Loader
テキトーなデータ作りたいだけの場合はさすがにコレの出番は無いのだけど。ただ、テスト用に巨大なデータファイルを取り込みたいとかの場合は出番になるかと。最初こそ制御ファイルとかのとっつきづらさは大きいものの、ややこしいことをやらないのであればそんなに難しいもんでもない。
外部表
外部表 - オラクル・Oracleをマスターするための基本と仕組み
これ使うメリットは、データファイルを更新すると、テーブルの検索結果にすぐ反映されるところ。ただ、データファイルが外部表の定義と合わないと、実際に検索するときまでエラー内容が分からず、そのエラーメッセージも分かりやすいとは言えないので、ハマるとヤバイ。
SQL Developerのインポート機能
注意点として、これはOracleのimp/expユーティリティーとは無関係。SQL Developerのインポート機能でken_all.csvを読み込んでみる - kagamihogeの日記かコピペしてきた下記表を参照して欲しいが、一部を除いて、ファイルを1行ずつINSERTしている。手軽さで言えばAccessからぶっこめるのには劣る。
| 種類 | 実際に行われること |
|---|---|
| 挿入 | CREATE TABLE → INSERT |
| 挿入スクリプト | CREATE TABLE → INSERT |
| 外部表 | CREATE TABLE 外部表 |
| ステージング外部表 | CREATE TABLE 実テーブル → CREATE TABLE ステージング外部表 → INSERT INTO 実テーブル SELECT ステージング外部表 → DROP TABLE ステージング外部表 |
| SQL*Loaderユーティリティ | CREATE TABLE → SQL*Loader |
SQL Developer自体に、テーブルをエクセル風に編集できる画面は用意されている。が、Accessのリンクテーブル経由ほどの使い勝手は無い。参照：SQL Developerからテーブルにデータを入れる - kagamihogeの日記
A5:SQL Mk-2のテーブルエディタ機能
フリーのSQLクライアントA5:SQLのテーブルエディタ機能を使用する方法。以下は、A5:SQL Mk-2 Version 2.10.1(x64 edition) のバージョンを使用して行った。
クリップボード経由からデータを貼り付けられるため、Excel等からコピペでデータを放り込めるのは大変使い勝手がよろしい。TSV形式ならコピペで放り込めるので、適当なエディタさえあれば使用可能。
使用手順。まず、エクスプローラ上から編集対象のテーブルを開く。下記は、サンプルスキーマHR.EMPLOYEESをコピーして中身を空にしたテーブルを開いたところ。
下記は、OpenOffice Calc上の追加対象データをA5:SQLのテーブルエディタにコピペしたところ。データ元はExcelでなくてもモチロン良い。
TSV形式なら良いので下記のようなテキストをコピペしても良い。
なお、操作はキーボードだけではなく、データをコピー後に下記の場所にあるボタンを押しても良い。
A5:SQLには、他にもCSV/TSVファイルのテーブルデータのインポート機能もある。
A5:SQL Mk-2のテスト用ダミーデータ作成機能
A5:SQLのテスト用ダミーデータ作成機能を使用する方法。
下記は、OracleのサンプルHR.EMPLOYEES（をコピーしてカラにしたテーブルに）テストデータ1000件を作成してみた様子を示している。
実行後はこんな感じ。
