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 Go to next message
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
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 #198507 is a reply to message #198431] Tue, 17 October 2006 07:41 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
No, you cannot avoid the sort. Oracle 10.2 can perform GROUP BY with a hash algorithm that does not use a SORT, but it will still take up a lot of TEMP space.

Ross Leishman
Re: Is there any subsitude of UNION and UNION ALL [message #198788 is a reply to message #198067] Wed, 18 October 2006 09:24 Go to previous message
Bill B
Messages: 1484
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);
Previous Topic: Regarding Oracle Columns
Next Topic: Parsing Help
Goto Forum:
  


Current Time: Thu Dec 08 20:21:36 CST 2016

Total time taken to generate the page: 0.17989 seconds