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>

------解决方案--------------------
SQL code
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 code
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
--------