Oracle Database 11gR2 Performance Tuning Cookbook

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; ...