tag:blogger.com,1999:blog-12459003.post2957437649603492312..comments2023-08-03T09:19:28.438-04:00Comments on So What Co-operative: Interesting Optimizer ResultJeff Hunterhttp://www.blogger.com/profile/15701602600662849251noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-12459003.post-20475382203402293682010-01-13T19:19:36.928-05:002010-01-13T19:19:36.928-05:00Having a look at both predicates again, I'd gu...Having a look at both predicates again, I'd guess that, because a.id is the same is b.id, the optimizer is re-writing some_funky_package_call(a.id, b.description) to<br />some_funky_package_call(b.id, b.description) <br /><br />Then it is applying that to every row in 'b' before joining the results of that to 'a'. That would be valid if table 'b' was small, the SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.comtag:blogger.com,1999:blog-12459003.post-86156067323933205732010-01-13T18:21:04.574-05:002010-01-13T18:21:04.574-05:00I would try either:
Adding rownum to the inner qu...I would try either:<br /><br />Adding rownum to the inner query to force it to evaluate the inline view before the filter:<br /><br /><br />SELECT id, description, bal<br />FROM (<br /> SELECT<br /> a.id,<br /> b.description,<br /> some_funky_package_call(a.id, b.description) bal,<br /> rownum rn<br /> FROM<br /> tableA a,<br /> tableB b<br /> WHERE a.id = b.id<Craig Martinhttps://www.blogger.com/profile/01210939485432835552noreply@blogger.comtag:blogger.com,1999:blog-12459003.post-67043418018019138842010-01-13T18:00:46.566-05:002010-01-13T18:00:46.566-05:00"The GROUP BY essentially was doing nothing....."The GROUP BY essentially was doing nothing.... "<br />No offence but I hate that approach. Firstly the SQL becomes obscure/misleading and there's a tendency for someone to come along six months later and 'fix' it.<br />Secondly, there's the tendency for the optimizer to get smarter and say "Hey, I don't need to do this step". <br /><br />That doesn't SydOraclehttps://www.blogger.com/profile/08828771074492585943noreply@blogger.comtag:blogger.com,1999:blog-12459003.post-84106755126454157582010-01-13T16:36:16.199-05:002010-01-13T16:36:16.199-05:00I optimized many SQL with this form. The best way ...I optimized many SQL with this form. The best way to do it, from a performance perspective, is the remove the call all together and do it in SQL only. But for this solution to work you need a fairly simple PL/SQL procedure.Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-12459003.post-86541519944936974952010-01-13T08:10:39.565-05:002010-01-13T08:10:39.565-05:00Have you check any oddity for PLSQL functions and ...Have you check any oddity for PLSQL functions and their costing <br /><br />in 2008 UKOUG I joined a presentation of Joze Senegacnik and he gave some information about the oddities <br /><br />can be find something similar from Adrian Billington here <br /><br />http://www.oracle-developer.net/display.php?id=426Coskan Gundogarhttps://www.blogger.com/profile/11570869033287689498noreply@blogger.com