見出し画像

Oracleのインデックスが効かないケース

概要

SQLから実行計画を作る際、どのインデックスを使うと効率良く処理できるのかはオプティマイザが決めていきます。

インデックスを使って効率よく処理できるケースとして例えば、インデックスを張った列のMINやMAX関数を実行すると、オプティマイザはテーブルのデータを取得するよりもインデックスのデータから取得したほうが効率が良く結果を出せるので、テーブルの代わりにインデックスを見て結果を返します。同様に、インデックスを張った列をCOUNTすると、オプティマイザはテーブルの代わりにインデックスをCOUNTします。

ところが、インデックスを使って効率よく処理してくれることを期待して実行したものの、オプティマイザが実行計画を検討した結果、インデックスを使うとかえって効率が悪くなってしまうのでインデックスを使わない、ということが起きます。
やってしまいがちなパターンがいくつかあるので見ていきます。

<>や!=演算子の利用

インデックスは、テーブル内に「ある」データを検索する時は効率的に働きますが、インデックスを使って「ない」データを検索しようとしても「ない」データが多いのでかえって効率が悪くなってしまうため、オプティマイザは「ない」データの検索にインデックスを使いません。

例を見てみます。
EMPNO列にINDEXを張ったEMP_TESTテーブルを検索します(EMP_TESTテーブルはEMPテーブルをコピーして作りました。EMPテーブルというのはOracle製品で提供されているデモ用のテーブルです。
開発環境ではOracleをインストールする時に一緒にインストールすることが多いと思いますが、詳しくは別の記事で触れようと思います)。
次のSQLでは、EMP_TESTテーブルのデータのほとんどの行が該当するため、インデックスではなくテーブル・フルスキャンを行います。

SQL> col index_name for a20
SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'EMP_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE		UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- --------------------- --------- -------- ---------
EMP_TEST	     EMP_TEST_N1	  NORMAL		NONUNIQUE VALID    INVISIBLE
EMP_TEST	     EMP_TEST_N2	  NORMAL		UNIQUE	  VALID    VISIBLE
EMP_TEST	     EMP_TEST_N3	  NORMAL		UNIQUE	  VALID    VISIBLE

SQL> select table_name, index_name, column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'EMP_TEST'
  4  order by index_name, column_position;

TABLE_NAME	     INDEX_NAME 	  COLUMN_POSITION COLUMN_NAME
-------------------- -------------------- --------------- --------------------
EMP_TEST	     EMP_TEST_N1			1 EMPNO
EMP_TEST	     EMP_TEST_N1			2 ENAME
EMP_TEST	     EMP_TEST_N1			3 DEPTNO
EMP_TEST	     EMP_TEST_N2			1 EMPNO
EMP_TEST	     EMP_TEST_N2			2 ENAME
EMP_TEST	     EMP_TEST_N2			3 DEPTNO
EMP_TEST	     EMP_TEST_N3			1 EMPNO

7 rows selected.

select 

SQL> select job, empno
  2  from emp_test
  3  where empno <> 7788;

JOB	       EMPNO
--------- ----------
CLERK		7369
SALESMAN	7499
SALESMAN	7521
MANAGER 	7566
SALESMAN	7654
MANAGER 	7698
MANAGER 	7782
PRESIDENT	7839
SALESMAN	7844
CLERK		7876
CLERK		7900

JOB	       EMPNO
--------- ----------
ANALYST 	7902
CLERK		7934

13 rows selected.

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	7zhznrs82bm95, child number 0
-------------------------------------
select job, empno from emp_test where empno <> 7788

Plan hash value: 3124080142

------------------------------------------------------------------------------
| Id  | Operation	  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     |	     |	     |	   3 (100)|	     |
|*  1 |  TABLE ACCESS FULL| EMP_TEST |	  13 |	 156 |	   3   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"<>7788)


18 rows selected.

テーブルをAnalyzeすると、Oracleはテーブル内のデータの格納状況について統計情報を収集します。
(コストベース)オプティマイザはこの統計情報を使ってWhere句の検索にインデックスを使用する/しないを決めます。
アプリケーション開発/テスト工程でSQLの性能をテストする際は、なるべく本番環境の統計情報に近い情報になるようにして、オプティマイザが本番環境で選ぶ実行計画と同じものを選べるようにしておきます。
(と、これも教科書的に言うのは簡単ですが、実際にそのような環境を用意するのはなかなか大変です)

IS NULLやIS NOT NULLの利用

