oracle 模糊查询问题
我们建的索引是为了在查询时速度更快，但是在遇到oracle中的关键字如like时，就跳过了索引。比如SNAME加了索引，select * from student where SNAME '%zhang%'。这时的模糊查询速度并不快，
有没有什么办法能不让它跳过索引，请各位大师帮忙
[解决办法]
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 Connected as scott SQL> SQL> CREATE TABLE ttt 2 (a NUMBER(20)); Table created SQL> insert into ttt select rownum from dual connect by rownum<100000; 100000 rows inserted SQL> create index ttt_1 on ttt(a); Index created SQL> explain plan for select * from ttt where a=1; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT--------------------------------------------Plan hash value: 1579783580--------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| TTT_1 | 1 | 13 | 1 (0)| 00:00:01 |--------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("A"=1)Note----- - dynamic sampling used for this statement 17 rows selected SQL> explain plan for select * from ttt where instr(a,'99999')>0; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT--------------------------------------------Plan hash value: 3525241920--------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------| 0 | SELECT STATEMENT | | 3 | 39 | 44 (7)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TTT | 3 | 39 | 44 (7)| 00:00:01 |--------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(INSTR(TO_CHAR("A"),'99999')>0)Note----- - dynamic sampling used for this statement 17 rows selected SQL>
[解决办法]
Connected to Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 Connected as scott SQL> drop table ttt; Table dropped SQL> SQL> CREATE TABLE ttt 2 (a VARCHAR2(20)); Table created SQL> insert into ttt select rownum from dual connect by rownum<100000; 100000 rows inserted SQL> create index ttt_1 on ttt(a); Index created SQL> explain plan for select * from ttt where nvl(a,1)=1; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT--------------------------------------------Plan hash value: 3525241920--------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------| 0 | SELECT STATEMENT | | 2 | 24 | 46 (11)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TTT | 2 | 24 | 46 (11)| 00:00:01 |--------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(TO_NUMBER(NVL("A",'1'))=1)Note----- - dynamic sampling used for this statement 17 rows selected SQL>
[解决办法]
SQL> CREATE TABLE ttt(a VARCHAR2(20)); Table created SQL> insert into ttt select rownum from dual connect by rownum<100000; 100000 rows inserted SQL> create index ttt_1 on ttt(a); Index created SQL> explain plan for select * from ttt where instr(a,'99999')>0; Explained SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT--------------------------------------------Plan hash value: 3525241920--------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------| 0 | SELECT STATEMENT | | 2 | 24 | 44 (7)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TTT | 2 | 24 | 44 (7)| 00:00:01 |--------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(INSTR("A",'99999')>0)Note----- - dynamic sampling used for this statement 17 rows selected SQL> desc tttName Type Nullable Default Comments ---- ------------ -------- ------- -------- A VARCHAR2(20) Y SQL>