Rules for Better SQL Programming # Tip 4

Tip #4: Keep the Cost of the Search Down
Specify the WHERE clause to keep the number of searches down and create suitable indices if necessary.

Wrong

SELECT bookid FROM sbook INTO xflight
WHERE orderdate = '20020304'.
WRITE: / xbookid.
ENDSELECT.

Right

SELECT bookid
FROM sbook INTO xbookid
WHERE carrid = 'LH ' AND
connid = '0300' AND
fldate = '20020304'.
WRITE: / xbookid.
ENDSELECT.

Make sure that the first n fields of the designated index are stated with EQ within the WHERE clause.

Wrong

SELECT * FROM sflight INTO xflight
WHERE carrid = 'LH ' AND
fldate LIKE '2002%'.
WRITE: / xflight-fldate.
ENDSELECT.

Right

SELECT * FROM sflight
INTO xflight
WHERE carrid = 'LH ' AND
connid = '0300' AND
fldate LIKE '2002%'.
WRITE: / xflight-fldate.
ENDSELECT.

Replace the inner OR with an IN operator

Wrong

SELECT * FROM sflight
INTO xflight
WHERE carrid = 'LH ' AND
(connid = '0300' OR connid = '0302') AND
fldate LIKE '2002%'.
WRITE: / xflight-fldate.
ENDSELECT.

Right

SELECT * FROM sflight
INTO xflight
WHERE carrid = 'LH ' AND
connid IN ('0300', '0302') AND
fldate LIKE '2002%'.
WRITE: / xflight-fldate.
ENDSELECT.

You cannot process NOT operators in SELECT using an index.

Wrong

SELECT * FROM sflight
INTO xflight
WHERE carrid <> 'LH ' AND
connid = '0300'.
WRITE: / xflight-fldate.
ENDSELECT.

Right

SELECT * FROM sflight
INTO xflight
WHERE carrid IN ('AA ', 'QM ') AND
connid = '0300'.
WRITE: / xflight-fldate.
ENDSELECT.

Think about optimizer hints if the optimizer fails to find a sound execution plan.

Wrong

SELECT carrid connid cityfrom
FROM spfli INTO (xcarrid, xconnid, xcityfrom)
WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'.
WRITE: / xcarrid, xconnid, xcityfrom.
ENDSELECT.

Right

SELECT carrid connid cityfrom
FROM spfli INTO (xcarrid, xconnid, xcityfrom)
WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'
%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.
WRITE: / xcarrid, xconnid, xcityfrom.
ENDSELECT.