Examples and Solutions for Operations on Indexed Columns

In certain cases  operations and  functions on indexed  columns  prevent using  indexes.Here  are  some examples  and solutions  to them

1. Most common error  is  to  select  the dates  that  has  time on it.e.x selecting the records of  yesterday .column_name  ise  insert_date  and  has an index on it
   
   ....where  trunc(insert_date)=trunc(sysdate-1)  
   
   Above example  prevents index usage  instead  of it  try this 
   
   ... where  insert_date >=trunc(sysdate-1)    and   insert_date  <trunc(sysdate)
   
   
   Lets'  arrange  this example  for  a  determined  date  (e.x '22/09/2010')
   
   ... where insert_date >=to_date('22/09/2010','dd/mm/yyyy') and insert_date <to_date('23/09/2010','dd/mm/yyyy');
2. Adding,substracting  .. an expression  to indexed  column.(e.x There is an index  on  salary  column which we  expect  to be used )
   
   select  count(*) from employee  where  salary+100 >1000;
   
   Above example prevents index usage instead of it try this
   
   
   select count(*) from employee where salary >1000-100;
   
   also  this operation  can be  while  a   join operation   like 
   
   select  * from  table1 a,table2 b  where  a.start_date > sysdate-10  and b.salary+100=b.min_value;
   
   Above example prevents index usage instead of it try this
   
   select * from table1 a,table2 b where a.start_date > sysdate-10  and  b.salary=b.min_value-100;
3. Concatenate index column (e.x we  have  index  on first_name,last_name)
   
   select  * from employee  where  first_name||' '||last_name='SERDAR TURGUT';
   
   instead of it  try this
   
   select * from employee where first_name='SERDAR'  and  last_name='TURGUT';
4. substring operations
   
   ...where  substr(id,1,2) = 'AA';
   
   Think that  first two latter  of  a  column indicate  someting  so we  try to query the first  n later then try   this
   
   ...where id like   'AA%';
   
   If we  do not  use  only   first n characters  also  we  use  starting from n th character  like
   
   ...where substr(id,3,2) = 'AA';
   
   Then I can advice  two solutions.  first solution  is  tell your  dba  to    create  a  function based   index  on  substr(id,3,2) .Second  solution is  review  the data model  and   usage  of  this  column .Your  mistake  is  Instead  of defining  a different column  you have  concatenated  the  different columns on one column. like  first two characters are a different property and then three  characters  are  another .. Then you can divide this  column  into different columns 
5. Inequalities   prevent  index usage  (e.x  we have  index  on status  integer  column )
   .. where  status  <>0;
   
   Think that all the  values  >=0   so we  want to  select  all the rows  except 0
   
   ...where  status  >0;

Note:This articale is for SQL developers for SQL Tuning

Related  Topics:Index not used because of type conversion