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.
If you like this post, you may as well like these too:
- Rules for Better SQL Programming # Tip 1 Always try to keep the hit list small by using the where clause were ever required or by describing the full search condition in the where clause. Select Query #1...
- Rules for Better SQL Programming # Tip 2 Tip #2 : Minimize the Amount of Transferred Data Minimize the amount of data transferred between the database and the application server. Wrong SELECT * FROM sflight INTO xflight WHERE...
- Rules for Better SQL Programming # Tip 3 Tip #3: Keep the number of round trips between the database and the application server small. Use high-speed array operations with UPDATE, INSERT, DELETE, MODIFY. Wrong LOOP AT itab INTO...
- Rules for Better SQL Programming # Tip 5 Tip #5. Remove the load from the database. Check if the table meets the criteria for table buffering. When applying the table buffering check if the table is frequently read,...
Leave a Reply