【Oracle】SQL Loaderのdirect=trueオプションでKEY重複データが取り込めることに対する愚痴

2017-05-24

SQL Loaderでdirect=trueをオプション指定すると、KEY重複してるデータが登録できてしまう。
PRIMARY KEY INDEXを張ってる場合、この結果、そのPK INDEXが破損するので、
そのPK INDEXを明示的に指定しているhint文があると、SQLエラーになる。
ひどい話である。
SQL LoaderもPKもhint文も結局はOracleの技術なはずなのに、
同社内の技術同士で首を絞めあう矛盾を作り上げてしまっている。
なんでそんな作りにしたんだOracleは…理解できない。
………というのの愚痴とメモ。

例えば以下のようなテーブルがあったとする
■TEST_TAB5

create table TEST_TAB5   
(  
   test_no char(10)   
  ,test_memo varchar(200)  
  ,constraints pk_test_tab5 primary key (test_no)  
)  
/

見ての通りPKは「TEST_NO」である。ついでにこの項目に対し、「PK_TEST_TAB5」という名前でPK INDEXを張っている。

テーブルのCREATE直後でデータが０件の場合、PK INDEXは正常状態になっている。
下記のSQLでINDEXの状態を確認できる。
SQLを実行すると「STATUS」項目の値は「VALID」になっている。

select  
  a.STATUS   
from USER_INDEXES a  
where a.INDEX_NAME = 'PK_TEST_TAB5'

なので、下記のようなSQLも問題なく動作する（データが入ってない＝０件なので結果も０件だが）。

select /*+ INDEX(a PK_TEST_TAB5) */  
a.*  
from TEST_TAB5 a  
where a.TEST_NO = '1'

ここまではまあ前段であり、当然の話しなのであるが……

ここで以下のようなCSVを用意し、SQL Loaderで取り込んでみる。
■TEST_TAB5_data.csv

1,メモ１です。　←これと  
2,メモ２です。  
3,メモ３です。  
4,メモ４です。  
1,メモ１重複です！　←これが重複

項目の並びはテーブルの定義と合わせてある。左側が「TEST_NO」つまりPKにあたる項目で、右側が「TEST_MEMO」。
見てわかる通り、赤太字にしてある一番下の行は、一番上の行とPKが重複（両レコードともTEST_NO='1'）しており、
普通に考えるとテーブル定義に違反するので一番下の行は一意制約違反になって取り込めない。
が、SQL Loaderでdirect=trueオプションを指定する↓とこれがなぜか取り込める。

sqlldr testuser/testpass@testdb control=TEST_TAB5.ctl direct=true

ちなみにコントロールファイルは以下↓

options(rows=-1,errors=-1,skip=0,bindsize=67108864,readsize=67108864)  
LOAD DATA  
INFILE 'TEST_TAB5_data.csv' "str '\r\n'"  
APPEND  
INTO TABLE TEST_TAB5  
FIELDS TERMINATED BY ','  
TRAILING NULLCOLS  
(  
        TEST_NO  
       ,TEST_MEMO  
)

実行後にテーブルみてみるとマジでデータが入ってることが確認できる。

★TEST_NOTEST_MEMO

1	メモ１です。
2	メモ２です。
3	メモ３です。
4	メモ４です。
1	メモ１重複です！

↑一番上の行と一番下の行は同じTEST_NO = '1'だが、同一KEYの別レコードとして実在している。
ということで、PK違反するデータが格納できてしまうのである。

ここでさっきの、PK INDEXを使ったhint文付のSQLを実行してみると

select /*+ INDEX(a PK_TEST_TAB5) */  
a.*  
from TEST_TAB5 a  
where a.TEST_NO = '1'  
------------------------------------------  
ORA-01502: 索引'TESTUSER.PK_TEST_TAB5'またはそのパーティションが使用不可の状態です。

となってSQLエラーになる。
この例ではWHERE句に実際にPK重複しているTEST_NO='1'を記載しているが、
TEST_NO='4'とか、実態として重複していないレコードを特定する条件でも同じ。
WHERE句云々ではなく、hint文に記載されたロジックでOracleが動けないという意味だからである（つまりWHERE句なしでもSQLエラーになる）。

