SQL*Plus - WHERE clause performance
Date: 30 Oct 92 11:02:44 +1100
Message-ID: <1992Oct30.110244.451_at_hhcs.gov.au>
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(*)1 row selected.
--------
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. * *******************************************************************Received on Fri Oct 30 1992 - 01:02:44 CET