SQL*Plus - WHERE clause performance

Some tinkering done by co worker that I thought you might find interesting.

Characteristic was confirmed on UNIX Oracle V6.0.26 and VMS Oracle V6.0.34.

Would people care to confirm the characteristic on their own hardware/software release as well.

It would be nice if someone from Oracle could clarify this operating charastic and confirm if it will continue to operate this way in Oracle7 and later releases.

Is this characteristic still relevant with cost-based-optimisation?

It would also be nice if this information was included in the Oracle tuning manuals and training courses.

  Investigations have shown that the order of predicates in the WHERE   statement can be critical to the performance of the query. Examples proving   this are included below.   

  In summary :   

  If your predicate is of the form "X AND Y", then place the predicate that   is most likely to *fail* on the right (i.e. the Y one). If the Y predicate   fails then the X predicate will not be evaluated.   

  eg ([unknown] AND FALSE) gives FALSE and Oracle doesn't evaluate [unknown]

     at all and that means less processing and faster response.   

  If your predicate is of the form "X OR Y", then place the predicate that is   most likely to *succeed* on the left (i.e. the X one). If the X predicate is   true then the Y predicate will not be evaluated.

  eg (TRUE OR [unknown]) gives TRUE and Oracle doesn't evaluate [unknown]

     at all and that means less processing and faster response.   

  The implications are that performance gains can be made if you rearrange   the ordering of the predicates in your WHERE clause.   

  The following examples highlight this :      

• First a description of the table.

  DEV6A> desc ord

   Name                            Null?    Type
   ------------------------------- -------- ----
   ORDID                           NOT NULL NUMBER(4)
   ORDERDATE                                DATE
   COMMPLAN                                 CHAR(1)
   CUSTID                          NOT NULL NUMBER(6)
   SHIPDATE                                 DATE
   TOTAL                                    NUMBER(8,2)
  

  DEV6A> select count(*) from ord;   

  COUNT(*)                                                                        


  --------                                                                        


        21                                                                        

  DEV6A> set timing on   

--

• Test 1 : Looking at the AND clause
  --
  

  DEV6A> get temp1
    1 select *
    2 from ord
    3 where (-1 = (select count(a.ordid)

    4               from ord a, ord b, ord c, ord d)) and 
    5*       (1 = 0)
  

  no rows selected   

   ELAPSED: 0 00:00:00.06 CPU: 0:00:00.04 BUFIO: 2 DIRIO: 2 FAULTS: 0      DEV6A> get temp2
    1 select *
    2 from ord
    3 where (1 = 0) and

    4        (-1 = (select count(a.ordid) 
    5*              from ord a, ord b, ord c, ord d))
  

  no rows selected   

   ELAPSED: 0 00:00:46.50 CPU: 0:00:44.19 BUFIO: 2 DIRIO: 0 FAULTS: 0   

• Note the major difference in run time. This can only be attributed to
• the fact that the second query is performing the subquery first and then
• doing the (1 = 0) clause.

--

• Looking at the OR clause
  --
  

  DEV6A> get temp3
    1 select *
    2 from ord
    3 where (1 = 1) or

    4        (-1 = (select count(a.ordid) 
    5*              from ord a, ord b, ord c, ord d ))
  
       ORDID ORDERDATE C     CUSTID SHIPDATE       TOTAL


  ---------- --------- - ---------- --------- ----------


         610 07-JAN-87 A        101 08-JAN-87      101.4
         >>>> 19 rows deleted for brevity <<<<
         621 15-MAR-87 A        100 01-JAN-87        730
  

  21 rows selected.   

   ELAPSED: 0 00:00:02.04 CPU: 0:00:00.17 BUFIO: 28 DIRIO: 8 FAULTS:15      DEV6A> get temp4
    1 select *
    2 from ord
    3 where (-1 = (select count(a.ordid)

    4               from ord a, ord b, ord c, ord d)) or 
    5*       (1 = 1)
  
       ORDID ORDERDATE C     CUSTID SHIPDATE       TOTAL


  ---------- --------- - ---------- --------- ----------


         610 07-JAN-87 A        101 08-JAN-87      101.4
         >>>> 19 rows deleted for brevity <<<<
         621 15-MAR-87 A        100 01-JAN-87        730
  

  21 rows selected.   

   ELAPSED: 0 00:00:47.94 CPU: 0:00:44.16 BUFIO: 28 DIRIO: 4 FAULTS: 0   

• Note the major difference in run time. This can only be attributed to
• the fact that the first query is performing the subquery first and then
• doing the (1 = 1) clause.

  ---

Interesting isn't it.

-- 

Bruce...        pihlab_at_hhcs.gov.au
                 ^^
*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Health, Housing & Community Services *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************