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
:
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
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 a
]
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
table_a x
UNION ALL
select /*parallel (y,8) */
item,
loc,
to_number(NULL),
qty_type_2
from
table_b y
[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
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
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
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
. 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
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
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
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 accessi
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 l
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&qu
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
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
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
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
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
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
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
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
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
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
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
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]
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
- 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
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
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
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
- 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
' -- 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
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
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
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
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
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
arn
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 t
/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
=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
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
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
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
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
, 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
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
)
Sent by: Subject: Suppressing a blank line in a
union
[EMAIL PROTECTED
. 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
, 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
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
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
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
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
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
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
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
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
- 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
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
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
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
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
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
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
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
: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
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
69 matches
Mail list logo
