Table Indices and SQL

Here are some more common patterns to look out for. (This is not meant to be a complete SQL tuning guide.)

• Put Indexes on the most commonly used criteria. If there is no proper index, the optimizer does a full table scan. Consider:
  • Primary keys, foreign keys, ORDER BY fields.
  • Secondary Unique Indexes
• DO: Use a JOIN instead of EXISTS. This is faster for unique scan indexes.
• DO: Use EXISTS instead of IN when working with ID fields, use '=' instead of LIKE. Using LIKE on a system-generated key isn't "reasonable"
• CONSIDER: Using functions like TO_​DATE(), SUBSTR() etc. means indexes on those fields won't be used! Use only when necessary.
• DO: Use the power of optional filters - and not just in the WHERE clause.

  
  FROM d1_tou_map tm, d1_tou_map_l tml
  FROM d1_tou_map tm,  [(F1) d1_tou_map_l tml,] 
  
  

• DO: Only include necessary tables:

  
  SELECT A.usg_grp_cd, A.usg_rule_cd, A.exe_seq,A.referred_usg_grp_cd,A.usg_rule_cat_flg, B.crit_seq, C.descr100 DESCR
  FROM D1_USG_RULE A, d1_usg_rule_elig_crit B, d1_usg_rule_elig_crit_l C
  WHERE A.usg_grp_cd= :H1
  AND A.usg_grp_cd = B.usg_grp_cd
  AND A.usg_rule_cd = B.usg_rule_cd
  AND b.usg_grp_cd = C.usg_grp_cd
  AND b.usg_rule_cd = C.usg_rule_cd
  AND b.crit_seq = C.crit_seq
  AND C.language_cd= :language 
  

  Note that Table B is not necessary; you could instead simply link directly from A to C.

• Offload tasks, like string manipulations, concatenations, row numbering, case conversions, type conversions etc., to the front-end applications
• Do basic validations in the front-end during data entry. This saves unnecessary network roundtrips.
• Avoid using UNION - use UNION ALL if it is possible.
• Operators <> and != will NOT use the index! Also the word "NOT" Use the Greater Than or Less Than operators.

  
  select * from ci_scr_step where  (scr_cd <> 'ZZCW03') has cost 68
  select * from ci_scr_step where (scr_cd > 'ZZCW03' or scr_cd < 'ZZCW03') has cost 1!!!