Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

田

田中健輔

@田中健輔

田中健輔さんの投稿

Oracleに投稿されました • 続きを読む

１）ヒントとは

ヒントは、SQLに記述し、SQLが指定したアクセスパスや実行計画でデータの取得や結合を行うためのものです。

通常、SQLを実行したとき、ORACLEデータベースでは、オプティマイザが表や索引の統計情報を基に、最適な実行計画を作成します。
そのためSQLを実行する人は、実行計画やアクセスパスを意識する必要がありませんが、特定の問合せに対してオプティマイザの決定よりも他の実行計画が有効であることが判っている場合、ヒントを使用してオプティマイザに特定の実行計画を使用するようにできます。

２）ヒントの使用法

｛SELECT | DELETE | UPDATE}  /*+  hint  [text]  */

hint：後述するヒントを記述します。複数指定する場合は空白で区切ります

text：ヒント以外のコメントがある場合、記述します

・ヒントは、SELECT文、DELETE、UPDATE文に含むことができます

・ヒントを含むコメントは、 SELECT、DELETE、UPDATEコマンドの直後に指定します

・ヒントの指定が間違っている場合、ヒントは無視されます

例）副問合せを含む文でのヒントの使用（SAMPLE表のSEQ列、SAMPLE_TEST表のCOL3に索引を作成済み）

主問合せのSAMPLE表はフルテーブルスキャンが行われるが、副問合せのSAMPLE_TEST表は索引スキャンが行われるSQL
>SELECT  /*+  FULL(sample)  */  col1,  col2
     2  FROM  sample
     3  WHERE  seq  IN  (SELECT  /*+  INDEX(sample_test  col3_ind)  */  seq
     4  FROM  sample_test  WHERE  col3 = 100);
実行計画
-------------------------------------------------------------------------------------------------------------------------------------
0        SELECT  STATEMENT Optimizer=CHOOSE
1    0       HASH   JOIN(SEMI)
2    1            TABLE  ACCESS  (FULL)  OF ’SAMPLE’
3    1            TABLE  ACCESS  (BY INDEX  ROWID)  OF  ’SAMPLE_TEST’
4    3                INDEX  (RANGE  SCAN)  OF  ’ COL3_IND’  (NON-UNIQUE)

３）ヒントの種類

ヒントで指定したアクセス・パスが使用可能な場合のみ有効になります。

FULL

FULL：指定された表に対して、フル・テーブル・スキャンを選択します

FULL　（表名）

例）SAMPLE表のCOL3列の中で値「１」が占める割合が大きい場合、索引スキャンは好ましくない。その為、

　　FULLヒントを使用して索引スキャンではなくフル・テーブル・スキャンを選択するように指定

　　（ SAMPLE表のCOL3列に索引を作成済み）

／＊　索引スキャンを使用した問合せ　＊／
SQL>SELECT  seq, col1,  col3  FROM  sample
     2   WHERE col3 = 1;
実行計画
-------------------------------------------------------------------------------------------------------------------------------------
0        SELECT  STATEMENT Optimizer=CHOOSE
1    0      TABLE  ACCESS  (BY INDEX  ROWID)  OF  ’SAMPLE’
2    1            INDEX  (RANGE  SCAN)  OF  ’ COL3_IND’  (NON-UNIQUE)
／＊　FULLヒントを使用した問合せ　＊／
SQL>SELECT /*+  FULL(sample)  */ 　seq, col1,  col3  FROM  sample
     2   WHERE col3 = 1; 
実行計画
-------------------------------------------------------------------------------------------------------------------------------------
0        SELECT  STATEMENT Optimizer=CHOOSE
1    0      TABLE  ACCESS  (FULL)  OF  ’SAMPLE’

INDEX

INDEX：指定された表に対して、索引スキャンを選択します

INDEX　（表名　　索引名　［　索引名・・・　］　）

索引を１つだけ指定した場合、指定した索引を使用して索引スキャンを行います。
索引を複数指定した場合、指定した各索引のコスト、及び索引をマージした場合のコストを見積り、もっとも　コストが低いアクセス・パスを使用して索引スキャンを行います。
索引を指定しなかった場合、使用可能な各索引のコスト、及び索引をマージした場合のコストを見積り、　もっともコストが低いアクセス・パスを使用して索引スキャンを行います。

／＊　フル・テーブル・スキャンを使用した問合せ　＊／
SQL>SELECT  seq  FROM  sample
     2   WHERE col1 = 10; 
