Using short-circuit IF statements
In this recipe, we will see how the order in which we evaluate a compound IF
statement of more than one condition, may affect performance.
How to do it...
The following steps will demonstrate compound IF
statements:
- Connect to the
SH
schema:CONNECT sh@TESTDB/sh
- Retrieve the records in the
SALES
table and loop through them to count the number of sales that took place before June 28, 1998 with a quantity greater than 1:SET TIMING ON DECLARE TAB_QTY DBMS_SQL.NUMBER_TABLE; TAB_TIME DBMS_SQL.DATE_TABLE; CNT NUMBER := 0; BEGIN SELECT AMOUNT_SOLD, TIME_ID BULK COLLECT INTO TAB_QTY, TAB_TIME FROM SALES; FOR J IN TAB_QTY.FIRST..TAB_QTY.LAST LOOP IF TAB_QTY(J) > 1 AND TAB_TIME(J) < '27-JUN-98' THEN CNT := CNT + 1; ...
Get Oracle Database 11gR2 Performance Tuning Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.