Here are some more common patterns to look out for. (This is not meant to be a complete SQL tuning guide.)
TO_DATE()
, SUBSTR()
, and so
on means indexes on those fields will not be used. Use only when necessary.
FROM d1_tou_map tm, d1_tou_map_l tml
FROM d1_tou_map tm, [(F1) d1_tou_map_l tml,]
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.
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!!!