実行計画
-------------------------------------------------------------------------------------------------------------------------------------
    0        SELECT  STATEMENT Optimizer=CHOOSE
    1    0      TABLE  ACCESS  (FULL)   OF  ’SAMPLE’
／＊　INDEXヒントを使用した問合せ（この例では索引スキップ・スキャンを行う）　＊／
SQL>SELECT /*+  INDEX(sample  col3_col1_ind)  */ 　seq  FROM  sample
     2   WHERE col1 = 10; 
実行計画
-------------------------------------------------------------------------------------------------------------------------------------
    0        SELECT  STATEMENT Optimizer=CHOOSE
    1    0      TABLE  ACCESS  (BY INDEX  ROWID)  OF  ’SAMPLE’
    2    1            INDEX  (SKIP  SCAN)  OF  ’ COL3_COL1_IND’

INDEX_FFS

INDEX_FFS：フル・テーブル・スキャンまたは索引レンジ・スキャンではなく高速全索引スキャンを行います。

INDEX_FFS　（表名　　索引名　［　索引名・・・　］　）

問合せに必要な全ての列を持つ索引が存在する場合、フル・テーブル・スキャンまたは索引レンジ・スキャンに代わり指定できます。

例）問合せで必要な全ての列を持つ索引 (COL3_COL1_IND) を使用した高速全索引スキャンを行う。

／＊　索引レンジ・スキャンを使用した問合せ　（シングル・ブロック読込み）　＊／
SQL>SELECT  seq  FROM  sample
     2   WHERE col3 < 2; 
実行計画
-------------------------------------------------------------------------------------------------------------------------------------
	0		SELECT  STATEMENT Optimizer=CHOOSE
	1	0	  INDEX  (RANGE  SCAN)   OF  ’COL3_COL1_IND’  (NON-UNIQUE)
／＊　高速全索引スキャンを使用した問合せ（マルチ・ブロック読込み）　＊／
SQL>SELECT /*+  INDEX_FFS (sample  col3_col1_ind)  */ 　col1  FROM  sample
     2   WHERE col3 < 2; 
実行計画
-------------------------------------------------------------------------------------------------------------------------------------
	0		SELECT  STATEMENT Optimizer=CHOOSE
	1	0	  INDEX  (FAST  FULL  SCAN)  OF  ’ COL3_COL1_IND’

ORDERED

ORDERED：FROM 句で指定された順番で結合を行います。

ORDERED

指定しない場合は、オプティマイザが結合順序を決定します。

例） FROM 句で指定されている順番でDEPT表、EMP表、SALGRADE表を結合する。

SQL>SELECT /*+  ORDERED  */ ename,dname,grade
	 2  FROM  dept, emp, salagrade
     3   WHERE  emp.deptno = dept.deptno
	 4  AND  emp.sal  BETWEEN  salagrade.losal  AND  salagrade.hisal;  
実行計画
-------------------------------------------------------------------------------------------------------------------------------------
	0		SELECT  STATEMENT Optimizer=CHOOSE
	1	0	    MERGE  JOIN
	2	1		SORT   (JOIN)
	3	2			HASH  JOIN
	4	3				TABLE ACCESS  (FULL)  OF  ’DEPT’   ←　結合順序１
	5	4				TABLE ACCESS  (FULL)  OF  ’EMP’  　←　結合順序２
	6	1		FILTER
	7	6			SORT  (JOIN)
	8	7				TABLE ACCESS  (FULL)  OF  ’SALGRADE’  　←　結合順序３

他のORDEREDヒントと組み合わせたヒント例（ヒントで指定された表は、結合順序が、その表の直前の表と結合）

/*+  ORDERED  USE_NL  （表名　［表名　表名・・・］　）　*/ ：ネスティッド・ループ結合
/*+  ORDERED  USE_MERGE  （表名　［表名　表名・・・］　）　*/ ：ソート／マージ結合
/*+  USE_HASH  （表名　［表名　表名・・・］　）　*/ ：ハッシュ結合　（ ORDEREDヒントと組み合わせて使用も可能）

Oracleに投稿されました • 続きを読む

索引が存在するだけでは、必ずしもオプティマイザは索引を使用するアクセス・パスを使用するとは限りません。

索引を使用するか否かは、発行するSQLの記述方法によって左右されます。例え同一の結果を返すSQLでも、記述方法によって索引を使用する場合と使用しない場合があります。この動作はオプティマイザのアプローチによっても異なります。

