Re: Using a Select as a Var??

On Jul 12, 7:13 pm, valigula <valig..._at_gmail.com> wrote:
> On 12 jul, 19:08, "Chris L." <diver..._at_uol.com.ar> wrote:
>
>
>
>
>
> > On Jul 12, 9:36 am, valigula <valig..._at_gmail.com> wrote:
>
> > > 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-Ocultartextodelacita -
> > > > >>> - 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- Hide quoted text -
>
> > > - Show quoted text -
>
> > On your "Select 1" you say
>
> > LEFT JOIN TR_CON_MAPEO_CLIENTES tr
> > ON (h1.mov_destino = m.alm_codigo)
> > WHERE tr.cod_cli_externo <> 0
>
> > Is this correct? On what field is table TR joining with the rest?
> > Isn't this generating a cartesian product?
>
> > Please post the CREATE TABLE and CREATE INDEX sentences for the tables
> > involved, thank you- Ocultar texto de la cita -
>
> > - Mostrar texto de la cita -
>
> Sorry about the cartesian i eliminate a few where clauses so it can be
> more easy to read.
>
> I did use the WITH before (with 9i ..)but i dont manage to do it
> properly this time.- Hide quoted text -
>
> - Show quoted text -

An example using WITH is:

with t1 as (select * from sometable where a=1) select * from t1, t2
where t1.b = t2.c;

Good luck! Received on Fri Jul 13 2007 - 09:17:32 CDT