在没有创建数据直方图之前,查询优化器是cbo,可能不会选择代价最低(效率最高)的方式查询.
先创建表
--日语假名表 CREATE TABLE JAPANESE_SOUNDMARK ( ID INTEGER PRIMARY KEY, ROMAJI VARCHAR2(10), PHONETIC_SYMBOL VARCHAR(20) );
创建序列
--创建自增长的序列,用于主键 CREATE SEQUENCE SEQ_JAPANESE_SOUNDMARK START WITH 1 NOMAXVALUE INCREMENT BY 1 NOCYCLE CACHE 10;
创建反向键索引
--给需要模糊查询的列加上反向键索引 CREATE INDEX ROMAJI_REVERSE_INDEX ON JAPANESE_SOUNDMARK(ROMAJI) REVERSE;
创建普通索引
--普通索引 CREATE INDEX PHONETIC_SYMBOL_INDEX ON JAPANESE_SOUNDMARK(PHONETIC_SYMBOL);
注意:
以上语句均为DDL(Data Definition Language)语句,会自动提交事务,如果之前有DML(Data Manipulation Language)语句运行了,但没提交事务,会将之前所有的DML语句也提交事务,ROLLBACK会失效.
1.没有索引时(实际上不需要测试)
--模糊查询,等值 EXPLAIN PLAN FOR SELECT * FROM JAPANESE_SOUNDMARK WHERE PHONETIC_SYMBOL LIKE 'A'; --查看执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
运行结果:
显而易见:全表扫描
这个其实不需要测试,因为没有索引只能走全表扫描,所以,like '%字符串'和like'字符串%'都是全表扫描.
2.不使用通配符,hint使用普通索引
--模糊查询,等值 EXPLAIN PLAN FOR SELECT /*+ INDEX_RS(JAPANESE_SOUNDMARK PHONETIC_SYMBOL_INDEX) */ * FROM JAPANESE_SOUNDMARK WHERE PHONETIC_SYMBOL LIKE 'A'; --查看执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
结果:
Index Range Scan
3.使用通配符后缀,hint使用普通索引
--模糊查询,以...开头 EXPLAIN PLAN FOR SELECT /*+ INDEX_RS(JAPANESE_SOUNDMARK PHONETIC_SYMBOL_INDEX) */ * FROM JAPANESE_SOUNDMARK WHERE PHONETIC_SYMBOL LIKE 'A%'; --查看执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
结果:
同样是:
Index Range Scan
4.使用通配符前缀,hint使用普通索引
--模糊查询,以...结尾 EXPLAIN PLAN FOR SELECT /*+ INDEX_RS(JAPANESE_SOUNDMARK PHONETIC_SYMBOL_INDEX) */ * FROM JAPANESE_SOUNDMARK WHERE PHONETIC_SYMBOL LIKE '%A'; --查看执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
结果:
Index Full Scan
5.不使用通配符,hint使用反向键索引
--模糊查询,等值 EXPLAIN PLAN FOR SELECT /*+ INDEX_RS(JAPANESE_SOUNDMARK ROMAJI_REVERSE_INDEX) */ * FROM JAPANESE_SOUNDMARK WHERE ROMAJI LIKE 'A'; --查看执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
结果:
Index Range Scan
6.使用通配符后缀,hint使用反向键索引
--模糊查询,以...开头 EXPLAIN PLAN FOR SELECT /*+ INDEX_RS(JAPANESE_SOUNDMARK ROMAJI_REVERSE_INDEX) */ * FROM JAPANESE_SOUNDMARK WHERE ROMAJI LIKE 'A%'; --查看执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY)
结果:
Index Full Scan
7.使用通配符前缀,hint使用反向键索引
--模糊查询,以...结尾 EXPLAIN PLAN FOR SELECT /*+ INDEX_RS(JAPANESE_SOUNDMARK ROMAJI_REVERSE_INDEX) */ * FROM JAPANESE_SOUNDMARK WHERE ROMAJI LIKE '%A'; --查看执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
结果:
Index Full Scan
结论:
使用普通索引在使用通配符后缀时,只能走Index Range Scan,
而使用反向键索引在使用通配符后缀时,只能走Inde Full Scan,
其他情况,两种索引的索引使用方式相同.
一般来说,Index Range Scan效率要高于Index Full Scan,所以,使用普通索引来优化模糊查询就行了.
全模糊查询:
1.使用普通索引时
--全模糊查询 EXPLAIN PLAN FOR SELECT /*+ INDEX_RS(JAPANESE_SOUNDMARK PHONETIC_SYMBOL_INDEX) */ * FROM JAPANESE_SOUNDMARK WHERE PHONETIC_SYMBOL LIKE '%A%'; --查看执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
结果:
Index Full Scan
但是,它有另外一种等价写法:
--全模糊查询 EXPLAIN PLAN FOR SELECT /*+ INDEX_RS(JAPANESE_SOUNDMARK PHONETIC_SYMBOL_INDEX) */ * FROM JAPANESE_SOUNDMARK WHERE PHONETIC_SYMBOL LIKE '%A' AND PHONETIC_SYMBOL LIKE 'A%'; --查看执行计划 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
结果:
Index Range Scan