Re: Using a Select as a Var??

From: valigula <valigula_at_gmail.com>
Date: Thu, 12 Jul 2007 05:36:07 -0700
Message-ID: <1184243767.529489.182260@o61g2000hsh.googlegroups.com>

On 6 jul, 02:02, DA Morgan <damor..._at_psoug.org> wrote:
> Chris L. 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
>
> given that you didn't post your SQL there's not much anyone can say
> other than 9.2.0.4? Applying a patch and getting closer to a supported
> product might make a difference.
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

select 1
/* Formatted on 2007/07/12 14:26 (Formatter Plus v4.8.7) */ SELECT h1.mov_matricula matricula, m.alm_codigo concesionario,

       tr.cod_cli_externo cliente, h1.mov_tipmov tipo_movimiento,
       TO_CHAR (h1.mov_fecmov, 'dd/mm/yyyy') fecha_movimiento,
       h1.mov_contrato contrato, h1.mov_tipmov tipo_baja
  FROM HI_CON_MOVIMIENTOS h1 INNER JOIN MA_CON_MATRICULAS m
       ON h1.mov_matricula = m.mat_codigo
	    AND h1.mov_tipmov = '04'
       LEFT JOIN TR_CON_MAPEO_CLIENTES tr
       ON (h1.mov_destino = m.alm_codigo)

 WHERE tr.cod_cli_externo <> 0

/* Formatted on 2007/07/12 14:28 (Formatter Plus v4.8.7) */ SELECT h1.mov_matricula matricula, m.alm_codigo concesionario,

       tr.cod_cli_externo cliente, h1.mov_tipmov tipo_movimiento,
       TO_CHAR (h1.mov_fecmov, 'dd/mm/yyyy') fecha_movimiento,
       h1.mov_contrato contrato, h1.mov_tipmov tipo_baja
  FROM HI_CON_MOVIMIENTOS h1 INNER JOIN MA_CON_MATRICULAS m
       ON h1.mov_matricula = m.mat_codigo
	    AND h1.mov_tipmov = '04'
       LEFT JOIN TR_CON_MAPEO_CLIENTES tr
       ON (h1.mov_destino = tr.cod_cli_interno)
 WHERE
       tr.cod_cli_externo = 0

As william said i can use a union but the tables are quiet big and the costing time is to big. that is why i was thinking on using the WITH. And then use the result of the select to do the union , that is make any sense??.
By the way I am using oracle 9i

Thanks Received on Thu Jul 12 2007 - 07:36:07 CDT