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-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
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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Thu Jul 05 2007 - 19:02:11 CDT