Where句でIS NULLやIS NOT NULLを使用した場合もインデックスが効きません。「このテーブルのこの列にはNULLを入れる」のように言うことがあるので、テーブルにNULLという値が入っているように感じますが実際にはNULLという値が入っているわけではなくて「未定義」(何もない)という状態になっています。「未定義」つまり「何もない」状態なのでNULLとNULLも同一にはなりません。

インデックスは、テーブル内に「ある」データを検索する時は効率的に働きますが、インデックスを使って「ない」データを検索しようとしても「ない」データが多いのでかえって効率が悪くなってしまうため、オプティマイザは「ない」データの検索にインデックスを使いません。

例として、EMP_TEST_N4テーブルのSAL列にインデックスを作成し、SALがNULLの列を検索してみます。 インデックスは使われず、テーブルフルスキャンになります。

SQL> create index emp_test_n4 on emp_test(sal);

Index created.

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'EMP_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE		UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- --------------------- --------- -------- ---------
EMP_TEST	     EMP_TEST_N1	  NORMAL		NONUNIQUE VALID    INVISIBLE
EMP_TEST	     EMP_TEST_N2	  NORMAL		UNIQUE	  VALID    VISIBLE
EMP_TEST	     EMP_TEST_N3	  NORMAL		UNIQUE	  VALID    VISIBLE
EMP_TEST	     EMP_TEST_N4	  NORMAL		NONUNIQUE VALID    VISIBLE

SQL> select table_name, index_name, column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'EMP_TEST'
  4  order by index_name, column_position;

TABLE_NAME	     INDEX_NAME 	  COLUMN_POSITION COLUMN_NAME
-------------------- -------------------- --------------- --------------------
EMP_TEST	     EMP_TEST_N1			1 EMPNO
EMP_TEST	     EMP_TEST_N1			2 ENAME
EMP_TEST	     EMP_TEST_N1			3 DEPTNO
EMP_TEST	     EMP_TEST_N2			1 EMPNO
EMP_TEST	     EMP_TEST_N2			2 ENAME
EMP_TEST	     EMP_TEST_N2			3 DEPTNO
EMP_TEST	     EMP_TEST_N3			1 EMPNO
EMP_TEST	     EMP_TEST_N4			1 SAL

8 rows selected.

SQL> select empno, ename, deptno
  2  from emp_test
  3  where sal is null;

no rows selected

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID	0fnm8sgf7qwyb, child number 0
-------------------------------------
select empno, ename, deptno from emp_test where sal is null

Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |	 |	 |     3 (100)| 	 |
|*  1 |  TABLE ACCESS FULL| EMP_TEST  |     1 |    17 |     3   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL" IS NULL)


18 rows selected.

列にNULLを入れないようにしたい場合は、テーブルの列にNOT NULL制約をかけます。
テーブルにすでにデータが入っている場合は、列にNULLが入っていない場合だけNOT NULL制約をかけられます。
列にNULLが入っているけどNOT NULLにしたい場合は、DEFAULT句を使って必ずNULL以外の値が設定されるようにするとNOT NULL制約をかけられます。

次の例は、EMP_TESTテーブルのSAL列にNOT NULL制約をかけています。

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

SQL> alter table emp_test modify
  2  (sal not null);

Table altered.

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

SAL列に対してNULLを入れようとするとエラーになります。

SQL> insert into emp_test(sal) values(null);
insert into emp_test(sal) values(null)
                               *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."EMP_TEST"."SAL")

列にNOT NULL制約をかけるとNULLを入れられず、NULLを検索する時にフルテーブルスキャンになって性能が悪くなるのを防げます。
次のCREATE TABLE文ではDEPTNO列のデフォルト値を設定しています。
Insert文を実行する時に列の値が指定されていなければデフォルト値が入ります。CREATE TABLE文でデフォルト値を設定した上で、あえてNULLを入れたい場合はInsert文で明示的にNULLを設定します。

SQL> create table emp_test2
  2  (emp_id number(8) not null
  3  ,first_name varchar2(20) not null
  4  ,last_name varchar2(20) not null
  5  ,deptno number(4) default 10);

Table created.

SQL> insert into emp_test2(emp_id, first_name, last_name)
  2  values(8100, 'SCOTT2','TIGER2');

1 row created.

SQL> select * from emp_test2;

    EMP_ID FIRST_NAME		LAST_NAME		 DEPTNO
---------- -------------------- -------------------- ----------
      8100 SCOTT2		TIGER2			     10

SQL> insert into emp_test2
  2  values(8200, 'SCOTT3', 'TIGER3', NULL);

1 row created.

