Index not used because of type conversion

If  the  data type  of  a  variable  is  not  the same  with  the  column data type  then  an implicit  data type  conversion is  occured.This means  one side of equality  is  converted  to  other  side. For number  to  char conversion  char side is  converted  to  number(Because all numbers  are  charecters but  not all charecters  are  numbers ).Also  similiar  conversions are  made  for  different  combination of data types.

Let is  look  some  examples

Example  1:

1. Create  table   and indexes ,insert some rows and also  analyze table 
   create table employee (
   emp_id        varchar2(10),emp_name  varchar2(100),
   emp_entry_date date ,
   emp_dept_no number,
   emp_born_date date);
   
   alter table employee add constraints emp_id_pk primary key(emp_id ) using index ;
2. select  from table   emp_id is  character  but  the variable (100)  is  number
   
   SQL>set autotrace  traceonly
   SQL>select * from employee where emp_id=100;
   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=41)
   1 0 TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=2 Card=1 Bytes=41)  
3. Try it with  the  variable  '100' no index  suppression.
   
   SQL>set autotrace traceonly
   SQL>select * from employee where emp_id='100';
   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=41)
   1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMPLOYEE' (TABLE) (Cost=1 Card=1 Bytes=41)
   2 1 INDEX (UNIQUE SCAN) OF 'EMP_ID_PK' (INDEX (UNIQUE)) (Cost=1 Card=1)

Example  2 :
wrong  type  definition  in java  or  .net  code .

• Here is  an  example of  java code  for  index  suppression (The  table is  employee  table in example 1).Notice  that     emp_id  is   varchar2  in table  empID  is  defined  as  number 

StringBuffer stringBuffer = new StringBuffer();

String employeeAd = "";

int empID=100;

stringBuffer.append(" select emp_name ");

stringBuffer.append(" from employee where emp_id=? ");

...

..
statement.setInt(1, empID);

• Here is an example of java code for index suppression (The table is employee table in example 1)
  string SQLCHK = @"select emp_name from employee where emp_id=:empID";
  int empID = 100;
  string empAd = "";
  ....
  empAdCMD.AddInParameter("empID", DbType.Int16, empID);

• And   The   execution plan  of  the  the above sql's

SQL_ID 12hrzrx0n0wqx, child number 0
-------------------------------------
select emp_name from employee where emp_id=:1
Plan hash value: 1970680878

------------------------------------------------------------------------------
|Id |Operation |Name |Rows |Bytes |Cost (%CPU)|Time |
------------------------------------------------------------------------------
|0|SELECT STATEMENT ||||2 (100)||
|1|TABLE ACCESS FULL|EMPLOYEE |1|23|2 (0)|00:00:01 |
------------------------------------------------------------------------------




Example 3:
Also  conversions may be  done  if  different  type  of  columns  used  for  join operation 

1. Create table sales  insert some rows and also analyze table
   
   create table SALES (
   
   emp_id         number,
   sale_date     date,
   sale_amount number );
   
   create index SALES_emp_ix on SALES(emp_id);
   create index SALES_date_ix on SALES(SALE_DATE);
   
   
2. 
   
   Try to  find  the sales (and  the employees who sales )  5 days  ago
   
   SQL>set autotrace traceonly
   

   SQL> select e.emp_id,e.emp_name,s.sale_amount
                    from employee e,sales s
                    where s.sale_date =TRUNC(SYSDATE)-5
                      and e.emp_id=s.emp_id;
   
   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=37)
   1 0 HASH JOIN (Cost=4 Card=1 Bytes=37)
   2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SALES' (TABLE) (Cost=1 Card=1 Bytes=14)
   3 2 INDEX (RANGE SCAN) OF 'SALES_DATE_IX' (INDEX) (Cost=1 Card=1)
   4 1 TABLE ACCESS (FULL) OF 'EMPLOYEE' (TABLE) (Cost=2 Card=100 Bytes=2300)

   
   

Since  sales.emp_id is  number  and  employee.emp_id  is  varchar2  then   "e.emp_id=s.emp_id"  is internally  converted  to   "to_number(e.emp_id)=s.emp_id"  and  the  index  of  employee  (emp_id_pk ) will not we  used.

Note:This  articale is  for  SQL  developers for  SQL Tuning

See also:
Examples and Solutions for Operations on Indexed Columns