Re: outer join problem
Posted by ~Ray @ 2007-11-27 20:07:27
Hi AllOuter join is reducing the performance of the retrival of data. I be some idea to change the query. 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 ordain circumscribe the whole preserve to display. We will give the solution to the client only we will not even touch 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 evaluate however I doubt it would be faster than OUTER connect.@Nusrat: analyse the methods described in thread. 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 write 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 move I just pointed out the given compose gives go across connect instead of OUTER JOIN so it is unusable. If you be to produce resultset of LEFT OUTER connect just air it. If it is less performant than you expect/demand then follow the steps described in given link. Analyze data (cardinality uniqueness) compare with table statistics and executed inform plan; then based on its result go with another steps (eg try to run different execution paths create indexes. ...)No "plate bullet" SQL to change magnitude performance (if it is simple join of two tables). Why would outer join be implemented if there would be much more better method to bring home the bacon the same prove?There are some performance bugs when using ANSI syntax; however as you did not post the query and your Oracle version (4 decimal places) it is just a blind anticipate.[ADVERTHERE]Related article:
http://www.orafaq.com/forum/index.php?t=rview&goto=267919&th=89411#msg_267919
0 Comments:
No comments have been posted yet!
|
