======== 测试表1信息 =======
SQL> select count(*) from tb_test;
COUNT(*)
----------
3000000
SQL> select count(*) from tb_test where name=‘1‘ or name=‘0‘;
COUNT(*)
----------
3000000
SQL> desc tb_test
Name Null? Type
-------------------------- -------- -----------------
ID NUMBER
INC_DATETIME VARCHAR2(19)
RANDOM_ID NUMBER
RANDOM_STRING VARCHAR2(4000)
NAME VARCHAR2(20)
=========== 索引信息 ===========
OWNER TABLE_NAME INDEX_NAME INDEX_TYPE
------- ------------ ------------- ------------
TEST TB_TEST IDX_INC NORMAL
TEST TB_TEST NAME_IX NORMAL
TEST TB_TEST ID_IX NORMAL
====== 测试表2信息 ==========
SQL> desc tb_test1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
INC_DATETIME VARCHAR2(19)
RANDOM_ID NUMBER
RANDOM_STRING VARCHAR2(4000)
SQL> select count(*) from tb_test1
COUNT(*)
----------
110
============ 使用隐式转换 ============
使用隐式转换未使用索引，全表扫描，IO开销大，执行速度慢。
explain plan for select id from tb_test where
id not in ( select id from tb_test1 ) and
RANDOM_STRING=0 and name=1;
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------
Plan hash value: 2850541703
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 2762 (1)| 00:00:34 |
|* 1 | HASH JOIN ANTI NA | | 1 | 26 | 2762 (1)| 00:00:34 |
|* 2 | TABLE ACCESS FULL| TB_TEST | 1 | 13 | 2758 (1)| 00:00:34 |
| 3 | TABLE ACCESS FULL| TB_TEST1 | 110 | 1430 | 4 (0)| 00:00:01 |
-------------------------------------------------------------------------------
=============== 使用显示转换 ==================
使用显示转换，索引扫描，IO开销小，执行快。
SQL> explain plan for select id from tb_test where
2 id not in ( select id from tb_test1 ) and
3 RANDOM_STRING=‘0‘ and name=‘1‘;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------
Plan hash value: 3484870784
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 8 (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA | | 1 | 26 | 8 (0)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| TB_TEST | 1 | 13 | 4 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | NAME_IX | 6 | | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TB_TEST1 | 110 | 1430 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
总结:1、隐式转换发生在字段列上时将使索引失效
2、RANDOM_STRING和name虽含有大量重复值，使用索引的效率仍然很高。