有这么一张表：
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<=400000 order by dbms_random.random
然后查看以下SQL的解释计划：
EXPLAIN PLAN FOR select * from hy_emp where deptid=10 and name='ZUVG' select * from table(dbms_xplan.display);
解释计划如下：
Plan hash value: 910676026 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 34 | 1632 | 548 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| HY_EMP | 34 | 1632 | 548 (1)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("DEPTID"=10 AND "NAME"=U'ZUVG') Note ----- - dynamic statistics used: dynamic sampling (level=2)
没有任何索引，自然是全表扫描，Cost是548.
然后我们给deptid和name两个字段加个位图索引：
create bitmap index hy_emp_idx_2 on hy_emp(deptid,name);
再次查看解释计划：
Plan hash value: 1386009635 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 48 | 10 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| HY_EMP | 1 | 48 | 10 (0)| 00:00:01 | | 2 | BITMAP CONVERSION TO ROWIDS | | | | | | |* 3 | BITMAP INDEX SINGLE VALUE | HY_EMP_IDX_2 | | | | | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("DEPTID"=10 AND "NAME"=U'ZUVG') Note ----- - dynamic statistics used: dynamic sampling (level=2)
Cost降到了10，因为新加的索引已经用上了。
然后我们来验证全模糊查询是否能用上这个索引：
EXPLAIN PLAN FOR select * from hy_emp where deptid=10 and name like '%ZUVG%' select * from table(dbms_xplan.display);
解释计划如下：
Plan hash value: 2088399487 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 22 | 1056 | 501 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| HY_EMP | 22 | 1056 | 501 (0)| 00:00:01 | | 2 | BITMAP CONVERSION TO ROWIDS | | | | | | |* 3 | BITMAP INDEX RANGE SCAN | HY_EMP_IDX_2 | | | | | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("DEPTID"=10) filter("NAME" LIKE U'%ZUVG%' AND "DEPTID"=10) Note ----- - dynamic statistics used: dynamic sampling (level=2)
从上面可以发现，新加的索引还是用上了，Cost也比全表查询要低一点，虽然低的不多。看来网上说的模糊查询会导致索引全失效的说法有点偏颇了，至少位图索引能起到聊胜于无的作用。
--2020-03-17--