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 ENAMEENAME列を中間一致で検索してみます。
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_NUMBEREMP_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/