Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Order of where clause matters!

Re: Order of where clause matters!

From: <dejaisbogus_at_my-deja.com>
Date: 2000/06/19
Message-ID: <8ilr4h$ugd$1@nnrp1.deja.com>#1/1

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US