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:

1. Connect to the SH schema:

   CONNECT sh@TESTDB/sh
   

2. 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;
       END IF;
     END LOOP;
   END;
   /
   SET TIMING OFF
   

3. In the previous script, we change the order in which the two conditions are expressed in the IF statement:

   SET TIMING ON
   DECLARE
     TAB_QTY DBMS_SQL.NUMBER_TABLE;
     TAB_TIME DBMS_SQL...