| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Using a Select as a Var??
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-Ocultartextode 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- 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 Received on Thu Jul 12 2007 - 12:08:09 CDT
 |
 |