valigula 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-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
Perhaps someone with 9i can verify it but I don't recall the WITH
clause existing in 9i.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Jul 12 2007 - 10:58:41 CDT
