实验：模糊查询会使普通索引失效吗？

实验用表：

create table hy_emp(
id number(7,0) not null primary key,
deptid number(2,0) not null,
name nvarchar2(20) not null)

充值：

insert into hy_emp
select rownum,dbms_random.value(1,10),dbms_random.string('*',dbms_random.value(3,18))
from dual
connect by level<=500000
order by dbms_random.random

未加索引时，查看以下语句的解释计划：

EXPLAIN PLAN FOR
select * from hy_emp where deptid=8 and name like '%QENKSGIOG%'
select * from table(dbms_xplan.display);

结果如下：

Plan hash value: 910676026
 
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    28 |  1344 |   686   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| HY_EMP |    28 |  1344 |   686   (1)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DEPTID"=8 AND "NAME" LIKE U'%QENKSGIOG%')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

 

再加个索引：

create index hy_emp_idx_2 on hy_emp(deptid,name);

再看：

Plan hash value: 910676026
 
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    28 |  1344 |   686   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| HY_EMP |    28 |  1344 |   686   (1)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DEPTID"=8 AND "NAME" LIKE U'%QENKSGIOG%')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

果然，依然是全表存取，Cost也没变。

 

再创建个索引：

create index hy_emp_idx_1 on hy_emp(name);

再看执行计划：

EXPLAIN PLAN FOR
select * from hy_emp where deptid=8 and name like '%QENKSGIOG%'
select * from table(dbms_xplan.display);

结果：

Plan hash value: 910676026
 
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    28 |  1344 |   686   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| HY_EMP |    28 |  1344 |   686   (1)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DEPTID"=8 AND "NAME" LIKE U'%QENKSGIOG%')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

结果没变。

 

换一种查询方式：

select * from hy_emp where deptid=8 and name like 'QENKSGIOG%'

再看：

Plan hash value: 584260660
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    28 |  1344 |    34   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| HY_EMP       |    28 |  1344 |    34   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | HY_EMP_IDX_1 |    28 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DEPTID"=8)
   2 - access("NAME" LIKE U'QENKSGIOG%')
       filter("NAME" LIKE U'QENKSGIOG%')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

发现HY_EMP_IDX_1用上了，cost也降了一个数量级。

 

结论：

全模糊匹配不走索引完全是真的，右匹配能用上索引也是对的。

--2020-03-17--