Re: Using a Select as a Var??

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-Ocultar texto 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 Received on Thu Jul 05 2007 - 14:15:53 CDT