Re: Conjunction junction

"Mikito Harakiri" <mikharakiri_at_iahu.com> wrote in message news:RFvhd.16$J%6.187_at_news.oracle.com...
> "Alan" <alan_at_erols.com> wrote in message
> news:2unbuhF2cbaliU1_at_uni-berlin.de...
> > UNION. Of course this can be done procedureally, but the XOR I described
> > would have allowed it in straight SQL. Maybe call it an IOR (Immediate
OR)
> > if XOR is confusing.
>
> There are 2 separate concepts:
> 1. what connectors between the boolean expressions are allowed, and
> 2. row limit "predicate".
>
> Admittedly item #2 would be different in different SQL dialects. For
example
>
> SELECT stock_location
> FROM pennants
> WHERE (color = 'red' OR color = 'green')
> and rownum <=1
> ^^^^^^^^^^^^
>
> Alternatively, it might be the client that controls how many rows are
> fetched.
>
> Performance of the above query is completely different topic. For example,
> optimizer might decide to convert this query into a concatenation. It
> executes the first branch, and the row satisfies the condition color =
> 'red', then it goes to the output, and execution stops immediately.
> Otherwise, it would follow to the second branch. Alternative execution
plan
> is what you suggested: it iterates the relation tuples and checks the
> [complex] condition color = 'red' OR color = 'green' and stops as soon as
a
> tuple is found.

There's another consideration. There are optimisation strategies that try to minimize the cost of getting the FIRST row in the result, and there are different strategies that try to minimize the cost of retrieving the LAST row, and all the others before it.

There are even circumstances where it might be desirable to let the client decide whether fast first or fast last is better. There are optimizers that let you choose at run time. Received on Mon Nov 01 2004 - 22:31:28 CET