#like（1）当使用like查询时，后模糊匹配，则走索引，如like'test%'（2）当使用like查询时，前模糊匹配，则不走索引，如like'%test'#<>不走索引因为不等于，即等于大量数据，所以不走索引#隐式转换，当发生在索引列时，不走索引，发生在条件值列时，走索引（1）如果隐式转换发生在值列，则走索引，例如查询使用日期查询时，select*fromtest_implicwherebir_date='2018012214:22:32';（2）如果索引列发生了隐式转换，则不走索引，如列数据类型为varchar2，使用如下查询时selectbir_datefromtest_implicwhereid=2000;（3）当number列等于字符串时，走索引2.测试

（1）like 后模糊匹配走索引like 前模糊匹配走全表

#创建测试表createtabletest_bind(idnumber,namevarchar2(20));#插入数据declareinumber;beginforiin1..100000loopinsertintotest_bindvalues(i,'haha');endloop;end;/declareinumber;beginforiin100000..100010loopinsertintotest_bindvalues(i,'test');endloop;end;/#创建索引createindexIDX_TEST_BINDontest_bind(name);#收集统计信息execdbms_stats.gather_table_stats('LIBAI','TEST_BIND');#查询，后模糊匹配，可以看到走了索引LIBAI@honor1>setautotraceonLIBAI@honor1>select*fromtest_bindwherenamelike'te%';IDNAME--------------------------------------------------------------------------------100001test100002test100003test100004test100005test100006test100007test100008test100009test100010test10rowsselected.Elapsed:00:00:00.00ExecutionPlan----------------------------------------------------------Planhashvalue:2889536435---------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||9|90|3(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|TEST_BIND|9|90|3(0)|00:00:01||*2|INDEXRANGESCAN|IDX_TEST_BIND|9||2(0)|00:00:01|---------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("NAME"LIKE'te%')filter("NAME"LIKE'te%')Statistics----------------------------------------------------------1recursivecalls0dbblockgets5consistentgets0physicalreads0redosize782bytessentviaSQL*Nettoclient519bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed#前模糊匹配，可以看到走了全表扫描LIBAI@honor1>select*fromtest_bindwherenamelike'%st';IDNAME--------------------------------------------------------------------------------100001test100002test100003test100004test100005test100006test100007test100008test100009test100010test10rowsselected.Elapsed:00:00:00.02ExecutionPlan----------------------------------------------------------Planhashvalue:3519963602-------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-------------------------------------------------------------------------------|0|SELECTSTATEMENT||5001|50010|69(2)|00:00:01||*1|TABLEACCESSFULL|TEST_BIND|5001|50010|69(2)|00:00:01|-------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-filter("NAME"LIKE'%st'AND"NAME"ISNOTNULL)Statistics----------------------------------------------------------1recursivecalls0dbblockgets236consistentgets0physicalreads0redosize734bytessentviaSQL*Nettoclient519bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed

（2）<> 不走索引

LIBAI@honor1>select*fromtest_bindwherename<>'test';IDNAME--------------------------------------------------------------------------------100001test100002test100003test100004test100005test100006test100007test100008test100009test100010test10rowsselected.Elapsed:00:00:00.01ExecutionPlan----------------------------------------------------------Planhashvalue:3519963602-------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|-------------------------------------------------------------------------------|0|SELECTSTATEMENT||18|180|69(2)|00:00:01||*1|TABLEACCESSFULL|TEST_BIND|18|180|69(2)|00:00:01|-------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-filter("NAME"<>'haha')Statistics----------------------------------------------------------1recursivecalls0dbblockgets236consistentgets0physicalreads0redosize734bytessentviaSQL*Nettoclient519bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)10rowsprocessed

（3）隐式转换

# 构造测试环境

createtabletest_implic(idvarchar2(20),namevarchar2(20),bir_datedatedefaultsysdate);declareivarchar2(10);beginforiin1..10000loopinsertintotest_implicvalues(i,'czh',sysdate);endloop;commit;end;/createindexidx_test_implic_idontest_implic(id);createindexidx_test_implic_bir_dateontest_implic(bir_date);execdbms_stats.gather_table_stats('LIBAI','TEST_IMPLIC');

# 当varchar2类型等于数字时，不走索引

LIBAI@honor1>selectbir_datefromtest_implicwhereid=2000;BIR_DATE-------------------2020-01-1920:00:51ExecutionPlan----------------------------------------------------------Planhashvalue:965190314---------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|13|11(0)|00:00:01||*1|TABLEACCESSFULL|TEST_IMPLIC|1|13|11(0)|00:00:01|---------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-filter(TO_NUMBER("ID")=2000)Statistics----------------------------------------------------------1recursivecalls0dbblockgets38consistentgets0physicalreads0redosize531bytessentviaSQL*Nettoclient519bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessedLIBAI@honor1>selectbir_datefromtest_implicwhereid=to_char(2000);BIR_DATE-------------------2020-01-1920:00:51ExecutionPlan----------------------------------------------------------Planhashvalue:3908402167--------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|13|2(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|TEST_IMPLIC|1|13|2(0)|00:00:01||*2|INDEXRANGESCAN|IDX_TEST_IMPLIC_ID|1||1(0)|00:00:01|--------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("ID"='2000')Statistics----------------------------------------------------------1recursivecalls0dbblockgets4consistentgets4physicalreads0redosize531bytessentviaSQL*Nettoclient519bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)1rowsprocessed

# 当number等于字符串时，走索引

LIBAI@honor1>select*fromtest_bindwhereid='1000';IDNAME--------------------------------------------------------------------------------1000hahaExecutionPlan----------------------------------------------------------Planhashvalue:2345277976------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|10|2(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|TEST_BIND|1|10|2(0)|00:00:01||*2|INDEXRANGESCAN|IDX_TEST_BIND_ID|1||1(0)|00:00:01|------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("ID"=1000)Statistics----------------------------------------------------------14recursivecalls0dbblockgets33consistentgets0physicalreads0redosize595bytessentviaSQL*Nettoclient519bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient1sorts(memory)0sorts(disk)1rowsprocessed

# 当日期等于字符串时，走索引

LIBAI@honor1>select*fromtest_implicwherebir_date='2018012214:22:32';norowsselectedExecutionPlan----------------------------------------------------------Planhashvalue:3390782276--------------------------------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|--------------------------------------------------------------------------------------------------------|0|SELECTSTATEMENT||1|17|2(0)|00:00:01||1|TABLEACCESSBYINDEXROWID|TEST_IMPLIC|1|17|2(0)|00:00:01||*2|INDEXRANGESCAN|IDX_TEST_IMPLIC_BIR_DATE|1||1(0)|00:00:01|--------------------------------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------2-access("BIR_DATE"='2018012214:22:32')Statistics----------------------------------------------------------1recursivecalls0dbblockgets2consistentgets4physicalreads0redosize466bytessentviaSQL*Nettoclient508bytesreceivedviaSQL*Netfromclient1SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)0rowsprocessed