实验用表：
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--