Re: Using a Select as a Var??

On Jul 5, 8:15 pm, "Chris L." <diver..._at_uol.com.ar> wrote:
> On Jul 5, 3:51 pm, valigula <valig..._at_gmail.com> wrote:
>
>
>
> > On 5 jul, 19:50, DA Morgan <damor..._at_psoug.org> wrote:
>
> > > Chris L. wrote:
> > > >>> On Jul 5, 5:50 pm, valigula <valig..._at_gmail.com> wrote:
> > > >>>> Hi All
> > > >>>> Need top repeat a select 3 times but with differents where condition.
> > > >>>> I tried to repeat the selects and after use a couple of union to join
> > > >>>> then, but it takes toooo long.
>
> > > > Instead of UNION you could use OR in your WHERE clause.
>
> > > > Select * from sometable where name = 'JOHN'
> > > > union
> > > > Select * from sometable where salary > 500
> > > > union
> > > > Select * from sometable where state <> 'NY';
>
> > > > Select * from sometable where name = 'JOHN' OR salary > 500 OR state
> > > > <> 'NY';
>
> > > > If your queries take too long, try and index the tables. The index
> > > > fields must match your WHERE fields.
>
> > > Very inefficient compared with WITH.
> > > --
> > > Daniel A. Morgan
> > > University of Washington
> > > damor..._at_x.washington.edu (replace x with u to respond)
> > > Puget Sound Oracle Users Groupwww.psoug.org-Ocultartexto de la cita -
>
> > > - Mostrar texto de la cita -
>
> > Thanks chris the problem is: the querys are morre restrictives to do
> > them in just 1 query ..- Hide quoted text -
>
> > - Show quoted text -
>
> You said previously, you got it working using UNIONs, only it was "too
> slow".
>
> If you do something like this
>
> select (something) from (somewhere) where (one condition)
> union
> select (something) from (somewhere) where (another condition)
> union
> select (something) from (somewhere) where (third condition);
>
> Oracle will do (worst case) three full table scans (if there are no
> relevant indexes).
>
> Instead if you do
>
> select (something) from (somewhere)
> where (one condition) or (another condition) or (third condition);
>
> Oracle will do one single full table scan (at least that's what my
> explain_plan shows on Oracle 9.2.0.4.0 either with optimizer_mode=rule
> or =choose)
>
> If "somewhere" is more than one table, don't repeat the join
> conditions on the WHERE clause.
>
> I don't know how WITH would be more efficient in this case, I've tried
> modifying the query to use WITH but got no better explain plan.
>
> Greetings

Surely UNION changes the logic in the case where duplicates could be returned, as well as adding a DISTINCT sort/hash operation that may be unnecessary.

The idea of the WITH clause (subquery factoring) is for the case where the "(somewhere)" in your query is something more complicated than a single table, in which case you effectively declare it at the top and reuse it multiple times. Received on Fri Jul 06 2007 - 12:23:23 CDT