SQL> select * from emp_test2;

    EMP_ID FIRST_NAME		LAST_NAME		 DEPTNO
---------- -------------------- -------------------- ----------
      8100 SCOTT2		TIGER2			     10
      8200 SCOTT3		TIGER3

以上で見てみた通り、NULLが入っていてインデックスが使えない、ということは良くあります。
できるだけNULLが入らないように、NOT NULL制約とDEFAULT句を使って、インデックスが効きやすいテーブルを作成するようにしましょう。

LIKEの利用

LIKEの場合は、インデックスを使用する場合も使用しない場合もあります。
LIKEでよく使うのは「LIKE '%somevalue%'(中間一致)」や「LIKE 'somevalue%'(前方一致)」です。中間一致だとインデックスを使えません。
前方一致のみ、インデックスを使用できます。

例を見てみます。
EMP_TESTテーブルのENAME列にインデックスを作成します。
ENAME列をLIKEで検索し、インデックス・レンジ・スキャンを使えるケースと使えないケースを見てみます。

インデックスが多くなってきたので一旦全て削除してから新たにインデックスを作成します。

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'EMP_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE		UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- --------------------- --------- -------- ---------
EMP_TEST	     EMP_TEST_N1	  NORMAL		NONUNIQUE VALID    INVISIBLE
EMP_TEST	     EMP_TEST_N2	  NORMAL		UNIQUE	  VALID    VISIBLE
EMP_TEST	     EMP_TEST_N3	  NORMAL		UNIQUE	  VALID    VISIBLE
EMP_TEST	     EMP_TEST_N4	  NORMAL		NONUNIQUE VALID    VISIBLE

SQL> drop index emp_test_n1 online;

Index dropped.

SQL> drop index emp_test_n2 online;

Index dropped.

SQL> drop index emp_test_n3 online;

Index dropped.

SQL> drop index emp_test_n4 online;

Index dropped.

SQL> create index emp_test_n5 on emp_test(ename);

Index created.

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'EMP_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE		UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- --------------------- --------- -------- ---------
EMP_TEST	     EMP_TEST_N5	  NORMAL		NONUNIQUE VALID    VISIBLE

SQL> select table_name, index_name, column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'EMP_TEST'
  4  order by index_name, column_position;

TABLE_NAME	     INDEX_NAME 	  COLUMN_POSITION COLUMN_NAME
-------------------- -------------------- --------------- --------------------
EMP_TEST	     EMP_TEST_N5			1 ENAME

ENAME列を中間一致で検索してみます。

SQL> set autotrace traceonly
SQL> select empno, ename, hiredate
  2  from emp_test
  3  where ename like '%SCOTT%';


Execution Plan
----------------------------------------------------------
Plan hash value: 4023482432

---------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		  |	1 |    18 |	2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP_TEST	  |	1 |    18 |	2   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN		    | EMP_TEST_N5 |	1 |	  |	1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("ENAME" LIKE '%SCOTT%' AND "ENAME" IS NOT NULL)


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	  3  consistent gets
	  0  physical reads
	  0  redo size
	715  bytes sent via SQL*Net to client
	409  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

インデックス・フルスキャンを実行しています。
インデックスを使っているのですが、フルスキャンなのでインデックス・ブロックを全部読み込んでしまっていて効率は良くありません。

続いて前方一致で検索してみます。

SQL> select empno, ename, hiredate
  2  from emp_test
  3  where ename like 'SCOTT%';


Execution Plan
----------------------------------------------------------
Plan hash value: 306961105

---------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		  |	1 |    18 |	2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP_TEST	  |	1 |    18 |	2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN		    | EMP_TEST_N5 |	1 |	  |	1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ENAME" LIKE 'SCOTT%')
       filter("ENAME" LIKE 'SCOTT%')


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	  3  consistent gets
	  0  physical reads
	  0  redo size
	715  bytes sent via SQL*Net to client
	431  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

以上の通り、中間一致ではインデックス・フルスキャンを行い、前方一致ではインデックス・レンジ・スキャンを行います。
今回は性能に差がでませんでしたが、大量データで比較するとGetsや実行時間で差が出てきます。

Functionの利用

列にインデックスを張ってあっても、列にFUNCTIONをかけて検索してしまうとインデックスが使えません。
例えばTRUNC(HIREDATE)、TO_NUMBER(EMP_NUMBER)のようにFUNCTIONをかけてしまうと列の値が変更されて別の列のようになってしまうのでインデックスを使えません。

