Search notes:

Oracle: Indexes not being used

Create a demonstration table …

create table    tq84_tab (
   num    varchar2(10), --  <<< will be filled with numbers and should be number
   xyz    varchar2(10)
);

… and fill some values into it:

insert into tq84_tab
select
   level,
   rpad('x', 10, 'x')
from
   dual connect by level <= 10000;

Create a (unique) index on num:

create unique index tq84_tab_ix on tq84_tab(num);

Make sure optimizer statistics are up to date:

begin
   dbms_stats.gather_table_stats(user, 'tq84_tab');
end;
/

Show the execution plan for a string comparison (num = '42'). The index is used:

explain plan for select xyz from tq84_tab where num = '42';

select * from table(dbms_xplan.display(format=>'basic predicate'));
--
-- ---------------------------------------------------
-- | Id  | Operation                   | Name        |
-- ---------------------------------------------------
-- |   0 | SELECT STATEMENT            |             |
-- |   1 |  TABLE ACCESS BY INDEX ROWID| TQ84_TAB    |
-- |*  2 |   INDEX UNIQUE SCAN         | TQ84_TAB_IX |
-- ---------------------------------------------------
--  
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--  
--    2 - access("NUM"='42')

Almost the same thing, but the comparison uses a number (num = 42). The index is not used. The reason for not using the index is that behind the scenes, the query planner reformulates the predicate to to_number(num)=42. (Note also how the predicate information changed from access(…) to filter(…)):

explain plan for select xyz from tq84_tab where num = 42;

select * from table(dbms_xplan.display(format=>'basic predicate'));
--
-- --------------------------------------
-- | Id  | Operation         | Name     |
-- --------------------------------------
-- |   0 | SELECT STATEMENT  |          |
-- |*  1 |  TABLE ACCESS FULL| TQ84_TAB |
-- --------------------------------------
--  
-- Predicate Information (identified by operation id):
-- ---------------------------------------------------
--  
--    1 - filter(TO_NUMBER("NUM")=42)

The same problem can also occur in PL/SQL procedures or functions:

create function tq84_fnc(val number) return varchar2
is
   res varchar2(10);
begin

   select xyz into res from tq84_tab where num = val;
   return res;

end;
/

The init parameter plsql_warnings allows one to be warned about such problems:

alter function tq84_fnc compile plsql_warnings='enable:7204';

select
   line,
   position            col,
   substr(text, 1, 80) text,
   attribute
from
   user_errors
where
   name = 'TQ84_FNC';
--
--       LINE COL TEXT                                                                             ATTRIBUTE
-- ---------- --- -------------------------------------------------------------------------------- ---------
--          6  44 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING

Index