Re: OR Vs UNION
Title: Message
My view is...
You can either have
Select ..blah..blah..blah...where col =
'A'
OR col='B'
OR col='c'
also, you can wrtie..
Select...blah..blah..blahwhere
col='A'
union
select...blah..blah..blah...where
col='B'
union
select...blah..blah.blah..where
col='C'
Or better.
Select ...blah..blah..blahwhere col in
('A','B','C') ; (with proper index in 'col')
Its really upto you.
-Arun.
- Original Message -
From:
Ganesh
Raja
To: Multiple recipients of list ORACLE-L
Sent: Tuesday, June 24, 2003 11:25
AM
Subject: RE: OR Vs UNION
IMHO ...
Cannot be.. the Access Path Taken is Different.. "OR" is Transfered to
Inlist Iterator usually ...
Best Regards,Ganesh RDID : +65-6215-8413HP
: +65-9067-8474===Live to
learn... forget... and learn
again.===
-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Jamadagni, RajendraSent: Tuesday, June 24, 2003 11:10
PMTo: Multiple recipients of list ORACLE-LSubject: RE:
OR Vs UNION
I believe OR is internally transformed to UNION (or UNION
ALL) ??
The answer you are looking is ... test and measure it in
_your_ _environment_.
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly
personal. QOTD: Any clod can have facts, having an
opinion is an art !
>- --- Original Message --- -
>From: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent:
Mon, 23 Jun 2003 21:34:52 > > > >
> >Hi All, > >Could someone explain to me which
one is best in >the following two queries
>w.r.t performance? >
>Thanks >Sami
> >Query1) > >select distinct empployee_id
from employees where >department_id=10 or
>department_id=20 >/
> >Query2) >=== >select employee_id from
employees where >department_id=10
>union >select employee_id
from employees where >department_id=20
>/ >
Re: RE: OR Vs UNION
the algorithm for 'or' is simply a series of key value searches right that is short circuited right? oracle searches to see if the first value exists, if it does, stop, else look for the second value. then turns that into a resultset correct? > > From: "Ganesh Raja" <[EMAIL PROTECTED]> > Date: 2003/06/24 Tue PM 12:25:02 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: OR Vs UNION > > IMHO ... > > Cannot be.. the Access Path Taken is Different.. "OR" is Transfered to > Inlist Iterator usually ... > > Best Regards, > Ganesh R > DID : +65-6215-8413 > HP : +65-9067-8474 > === > Live to learn... forget... and learn again. > === > > > -Original Message- > Jamadagni, Rajendra > Sent: Tuesday, June 24, 2003 11:10 PM > To: Multiple recipients of list ORACLE-L > > > > I believe OR is internally transformed to UNION (or UNION ALL) ?? > > The answer you are looking is ... test and measure it in _your_ > _environment_. > > Raj > > > Rajendra dot Jamadagni at nospamespn dot com > All Views expressed in this email are strictly personal. > QOTD: Any clod can have facts, having an opinion is an art ! > > >- --- Original Message --- - > >From: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L > ><[EMAIL PROTECTED]> > >Sent: Mon, 23 Jun 2003 21:34:52 > > > > > > > > > > > >Hi All, > > > >Could someone explain to me which one is best in > >the following two queries > >w.r.t performance? > > > >Thanks > >Sami > > > >Query1) > > > >select distinct empployee_id from employees where > >department_id=10 or > >department_id=20 > >/ > > > >Query2) > >=== > >select employee_id from employees where > >department_id=10 > >union > >select employee_id from employees where > >department_id=20 > >/ > > > > > Title: Message IMHO ... Cannot be.. the Access Path Taken is Different.. "OR" is Transfered to Inlist Iterator usually ... Best Regards,Ganesh RDID : +65-6215-8413HP : +65-9067-8474===Live to learn... forget... and learn again.=== -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, June 24, 2003 11:10 PMTo: Multiple recipients of list ORACLE-LSubject: RE: OR Vs UNION I believe OR is internally transformed to UNION (or UNION ALL) ?? The answer you are looking is ... test and measure it in _your_ _environment_. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! >- --- Original Message --- - >From: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Mon, 23 Jun 2003 21:34:52 > > > > > >Hi All, > >Could someone explain to me which one is best in >the following two queries >w.r.t performance? > >Thanks >Sami > >Query1) > >select distinct empployee_id from employees where >department_id=10 or >department_id=20 >/ > >Query2) >=== >select employee_id from employees where >department_id=10 >union >select employee_id from employees where >department_id=20 >/ >
RE: OR Vs UNION
Title: Message IMHO ... Cannot be.. the Access Path Taken is Different.. "OR" is Transfered to Inlist Iterator usually ... Best Regards,Ganesh RDID : +65-6215-8413HP : +65-9067-8474===Live to learn... forget... and learn again.=== -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jamadagni, RajendraSent: Tuesday, June 24, 2003 11:10 PMTo: Multiple recipients of list ORACLE-LSubject: RE: OR Vs UNION I believe OR is internally transformed to UNION (or UNION ALL) ?? The answer you are looking is ... test and measure it in _your_ _environment_. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! >- --- Original Message --- - >From: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Mon, 23 Jun 2003 21:34:52 > > > > > >Hi All, > >Could someone explain to me which one is best in >the following two queries >w.r.t performance? > >Thanks >Sami > >Query1) > >select distinct empployee_id from employees where >department_id=10 or >department_id=20 >/ > >Query2) >=== >select employee_id from employees where >department_id=10 >union >select employee_id from employees where >department_id=20 >/ >
RE: OR Vs UNION
Title: RE: OR Vs UNION I believe OR is internally transformed to UNION (or UNION ALL) ?? The answer you are looking is ... test and measure it in _your_ _environment_. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! >- --- Original Message --- - >From: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Mon, 23 Jun 2003 21:34:52 > > > > > >Hi All, > >Could someone explain to me which one is best in >the following two queries >w.r.t performance? > >Thanks >Sami > >Query1) > >select distinct empployee_id from employees where >department_id=10 or >department_id=20 >/ > >Query2) >=== >select employee_id from employees where >department_id=10 >union >select employee_id from employees where >department_id=20 >/ > *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
RE: OR Vs UNION
SET TIMING ON SET AUTOTRACE TRACEONLY >- --- Original Message --- - >From: [EMAIL PROTECTED] >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Mon, 23 Jun 2003 21:34:52 > > > > > >Hi All, > >Could someone explain to me which one is best in >the following two queries >w.r.t performance? > >Thanks >Sami > >Query1) > >select distinct empployee_id from employees where >department_id=10 or >department_id=20 >/ > >Query2) >=== >select employee_id from employees where >department_id=10 >union >select employee_id from employees where >department_id=20 >/ > > >-- >Please see the official ORACLE-L FAQ: >http://www.orafaq.net >-- >Author: > INET: [EMAIL PROTECTED] > >Fat City Network Services-- 858-538-5051 >http://www.fatcity.com >San Diego, California-- Mailing list and >web hosting services >To REMOVE yourself from this mailing list, send an >E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of >'ListGuru') and in >the message BODY, include a line containing: UNSUB >ORACLE-L >(or the name of mailing list you want to be removed >from). You may >also send the HELP command for other information >(like subscribing). >--- >-- Regards, Stephane Faroult Oriole -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OR Vs UNION
In that simple example the OR is almost certainly faster... 1) UNION forces a sort operation to be performed (UNION ALL is better in this respect). 2) If the query is forced to perform a full table scan then the union option will perform two scans. There will be similar issues if a full index scan is performed. Of course a lot of data will be cached for the second pass but it must still be done. Therefore, in your simple example (not knowing table volumes, indexing strategies, etc) I would suggest the OR option. Having said that, there are examples in complex queries where a union may perform better (and it may be easier to write as well). Regards, Mark. Saminathan_Seeran [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Sent by: cc: [EMAIL PROTECTED]Subject: OR Vs UNION .com 24/06/2003 15:34 Please respond to ORACLE-L Hi All, Could someone explain to me which one is best in the following two queries w.r.t performance? Thanks Sami Query1) select distinct empployee_id from employees where department_id=10 or department_id=20 / Query2) === select employee_id from employees where department_id=10 union select employee_id from employees where department_id=20 / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). <<>> Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. <<<> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may
