ヒントとは？
Oracle DatabaseにはSQL文のオプティマイザに対する指示を行う「ヒント」と呼ばれる構文があります。ヒントはSQL文中に特殊な表記のコメントとして記述します。ヒントの多くは実行計画に対して指示を行います。
下記の例はヒントを指定しない場合、INDEX UNIQUE SCANになっているSQL文にFULLヒントを指定して、実行計画をTABLE FULL SCANに変更しています。
SQL> SELECT * FROM hint1 WHERE c1=1000;
C1 C2
---------- ----------
1000 data1
実行計画
----------------------------------------------------------
Plan hash value: 664097318
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| HINT1 | 1 | 11 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C006410 | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("C1"=1000)
SQL> SELECT /*+ FULL(hint1) */ * FROM hint1 WHERE c1=1000;
C1 C2
---------- ----------
1000 data1
実行計画
----------------------------------------------------------
Plan hash value: 1290803087
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 431 (2)| 00:00:07 |
|* 1 | TABLE ACCESS FULL| HINT1 | 1 | 11 | 431 (2)| 00:00:07 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=1000)
ヒントはコメント内に記述するので、ほとんどのヒントは以下の仕様になっています。
- 構文を間違えてもエラーにはならない。
- 構文は合っていてもテーブル名やインデックス名が間違っていてもエラーにはならない。
- 実行計画は変わってもSQL文の実行結果は変わらない。
しかし、ごく稀に上記の仕様を無視するヒントがあるためここで紹介します。これらのヒントはSQL文の動作自体を変えたり、発生するエラー番号を変えたりする特殊なヒントです。
IGNORE_ROW_ON_DUPKEY_INDEX ヒント
このヒントはINSERT文で制約違反が発生した場合にエラーの発生を抑止して、INSERT文の実行も無視します。
データの準備
下記の例ではc1列に主キーを設定したhint1テーブルを作成し、１レコード格納しています。
SQL> CREATE TABLE hint1(c1 NUMBER PRIMARY KEY, c2 VARCHAR2(10));
表が作成されました。
SQL> INSERT INTO hint1 VALUES (100, 'data1');
1行が作成されました。
SQL> COMMIT;
コミットが完了しました。
ヒントによる動作変更
次に主キー違反が発生するレコードをINSERTします。通常はORA-00001エラーが発生しますが、IGNORE_ROW_ON_DUPKEY_INDEXヒントを指定して主キー違反のレコードでINSERT文を実行するとエラーが発生しません。
BEFORE INSERTトリガーは実行されますが、AFTER INSERTトリガーは実行されないようです。
SQL> INSERT INTO hint1 VALUES (100, 'duplicate');
INSERT INTO hint1 VALUES (100, 'duplicate')
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(SCOTT.SYS_C006410)に反しています
SQL> INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(hint1(c1)) */ INTO hint1 VALUES (100, 'duplicate');
0行が作成されました。
ヒントの構文を間違えるとヒントは無効になります（制約エラーが発生）が、制約列が間違っている場合には構文エラー（ORA-38913）が発生します。
SQL> INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(hint1(c1)( */ INTO hint1 VALUES (100, 'duplicate');
INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(hint1(c2)( */ INTO hint1 VALUES (100, 'duplicate')
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(SCOTT.SYS_C006410)に反しています
SQL> INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(hint1(c2)) */ INTO hint1 VALUES (100, 'duplicate') ;
INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(hint1(c2)) */ INTO hint1 VALUES (100, 'duplicate')
*
行1でエラーが発生しました。:
ORA-38913: 索引ヒント内で指定されている索引が無効です
CHANGE_DUPKEY_ERROR_INDEXヒント
CHANGE_DUPKEY_ERROR_INDEXヒントは、一意制約が発生した場合のエラーをORA-00001からORA-38911に変更します。
主キー以外の特定の列の一意キーで一意制約違反が発生した場合にエラー番号を変更することができます。
テーブルの準備
主キーと一意キーを指定したテーブルhint2を作成し、１レコードを挿入します。
SQL> CREATE TABLE hint2(c1 NUMBER PRIMARY KEY, c2 NUMBER UNIQUE, c3 VARCHAR2(10));
表が作成されました。
SQL> INSERT INTO hint2 VALUES (100, 100, 'data1');
1行が作成されました。
このテーブルにc2列の一意キー違反となるレコードを挿入しようとすると、通常はORA-00001が返ります。
SQL> INSERT INTO hint2 VALUES (200, 100, 'data2');
INSERT INTO hint2 VALUES (200, 100, 'data2')
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(SCOTT.SYS_C006412)に反しています
CHANGE_DUPKEY_ERROR_INDEXヒントを指定すると、c2列の一意キー違反の場合にはORA-38911が返ることがわかります。
SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(hint2(c2)) */ INTO hint2 VALUES (200, 100, 'data2');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(hint2(c2)) */ INTO hint2 VALUES (200, 100, 'data2')
*
行1でエラーが発生しました。:
ORA-38911: 一意制約(SCOTT.SYS_C006412)に反しています
ヒントの構文が間違っている場合にはヒントは無視されますが、指定された列に一意キーが存在しない場合にはSQL文自体がエラー（ORA-38913）になります。
SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(hint2(c2)( */ INTO hint2 VALUES (200, 100, 'data2');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(hint2(c2) */ INTO hint2 VALUES (200, 100, 'data2')
*
行1でエラーが発生しました。:
ORA-00001: 一意制約(SCOTT.SYS_C006412)に反しています
SQL> INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(hint2(c3)) */ INTO hint2 VALUES (200, 100, 'data2');
INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(hint2(c3)) */ INTO hint2 VALUES (200, 100, 'data2')
*
行1でエラーが発生しました。:
ORA-38913: 索引ヒント内で指定されている索引が無効です
RETRY_ON_ROW_CHANGEヒント
UPDATE文またはDELETE文で有効なヒントです。このヒントを指定すると、変更対象のレコードが決定された時点から、ブロックが実際に変更される時点までの間に、
対象レコード内の1つ以上のレコードのORA_ROWSCNが変更された場合に、操作が再試行されます。
IGNORE_ROW_ON_DUPKEY_INDEXヒントとRETRY_ON_ROW_CHANGEヒントはOracle Database 11g Release 2から、CHANGE_DUPKEY_ERROR_INDEXヒントはOracle Database 12c Release 1から利用できます。