Re: outer join problem
Posted by ~Ray @ 2007-11-09 17:32:26
Hi AllOuter join is reducing the performance of the retrival of data. I be some idea to change the ask. Thanks in advanceNusrat
Hi Vamsi I do not undergo rights to find the database so i can not give 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 apply the "Where" instruct because it ordain restrict the whole preserve to display. We ordain give the solution to the client only we will not even comprehend 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 evaluate however I doubt it would be faster than OUTER JOIN.@Nusrat: analyse the methods described in thread. 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 compose and give to them there is no be to touch the database. I have given the ask 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 script gives go across JOIN instead of OUTER connect so it is unusable. If you want to produce resultset of LEFT OUTER connect just issue it. If it is less performant than you expect/demand then go the steps described in given cerebrate. Analyze data (cardinality uniqueness) compare with table statistics and executed explain 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 result?There are some performance bugs when using ANSI syntax; however as you did not affix 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=267917&th=89411#msg_267917
0 Comments:
No comments have been posted yet!
|
