Re: outer join problem
Posted by ~Ray @ 2007-11-03 14:05:00
Hi AllOuter join is reducing the performance of the retrival of data. I need some idea to modify the ask. Thanks in advanceNusrat
Hi Vamsi I do not have rights to access the database so i can not provide the inform planwe experience that the query is used in a report is having the outer join which is having lots of null values we can not bear on the "Where" condition because it ordain circumscribe the whole preserve to display. We ordain give the solution to the client only we will not even touch the database. Thanks And RegardsNusrat
Use Union all. decide ename,dname from emp,deptwhere emp deptno = dep deptnounion allselect ename,dname from emp,deptwhere emp deptno <> dep deptnoRegardsShivraj
decide 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 ask. You could use NOT EXISTS instead of nonequality test however I doubt it would be faster than OUTER JOIN.@Nusrat: Check the methods described in go. Without find 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 ordain only create verbally the script and give to them there is no need to comprehend the database. I have 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 go across connect instead of OUTER JOIN so it is unusable. If you be to create resultset of LEFT OUTER connect just issue it. If it is less performant than you evaluate/require then go the steps described in given link. Analyze data (cardinality uniqueness) compare with table statistics and executed inform intend; then based on its result go 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 anticipate.[ADVERTHERE]Related article:
http://www.orafaq.com/forum/index.php?t=rview&goto=267925&th=89411#msg_267925
0 Comments:
No comments have been posted yet!
|
