Home » SQL & PL/SQL » SQL & PL/SQL » Is there any subsitude of UNION and UNION ALL
| Is there any subsitude of UNION and UNION ALL [message #198067] |
Sat, 14 October 2006 04:14  |
masum_parvej
Messages: 3
Registered: May 2006
|
Junior Member |
|
|
Hi all,
I have faced one problem and want to share with you people and also need your help.
The problem is:
I have one cursor in the procedure that gets the data from dataware house table (tables name started by dw_) and some main operational table (like dept, dept_details).
The Original cursor is:
Cursor c1 is
Select a.name name ,
null Location ,
a.id emp_id ,
d.dept_code dept_code ,
a.sal salary
from dw_emp_detail a,dept d
where a.dpt_code=d.id
union all
Select ed.name name ,
ed.add Location,
ed.id emp_id ,
dd.code dept_code ,
ed.sal salary
from dw_emp_detail ed,dept_details dd
where ed.dpt_code=dd.code
union all
select eh.first_name ,
null Location ,
eh.id emp-id ,
eh.dept_code dept_code ,
eh.salary salary
from dw_emp_history eh,dept d
where eh.dpt_code=d.id
My purpose is to get all the data from dataware house tables (regardless whether the corresponding data is present or not in operational table) which was joined with some main operational table.
So I had used outer join. But here was one another problem introduced i.e. the cursor was hold duplicate data as I had used UNION ALL. (UNION ALL holds duplicate entries also).
So again I have replaced UNION ALL by UNION to get distinct value.
It seemed to me that my problem was solved. After using outer join and UNION clause my code looked like:
Cursor c1 is
select ………….
from dw_emp_detail a,dept d
where a.dpt_code=d.id (+)
union
select ……..
from dw_emp_detail ed, dept_details dd
where a.dpt_code=dd.code(+)
union
select …………….
from dw_emp_history eh,dept d
where eh.dpt_code=d.id(+) ;
But as I had check that the dataware house tables contained 200000+ data. As per my view, as UNION clause is used so it will find distinct value and that will hugely effect sorting area size in PGA and also reduce the performance.
So, can you people suggest me what to use or what is the best option to get all the data from dataware house table, keeping the database performance in mind?
eagerly waiting for your reply??
[Moderator]: added formatting
[Updated on: Sat, 14 October 2006 21:06] by Moderator Report message to a moderator |
|
|
|
| Re: Is there any subsitude of UNION and UNION ALL [message #198101 is a reply to message #198067] |
Sat, 14 October 2006 21:13  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
If there are no dups between dw_emp_detail and dw_emp_history, then this would work.
select a.name name ,
null Location ,
a.id emp_id ,
NVL(d.dept_code, dd.code) dept_code ,
a.sal salary
from dw_emp_detail a
, dept d
, dept_details dd
where a.dpt_code=d.id (+)
AND a.dpt_code=dd.code (+)
UNION ALL
select eh.first_name ,
null Location ,
eh.id emp-id ,
eh.dept_code dept_code ,
eh.salary salary
from dw_emp_history eh
, dept d
where eh.dpt_code=d.id
PS. Could you enclose SQL in [code] , [/code] tags next time so we can read it (I updated your earlier post to add them).
Ross Leishman
|
|
|
|
| Re: Is there any subsitude of UNION and UNION ALL [message #198431 is a reply to message #198101] |
Tue, 17 October 2006 02:57 |
masum_parvej
Messages: 3
Registered: May 2006
|
Junior Member |
|
|
Thanks rleishman,
But there are lots of dups between dw_emp_detail and dw_emp_history.These two tables are near about same and have same data.
That's why I had replaced UNION ALL by UNION. But as per sorting is concerned to get distinct value due to use UNION, so it will impact SORT_AREA_SIZE in PGA and become a performance issue.
So, is there any other option so that my purpose (to get all the data in the Cursor c1 from dataware house tables, regardless whether the corresponding data is present or not in operational table ) gets solved?
Please help me with your expert experience.
[Updated on: Tue, 17 October 2006 03:01] Report message to a moderator |
|
|
|
|
|
| Re: Is there any subsitude of UNION and UNION ALL [message #198788 is a reply to message #198067] |
Wed, 18 October 2006 09:24 |
Bill B
Messages: 1971
Registered: December 2004
|
Senior Member |
|
|
Just check in the query that there are no duplicates between the history and live tables.
Cursor c1 is
Select a.name name ,
null Location ,
a.id emp_id ,
d.dept_code dept_code ,
a.sal salary
from dw_emp_detail a,dept d
where a.dpt_code=d.id
union all
Select ed.name name ,
ed.add Location,
ed.id emp_id ,
dd.code dept_code ,
ed.sal salary
from dw_emp_detail ed,dept_details dd
where ed.dpt_code=dd.code
union all
select eh.first_name ,
null Location ,
eh.id emp-id ,
eh.dept_code dept_code ,
eh.salary salary
from dw_emp_history eh,dept d
where eh.dpt_code=d.id
and not exist (select null
from dw_emp_detail a
where sh.dpt_code = a.dpt_code); |
|
|
|
Goto Forum:
Current Time: Thu Dec 05 17:03:03 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
