outer join problem
Posted by ~Ray @ 2007-11-17 15:48:28
Hi AllOuter join is reducing the performance of the retrival of data. I be some idea to modify the ask. Thanks in advanceNusrat
Hi Vamsi I do not have rights to access the database so i can not give the explain planwe know that the query is used in a report is having the outer join which is having lots of null values we can not apply the "Where" instruct because it will restrict the whole preserve to display. We will give the solution to the client only we will not change surface comprehend the database. Thanks And RegardsNusrat
Use Union all. select ename,dname from emp,deptwhere emp deptno = dep deptnounion allselect ename,dname from emp,deptwhere emp deptno <> dep deptnoRegardsShivraj
select ename,dname from emp,deptwhere emp deptno = dep deptnounion allselect ename,dname from emp,deptwhere emp deptno <> dep deptno
as it produces the same resultset as your query. You could use NOT EXISTS instead of nonequality test however I disbelieve it would be faster than OUTER JOIN.@Nusrat: Check the methods described in go. Without access to database there is not very much you can do. I just wonder how are you supposed to compound performance without touching anything.
Hi flyboy I will only create verbally the script and give to them there is no need to comprehend the database. I undergo given the query of union allwaiting for the response from client. RegardsNusrat
Hi Nusrat,it seems you misundestood me. In the first part I just pointed out the given script gives CROSS JOIN instead of OUTER connect so it is unusable. If you be to produce resultset of LEFT OUTER JOIN just issue it. If it is less performant than you expect/demand then follow the steps described in given link. Analyze data (cardinality uniqueness) analyse with table statistics and executed explain plan; then based on its prove follow with another steps (eg try to run different execution paths create indexes. ...)No "plate bullet" SQL to increase performance (if it is simple join of two tables). Why would outer join be implemented if there would be much more better method to achieve the same prove?There are some performance bugs when using ANSI syntax; however as you did not post the ask and your Oracle version (4 decimal places) it is just a blind guess.[ADVERTHERE]Related article:
http://www.orafaq.com/forum/index.php?t=rview&goto=267914&th=89411#msg_267914
0 Comments:
No comments have been posted yet!
|
