# 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 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 is not recommended.
- CONSIDER: Using functions like `TO_DATE()`, `SUBSTR()`, and so on means indexes on those fields will not 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.

- Off load tasks, like string manipulations, concatenations, row numbering, case conversions, type conversions, and so on, 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!!!

Parent topic: Data Explorer