Re: Order of where clause matters!

In article <8ilr4h$ugd$1_at_nnrp1.deja.com>, dejaisbogus_at_my-deja.com wrote:

> 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.

[ Sorry about the delay in noticing this post - I went away for a bit, and when I got back the thread had fallen off the bottom of my newsfeed - hence this post from Deja ]

The problem which Evan wanted someone to raise with Oracle was *his* assertion that the parser is not conforming to specification because you can't control the order in which clauses are executed by the use of parentheses. He's got zero chance of that being implemented.

The problem about divide-by-zero has more merit because if you've coded your SQL this way, whether or not you get the error is dependent on the order in which the optimiser executes the clauses, which is not (easily) deterministic, and may change without warning if you are using the CBO and the statistics about your tables changes. Personally, I'd be happy with a statement in the documentation which says something along the lines of "Do not write where clauses such that the validity of one cause is dependent on the prior execution of other clauses".

I'm not sure what point you are trying to make with your outer-join examples.

A statement of the form:

    select .....

      from table_a,
           table_b
     where table_a.col_1 = table_b.col_1(+)
       and table_b.rowid is null;

simply means find all rows in table_a where there is no matching row on table_b. Both your examples demonstrate this behaviour. They are not inconsistent.

Admittedly it *looks* confusing because the query won't return matching values from table_b despite the table_a.col1 = table_b.col1(+) clause (because of the table_b.rowid is null clause), but DOES return rows where there is no match, even though table_a.col_1 is patently != table_b.col1 (because table_b.col1 will be null in this condition). But you've got to remember the weird three-way logic with nulls. If table_b.col_1 is null (from a dummy row to satisfy the outer-join), then you are making a comparison against a null value *which is an undefined operation* - it is as if that clause where not there, so the only condition which applies is "table_b.rowid is null", which is true, so the composite row is returned.

I don't understand your claim that these examples demonstrate indeterminancy in where clause execution. I believe if you do an explain plan, you'll find that they're both executed the same way. Also, changing the order of the where clauses in your query won't make any difference to the number of rows returned (which I think you were claiming). Switch them around in both queries - it has no effect.

Dave.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jun 29 2000 - 00:00:00 CDT