以下、ルールベース・オプティマイザで索引を使用する為の基本ルールを示します。（索引列は下線で表示）

（１）索引列値を変更した場合

基本ルール：WHERE句で索引列値を変更した場合、索引は使用されない

例１：×　SELECT ename FROM emp WHERE sal*1.1 > 950;
　　　○　SELECT ename FROM emp WHERE sal > 950/1.1;

例２：×　SELECT ename FROM emp WHERE TO_CHAR(hiredate,’YYYYMMDD’) = ’20190801’;
　　　○　SELECT ename FROM emp WHERE hiredate = TO_DATE(’20190801’,’YYYYMMDD’);

（２）異なるデータ型を比較した場合

基本ルール：索引列に対し、異なるデータ型の比較を行う場合、索引が使用されない場合がある

例１：EMPNO列がVARCHAR2型で定義してある場合
　　　×　SELECT ename FROM emp WHERE empno = 7788;　
　　　　⇒　この場合内部的には以下のように変換される為、索引は使用されません
　　　×　SELECT ename FROM emp WHERE TO_NUMBER(empno) = 7788;
　　 ○　SELECT ename FROM emp WHERE empno = ’7788’;

（３）NULL値を条件に検索した場合

基本ルール：検索条件にNULL値を指定した場合、索引は使用されない

＊ｺｽﾄﾍﾞｰｽ・ｵﾌﾟﾃｨﾏｲｻﾞで運用している場合は、ﾋﾞｯﾄﾏｯﾌﾟ索引を使用できます

例：

×　SELECT ename FROM emp WHERE comm IS NULL;
×　SELECT ename FROM emp WHERE comm IS NOT NULL;

ｺｽﾄﾍﾞｰｽ・ｵﾌﾟﾃｨﾏｲｻﾞを使用した場合、WHERE句に IS NOT NULL を指定しても、ヒントによって指定した索引を使用するように指示を出すことが可能です。

◎　SELECT /*+ INDEX(emp emp_comm) */ ename FROM emp WHERE　comm IS NOT NULL

索引列が NUMBER型の場合は、NULL値の代わりとなる値（０など）を設定するようにする

◎　SELECT ename FROM emp WHERE comm = 0;

（４）NOT EQUAL演算子を使用した場合

基本ルール：NOT EQUAL演算子(NOT INも)を使用した検索では、索引は使用されない

例１：×　SELECT ename FROM emp WHERE deptno != 30;

（５）比較演算子、またはBETWEEN演算子を使用した場合

基本ルール：比較演算を行う検索でも索引は使用される

　　　○　SELECT ename FROM emp WHERE comm >= 0;
　　　○　SELECT ename FROM emp WHERE comm BETWEEN 500 AND 1000;

（６）AND論理演算子で複数の索引列を指定した場合

基本ﾙｰﾙ1：複数の索引列が WHERE句で指定された場合、優先順位が一番高い索引が適用される
基本ﾙｰﾙ2：優先順位が同じ場合は、索引のマージが行われる
基本ﾙｰﾙ3：演算子が等価演算子以外の場合は、最後に作成された索引だけが使用される

例：empno列に一意索引、ename列、job列、depno列に非一意索引を順に作成した場合

○　SELECT ename FROM emp WHERE ename = ’SCOTT’ AND empno = 7788;

⇒　empno列の索引が使用されます

○　SELECT ename FROM emp WHERE deptno = 20 AND job = ’MANAGER’;

⇒ deptno列と job列の両方の索引が使用されます

○　SELECT ename FROM emp WHERE deptno < 30 AND empno < 7000;

⇒ 索引の作成順が最後だったdeptno列の索引が使用されます

（７）OR論理演算子を使用した場合

基本ルール：OR論理演算子で指定する全ての列に索引が存在する場合のみ、索引が使用される

×　SELECT ename FROM emp WHERE sal = 3000 or job = ’MANAGER’;

◎　SELECT ename FROM emp WHERE sal = 3000 or job = ’MANAGER’;

（８）LIKE演算子を使用した場合

基本ルール：LIKE演算子を使用した場合、索引列が CHAR型、もしくは VARCHAR2型で前方一致の場合のみ索引が使用される

×　SELECT ename FROM emp WHERE ename LIKE ’%K%’;
◎　SELECT ename FROM emp WHERE ename LIKE ’S%’;

（９）IN論理演算子を使用した場合