例を見てみます。 HIREDATE列にインデックスを張っても、TRUNC(HIREDATE)とFUNCTIONをかけてしまうとインデックスを使えません。

SQL> create index emp_test_n6 on emp_test(hiredate);

Index created.

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'EMP_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE		UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- --------------------- --------- -------- ---------
EMP_TEST	     EMP_TEST_N5	  NORMAL		NONUNIQUE VALID    VISIBLE
EMP_TEST	     EMP_TEST_N6	  NORMAL		NONUNIQUE VALID    VISIBLE

SQL> select table_name, index_name, column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'EMP_TEST'
  4  order by index_name, column_position;

TABLE_NAME	     INDEX_NAME 	  COLUMN_POSITION COLUMN_NAME
-------------------- -------------------- --------------- --------------------
EMP_TEST	     EMP_TEST_N5			1 ENAME
EMP_TEST	     EMP_TEST_N6			1 HIREDATE

SQL> set autotrace traceonly
SQL> select empno, ename, deptno
  2  from emp_test
  3  where trunc(hiredate) = '19-APR-87';

Execution Plan
----------------------------------------------------------
Plan hash value: 3124080142

------------------------------------------------------------------------------
| Id  | Operation	  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     |	   1 |	  21 |	   3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP_TEST |	   1 |	  21 |	   3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TRUNC(INTERNAL_FUNCTION("HIREDATE"))='19-APR-87')


Statistics
----------------------------------------------------------
	  0  recursive calls
	  0  db block gets
	  7  consistent gets
	  0  physical reads
	  0  redo size
	708  bytes sent via SQL*Net to client
	439  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

次のように、列の値を変更しないようにすればインデックスを使えます。

SQL> select empno, ename, deptno
  2  from emp_test
  3  where hiredate >= to_date('19-APR-87 00:00:00','DD-MON-RR HH24:MI:SS')
  4    and hiredate <  to_date('20-APR-87 00:00:00','DD-MON-RR HH24:MI:SS');


Execution Plan
----------------------------------------------------------
Plan hash value: 3488032873

