Rearrangement of Conditions (sensitive)
From: Roman Gross <gross_at_inf.ethz.ch>
Date: Fri, 15 Jan 1993 10:43:55 GMT
Message-ID: <1993Jan15.104355.28907_at_neptune.inf.ethz.ch>
Date: Fri, 15 Jan 1993 10:43:55 GMT
Message-ID: <1993Jan15.104355.28907_at_neptune.inf.ethz.ch>
We have the following trouble with the arrangement of the condition within the WHERE part.
consider the following example:
test1
c0| c1|
9 | 0 | 4 | 1 | 5 | 2 |
SQL> select c0/c1 from test1 where c1 <> 0 and c0/c1 < 3;
ERROR:
ORA-01476: divisor is equal to zero
SQL> select c0/c1 from test1 where c0/c1 < 3 and c1 <> 0;
C0/C1
2.5
- End of Example ------------------------
Does Orcale ALWAYS handle the conditions from RIGHT TO LEFT ? Or does the optimiser sometimes rearrange them ? Does there exists an other possibility to enforce that the condition c1 <> 0 has to be considered first so that we don't get the division by zero error?
Thanks
-- Roman Gross | Informationssysteme | ETH-Zentrum | Phone: +41 1 254 72 62 CH-8092 Zurich, Switzerland | E-Mail: gross_at_inf.ethz.chReceived on Fri Jan 15 1993 - 11:43:55 CET