基本ルール：IN 演算子を使用した場合、索引は使用されるが、NOT IN 演算子の場合は使用されない

×　SELECT ename FROM emp WHERE deptno NOT IN (10,20);
◎　SELECT ename FROM emp WHERE deptno IN (10,20);

（1０）DISTINCT句を使用した場合

基本ルール：DISTINCT句に対して索引は使用されない

×　SELECT DISTINCT empno FROM emp；

（１１）ORDER BY句を使用した場合

基本ルール：特定の場合のみ使用される

以下の条件を全て満たす

ORDER BY句で指定した列に PRIMARY KEY 制約もしくは NOT NULL 制約が定義されている
WHERE句に索引を使用できる構文を含んでいない場合

SQL>DESC  emp
Name        Null?        Type
-------------------------  -------------------------- -----------------------------
EMPNO        NOT  NULL    NUMBER(4)
ENAME                VARCHAR2(10)
JOB                VARCHAR2(9)
MGR                NUMBER(4)
HIREDATE            DATE
SAL                NUMBER(7,2)
COMM                NUMBER(7,2)
DEPTNO                NUMBER(2)

△　SELECT ename FROM emp WHERE deptno = 20 ORDER BY empno;

⇒　deptno列の索引が使用される

◎　SELECT ename FROM emp ORDER BY empno;

⇒　empno列の索引が使用される

（１２）MAX, MIN関数を使用した場合

基本ルール：特定の場合のみ使用される

以下の条件を全て満たす

MAX (MIN) が唯一の選択リストの場合
問い合わせが結合でない場合
WHERE 句、GROUP BY 句が使用されていない場合

例：

×　SELECT MAX(sal) FROM emp WHERE deptno = 20;
◎　SELECT MAX(sal) FROM emp;

（１３）COUNT関数を使用した場合

基本ルール：特定の場合のみ使用される（ｺｽﾄﾍﾞｰｽ・ｵﾌﾟﾃｨﾏｲｻﾞのみ）

表の列に NOT NULL 制約が定義されていて、以下のどちらかの条件を満たす

COUNT(*)を使用した場合　（表の行数を数えるとき）
NOT NULL 制約が定義されている列を COUNT関数で指定した場合

例：EMP表の empno列には NOT NULL 制約が定義されている

◎　SELECT COUNT(*) FROM emp;
◎　SELECT COUNT(empno) FROM emp;

（１４）GROUP BY句を使用した場合

基本ルール：特定の場合のみ使用される（ｺｽﾄﾍﾞｰｽ・ｵﾌﾟﾃｨﾏｲｻﾞのみ）

以下の条件を全て満たす

選択リストの列と GROUP BY句で指定した列のコンポジット索引が存在する場合（但し先頭列は GROUP BY句で指定した列）
GROUP BY句で指定した列に NOT NULL 制約が定義されている

例：EMP表の deptno列には NOT NULL 制約が定義されている。また、deptno列にコンポジット索引が定義されている。

◎　SELECT deptno,MAX(sal) FROM emp GROUP BY deptno;

（１５）HAVING句を使用した場合

基本ルール：HAVING句に対して索引は使用されない

×　SELECT deptno,COUNT(*) FROM emp GROUP BY deptno HAVING deptno = 30;

可能であれば、WHERE 句に書き換えて索引を使用するようにする。

◎　SELECT deptno,COUNT(*) FROM emp WHERE deptno = 30 GOUP BY deptno;

（１６）コンポジット索引を使用した場合

基本ルール：検索条件にコンポジット索引の先頭列が含まれている場合のみ使用される（先頭列とは、CREATE INDEX文の列リストの1つ目の列を意味する）

例： CREATE INDEX i_sample ON sample (col1, col2, col3)；

col1 列に NOT NULL 制約が定義されている場合

◎　SELECT col1 FROM sample WHERE col1 = 10;
◎　SELECT col1 FROM sample WHERE col1 = 10 AND col2 = 20;
◎　SELECT col1 FROM sample WHERE col1 = 10 AND col2 = 20 AND col3 = 30;
◎　SELECT col1 FROM sample WHERE col1 = 10 AND col3 = 30;
◎　SELECT col1 FROM sample ORDER BY col1;
◎　SELECT col1 FROM sample ORDER BY col1,col3;
×　SELECT col1 FROM sample WHERE col2 = 20 AND col3 = 30；
×　SELECT col1 FROM sample WHERE col3 = 30;

1 / 1