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 

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

Diagnosing Oracle Clusterware Node evictions (Diagwait)

Oracle Clusterware evicts the node from the cluster when

• Node is not pinging via the network heartbeat
• Node is not pinging the Voting disk
• Node is hung/busy and is unable to perform either of the earlier tasks

In Most cases when the node is evicted, there is information written to the logs to analyze the cause of the node eviction. However in certain cases this may be missing, the steps documented in this note are to be used for those cases where there is not enough information or no information to diagnose the cause of the eviction for Clusterware versions less than 11gR2 (11.2.0.1)

How to setup diagwait

1. execute  as  root and   run it on both node
   #crsctl stop crs
   #$CRS_HOME/bin/oprocd stop
2. Ensure that Clusterware stack is down on all nodes by executing  do not  continue If there are clusterware processes running and you proceed to the next step, you will corrupt your OCR on both node
   
   #ps -ef |egrep "crsd.bin|ocssd.bin|evmd.bin|oprocd"
3. From one node of the cluster, change the value of the "diagwait" parameter to 13 seconds by issuing the command as root:Changing the Clusterware parameter diagwait to 13 is the Oracle supported technique to change the oprocd margin to 10 seconds Note that 13 is the only allowed value for setting the diagwait parameter to. Any value other than 13 (or unset) is not allowed and not support
   
   #crsctl set css diagwait 13 -force
4. Check if diagwait is set successfully by executing. the following command.The command should return 13. If diagwait is not set, the following message will be returned "Configuration parameter diagwait is not defined"
   
   #crsctl get css diagwait
5. Restart the Oracle Clusterware on all the nodes by executing
   
   #crsctl start crs
6. Check that nodes  are running
   
   #crsctl check crs

Unsetting/Removing diagwaitDiagwait should not be unset without fixing the OS scheduling issues as that can lead to node evictions via reboot.Diagwait delays the node eviction (and reconfiguration) by diagwait (13) seconds .In case there is a need to remove diagwait, the above mentioned steps need to be followed except step 3 needs to be replaced by the following command
#crsctl unset css diagwait