select * from student where SNAME LIKE 'zhang%'可以用索引 但一下两种LIKE 不能使用索引: select * from student where LIKE SNAME '%zhang%' select * from student where SNAME LIKE '%zhang'
select * from student where instr(SNAME,'zhang')>0 可以走索引,并且也可以实现模糊查询,呵呵,你试试看吧
你做一个 执行计划 看看,这个是 oracle 内置函数
select * from student where SNAME LIKE 'zhang%' 把 % 放到后面吧,只能这样
函数索引明显也不符合楼主要求,因为zhang这个字串不是固定的,有可能是li 是wang...
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>
楼上,把类型改 varchar2 试试看
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 ttt Name Type Nullable Default Comments ---- ------------ -------- ------- -------- A VARCHAR2(20) Y
oracle不给你走索引 是经过分析的
强制走索引效果估计会更糟
但一下两种LIKE 不能使用索引:
select * from student where LIKE SNAME '%zhang%'
select * from student where SNAME LIKE '%zhang'
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 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>
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 ttt
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
A VARCHAR2(20) Y
SQL>