Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Order of where clause matters!
In article <TRsZ4.4818$DC2.676807_at_nnrp3.clara.net>,
"Dave Wotton" <Dave.Wotton_at_dwotton.nospam.clara.co.uk> wrote:
> Evan wrote in message <39355A95.4BCF74B0_at_cadvision.com>...
> >Oracle 8 documentation says the same thing. However, considering the
> >documentation misleading is being a bit courteous. Oracle's license
(as does
> >most software) has a clause warrenting that the software conforms to
documented
> >behaviour.
> >
> Gentle nudge: Why don't *you* report the "error" to Oracle? After all,
> you're the one who misinterpreted the documentation. Neither I nor
Steve
> Haynes, the original poster, expect brackets to work in this way.
>
> If you do, I guarantee that the most Oracle will do is amend the
> documentation, they won't change the optimiser. If they changed it as
you
> suggest, millions of lines of SQL all over the world would "break"
from a
> performance point of view, as the optimiser would be forced to process
> bracketted conditions differently to how it does now.
>
> Dave.
I myself have opened a documentation bug with Oracle concerning this behavior of the DIVIDE-BY-ZERO (I ran across it while testing behavior of alternatives to DECODE). I agree this Oracle behavior has been around at least since 7.x, and there is an amusing section of the 1997 biography of Larry Ellison (Even God doesn't think he's Larry...) where Ellison defends the nonexistent Oracle optimization of early versions as 'syntactic optimization' vs. the 'semantic optimization' of Ingres et. al. This 'optimization' simply executed each line of the SELECT after the FROM in-order as it encountered it (which is not too different from present rule-optimization, altho that is 'bottom-up').
It is not at all obvious how even the rule-optimizer orders WHERE clauses. Take the example for outer-joins, where 'table.rowid is null' normally fails (i.e. any row must have a non-null rowid) unless an outer-join follows, in which case it depends on whether a null row had to be provided:
SQL> select * from table1;
F1 F2 F3
---------- ---------- ----------
test1 test2 test1 test3 test4 test3 test5 test6
SQL> select * from table5;
F1 F2 F3
---------- ---------- ----------
test1 test2 test1 test3 test4 test3 test5 test6
SQL> select * from table1, table5 where table1.f1=table5.f1(+) and 2 table5.rowid is null;
no rows selected
but see the following:
SQL> select * from table2;
F1 F2 F3
---------- ---------- ----------
1 test2 test1 999 test4 test3
SQL> select * from table1, table2 where table2.rowid is null and 2 table1.f1=table2.f3(+);
F1 F2 F3 F1 F2 F3 ---------- ---------- ---------- ---------- ---------- ---------- test5 test6
where 'table2.rowid is null' succeeds since the outerjoin matched on a non-existent table2 row.
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Mon Jun 19 2000 - 00:00:00 CDT