Oraclelike、不等于、隐式转换走索引与不走索引情况
1. 概述
# like （1）当使用like查询时，后模糊匹配，则走索引，如like 'test%' （2）当使用like查询时，前模糊匹配，则不走索引，如like '%test' # <> 不走索引 因为不等于，即等于大量数据，所以不走索引 # 隐式转换，当发生在索引列时，不走索引，发生在条件值列时，走索引 （1）如果隐式转换发生在值列，则走索引，例如查询使用日期查询时， select * from test_implic where bir_date = '20180122 14:22:32'; （2）如果索引列发生了隐式转换，则不走索引，如列数据类型为varchar2，使用如下查询时 select bir_date from test_implic where id = 2000; （3）当number列等于字符串时，走索引
2.测试
（1） like 后模糊匹配走索引 like 前模糊匹配走全表
站在用户的角度思考问题，与客户深入沟通，找到张家界网站设计与张家界网站推广的解决方案，凭借多年的经验，让设计与互联网技术结合，创造个性化、用户体验好的作品，建站类型包括：网站制作、网站建设、企业官网、英文网站、手机端网站、网站推广、域名申请、虚拟主机、企业邮箱。业务覆盖张家界地区。
# 创建测试表
create table test_bind(id number,name varchar2(20));
#插入数据
declare
i number;
begin
for i in 1..100000
loop
insert into test_bind values(i,'haha');
end loop;
end;
/
declare
i number;
begin
for i in 100000..100010
loop
insert into test_bind values(i,'test');
end loop;
end;
/
# 创建索引
create index IDX_TEST_BIND on test_bind(name);
# 收集统计信息
exec dbms_stats.gather_table_stats('LIBAI','TEST_BIND');
# 查询，后模糊匹配，可以看到走了索引
LIBAI@honor1 > set autotrace on
LIBAI@honor1 > select * from test_bind where name like 'te%';
ID NAME
---------------------------------------- ----------------------------------------
100001 test
100002 test
100003 test
100004 test
100005 test
100006 test
100007 test
100008 test
100009 test
100010 test
10 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 2889536435
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 90 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_BIND | 9 | 90 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_BIND | 9 | | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME" LIKE 'te%')
filter("NAME" LIKE 'te%')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
782 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
# 前模糊匹配，可以看到走了全表扫描
LIBAI@honor1 > select * from test_bind where name like '%st';
ID NAME
---------------------------------------- ----------------------------------------
100001 test
100002 test
100003 test
100004 test
100005 test
100006 test
100007 test
100008 test
100009 test
100010 test
10 rows selected.
Elapsed: 00:00:00.02
Execution Plan
----------------------------------------------------------
Plan hash value: 3519963602
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5001 | 50010 | 69 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_BIND | 5001 | 50010 | 69 (2)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME" LIKE '%st' AND "NAME" IS NOT NULL)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
236 consistent gets
0 physical reads
0 redo size
734 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed（2） <> 不走索引
LIBAI@honor1 > select * from test_bind where name <> 'test';
ID NAME
---------------------------------------- ----------------------------------------
100001 test
100002 test
100003 test
100004 test
100005 test
100006 test
100007 test
100008 test
100009 test
100010 test
10 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3519963602
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 18 | 180 | 69 (2)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_BIND | 18 | 180 | 69 (2)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"<>'haha')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
236 consistent gets
0 physical reads
0 redo size
734 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed（3） 隐式转换
# 构造测试环境
create table test_implic (id varchar2(20),name varchar2(20),bir_date date default sysdate);
declare
i varchar2(10);
begin
for i in 1..10000
loop
insert into test_implic values(i,'czh',sysdate);
end loop;
commit;
end;
/
create index idx_test_implic_id on test_implic(id);
create index idx_test_implic_bir_date on test_implic(bir_date);
exec dbms_stats.gather_table_stats('LIBAI','TEST_IMPLIC');# 当varchar2类型等于数字时，不走索引
LIBAI@honor1 > select bir_date from test_implic where id = 2000;
BIR_DATE
-------------------
2020-01-19 20:00:51
Execution Plan
----------------------------------------------------------
Plan hash value: 965190314
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_IMPLIC | 1 | 13 | 11 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("ID")=2000)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
LIBAI@honor1 > select bir_date from test_implic where id = to_char(2000);
BIR_DATE
-------------------
2020-01-19 20:00:51
Execution Plan
----------------------------------------------------------
Plan hash value: 3908402167
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_IMPLIC | 1 | 13 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_IMPLIC_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"='2000')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
4 physical reads
0 redo size
531 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed# 当number等于字符串时，走索引
LIBAI@honor1 > select * from test_bind where id = '1000';
ID NAME
---------------------------------------- ----------------------------------------
1000 haha
Execution Plan
----------------------------------------------------------
Plan hash value: 2345277976
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_BIND | 1 | 10 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_BIND_ID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1000)
Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
33 consistent gets
0 physical reads
0 redo size
595 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed# 当日期等于字符串时，走索引
LIBAI@honor1 > select * from test_implic where bir_date = '20180122 14:22:32';
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3390782276
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_IMPLIC | 1 | 17 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_IMPLIC_BIR_DATE | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BIR_DATE"='20180122 14:22:32')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
4 physical reads
0 redo size
466 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
网站标题：Oraclelike、不等于、隐式转换走索引与不走索引情况
分享URL：http://myzitong.com/article/ghpepg.html