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!!!