RE: UNION ALL Query: Riddle
I have the same problem like you Rajesh, the query also gives different rowcount each time executed eventhough there's no one updating base tables, in my opinion it's because of the sorting operation (your group by clause). In my case after I remove some group functions, the result goes well. Also I reduce the use of order by clause where it's not needed. I still haven't found the exact solution to this problem. But just now I've tried to decrease the sort area size parameter value (I think I oversize it), and run the query again, the result goes stable with the problematic query but it runs slower. I haven't tried intensively, I try to do that tomorrow. Meanwhile if, there's any of the Gurus can give us clearer explanation, please do so... Thank you all in advance. Regards, Wendry. -Original Message- Pillai, Rajesh Sent: Thursday, January 29, 2004 2:24 AM To: Multiple recipients of list ORACLE-L Hi Jared, Thanks for your response. different results mean that number of records are different sometimes, and sometimes the some of the quantities are not correct. Your help is really appreciated. Thanks, Rajesh -Original Message- Sent: Tuesday, January 27, 2004 2:29 PM To: Multiple recipients of list ORACLE-L Q: What does different results mean? Different row count? Completely different data? Partially different data? Some columns have incorrect value? What about doing it without the parallel hints? The tables aren't so big that it would take a long time to find out. Jared Pillai, Rajesh [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/27/2004 01:09 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: UNION ALL Query: Riddle Hi All, The following query is giving different results in each run. I assure that no data modified between consecutive runs - INSERT /* append parallel (z,8) */ INTO some_table (SELECT /*parallel (a,8) */ a.item, a.loc, SUM(a.qty_type_1), SUM(a.qty_type_2) FROM (select /*parallel (x,8) */ item, loc, qty_type_1, to_number(NULL) from table_a x UNION ALL select /*parallel (y,8) */ item, loc, to_number(NULL), qty_type_2 from table_b y ) a GROUP BY a.item, a.loc); Additional info - Number of records in table_a and table_b is around 3M and 6M. SQL select * from v$version; BANNER Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production PL/SQL Release 8.1.7.2.0 - Production CORE 8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I would appreciate any help in solving this mystery and all hints are welcome. Thanks, Rajesh Pillai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wendry 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
RE: UNION ALL Query: Riddle
If what you are describing is completely accurate, ( no DML, change S_A_S fixes the problem ) then it would appear you have encountered a bug. A search on MetaLink is in order, and failing that, you need to open a TAR. Jared On Thu, 2004-01-29 at 04:59, Wendry wrote: I have the same problem like you Rajesh, the query also gives different rowcount each time executed eventhough there's no one updating base tables, in my opinion it's because of the sorting operation (your group by clause). In my case after I remove some group functions, the result goes well. Also I reduce the use of order by clause where it's not needed. I still haven't found the exact solution to this problem. But just now I've tried to decrease the sort area size parameter value (I think I oversize it), and run the query again, the result goes stable with the problematic query but it runs slower. I haven't tried intensively, I try to do that tomorrow. Meanwhile if, there's any of the Gurus can give us clearer explanation, please do so... Thank you all in advance. Regards, Wendry. -Original Message- Pillai, Rajesh Sent: Thursday, January 29, 2004 2:24 AM To: Multiple recipients of list ORACLE-L Hi Jared, Thanks for your response. different results mean that number of records are different sometimes, and sometimes the some of the quantities are not correct. Your help is really appreciated. Thanks, Rajesh -Original Message- Sent: Tuesday, January 27, 2004 2:29 PM To: Multiple recipients of list ORACLE-L Q: What does different results mean? Different row count? Completely different data? Partially different data? Some columns have incorrect value? What about doing it without the parallel hints? The tables aren't so big that it would take a long time to find out. Jared Pillai, Rajesh [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/27/2004 01:09 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:UNION ALL Query: Riddle Hi All, The following query is giving different results in each run. I assure that no data modified between consecutive runs - INSERT /* append parallel (z,8) */ INTO some_table (SELECT /*parallel (a,8) */ a.item, a.loc, SUM(a.qty_type_1), SUM(a.qty_type_2) FROM (select /*parallel (x,8) */ item, loc, qty_type_1, to_number(NULL) from table_a x UNION ALL select /*parallel (y,8) */ item, loc, to_number(NULL), qty_type_2 from table_b y ) a GROUP BY a.item, a.loc); Additional info - Number of records in table_a and table_b is around 3M and 6M. SQL select * from v$version; BANNER Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production PL/SQL Release 8.1.7.2.0 - Production CORE8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I would appreciate any help in solving this mystery and all hints are welcome. Thanks, Rajesh Pillai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City
RE: UNION ALL Query: Riddle
Hi
Jared,
Thanks for your response.
different results mean that number of records are different sometimes, and
sometimes the some of the quantities are not correct. Your help is really
appreciated.
Thanks,
Rajesh
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]Sent: Tuesday, January 27, 2004
2:29 PMTo: Multiple recipients of list ORACLE-LSubject:
Re: UNION ALL Query: RiddleQ: What does "different results" mean? Different row count? Completely different data? Partially different data? Some columns have incorrect value? What about doing it without the parallel hints?
The tables aren't so big that it
would take a long time to find out. Jared
"Pillai, Rajesh"
[EMAIL PROTECTED] Sent by: [EMAIL PROTECTED]
01/27/2004 01:09 PM
Please respond to ORACLE-L
To:
Multiple recipients of list ORACLE-L
[EMAIL PROTECTED] cc:
Subject:UNION ALL Query:
RiddleHi All,The
following query is giving different results in each run. I assure that no data
modified between consecutive runs -
INSERT /* append parallel (z,8) */
INTO some_table
(SELECT /*parallel (a,8)
*/
a.item,
a.loc,
SUM(a.qty_type_1),
SUM(a.qty_type_2)
FROM
(select
/*parallel (x,8) */
item,
loc,
qty_type_1,
to_number(NULL)
from
table_a x
UNION ALL
select /*parallel (y,8)
*/
item,
loc,
to_number(NULL),
qty_type_2
from
table_b y
) a
GROUP BY
a.item,
a.loc);Additional info - Number of records in table_a
and table_b is around 3M and 6M.SQL select * from
v$version;BANNEROracle8i
Enterprise Edition Release 8.1.7.2.0 - ProductionPL/SQL Release 8.1.7.2.0
- ProductionCORE 8.1.7.0.0
ProductionTNS for Solaris: Version 8.1.7.2.0 - ProductionNLSRTL
Version 3.4.1.0.0 - ProductionI would appreciate any help in solving
this mystery and all hints are welcome.Thanks,Rajesh
Pillai-- Please see the official ORACLE-L FAQ:
http://www.orafaq.net-- Author: Pillai, RajeshINET:
[EMAIL PROTECTED]Fat City Network Services --
858-538-5051 http://www.fatcity.comSan Diego, California
-- Mailing list and web hosting
services-To
REMOVE yourself from this mailing list, send an E-Mail messageto:
[EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message
BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing
list you want to be removed from). You mayalso send the HELP command
for other information (like
subscribing).
RE: UNION ALL Query: Riddle
It would be my guess that someone was doing DML on your table while you're running the first query, and you don't see the results of that until the second query. Try running your SQL statement twice in a single transaction and see if the results are the same then. eg. rollback; set transaction read only; run SQL once here run it a second time here The results should be the same. Or, you could get the old ORA-1555, if a number of changes have been made and your rollback segments can't keep up. Jared On Wed, 2004-01-28 at 11:24, Pillai, Rajesh wrote: Hi Jared, Thanks for your response. different results mean that number of records are different sometimes, and sometimes the some of the quantities are not correct. Your help is really appreciated. Thanks, Rajesh -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 27, 2004 2:29 PM To: Multiple recipients of list ORACLE-L Subject: Re: UNION ALL Query: Riddle Q: What does different results mean? Different row count? Completely different data? Partially different data? Some columns have incorrect value? What about doing it without the parallel hints? The tables aren't so big that it would take a long time to find out. Jared Pillai, Rajesh [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/27/2004 01:09 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: UNION ALL Query: Riddle Hi All, The following query is giving different results in each run. I assure that no data modified between consecutive runs - INSERT /* append parallel (z,8) */ INTO some_table (SELECT /*parallel (a,8) */ a.item, a.loc, SUM(a.qty_type_1), SUM(a.qty_type_2) FROM (select /*parallel (x,8) */ item, loc, qty_type_1, to_number(NULL) from table_a x UNION ALL select /*parallel (y,8) */ item, loc, to_number(NULL), qty_type_2 from table_b y ) a GROUP BY a.item, a.loc); Additional info - Number of records in table_a and table_b is around 3M and 6M. SQL select * from v$version; BANNER Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production PL/SQL Release 8.1.7.2.0 - Production CORE8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I would appreciate any help in solving this mystery and all hints are welcome. Thanks, Rajesh Pillai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh 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
UNION ALL Query: Riddle
Hi All, The following query is giving different results in each run. I assure that no data modified between consecutive runs - INSERT /* append parallel (z,8) */ INTO some_table (SELECT /*parallel (a,8) */ a.item, a.loc, SUM(a.qty_type_1), SUM(a.qty_type_2) FROM (select /*parallel (x,8) */ item, loc, qty_type_1, to_number(NULL) from table_a x UNION ALL select /*parallel (y,8) */ item, loc, to_number(NULL), qty_type_2 from table_b y ) a GROUP BY a.item, a.loc); Additional info - Number of records in table_a and table_b is around 3M and 6M. SQL select * from v$version; BANNER Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production PL/SQL Release 8.1.7.2.0 - Production CORE8.1.7.0.0 Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I would appreciate any help in solving this mystery and all hints are welcome. Thanks, Rajesh Pillai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh 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: UNION ALL Query: Riddle
Q: What does different results mean? Different row count? Completely different data? Partially different data? Some columns have incorrect value? What about doing it without the parallel hints? The tables aren't so big that it would take a long time to find out. Jared Pillai, Rajesh [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 01/27/2004 01:09 PM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:UNION ALL Query: Riddle Hi All, The following query is giving different results in each run. I assure that no data modified between consecutive runs - INSERT /* append parallel (z,8) */ INTO some_table (SELECT /*parallel (a,8) */ a.item, a.loc, SUM(a.qty_type_1), SUM(a.qty_type_2) FROM (select /*parallel (x,8) */ item, loc, qty_type_1, to_number(NULL) from table_a x UNION ALL select /*parallel (y,8) */ item, loc, to_number(NULL), qty_type_2 from table_b y ) a GROUP BY a.item, a.loc); Additional info - Number of records in table_a and table_b is around 3M and 6M. SQL select * from v$version; BANNER Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production PL/SQL Release 8.1.7.2.0 - Production CORE 8.1.7.0.0Production TNS for Solaris: Version 8.1.7.2.0 - Production NLSRTL Version 3.4.1.0.0 - Production I would appreciate any help in solving this mystery and all hints are welcome. Thanks, Rajesh Pillai -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pillai, Rajesh 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).
rewriting query without using UNION
Hi, I wonder if there's a better way of writing the query below. Basically, I would like to return employee records where employee name='JOSE' + all employees in deptno=50. My query can have multiple 'OR' criterias where the next criteria maybe returning all employees with salary6 in addition to the above two criterias. Building the query with multiple UNIONs will definitely degrade the query performance. Is there a better way of rewriting the query? Thanks! linda select e.id, e.name, d.deptname from emp e, dept d where e.deptno=d.deptno and e.name='JOSE' union select e.id, e.name, d.deptname from emp e, dept d where e.deptno=d.deptno and d.deptno=50; _ Want to check if your PC is virus-infected? Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Linda Wang 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: rewriting query without using UNION
I don't see why would query with multiple unions necessarily degrade performance, but here is another way for writing your query: select e.id, e.name, d.deptname from emp e, dept d where e.deptno=d.deptno and ( e.name='JOSE' or d.deptno=50) / That would be a union of all employees from the department with deptno=50 plus the ones called JOSE. On 10/29/2003 12:54:26 PM, Linda Wang wrote: Hi, I wonder if there's a better way of writing the query below. Basically, I would like to return employee records where employee name='JOSE' + all employees in deptno=50. My query can have multiple 'OR' criterias where the next criteria maybe returning all employees with salary6 in addition to the above two criterias. Building the query with multiple UNIONs will definitely degrade the query performance. Is there a better way of rewriting the query? Thanks! linda select e.id, e.name, d.deptname from emp e, dept d where e.deptno=d.deptno and e.name='JOSE' union select e.id, e.name, d.deptname from emp e, dept d where e.deptno=d.deptno and d.deptno=50; _ Want to check if your PC is virus-infected? Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Linda Wang 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). Mladen Gogala Oracle DBA Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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: rewriting query without using UNION
from basic set theory: union = OR intersect = AND select e.id, e.name, d.deptname from emp e, dept d where e.deptno=d.deptno and e.name='JOSE' or d.deptno = 50; From: Linda Wang [EMAIL PROTECTED] Date: 2003/10/29 Wed PM 12:54:26 EST To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: rewriting query without using UNION Hi, I wonder if there's a better way of writing the query below. Basically, I would like to return employee records where employee name='JOSE' + all employees in deptno=50. My query can have multiple 'OR' criterias where the next criteria maybe returning all employees with salary6 in addition to the above two criterias. Building the query with multiple UNIONs will definitely degrade the query performance. Is there a better way of rewriting the query? Thanks! linda select e.id, e.name, d.deptname from emp e, dept d where e.deptno=d.deptno and e.name='JOSE' union select e.id, e.name, d.deptname from emp e, dept d where e.deptno=d.deptno and d.deptno=50; _ Want to check if your PC is virus-infected? Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Linda Wang 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: rewriting query without using UNION
select e.ID, e.NAME, d.DEPTNAME from EMP e, DEPT d where e.DEPTNO = d.DEPTNO and (e.NAME = 'JOSE' or d.DEPTNO = 50); -Original Message- Sent: Wednesday, October 29, 2003 12:54 PM To: Multiple recipients of list ORACLE-L Hi, I wonder if there's a better way of writing the query below. Basically, I would like to return employee records where employee name='JOSE' + all employees in deptno=50. My query can have multiple 'OR' criterias where the next criteria maybe returning all employees with salary6 in addition to the above two criterias. Building the query with multiple UNIONs will definitely degrade the query performance. Is there a better way of rewriting the query? Thanks! linda select e.id, e.name, d.deptname from emp e, dept d where e.deptno=d.deptno and e.name='JOSE' union select e.id, e.name, d.deptname from emp e, dept d where e.deptno=d.deptno and d.deptno=50; _ Want to check if your PC is virus-infected? Get a FREE computer virus scan online from McAfee. http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Linda Wang 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rudy Zung 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: Another UNION question
Hi! AFAIK, Rule Based Optimizer always converts ORs to Union alls (except when doing an outer join or connect by query). That's called OR expansion. CBO seems to prefer inlist iterators: SQL create table t as select * from sys.obj$; Table created. SQL select * from t where obj# = 1 or obj# = 2 or obj# = 3 or obj# = 4 or obj# = 5 or obj# = 6 or obj# = 7; Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 10 TABLE ACCESS (FULL) OF 'T' SQL create index i on t(obj#); Index created. SQL select * from t where obj# = 1 or obj# = 2 or obj# = 3 or obj# = 4 or obj# = 5 or obj# = 6 or obj# = 7; Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE 10 CONCATENATION 21 TABLE ACCESS (BY INDEX ROWID) OF 'T' 32 INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE) 41 TABLE ACCESS (BY INDEX ROWID) OF 'T' 54 INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE) 61 TABLE ACCESS (BY INDEX ROWID) OF 'T' 76 INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE) 81 TABLE ACCESS (BY INDEX ROWID) OF 'T' 98 INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE) 101 TABLE ACCESS (BY INDEX ROWID) OF 'T' 11 10 INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE) 121 TABLE ACCESS (BY INDEX ROWID) OF 'T' 13 12 INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE) 141 TABLE ACCESS (BY INDEX ROWID) OF 'T' 15 14 INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE) SQL analyze table t compute statistics; Table analyzed. SQL select * from t where obj# = 1 or obj# = 2 or obj# = 3 or obj# = 4 or obj# = 5 or obj# = 6 or obj# = 7; Execution Plan -- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=7 Bytes=581) 10 INLIST ITERATOR 21 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=7 Byte s=581) 32 INDEX (RANGE SCAN) OF 'I' (NON-UNIQUE) (Cost=2 Card=7) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, August 01, 2003 3:54 AM I'm getting back to work on my union article, and I have yet another union question. Are there ever cases where a UNION might be used for performance reasons? For example, I could write: SELECT * FROM emp WHERE emp_type='HOURLY' OR emp_type='CONTRACT'; or I could write: SELECT * FROM emp WHERE emp_type='HOURLY' UNION SELECT * FROM emp WHERE emp_type='CONTRACT'; This is probably too simple of an example, but are there ever cases where using a UNION like this makes sense from a performance point-of-view? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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: union all problems
Yes, this is a trace file that
contains
ORA-07445: exception encountered: core
dump...
- Original Message -
From:
Jamadagni, Rajendra
To: Multiple recipients of list ORACLE-L
Sent: Thursday, July 31, 2003 3:39
PM
Subject: RE: "union all" problems
What does the trace file says on the server ??? do you see a ora-7445
trace file?
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: Ed Lewis
[mailto:[EMAIL PROTECTED]Sent: Thursday, July 31, 2003 2:59
PMTo: Multiple recipients of list ORACLE-LSubject:
"union all" problems
Hi,
We have a query which
uses
a "union all". After upgrading
to
a patch release of Oracle this
query
no longer works. We get the
following error :
ERROR at line 1:ORA-03113: end-of-file
on communication channel
ORA-24323: value not allowedError accessing package
DBMS_APPLICATION_INFOERROR:ORA-03114: not connected to ORACLE
I've searched metalink, but have been unsuccessful
finding a solution.
As a quick fix, we changed the "union all" to a "union",
and that worked.
We'll still like to find the root cause though.
The environment is AIX 4.3.3. It worked with
Oracle 8.1.7.2, but after upgrading to 8.1.7.4
we get this error.
Has anyone experienced this ? thanks.
ed
union all problems
Hi, We have a query which uses a "union all". After upgrading to a patch release of Oracle this query no longer works. We get the following error : ERROR at line 1:ORA-03113: end-of-file on communication channel ORA-24323: value not allowedError accessing package DBMS_APPLICATION_INFOERROR:ORA-03114: not connected to ORACLE I've searched metalink, but have been unsuccessful finding a solution. As a quick fix, we changed the "union all" to a "union", and that worked. We'll still like to find the root cause though. The environment is AIX 4.3.3. It worked with Oracle 8.1.7.2, but after upgrading to 8.1.7.4 we get this error. Has anyone experienced this ? thanks. ed
Re: union all problems
Hi! I haven't seen this issue before, but it seems like a bug. Check for .trc files in your user_dump_dest. The contents are probably quite cryptic, but you can send it to Support through Metalink. Tanel. - Original Message - From: Ed Lewis To: Multiple recipients of list ORACLE-L Sent: Thursday, July 31, 2003 9:59 PM Subject: "union all" problems Hi, We have a query which uses a "union all". After upgrading to a patch release of Oracle this query no longer works. We get the following error : ERROR at line 1:ORA-03113: end-of-file on communication channel ORA-24323: value not allowedError accessing package DBMS_APPLICATION_INFOERROR:ORA-03114: not connected to ORACLE I've searched metalink, but have been unsuccessful finding a solution. As a quick fix, we changed the "union all" to a "union", and that worked. We'll still like to find the root cause though. The environment is AIX 4.3.3. It worked with Oracle 8.1.7.2, but after upgrading to 8.1.7.4 we get this error. Has anyone experienced this ? thanks. ed
RE: union all problems
What does the trace file says on the server ??? do you see a ora-7445 trace file? 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: Ed Lewis [mailto:[EMAIL PROTECTED]Sent: Thursday, July 31, 2003 2:59 PMTo: Multiple recipients of list ORACLE-LSubject: "union all" problems Hi, We have a query which uses a "union all". After upgrading to a patch release of Oracle this query no longer works. We get the following error : ERROR at line 1:ORA-03113: end-of-file on communication channel ORA-24323: value not allowedError accessing package DBMS_APPLICATION_INFOERROR:ORA-03114: not connected to ORACLE I've searched metalink, but have been unsuccessful finding a solution. As a quick fix, we changed the "union all" to a "union", and that worked. We'll still like to find the root cause though. The environment is AIX 4.3.3. It worked with Oracle 8.1.7.2, but after upgrading to 8.1.7.4 we get this error. Has anyone experienced this ? thanks. ed *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
Another UNION question
I'm getting back to work on my union article, and I have yet another union question. Are there ever cases where a UNION might be used for performance reasons? For example, I could write: SELECT * FROM emp WHERE emp_type='HOURLY' OR emp_type='CONTRACT'; or I could write: SELECT * FROM emp WHERE emp_type='HOURLY' UNION SELECT * FROM emp WHERE emp_type='CONTRACT'; This is probably too simple of an example, but are there ever cases where using a UNION like this makes sense from a performance point-of-view? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: Another UNION question
I've used a UNION where I wanted MIN and MAX from an indexed column: select max(dspnd_date), min(dspnd_date) from dwcorp.t_claim partition (p_200206) SELECT STATEMENT Hint=CHOOSE SORT AGGREGATE BITMAP CONVERSION TO ROWIDS BITMAP INDEX FULL SCANX_CLAIM_N11 Note the bitmap index full scan in the above query, it took 13 seconds, around 35 million rows in the partition and 30 distinct values and an even distribution on dpsnd_date. Compare with the bitmap index single value approach below: select max(dspnd_date) from dwcorp.t_claim partition (p_200206) UNION ALL select min(dspnd_date) from dwcorp.t_claim partition (p_200206) SELECT STATEMENT Hint=CHOOSE SORT UNIQUE UNION-ALL SORT AGGREGATE BITMAP INDEX SINGLE VALUE X_CLAIM_N11 SORT AGGREGATE BITMAP INDEX SINGLE VALUE X_CLAIM_N11 This ran in 335 ms (as opposed to 13 seconds). And of course I could wrapper the second one in an inline view and use a decode or case trickery to bring onto a single line. And I know there are other cases, but I can't think of them off the top of my head. But the fact that the CBO will sometimes expand an OR into multiple UNIONED statements makes you think there are cases. The above was with a BMI. On 8.1.7 and a b-tree, you will see something like INDEX FULL SCAN (MIN/MAX) which really seems to operate using an asc or desc index scan and a stop key when using the UNION approach or an index (fast) full scan when doing both min and max in one statement. Once again the UNION ALL did it faster. Regards, Larry G. Elkins The Elkins Organization Inc. [EMAIL PROTECTED] 214.954.1781 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Jonathan Gennick Sent: Thursday, July 31, 2003 7:54 PM To: Multiple recipients of list ORACLE-L Subject: Another UNION question I'm getting back to work on my union article, and I have yet another union question. Are there ever cases where a UNION might be used for performance reasons? For example, I could write: SELECT * FROM emp WHERE emp_type='HOURLY' OR emp_type='CONTRACT'; or I could write: SELECT * FROM emp WHERE emp_type='HOURLY' UNION SELECT * FROM emp WHERE emp_type='CONTRACT'; This is probably too simple of an example, but are there ever cases where using a UNION like this makes sense from a performance point-of-view? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins 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: Another UNION question
If the union performed index lookups, but the table example performed a full table scan it might. Of course with iterative index usage the point is deprecated. However before they were introduced the first query would perform an FTS even if emp_type was the primary key. Even with iterative index usage the union statement is often faster. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Thursday, July 31, 2003 5:54 PM To: Multiple recipients of list ORACLE-L I'm getting back to work on my union article, and I have yet another union question. Are there ever cases where a UNION might be used for performance reasons? For example, I could write: SELECT * FROM emp WHERE emp_type='HOURLY' OR emp_type='CONTRACT'; or I could write: SELECT * FROM emp WHERE emp_type='HOURLY' UNION SELECT * FROM emp WHERE emp_type='CONTRACT'; This is probably too simple of an example, but are there ever cases where using a UNION like this makes sense from a performance point-of-view? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: MacGregor, Ian A. 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: Union quries: INTERSECT, MINUS, etc
not very slick but I used MINUS yesterday to find parents with no children so as to purge them we do this a lot in this 3rd party app. RI is sketchy at best and the app blows up if childless parents exists --- [EMAIL PROTECTED] wrote: Jonathan, I've used MINUS heavily in sql scripts and pl/sql to determine the differences in schemas: both structure and data. Of interest to DBA's and developers, and least when I did it it was for the developers. Jared On Thu, 24 Jul 2003, Jonathan Gennick wrote: I'm doing research for an article on union queries. I'm interested in finding examples of problems that were solved using UNION, UNION ALL, INTERSECT, or MINUS, with the latter two being of special interest because I don't see them used very often. If you can think of an interesting problem you've solved using one of these keywords, I'd love to hear about it. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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[2]: Union quries: INTERSECT, MINUS, etc
Friday, July 25, 2003, 6:39:35 AM, you wrote: RC not very slick but I used MINUS yesterday to find parents with no RC children so as to purge them Offhand, I'd think you could do this without using MINUS. Maybe I'm wrong. But assuming there is a non-MINUS solution, what led you to choose to use MINUS? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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[2]: Union quries: INTERSECT, MINUS, etc
Friday, July 25, 2003, 6:39:35 AM, Rachel wrote: RC not very slick but I used MINUS yesterday to find parents with no RC children so as to purge them Rachel, you make me very glad I have two kids. Please don't purge me, ok? grin Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: Re[2]: Union quries: INTERSECT, MINUS, etc
I'd purge myself first 'cause I don't have kids. Like the bad joke After I kill myself I'm turning the gun on you --- Jonathan Gennick [EMAIL PROTECTED] wrote: Friday, July 25, 2003, 6:39:35 AM, Rachel wrote: RC not very slick but I used MINUS yesterday to find parents with no RC children so as to purge them Rachel, you make me very glad I have two kids. Please don't purge me, ok? grin Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Re[2]: Union quries: INTERSECT, MINUS, etc
where will you publish this? Id like to read your results. From: Jonathan Gennick [EMAIL PROTECTED] Date: 2003/07/25 Fri AM 10:04:25 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re[2]: Union quries: INTERSECT, MINUS, etc Friday, July 25, 2003, 6:39:35 AM, Rachel wrote: RC not very slick but I used MINUS yesterday to find parents with no RC children so as to purge them Rachel, you make me very glad I have two kids. Please don't purge me, ok? grin Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Re[2]: Union quries: INTERSECT, MINUS, etc
okay, I answered this offlist but... it started out as do we have a problem, indicated by records in the parent table with no children select id from parent minus select parentid from child that identified that we had a problem. next step (I'm a paranoid DBA when it comes to permanently deleting data from production) create holding_table as select * from parent where id in (select id from parent minus select parentid from child) last step delete from parent where id in select id from holding_table elegant? no. Fast? yes. And when I'm doing this 10 minutes before I'm supposed to leave for the day, fast is what I want :) I'm a BIG believer in plain vanilla coding. Slick is fine, but if I have to spend too much more time than the slick query saves me in creating it, it's not worth it. This is a one-off. --- Jonathan Gennick [EMAIL PROTECTED] wrote: Friday, July 25, 2003, 6:39:35 AM, you wrote: RC not very slick but I used MINUS yesterday to find parents with no RC children so as to purge them Offhand, I'd think you could do this without using MINUS. Maybe I'm wrong. But assuming there is a non-MINUS solution, what led you to choose to use MINUS? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Re[2]: Union quries: INTERSECT, MINUS, etc
Love it classic execution of KISS, no bull it just works and quick and efficient Waiting for my plane home from Perth Have a good weekend as its Friday night here Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = If people did not sometimes do silly things, nothing intelligent would ever get done. - Ludwig Wittgenstein = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. Rachel Carmichael [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 26-07-2003 12:24 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: Re[2]: Union quries: INTERSECT, MINUS, etc okay, I answered this offlist but... it started out as do we have a problem, indicated by records in the parent table with no children select id from parent minus select parentid from child that identified that we had a problem. next step (I'm a paranoid DBA when it comes to permanently deleting data from production) create holding_table as select * from parent where id in (select id from parent minus select parentid from child) last step delete from parent where id in select id from holding_table elegant? no. Fast? yes. And when I'm doing this 10 minutes before I'm supposed to leave for the day, fast is what I want :) I'm a BIG believer in plain vanilla coding. Slick is fine, but if I have to spend too much more time than the slick query saves me in creating it, it's not worth it. This is a one-off. --- Jonathan Gennick [EMAIL PROTECTED] wrote: Friday, July 25, 2003, 6:39:35 AM, you wrote: RC not very slick but I used MINUS yesterday to find parents with no RC children so as to purge them Offhand, I'd think you could do this without using MINUS. Maybe I'm wrong. But assuming there is a non-MINUS solution, what led you to choose to use MINUS? Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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). -- 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
RE: Union quries: INTERSECT, MINUS, etc
There's nothing like a good high colonic to make you want to have kids! -Original Message- not very slick but I used MINUS yesterday to find parents with no children so as to purge them -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: Union quries: INTERSECT, MINUS, etc
Jonathan, We use MINUS technique heavily in our DW environment to get the source system changes since we last extracted. We do 2-way minus (src to ods and ods to src). Best Regards, Prasad -- 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).
Union quries: INTERSECT, MINUS, etc
I'm doing research for an article on union queries. I'm interested in finding examples of problems that were solved using UNION, UNION ALL, INTERSECT, or MINUS, with the latter two being of special interest because I don't see them used very often. If you can think of an interesting problem you've solved using one of these keywords, I'd love to hear about it. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: Union quries: INTERSECT, MINUS, etc
- Original Message - I'm doing research for an article on union queries. I'm interested in finding examples of problems that were solved using UNION, UNION ALL, INTERSECT, or MINUS, with the latter two being of special interest because I don't see them used very often. If you can think of an interesting problem you've solved using one of these keywords, I'd love to hear about it. UNION is useful to implement arcs. INTERSECT I've used very successfully with two CONNECT BY queries to retrieve all possible paths of travel between two points A and B in a table that implements flight legs. So don't go around saying it isn't used: I need it or the RAAF can't book people to flights. ;) Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: Re: Union quries: INTERSECT, MINUS, etc
what do you mean by 'arc'? From: Nuno Souto [EMAIL PROTECTED] Date: 2003/07/24 Thu AM 09:39:29 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: Union quries: INTERSECT, MINUS, etc - Original Message - I'm doing research for an article on union queries. I'm interested in finding examples of problems that were solved using UNION, UNION ALL, INTERSECT, or MINUS, with the latter two being of special interest because I don't see them used very often. If you can think of an interesting problem you've solved using one of these keywords, I'd love to hear about it. UNION is useful to implement arcs. INTERSECT I've used very successfully with two CONNECT BY queries to retrieve all possible paths of travel between two points A and B in a table that implements flight legs. So don't go around saying it isn't used: I need it or the RAAF can't book people to flights. ;) Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] 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: Union quries: INTERSECT, MINUS, etc
At a previous job, I used MINUS as part of a package to perform automated testing of transaction processing. Compared actual result set with expected result set via minus. IF rows returned then if failed and returned rows were written to error table for review. Worked well for what we needed it to do. David Phillips Support DBA Gasper Corp -Original Message- Sent: Thursday, July 24, 2003 9:04 AM To: Multiple recipients of list ORACLE-L I'm doing research for an article on union queries. I'm interested in finding examples of problems that were solved using UNION, UNION ALL, INTERSECT, or MINUS, with the latter two being of special interest because I don't see them used very often. If you can think of an interesting problem you've solved using one of these keywords, I'd love to hear about it. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Dave Phillips 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[2]: Union quries: INTERSECT, MINUS, etc
Thursday, July 24, 2003, 10:09:25 AM, Dave wrote: DP Compared actual result set DP with expected result set via minus. I've done that too. I need to search the cobwebs of my memory a bit, but I recall having use MINUS both ways to be sure: results MINUS expected_results tells you whether the real results included any rows that are unexpected expected_results MINUS results tells you whether results omitted any expected rows I seem to recall once having to use GROUP BY and COUNT to ensure that the *right quantity* of each row was in the result set. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick 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: Re: Union quries: INTERSECT, MINUS, etc
- Original Message - what do you mean by 'arc'? have a look: http://www.docm.mmu.ac.uk/online/SAD/T07/erd2.htm much better explanation than I can give here. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: Union quries: INTERSECT, MINUS, etc
Jonathon, I've got a query for you that uses all 3 set operators at once! I wrote it to compare two different versions of our 3rd Party Student Information System (SASI) in two different databases. We were getting ready to upgrade Production, having already upgraded a Test instance. The query hit the local schema, as well as the remote schema across a DB Link. The results of this query and a couple of others that showed brand-new tables/columns and dropped tables/columns helped our programmers figure out which of their reports, etc. needed modifications. I was impressed at performance, considering it queried across a DB Link, but mainly because this horrendous mess of an application has over 50,000 tables (User_Tab_Columns has over 1.4 million rows!). One of the DBs is on HP-UX, but the Production DB is on Win2k. Anyway, hope this is interesting enough. ;-) BTW, if you can find a way to improve it, please let me know. I sort of threw it together, knowing it would be a one-time thing, so it could probably be made better with some expert critique. /* Get a list of columns that have changed from SASI 4.5 to 5.0 for tables that are present in both versions only for the current school year. List only the first 4 characters of the table names, since all campuses will be the same. */ Spool SASI_45_50_Table_Compare.txt ( Select Substr(TABLE_NAME,1,4) Table -- New 5.0 Columns ,COLUMN_NAME Column ,'5.0'Ver ,DATA_TYPEDType ,DATA_LENGTH DLn ,DATA_PRECISION DPr ,DATA_SCALE DSc ,NULLABLE N? From User_Tab_Columns Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) 'D' AndTable_Name In ( Select Table_Name --...for Tables in both 4.5 and 5.0 From User_Tables Intersect Select Table_Name From [EMAIL PROTECTED] ) Minus --...remove unchanged columns ( Select Substr(TABLE_NAME,1,4) ,COLUMN_NAME ,'5.0' -- Constant allows Minus to work ,DATA_TYPE ,DATA_LENGTH ,DATA_PRECISION ,DATA_SCALE ,NULLABLE From User_Tab_Columns Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) 'D' Intersect Select Substr(TABLE_NAME,1,4) ,COLUMN_NAME ,'5.0' -- Constant allows Minus to work ,DATA_TYPE ,DATA_LENGTH ,DATA_PRECISION ,DATA_SCALE ,NULLABLE From [EMAIL PROTECTED] Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) 'D' ) ) Union ( Select Substr(TABLE_NAME,1,4) Table-- Old 4.5 columns... ,COLUMN_NAME Column ,'4.5'Ver ,DATA_TYPEDType ,DATA_LENGTH DLn ,DATA_PRECISION DPr ,DATA_SCALE DSc ,NULLABLE N? From [EMAIL PROTECTED] Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) 'D' AndTable_Name In ( Select Table_Name From User_Tables Intersect Select Table_Name From [EMAIL PROTECTED] ) Minus ( Select Substr(TABLE_NAME,1,4) ,COLUMN_NAME ,'4.5' ,DATA_TYPE ,DATA_LENGTH ,DATA_PRECISION ,DATA_SCALE ,NULLABLE From User_Tab_Columns Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) 'D' Intersect Select Substr(TABLE_NAME,1,4) ,COLUMN_NAME ,'4.5' ,DATA_TYPE ,DATA_LENGTH ,DATA_PRECISION ,DATA_SCALE ,NULLABLE From [EMAIL PROTECTED] Where SubStr(Table_Name,5,1) = '2' AndSubStr(Table_Name,6,1) 'D' ) ) / Spool Off Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] Jonathan Gennick [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: Union quries: INTERSECT, MINUS, etc [EMAIL PROTECTED] .com
Re: Union quries: INTERSECT, MINUS, etc
Jonathan, I can't think of any specific examples but the four operators all have their place: UNION - A quick way to merge result sets. If, for example, you have actual financial data in one table and budget financial data in another table and need to spool all data to a file then UNION is an easy way to merge the two tables into a single cursor. I guess a natural alternative would be a view - but this then breaks the statement up into two statements SELECT (with selection criteria) and VIEW (which will effectively hide the underlying tables from the main query) - making maintenance worse but could be useful if the tables are joined often. UNION ALL - More significant when you may be deliberately creating duplicate records and need to show both records or when you know that no duplicates will be created and can therefore save on a sort operation. My first example would be better implemented as union all since actual and budget being extracted as constants from each table ensures no overlap. INTERSECT - Can often be used in the same scenario's as WHERE EXISTS or IN but may allow more complex conditions to be compared. MINUS - Can often be used to implement complex WHERE NOT EXISTS or NOT IN. For example, a student table may hold number_of_enrolled_subjects and a studentsubject table may map students to subjects... If you need to return the students which have this attribute set incorrectly (ie: corrupt data) then a simple MINUS query can compare the attribute to the COUNT(*) from studentsubject. I think the important thing to remember is that all of these operations can normally be accomplished using different SQL syntax. The decision comes down to a couple of factors: 1) Maintenance - some ways of writing a query may represent the underlying logic much easier. MINUS, for example, can break a complex statement down into two simpler queries which may make their purpose easier to understand. UNION may negate the need for a view - which can be a good or bad thing depending on other factors. 2) Execution approach. Often the above operators are resolved using a sort - the volume of records in each side of the query and configuration of your database may make this desirable, or it may not. WHERE EXISTS, on the other hand will normally be resolved using nested loops or hash joins. With small recordsets (not necessarily the final resultset since two of these operators are effectively data filters) the approach probably doesn't matter, but as data volumes and performance demands increase the decision can be significant. Hopefully this has added some food for thought. Jonathan Gennick [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] .comcc: Sent by: Subject: Union quries: INTERSECT, MINUS, etc [EMAIL PROTECTED] .com 24/07/2003 23:04 Please respond to ORACLE-L I'm doing research for an article on union queries. I'm interested in finding examples of problems that were solved using UNION, UNION ALL, INTERSECT, or MINUS, with the latter two being of special interest because I don't see them used very often. If you can think of an interesting problem you've solved using one of these keywords, I'd love to hear about it. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698
Re: Union quries: INTERSECT, MINUS, etc
Jonathan, I've used MINUS heavily in sql scripts and pl/sql to determine the differences in schemas: both structure and data. Of interest to DBA's and developers, and least when I did it it was for the developers. Jared On Thu, 24 Jul 2003, Jonathan Gennick wrote: I'm doing research for an article on union queries. I'm interested in finding examples of problems that were solved using UNION, UNION ALL, INTERSECT, or MINUS, with the latter two being of special interest because I don't see them used very often. If you can think of an interesting problem you've solved using one of these keywords, I'd love to hear about it. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. -- 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).
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 also send the HELP command for other information (like subscribing).
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
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
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: 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 /
OR Vs UNION
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).
Updateable View with Select Union
Hi list
I need help on this. Trying to create a view with select
union all as part of it.
SQL @view01b
SQL create table t1 (ind1 varchar2(02) , rundate1 date)
2 /
Table created.
SQL create table t2 (ind2 varchar2(02) , rundate2 date)
2 /
Table created.
SQL insert into t1 values('T1' , sysdate - 100)
2 /
1 row created.
SQL insert into t2 values('T2' , sysdate)
2 /
1 row created.
SQL create view v1 as
2 select ind1 , rundate1 from t1
3 union all
4 select ind2 , rundate2 from t2
5 with check option
6 /
View created.
SQL select * from v1
2 /
IN RUNDATE1
-- -
T1 10-DEC-02
T2 20-MAR-03
SQL desc v1
Name Null?Type
-
IND1
VARCHAR2(2)
RUNDATE1 DATE
SQL /
IN RUNDATE1
-- -
T1 10-DEC-02
T2 20-MAR-03
SQL insert into v1 values ('T3' , sysdate + 10)
2 /
insert into v1 values ('T3' , sysdate + 10)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this
view
SQL set echo off
Any ideas?
TIA
__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Govindan=20K?=
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: Updateable View with Select Union
First thought -
which table would you want the new data to go into ?
If you really need to implement this type
of thing check instead of triggers.
Be warned, though, that this is okay for
small inserts- but if you are planning to
do batch/array inserts, any trigger turns
a fast array process into a slow single-row
process.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
For one-day tutorials:
(see http://www.jlcomp.demon.co.uk/tutorial.html )
UK___April 8th
UK___April 22nd
Denmark May 21-23rd
USA_(FL)_May 2nd
The three-day seminar:
(see http://www.jlcomp.demon.co.uk/seminar.html )
UK_(Manchester)_May
USA_(CA, TX)_August
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: 20 March 2003 23:08
Hi list
I need help on this. Trying to create a view with select
union all as part of it.
SQL @view01b
SQL create table t1 (ind1 varchar2(02) , rundate1 date)
2 /
Table created.
SQL create table t2 (ind2 varchar2(02) , rundate2 date)
2 /
Table created.
SQL insert into t1 values('T1' , sysdate - 100)
2 /
1 row created.
SQL insert into t2 values('T2' , sysdate)
2 /
1 row created.
SQL create view v1 as
2 select ind1 , rundate1 from t1
3 union all
4 select ind2 , rundate2 from t2
5 with check option
6 /
View created.
SQL select * from v1
2 /
IN RUNDATE1
-- -
T1 10-DEC-02
T2 20-MAR-03
SQL desc v1
Name Null?Type
-
IND1
VARCHAR2(2)
RUNDATE1 DATE
SQL /
IN RUNDATE1
-- -
T1 10-DEC-02
T2 20-MAR-03
SQL insert into v1 values ('T3' , sysdate + 10)
2 /
insert into v1 values ('T3' , sysdate + 10)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this
view
SQL set echo off
Any ideas?
TIA
__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: =?iso-8859-1?q?Govindan=20K?=
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
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).
SQL query without UNION clause
Hi all,
How do I forumlate the below query without using the UNION clause?
SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
from historie ,FAHRZEUG WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT AND
HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN('HU', 'AU')
UNION
SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+) AND H1.PRODUKT IN('DS', 'NFZ',
'BES', 'DekraSiegel', '70', '700202');
I can't use the union clause because I am using an Object-Relationship tool
called Cocobase which doesn't support the UNION clause. Any help in this
regard is very much appreciated.
Thanks and Regards,
Ranganath
WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this message
by anyone else is unauthorised. If you are not the intended recipient, any
disclosure, copying, or distribution of the message, or any action or
omission taken by you in reliance on it, is prohibited and may be unlawful.
Please immediately contact the sender if you have received this message in
error. Thank you.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Krishnaswamy, Ranganath
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: SQL query without UNION clause
You would be surprised to discover what you could do with OR and suitably placed
parentheses.
- Original Message -
From: Krishnaswamy, Ranganath
[EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Wed, 18 Dec 2002 23:53:44
Hi all,
How do I forumlate the below query without using
the UNION clause?
SELECT H1.OID
HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
from historie ,FAHRZEUG WHERE FAHRZEUG.OID =
HISTORIE.MYTECHOBJEKT AND
HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN('HU', 'AU')
UNION
SELECT H1.OID
HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+) AND
H1.PRODUKT IN('DS', 'NFZ',
'BES', 'DekraSiegel', '70', '700202');
I can't use the union clause because I am using an
Object-Relationship tool
called Cocobase which doesn't support the UNION
clause. Any help in this
regard is very much appreciated.
Thanks and Regards,
Ranganath
WARNING: The information in this message is
confidential and may be legally
privileged. It is intended solely for the
addressee. Access to this message
by anyone else is unauthorised. If you are not the
intended recipient, any
disclosure, copying, or distribution of the
message, or any action or
omission taken by you in reliance on it, is
prohibited and may be unlawful.
Please immediately contact the sender if you have
received this message in
error. Thank you.
--
Please see the official ORACLE-L FAQ:
http://www.orafaq.net
--
Author: Krishnaswamy, Ranganath
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 Faroul
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: SQL query without UNION clause
Try this,
SELECT DISTINCT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND
((TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
from historie ,FAHRZEUG WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT AND
HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN('HU', 'AU')) OR H1.PRODUKT IN('DS', 'NFZ',
'BES', 'DekraSiegel', '70', '700202'))
Regards
Naveen
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 1:24 PM
To: Multiple recipients of list ORACLE-L
Hi all,
How do I forumlate the below query without using the UNION clause?
SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
from historie ,FAHRZEUG WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT AND
HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN('HU', 'AU')
UNION
SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+) AND H1.PRODUKT IN('DS', 'NFZ',
'BES', 'DekraSiegel', '70', '700202');
I can't use the union clause because I am using an Object-Relationship tool
called Cocobase which doesn't support the UNION clause. Any help in this
regard is very much appreciated.
Thanks and Regards,
Ranganath
WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this message
by anyone else is unauthorised. If you are not the intended recipient, any
disclosure, copying, or distribution of the message, or any action or
omission taken by you in reliance on it, is prohibited and may be unlawful.
Please immediately contact the sender if you have received this message in
error. Thank you.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Krishnaswamy, Ranganath
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Naveen Nahata
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: SQL query without UNION clause
I think you can translate it as OR, but you have to use some DISTINCT on
output rows (because you use UNION and not UNION ALL).
Without DISTINCT it is like:
SELECT
H1.OID HISTORIEOID
,FAHRZEUG.AMTLICHESKENNZEICHEN
,FAHRZEUG.OID
,H1.PRODUKT
,H1.AUFTRAGSPOSITIONSNR
,H1.MYTECHOBJEKT
FROM FAHRZEUG, HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND ( (TO_DATE(H1.DATUMSTR,'-MM-DD') = (
select max(TO_DATE(H1.DATUMSTR,'-mm-dd')) from historie, FAHRZEUG
WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT
AND HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN ('HU', 'AU')
)
OR
H1.PRODUKT IN('DS', 'NFZ','BES', 'DekraSiegel', '70', '700202'))
;
And I think you can change
select max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
to
select TO_DATE(MAX(H1.DATUMSTR),'-MM-DD')
it can now use index on column H1.DATUMSTR (or create function based index)
JP
On Thursday 19 December 2002 08:53, you wrote:
Hi all,
How do I forumlate the below query without using the UNION clause?
SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+)
AND TO_DATE(H1.DATUMSTR,'-MM-DD') = (select
max(TO_DATE(H1.DATUMSTR,'-mm-dd'))
from historie ,FAHRZEUG WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT AND
HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT)
AND H1.PRODUKT IN('HU', 'AU')
UNION
SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN,
FAHRZEUG.OID,
H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT
FROM FAHRZEUG,HISTORIE H1
WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+) AND H1.PRODUKT IN('DS', 'NFZ',
'BES', 'DekraSiegel', '70', '700202');
I can't use the union clause because I am using an Object-Relationship tool
called Cocobase which doesn't support the UNION clause. Any help in this
regard is very much appreciated.
Thanks and Regards,
Ranganath
WARNING: The information in this message is confidential and may be legally
privileged. It is intended solely for the addressee. Access to this
message by anyone else is unauthorised. If you are not the intended
recipient, any disclosure, copying, or distribution of the message, or any
action or omission taken by you in reliance on it, is prohibited and may be
unlawful. Please immediately contact the sender if you have received this
message in error. Thank you.
--
Pruner Jan
[EMAIL PROTECTED]
http://jan.pruner.cz/
-
Only Robinson Crusoe had all his work done by Friday
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jan Pruner
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: Suppressing a blank line in a union
How about removing the skip3
jack
Fink, Dan
[EMAIL PROTECTED]To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
cc: (bcc: Jack van
Zanen/nlzanen1/External/MEY/NL)
Sent by: Subject: Suppressing a blank line in a
union
[EMAIL PROTECTED]
18-09-2002 02:13
Please respond to
ORACLE-L
I've got a nasty bit of sql using a union to provide a header line.
SQL*Plus likes to place a blank line between the output of the unions and I
want to get rid of it. I've done it before, but I have forgotten. I do
recall that we never found documentation on it and 'stumbled' across the
solution.
The sql is below
TIA,
Dan Fink
column session_header format a1000
column sort_col1 noprint
column sort_col2 noprint
column sort_col3 noprint
set linesize 1001 trimspool on trimout on
break on sort_col1 skip 3
select s.sid sort_col1,
1 sort_col2,
0 sort_col3,
'System ID = '||to_char(s.sid,'999')||chr(10)||chr(9)||
'Username/Schemaname= '||s.username||'/'||s.schemaname||chr(10)
||chr(9)||
'Status = '||s.status||chr(10)||chr(9)||
'Client info'||chr(10)||chr(9)||chr(9)||
'O/S user = '||s.osuser||chr(10)||chr(9)||chr(9)||
'Machine Name = '||s.machine||chr(10)||chr(9)||chr(9)||
'Terminal Name = '||s.terminal||chr(10)||chr(9)||
'dbServer info'||chr(10)||chr(9)||chr(9)||
'O/S Process Id = '||p.spid||chr(10)||chr(9)||chr(9)||
'O/S Username = '||p.username||chr(10)||chr(9)||chr(9)||
'Terminal Name = '||p.terminal||chr(10)||chr(9)||chr(9)||
'Program = '||p.program||chr(10)||chr(9)||chr(9)||
'Login Time = '||to_char(s.logon_time, '/MM/DD:hh24:mi:ss')
session_header
from v$session s,
v$process p
where s.type != 'BACKGROUND'
and s.paddr = p.addr
union
select e.sid sort_col1,
2 sort_col2,
2 sort_col3,
'Wait Event Information '||chr(10)||chr(9)||
rpad('Event',30)||'(Waits/Timeouts/Waited/Avg Wait/Max Wait)'
wait_header
from v$session_event e
where e.sid in (select s.sid
from v$session s
where s.type != 'BACKGROUND')
and e.event not like 'SQL*N%'
union
select e.sid sort_col1,
3 sort_col2,
e.total_waits sort_col3,
chr(9)||
rpad(to_char(e.event),30)||'('||
lpad(to_char(e.total_waits),05)||
lpad(to_char(e.total_timeouts),09)||
lpad(to_char(e.time_waited),07)||
lpad(to_char(e.average_wait),09)||
lpad(to_char(e.max_wait),09)||')' wait_info
from v$session_event e
where e.sid in (select s.sid
from v$session s
where s.type != 'BACKGROUND')
and e.event not like 'SQL*N%'
order by sort_col1 asc, sort_col2 asc, sort_col3 desc;
System ID = 57
Username/Schemaname= SCOTT/TIGER
Status = INACTIVE
Client info
O/S user = scott
Machine Name = tiger
Terminal Name = unknown
dbServer info
O/S Process Id = 26276
O/S Username = oracle
Terminal Name = UNKNOWN
Program = oracle@tiger2 (TNS V1-V3)
Login Time = 2002/09/17:21:49:10
Wait Event Information
Event (Waits/Timeouts/Waited/Avg Wait/Max
Wait)
I
want to get rid of this line.
db file sequential read ( 27990 180
0)
log file sync
RE: Suppressing a blank line in a union
Dan,
I think set recsep off might do the trick as because that particular record
wraps (because of the chr(10)) it inserts the blank line as default
behaviour.
This would cause you to lose the desired blank lines between the other
records though unless you added an extra chr(10) at the end. The other
alternative would be to split it into two selects.
Iain Nicoll
-Original Message-
Sent: Wednesday, September 18, 2002 1:13 AM
To: Multiple recipients of list ORACLE-L
I've got a nasty bit of sql using a union to provide a header line. SQL*Plus
likes to place a blank line between the output of the unions and I want to
get rid of it. I've done it before, but I have forgotten. I do recall that
we never found documentation on it and 'stumbled' across the solution.
The sql is below
TIA,
Dan Fink
column session_header format a1000
column sort_col1 noprint
column sort_col2 noprint
column sort_col3 noprint
set linesize 1001 trimspool on trimout on
break on sort_col1 skip 3
select s.sid sort_col1,
1 sort_col2,
0 sort_col3,
'System ID = '||to_char(s.sid,'999')||chr(10)||chr(9)||
'Username/Schemaname=
'||s.username||'/'||s.schemaname||chr(10)||chr(9)||
'Status = '||s.status||chr(10)||chr(9)||
'Client info'||chr(10)||chr(9)||chr(9)||
'O/S user = '||s.osuser||chr(10)||chr(9)||chr(9)||
'Machine Name = '||s.machine||chr(10)||chr(9)||chr(9)||
'Terminal Name = '||s.terminal||chr(10)||chr(9)||
'dbServer info'||chr(10)||chr(9)||chr(9)||
'O/S Process Id = '||p.spid||chr(10)||chr(9)||chr(9)||
'O/S Username = '||p.username||chr(10)||chr(9)||chr(9)||
'Terminal Name = '||p.terminal||chr(10)||chr(9)||chr(9)||
'Program = '||p.program||chr(10)||chr(9)||chr(9)||
'Login Time = '||to_char(s.logon_time, '/MM/DD:hh24:mi:ss')
session_header
from v$session s,
v$process p
where s.type != 'BACKGROUND'
and s.paddr = p.addr
union
select e.sid sort_col1,
2 sort_col2,
2 sort_col3,
'Wait Event Information '||chr(10)||chr(9)||
rpad('Event',30)||'(Waits/Timeouts/Waited/Avg Wait/Max Wait)'
wait_header
from v$session_event e
where e.sid in (select s.sid
from v$session s
where s.type != 'BACKGROUND')
and e.event not like 'SQL*N%'
union
select e.sid sort_col1,
3 sort_col2,
e.total_waits sort_col3,
chr(9)||
rpad(to_char(e.event),30)||'('||
lpad(to_char(e.total_waits),05)||
lpad(to_char(e.total_timeouts),09)||
lpad(to_char(e.time_waited),07)||
lpad(to_char(e.average_wait),09)||
lpad(to_char(e.max_wait),09)||')' wait_info
from v$session_event e
where e.sid in (select s.sid
from v$session s
where s.type != 'BACKGROUND')
and e.event not like 'SQL*N%'
order by sort_col1 asc, sort_col2 asc, sort_col3 desc;
System ID = 57
Username/Schemaname= SCOTT/TIGER
Status = INACTIVE
Client info
O/S user = scott
Machine Name = tiger
Terminal Name = unknown
dbServer info
O/S Process Id = 26276
O/S Username = oracle
Terminal Name = UNKNOWN
Program = mailto:oracle@tiger2 oracle@tiger2 (TNS V1-V3)
Login Time = 2002/09/17:21:49:10
Wait Event Information
Event (Waits/Timeouts/Waited/Avg Wait/Max
Wait)
I
want to get rid of this line.
db file sequential read ( 27990 180
0)
log file sync ( 40902441
19)
db file scattered read( 3070 50
0)
latch free( 120 20
1)
direct path write (lob) (50 00
0)
async disk IO (40 00
0)
enqueue (30 93
8)
log file switch completion(10 44
4)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Nicoll, Iain \(Calanais\)
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: Suppressing a blank line in a union
Bingo! I turned recsep off and it did the trick. Thanks!
-Original Message-
Sent: Wednesday, September 18, 2002 2:37 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Dan,
I think set recsep off might do the trick as because that particular record
wraps (because of the chr(10)) it inserts the blank line as default
behaviour.
This would cause you to lose the desired blank lines between the other
records though unless you added an extra chr(10) at the end. The other
alternative would be to split it into two selects.
Iain Nicoll
-Original Message-
Sent: Wednesday, September 18, 2002 1:13 AM
To: Multiple recipients of list ORACLE-L
I've got a nasty bit of sql using a union to provide a header line. SQL*Plus
likes to place a blank line between the output of the unions and I want to
get rid of it. I've done it before, but I have forgotten. I do recall that
we never found documentation on it and 'stumbled' across the solution.
The sql is below
TIA,
Dan Fink
column session_header format a1000
column sort_col1 noprint
column sort_col2 noprint
column sort_col3 noprint
set linesize 1001 trimspool on trimout on
break on sort_col1 skip 3
select s.sid sort_col1,
1 sort_col2,
0 sort_col3,
'System ID = '||to_char(s.sid,'999')||chr(10)||chr(9)||
'Username/Schemaname=
'||s.username||'/'||s.schemaname||chr(10)||chr(9)||
'Status = '||s.status||chr(10)||chr(9)||
'Client info'||chr(10)||chr(9)||chr(9)||
'O/S user = '||s.osuser||chr(10)||chr(9)||chr(9)||
'Machine Name = '||s.machine||chr(10)||chr(9)||chr(9)||
'Terminal Name = '||s.terminal||chr(10)||chr(9)||
'dbServer info'||chr(10)||chr(9)||chr(9)||
'O/S Process Id = '||p.spid||chr(10)||chr(9)||chr(9)||
'O/S Username = '||p.username||chr(10)||chr(9)||chr(9)||
'Terminal Name = '||p.terminal||chr(10)||chr(9)||chr(9)||
'Program = '||p.program||chr(10)||chr(9)||chr(9)||
'Login Time = '||to_char(s.logon_time, '/MM/DD:hh24:mi:ss')
session_header
from v$session s,
v$process p
where s.type != 'BACKGROUND'
and s.paddr = p.addr
union
select e.sid sort_col1,
2 sort_col2,
2 sort_col3,
'Wait Event Information '||chr(10)||chr(9)||
rpad('Event',30)||'(Waits/Timeouts/Waited/Avg Wait/Max Wait)'
wait_header
from v$session_event e
where e.sid in (select s.sid
from v$session s
where s.type != 'BACKGROUND')
and e.event not like 'SQL*N%'
union
select e.sid sort_col1,
3 sort_col2,
e.total_waits sort_col3,
chr(9)||
rpad(to_char(e.event),30)||'('||
lpad(to_char(e.total_waits),05)||
lpad(to_char(e.total_timeouts),09)||
lpad(to_char(e.time_waited),07)||
lpad(to_char(e.average_wait),09)||
lpad(to_char(e.max_wait),09)||')' wait_info
from v$session_event e
where e.sid in (select s.sid
from v$session s
where s.type != 'BACKGROUND')
and e.event not like 'SQL*N%'
order by sort_col1 asc, sort_col2 asc, sort_col3 desc;
System ID = 57
Username/Schemaname= SCOTT/TIGER
Status = INACTIVE
Client info
O/S user = scott
Machine Name = tiger
Terminal Name = unknown
dbServer info
O/S Process Id = 26276
O/S Username = oracle
Terminal Name = UNKNOWN
Program = mailto:oracle@tiger2 oracle@tiger2 (TNS V1-V3)
Login Time = 2002/09/17:21:49:10
Wait Event Information
Event (Waits/Timeouts/Waited/Avg Wait/Max
Wait)
I
want to get rid of this line.
db file sequential read ( 27990 180
0)
log file sync ( 40902441
19)
db file scattered read( 3070 50
0)
latch free( 120 20
1)
direct path write (lob) (50 00
0)
async disk IO (40 00
0)
enqueue (30 93
8)
log file switch completion(10 44
4)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fink, Dan
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
Re: Suppressing a blank line in a union
Dan,
It's the chr(10).
SQL select ename||chr(10)||to_char(empno) title
2 from emp
3* where rownum 3
TITLE
---
SMITH
7369
ALLEN
7499
SQL set recsep off
SQL /
TITLE
---
SMITH
7369
ALLEN
7499
hth,
Chaim
Fink, Dan [EMAIL PROTECTED]@fatcity.com on 09/17/2002 08:13:25 PM
Please respond to [EMAIL PROTECTED]
Sent by:[EMAIL PROTECTED]
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
I've got a nasty bit of sql using a union to provide a header line.
SQL*Plus likes to place a blank line between the output of the unions and
I want to get rid of it. I've done it before, but I have forgotten. I do
recall that we never found documentation on it and 'stumbled' across the
solution.
The sql is below
TIA,
Dan Fink
column session_header format a1000
column sort_col1 noprint
column sort_col2 noprint
column sort_col3 noprint
set linesize 1001 trimspool on trimout on
break on sort_col1 skip 3
select s.sid sort_col1,
1 sort_col2,
0 sort_col3,
'System ID = '||to_char(s.sid,'999')||chr(10)||chr(9)||
'Username/Schemaname= '||s.username||'/'||s.schemaname||chr(10)
||chr(9)||
'Status = '||s.status||chr(10)||chr(9)||
'Client info'||chr(10)||chr(9)||chr(9)||
'O/S user = '||s.osuser||chr(10)||chr(9)||chr(9)||
'Machine Name = '||s.machine||chr(10)||chr(9)||chr(9)||
'Terminal Name = '||s.terminal||chr(10)||chr(9)||
'dbServer info'||chr(10)||chr(9)||chr(9)||
'O/S Process Id = '||p.spid||chr(10)||chr(9)||chr(9)||
'O/S Username = '||p.username||chr(10)||chr(9)||chr(9)||
'Terminal Name = '||p.terminal||chr(10)||chr(9)||chr(9)||
'Program = '||p.program||chr(10)||chr(9)||chr(9)||
'Login Time = '||to_char(s.logon_time, '/MM/DD:hh24:mi:ss')
session_header
from v$session s,
v$process
where s.type != 'BACKGROUND'
and s.paddr = p.addr
union
select e.sid sort_col1,
2 sort_col2,
2 sort_col3,
'Wait Event Information '||chr(10)||chr(9)||
rpad('Event',30)||'(Waits/Timeouts/Waited/Avg Wait/Max Wait)'
wait_header
from v$session_event e
where e.sid in (select s.sid
from v$session
where s.type != 'BACKGROUND')
and e.event not like 'SQL*N%'
union
select e.sid sort_col1,
3 sort_col2,
e.total_waits sort_col3,
chr(9)||
rpad(to_char(e.event),30)||'('||
lpad(to_char(e.total_waits),05)||
lpad(to_char(e.total_timeouts),09)||
lpad(to_char(e.time_waited),07)||
lpad(to_char(e.average_wait),09)||
lpad(to_char(e.max_wait),09)||')' wait_info
from v$session_event e
where e.sid in (select s.sid
from v$session
where s.type != 'BACKGROUND')
and e.event not like 'SQL*N%'
order by sort_col1 asc, sort_col2 asc, sort_col3 desc;
System ID = 57
Username/Schemaname= SCOTT/TIGER
Status = INACTIVE
Client info
O/S user = scott
Machine Name = tiger
Terminal Name = unknown
dbServer info
O/S Process Id = 26276
O/S Username = oracle
Terminal Name = UNKNOWN
Program = oracle@tiger2 (TNS V1-V3)
Login Time = 2002/09/17:21:49:10
Wait Event Information
Event (Waits/Timeouts/Waited/Avg Wait/Max
Wait)
I want to get rid of this line.
db file sequential read ( 2799 0 18 0
0)
log file sync ( 409 0 244 1
19)
db file scattered read ( 307 0 5 0
0)
latch free ( 12 0 2 0
1)
direct path write (lob) ( 5 0 0 0
0)
async disk IO ( 4 0 0 0
0)
enqueue ( 3 0 9 3
8)
log file switch completion ( 1 0 4 4
4)
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
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).
Suppressing a blank line in a union
I've got a nasty bit
of sql using a union to provide a header line. SQL*Plus likes to place a blank
line between the output of the unions and I want to get rid of it. I've done it
before, but I have forgotten. I do recall that we never found documentation on
it and 'stumbled' across the solution.
The sql is
below
TIA,
Dan
Fink
column
session_header format a1000column sort_col1 noprintcolumn sort_col2
noprintcolumn sort_col3 noprintset linesize 1001 trimspool on trimout
onbreak on sort_col1 skip 3
select s.sid
sort_col1, 1
sort_col2, 0
sort_col3, 'System ID =
'||to_char(s.sid,'999')||chr(10)||chr(9)||
'Username/Schemaname=
'||s.username||'/'||s.schemaname||chr(10)||chr(9)||
'Status = '||s.status||chr(10)||chr(9)||
'Client info'||chr(10)||chr(9)||chr(9)||
'O/S user =
'||s.osuser||chr(10)||chr(9)||chr(9)||
'Machine Name =
'||s.machine||chr(10)||chr(9)||chr(9)||
'Terminal Name =
'||s.terminal||chr(10)||chr(9)||
'dbServer
info'||chr(10)||chr(9)||chr(9)|| 'O/S
Process Id =
'||p.spid||chr(10)||chr(9)||chr(9)||
'O/S Username =
'||p.username||chr(10)||chr(9)||chr(9)||
'Terminal Name =
'||p.terminal||chr(10)||chr(9)||chr(9)||
'Program =
'||p.program||chr(10)||chr(9)||chr(9)||
'Login Time = '||to_char(s.logon_time, '/MM/DD:hh24:mi:ss')
session_headerfrom v$session s, v$process
pwhere s.type != 'BACKGROUND' and s.paddr =
p.addrunionselect e.sid
sort_col1, 2
sort_col2, 2
sort_col3, 'Wait Event Information
'||chr(10)||chr(9)||
rpad('Event',30)||'(Waits/Timeouts/Waited/Avg Wait/Max Wait)'
wait_headerfrom v$session_event ewhere e.sid in (select
s.sid
from v$session
s
where s.type != 'BACKGROUND') and e.event not like
'SQL*N%'unionselect e.sid
sort_col1, 3
sort_col2, e.total_waits
sort_col3,
chr(9)||
rpad(to_char(e.event),30)||'('||
lpad(to_char(e.total_waits),05)||
lpad(to_char(e.total_timeouts),09)||
lpad(to_char(e.time_waited),07)||
lpad(to_char(e.average_wait),09)||
lpad(to_char(e.max_wait),09)||')' wait_infofrom v$session_event ewhere
e.sid in (select
s.sid
from v$session
s
where s.type != 'BACKGROUND') and e.event not like 'SQL*N%'order
by sort_col1 asc, sort_col2 asc, sort_col3 desc;
System
ID = 57
Username/Schemaname= SCOTT/TIGER
Status = INACTIVE Client
info
O/S user
=scott
Machine Name
=tiger
Terminal Name = unknown dbServer
info
O/S Process Id =
26276
O/S Username =
oracle
Terminal Name =
UNKNOWN
Program = oracle@tiger2 (TNS
V1-V3)
Login Time = 2002/09/17:21:49:10
Wait Event
Information
Event
(Waits/Timeouts/Waited/Avg Wait/Max Wait)
I want to get rid of this
line.
db file
sequential read (
2799 0
18
0
0) log file
sync
( 409 0
244
1
19) db file scattered
read (
307 0
5
0
0) latch
free
( 12
0 2
0
1) direct path write
(lob) (
5 0
0
0
0) async disk
IO
( 4
0 0
0
0)
enqueue
( 3
0 9
3
8) log file switch
completion (
1 0
4
4 4)
UNION
Hi, I try to use union and order by first column of first select statment and also first column of second select statment but get error, Any Idea how to do this?? SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY A,D Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: UNION
In the order by section use the relative column numbers. Plus, you can not individually order by a single column from each union. Its a comprehensive sort of the entire column. SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY 1 -Original Message- Sent: Thursday, January 17, 2002 12:31 PM To: Multiple recipients of list ORACLE-L Hi, I try to use union and order by first column of first select statment and also first column of second select statment but get error, Any Idea how to do this?? SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY A,D Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: UNION
select * from ( SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF) x ORDER BY A,D -Original Message- From: Hamid Alavi [SMTP:[EMAIL PROTECTED]] Sent: Thursday, January 17, 2002 12:31 PM To: Multiple recipients of list ORACLE-L Subject: UNION Hi, I try to use union and order by first column of first select statment and also first column of second select statment but get error, Any Idea how to do this?? SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY A,D Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mac Isaac, John INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: UNION
Hamid, Look in the Sql manual. For UNION clauses, you must ORDER BY the item number: SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY 1 = lookee here hope this helps Tom Mercadante Oracle Certified Professional -Original Message- Sent: Thursday, January 17, 2002 1:31 PM To: Multiple recipients of list ORACLE-L Hi, I try to use union and order by first column of first select statment and also first column of second select statment but get error, Any Idea how to do this?? SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY A,D Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: UNION
You have to use ORDER BY column position as in SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY 1 HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 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. *2
Re: UNION
Hamid Alavi wrote:
Hi,
I try to use union and order by first column of first select statment and
also first column of second select statment but get error, Any Idea how to
do this??
SELECT A,B,C FROM TABLEABC
UNION
SELECT D,E,F FROM TABLEDEF
ORDER BY A,D
Hamid Alavi
Office 818 737-0526
Cell818 402-1987
Hamid,
Remember that the purpose of a union is to bring back rows from
several tables as if they were coming from a single table - a bit like a
join returns columns from several tables as if they were coming from a
single table. By convention, the column names which are assigned come
from the first table in the union. In your example, columns will be
named (A,B,C) even if actually the first part of the UNION returns no
row. Syntactically, to order the output of a union you must specified
column by position number in the select list, not by name - on your
example, it will be 'order by 1' ('1' refering to A or D indistinctly).
If you always want rows from TABLEABC to be returned before rows from
TABLEDEF, you must cheat and add a dummy column :
SELECT 1 dummy, A, B, C FROM TABLEABC
UNION
SELECT 2, D, E, F FROM TABLEDEF
ORDER BY 1, 2
(you can make the dummy column disappear from the output with SQL*Plus
by defining
col dummy noprint
In a program, just ignore it).
Note that there is a drawback to the dummy column use: with a standard
UNION (as opposed to UNION ALL) if a row in the first table is strictly
identical to a row in another table from the UNION, it appears only once
(duplicates are eliminated). With a dummy column, only duplicates from
the same table can be removed.
HTH
Stephane Faroult
Oriole Ltd
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: UNION
- Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 17, 2002 20:31 SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY A,D SELECT A as ord_col,B,C FROM TABLEABC UNION SELECT D as ord_col,E,F FROM TABLEDEF ORDER BY ord_col? hth, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: UNION
Did a little testing. I think that the columns names are decided by the first select only. The union adds the rows from the second select to the result set created by the first select. That's mean that your columns are a , b , c. So order by 'a' will work. It did in my test. Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] -Original Message- From: Hamid Alavi [SMTP:[EMAIL PROTECTED]] Sent: Thu, January 17, 2002 8:31 PM To: Multiple recipients of list ORACLE-L Subject: UNION Hi, I try to use union and order by first column of first select statment and also first column of second select statment but get error, Any Idea how to do this?? SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY A,D Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This e-mail was scanned by the eSafe Mail Gateway -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?= INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: UNION
SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY 1 Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 17, 2002 1:31 PM Hi, I try to use union and order by first column of first select statment and also first column of second select statment but get error, Any Idea how to do this?? SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY A,D Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: UNION
Use ORDER BY 1 (ie, the first column) At 10:31 AM 1/17/02 -0800, you wrote: Hi, I try to use union and order by first column of first select statment and also first column of second select statment but get error, Any Idea how to do this?? SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY A,D Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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[2]: UNION
If you do happen to need each part of the result set ordered individually, you can do something like the following: SELECT 1,A,B,C FROM TABLEABC UNION SELECT 2,D,E,F FROM TABLEDEF ORDER BY 1,2 Bizarre as it may seem, I've used this technique to good effect many times in the past. Basically, I once worked on a a set of reports where I had to return all results in one query (tool limitation) and I need to generate different sections in a single report. To do that, I used the technique above. Best regards, Jonathan Gennick mailto:[EMAIL PROTECTED] * 906.387.1698 http://Gennick.com * http://MichiganWaterfalls.com * http://ValleySpur.com Thursday, January 17, 2002, 2:11:20 PM, you wrote: KL In the order by section use the relative column numbers. Plus, you can not KL individually order by a single column from each union. Its a comprehensive KL sort of the entire column. KL SELECT A,B,C FROM TABLEABC KL UNION KL SELECT D,E,F FROM TABLEDEF KL ORDER BY 1 KL -Original Message- KL Sent: Thursday, January 17, 2002 12:31 PM KL To: Multiple recipients of list ORACLE-L KL Hi, KL I try to use union and order by first column of first select statment and KL also first column of second select statment but get error, Any Idea how to KL do this?? KL SELECT A,B,C FROM TABLEABC KL UNION KL SELECT D,E,F FROM TABLEDEF KL ORDER BY A,D KL Hamid Alavi KL Office 818 737-0526 KL Cell818 402-1987 KL The information contained in this message and any attachments is intended KL only for the use of the individual or entity to which it is addressed, and KL may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from KL disclosure under applicable law. If you have received this message in error, KL you are prohibited from copying, distributing, or using the information. KL Please contact the sender immediately by return e-mail and delete the KL original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Order By Position and UNION
This works in O7.3.4.5 select * from ( select empno e from emp union all select empno e from emp) order by e; -- Chris J. Guidry P.Eng. ATCO Electric, Metering Services Phone: (780) 420-4142 Fax: (780) 420-3854 Email: [EMAIL PROTECTED] -Original Message- From: Larry Elkins [SMTP:[EMAIL PROTECTED]] Sent: Thursday, July 12, 2001 04:40 PM To: Multiple recipients of list ORACLE-L Subject: OT: Order By Position and UNION Listers, Start 8.1.7 SQL Manual from tahiti For compound queries (containing set operators UNION, INTERSECT, MINUS, or UNION ALL), the ORDER BY clause must use positions, rather than explicit expressions. End 8.1.7 SQL Manual from tahiti Now, against an 8.1.7 DB on WIN2K: SQL select deptno, loc from dept 2 union 3 select empno, ename from emp 4 order by deptno -- DEPTNO, not 1 5 / DEPTNO LOC -- - 10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTON 7369 SMITH 7499 ALLEN snip I've always used positional notation in the ORDER BY on my queries using set operators, and, the snippet from the SQL manual still says you have to. The 9i manual at tahiti contains the same statement regarding positional notation. Now I've got someone telling me they *think* they were using expressions even back in 7.x. Now I'm going to play it safe and continue to use positional notation, but, I was wondering if anyone has any insight into this. I hadn't tried expressions in V7 and was curious if it was accepted even back then. Is this simply a documentation bug? There have been lots of enhancements to Oracle's SQL over the last few years so I could see them missing something. Regards, Larry G. Elkins [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Guidry, Chris INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
OT: Order By Position and UNION
Listers, Start 8.1.7 SQL Manual from tahiti For compound queries (containing set operators UNION, INTERSECT, MINUS, or UNION ALL), the ORDER BY clause must use positions, rather than explicit expressions. End 8.1.7 SQL Manual from tahiti Now, against an 8.1.7 DB on WIN2K: SQL select deptno, loc from dept 2 union 3 select empno, ename from emp 4 order by deptno -- DEPTNO, not 1 5 / DEPTNO LOC -- - 10 NEW YORK 20 DALLAS 30 CHICAGO 40 BOSTON 7369 SMITH 7499 ALLEN snip I've always used positional notation in the ORDER BY on my queries using set operators, and, the snippet from the SQL manual still says you have to. The 9i manual at tahiti contains the same statement regarding positional notation. Now I've got someone telling me they *think* they were using expressions even back in 7.x. Now I'm going to play it safe and continue to use positional notation, but, I was wondering if anyone has any insight into this. I hadn't tried expressions in V7 and was curious if it was accepted even back then. Is this simply a documentation bug? There have been lots of enhancements to Oracle's SQL over the last few years so I could see them missing something. Regards, Larry G. Elkins [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: OT: Order By Position and UNION
Larry, You can use column name if you only have two statements with your set operators. However, any more than that you must user positional. See below. 1 select empno, ename from emp 2 union 3 select deptno, dname from dept 4 union 5 select locid, room from location 6* order by empno SQL / order by empno * ERROR at line 6: ORA-00904: invalid column name SQL select empno, ename from emp 2 union 3 select deptno, dname from dept 4 union 5 select locid, room from location 6 order by 1 7 / EMPNO ENAME -- -- 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS 45 101 46 202 47 103 48 105 49 105 50 404 51 421 52 211 53 424 54 402 55 433 56 217 57 222 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 31 rows selected. SQL
RE: OT: Order By Position and UNION
Thanks for the response. I'm wondering if it has always been like that or if it changed with a recent version -- don't know since I have always used positional notation going back to V5 when I started using Oracle (and yes, I know people go back further than that -- that's not the point I was trying to make). Taking your example further, if I alias each column (in your case alias deptno and locid as empno), it will still work if I refer to empno in the ORDER BY. Not that our queries make much sense ;-) Anyway, I think it's obvious why I used OT in the subject line -- we are talking about some trivial things here ;-) But, there is still the curiosity factor that made me want to know if it has always been this way or if this behavior was introduced in a particular version or point release. For example, in-line views were there in 7.1 but not official until 7.2 (I think those are the correct versions, it's been a while). OK, I did a deja search since this is bugging me and at one point, based on posts I pulled up and the examples they provided, you were *required* to use positional. And as we see PL/SQL sometimes lagging SQL, there also were mentions of where referring to a column name was supported in SQL but not in PL/SQL, and references to in earlier versions where positional was required in both. But, none of the posts happened to mention what version of the DB. So I now know that I'm not crazy, that positional was mandatory at some point. It's interesting what the manuals say and your examples showing the difference between unioning 2 or 3 selects, and then what happens with aliasing all to be the same. I'll stick with positional to be safe. Regards, Larry G. Elkins [EMAIL PROTECTED] -Original Message- [EMAIL PROTECTED] Sent: Thursday, July 12, 2001 6:51 PM To: Multiple recipients of list ORACLE-L Larry, You can use column name if you only have two statements with your set operators. However, any more than that you must user positional. See below. 1 select empno, ename from emp 2 union 3 select deptno, dname from dept 4 union 5 select locid, room from location 6* order by empno SQL / order by empno * ERROR at line 6: ORA-00904: invalid column name SQL select empno, ename from emp 2 union 3 select deptno, dname from dept 4 union 5 select locid, room from location 6 order by 1 7 / EMPNO ENAME -- -- 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS 45 101 46 202 47 103 48 105 49 105 50 404 51 421 52 211 53 424 54 402 55 433 56 217 57 222 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER 31 rows selected. SQL -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Larry Elkins INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Union Views on InterMedia Indexed Tables
I've discovered a workaround for an Oracle InterMedia Text error. Our platform: Oracle 8.1.6 on Windows2000 Our problem: We get the following errors when querying (with a Contains() expression) a Union All View of identically-structured tables, each with an InterMedia index on the same CLOB column: ORA-2: interMedia Text error: DRG-10599: column is not indexed Each table can be successfully queried alone or within a Union All Select - just not when referenced in a view. The solution is to create a Select * From Table View on each individual table, then use those individual views in the Union All view. Bizarre, eh? Yes, but it works. It does not solve the problem of getting the same error messages as above when using the Score() function in a query that references the Union All view. This is a problem for us on both 8.1.6 and 8.1.7. BTW, 8.1.7 doesn't exhibit the above-mentioned problem that we experience on 8.1.6. 8.1.7 just gives the error when you use the Score() function. Hope this helps someone. Jack Jack C. Applewhite Database Administrator/Developer OCP Oracle8 DBA iNetProfit, Inc. Austin, Texas www.iNetProfit.com [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jack C. Applewhite INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