ちなみにhintなしなら問題なく動作する。↓のようなSQLとか。

select   
a.*  
from TEST_TAB5 a  
where a.TEST_NO = '1'

これ↑投げると実際２行返って来る（KEY指定してるはずなのに）。

この状態では、PK INDEXの状態が先ほどのように「正常」ではなく、「使用不可」という状態になっている。
上でも投げている下記↓のSQLを実行すると、返って来るSTATUS値が「UNUSABLE」になっている。

select  
  a.STATUS   
from USER_INDEXES a  
where a.INDEX_NAME = 'PK_TEST_TAB5'

SQL Loaderのdirect=trueオプションというのは
正確には「ダイレクト・パス・ロード」という技術らしくて、
簡単に言えば「普通のSQL Loaderより速い速度でロードできる」ようなオプションのことを指している。
その代償としてKEY重複のチェックは無視される－というより
・元データにKEY重複がなく
・ロードされる側のテーブルもSQL Loader実行前には中身が０件（空テーブル）
というような比較的限定された環境下で動かすことを前提としているようである。
Oracleのページだと難しいこと（下記↓、一部引用）書いてある
--【引用ここから】--------------------------------------------------------
従来型パス・ロードでは、Oracleデータベースの表に対して（1つ以上の）SQL INSERT文が実行されます。ダイレクト・パス・ロードでは、Oracleデータ・ブロックをフォーマットし、データ・ブロックを直接データ・ファイルに書き込むため、Oracleデータベースのオーバーヘッドが大幅に削減されます。ダイレクト・パス・ロードでは、データベース・リソースに対して他のユーザーとの競合が発生しないため、ディスク速度に近い速度でデータをロードできます。
--【引用ここまで】--------------------------------------------------------
（引用元：http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19211-01/ldr_modes.html）
が、要は「通常とは異なるやり方でデータを登録するので一般的にINSERTに際して働くチェックとかは動かないよ」と言っているのであろう。

なお、似たような単語に「ダイレクト・パス・インサート」（APPENDというhint句を使ったINSERT文）ってのがあるが、
最初は同じモンだと思ってたのだが、これは今回の件（ダイレクト・パス・ロード）とはちょっと違うらしい。
（INSERT処理の処理速度向上を目的としているという点では同じようだが）
「ダイレクト・パス・インサート」は、例えば以下のようなSELECT INSERT処理であるが、
こっちは一意制約違反がちゃんと返ってきた。

insert /*+ APPEND */ into TEST_TAB5  
select *  
from (select '1' as test_no , N'APPENDによるメモ１です。' as TEST_MEMO from dual  
      union all  
      select '2' as test_no , N'APPENDによるメモ２です。' as TEST_MEMO from dual  
      union all  
      select '3' as test_no , N'APPENDによるメモ３です。' as TEST_MEMO from dual  
      union all  
      select '4' as test_no , N'APPENDによるメモ４です。' as TEST_MEMO from dual  
      union all  
      select '1' as test_no , N'APPENDによるメモ１重複です！' as TEST_MEMO from dual  
     )  
------------------------------------------  
ORA-00001: 一意制約(TESTUSER.PK_TEST_TAB5)に反しています

ダイレクト・パス・インサートは、実際には、バルクで大量データを対象とした場面で使用することが多いようで、
上で挙げた例のように少量のデータを相手にした単発のINSERTにはほとんど無意味だそうなので（※）、
実用場面で使うとまた違ってくるのかもしれないが…
（※）参考：http://www.shift-the-oracle.com/inside/direct-pass-insert.html

SQL Loaderのdirect=trueオプションを使う場合、KEY重複でもデータが登録されるというのは、
Oracleで特にローダー使ってる人の間では比較的常識に近いことのようだ。
むしろ「当たり前じゃん何言っちゃってんの？(^^;」って感じで言われることがあった。
でも一意性の定義もSQL Loaderも作ったのはOracleなわけだから
そういう「自分で自分の首絞める技術」をなんでわざわざ用意するの？ってのは甚だ疑問である。
Oracleのこういうところは正直個人的に肌に合わない…
まあ俺みたいな素人には計り知れないコア技術の深い部分では、何か特別な目的を意識したものがあるのかもしれないが…