----------------------------------------------------------------------------------------------------
| Id  | Operation			     | Name	   | Rows  | Bytes | Cost (%CPU)| Time	   |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		     |		   |	 1 |	21 |	 2   (0)| 00:00:01 |
|*  1 |  FILTER 			     |		   |	   |	   |		|	   |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP_TEST    |	 1 |	21 |	 2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN		     | EMP_TEST_N6 |	 1 |	   |	 1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE('20-APR-87 00:00:00','DD-MON-RR HH24:MI:SS')>TO_DATE('19-APR-87
	      00:00:00','DD-MON-RR HH24:MI:SS'))
   3 - access("HIREDATE">=TO_DATE('19-APR-87 00:00:00','DD-MON-RR HH24:MI:SS') AND
	      "HIREDATE"<TO_DATE('20-APR-87 00:00:00','DD-MON-RR HH24:MI:SS'))


Statistics
----------------------------------------------------------
	 56  recursive calls
	  2  db block gets
	 25  consistent gets
	  0  physical reads
	  0  redo size
	708  bytes sent via SQL*Net to client
	766  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  1  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

以上のように、列にFUNCTIONをかけるのではなく、列と比較される値を操作することで、列に張ったインデックスを使用できるようになります。

データ型の不一致

Where句の比較で、左右の列や値の型が違っていてもOracleが自動的に変換して調整してしまえる場合はSQLの実行自体は特にエラーが発生せずに正常実行できてしまうので見つけにくいですが、違ったデータ型を比較する場合はインデックスを使えません。

例えば、VARCHAR2列と数値型を比較するSQLを実行する場合、OracleがVARCHAR2データを暗黙的に変換し、比較対象の数値データ型と一致させます。例で見てみます。
EMP_NUMBERをVARCHAR2型にして列追加します。

SQL> alter table emp_test add (emp_number VARCHAR2(5));

Table altered.

SQL> update emp_test
  2  set emp_number = lpad(empno, 5, '0');

14 rows updated.

SQL> select * from emp_test;

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO EMP_N
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -----
      7369 SMITH      CLERK	      7902 17-DEC-80	    800 		   20 07369
      7499 ALLEN      SALESMAN	      7698 20-FEB-81	   1600        300	   30 07499
      7521 WARD       SALESMAN	      7698 22-FEB-81	   1250        500	   30 07521
      7566 JONES      MANAGER	      7839 02-APR-81	   2975 		   20 07566
      7654 MARTIN     SALESMAN	      7698 28-SEP-81	   1250       1400	   30 07654
      7698 BLAKE      MANAGER	      7839 01-MAY-81	   2850 		   30 07698
      7782 CLARK      MANAGER	      7839 09-JUN-81	   2450 		   10 07782
      7788 SCOTT      ANALYST	      7566 19-APR-87	   3000 		   20 07788
      7839 KING       PRESIDENT 	   17-NOV-81	   5000 		   10 07839
      7844 TURNER     SALESMAN	      7698 08-SEP-81	   1500 	 0	   30 07844
      7876 ADAMS      CLERK	      7788 23-MAY-87	   1100 		   20 07876

     EMPNO ENAME      JOB	       MGR HIREDATE	    SAL       COMM     DEPTNO EMP_N
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- -----
      7900 JAMES      CLERK	      7698 03-DEC-81	    950 		   30 07900
      7902 FORD       ANALYST	      7566 03-DEC-81	   3000 		   20 07902
      7934 MILLER     CLERK	      7782 23-JAN-82	   1300 		   10 07934

14 rows selected.

SQL> drop index emp_test_n5 online;

Index dropped.

SQL> drop index emp_test_n6 online;

Index dropped.

SQL> create index emp_test_n7 on emp_test(emp_number);

Index created.

SQL> select table_name, index_name, index_type, uniqueness, status, visibility
  2  from user_indexes
  3  where table_name = 'EMP_TEST';

TABLE_NAME	     INDEX_NAME 	  INDEX_TYPE		UNIQUENES STATUS   VISIBILIT
-------------------- -------------------- --------------------- --------- -------- ---------
EMP_TEST	     EMP_TEST_N7	  NORMAL		NONUNIQUE VALID    VISIBLE

SQL> select table_name, index_name, column_position, column_name
  2  from user_ind_columns
  3  where table_name = 'EMP_TEST'
  4  order by index_name, column_position;

TABLE_NAME	     INDEX_NAME 	  COLUMN_POSITION COLUMN_NAME
-------------------- -------------------- --------------- --------------------
EMP_TEST	     EMP_TEST_N7			1 EMP_NUMBER

EMP_NUMBER列がVARCHAR2データ型なので、EMP_NUMBER列にインデックスが張ってあっても次のように検索するとテーブル・フルスキャンが実行されます。

SQL> set autotrace on

SQL> select empno, ename, emp_number
  2  from emp_test
  3  where emp_number = 7788;

EMPNO ENAME      EMP_N
---------- ---------- -----
      7788 SCOTT      07788


Execution Plan
----------------------------------------------------------
Plan hash value: 3124080142

------------------------------------------------------------------------------
| Id  | Operation	  | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	     |	   1 |	  14 |	   4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP_TEST |	   1 |	  14 |	   4   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("EMP_NUMBER")=7788)


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	  7  consistent gets
	  0  physical reads
	  0  redo size
	715  bytes sent via SQL*Net to client
	431  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

実行計画を見ると、Oracleが暗黙的型変換を行ったことが分かります。

TO_NUMBER("EMP_NUMBER")=7788

以上のように、EMP_NUBMERにインデックスが付いていても暗黙の型変換がかかるとインデックスが使えず、フルテーブルスキャンを実行しました。
次のようにして暗黙の型変換が起きないようにすると、インデックス・レンジ・スキャンを使えるようになります。

SQL> select empno, ename, emp_number
  2  from emp_test
  3  where emp_number = '07788';

EMPNO ENAME      EMP_N
---------- ---------- -----
      7788 SCOTT      07788


Execution Plan
----------------------------------------------------------
Plan hash value: 3468220609

---------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		  |	1 |    14 |	1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP_TEST	  |	1 |    14 |	1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN		    | EMP_TEST_N7 |	1 |	  |	1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMP_NUMBER"='07788')


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	  3  consistent gets
	  0  physical reads
	  0  redo size
	715  bytes sent via SQL*Net to client
	655  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

今度は暗黙的型変換は行われず、インデックス・レンジ・スキャンが実行されました。
以上のように、比較するデータ型が一致せず、暗黙の型変換が行われる状況ではインデックス・レンジ・スキャンが行われません。


【このSHIFT公式ブロガーの最新記事】



執筆者プロフィール:小澤 雅弘
DBMSベンダー、ISPなどでDB関連のプロジェクトを20数年経験。得意領域はDBのパフォーマンスチューニング。

お問合せはお気軽に
https://service.shiftinc.jp/contact/

SHIFTについて(コーポレートサイト)
https://www.shiftinc.jp/

SHIFTのサービスについて(サービスサイト)
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/