tag:blogger.com,1999:blog-7395977411859619892.post8151402486486847801..comments2024-01-30T09:21:06.579+01:00Comments on About Oracle: Connect By FilteringRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-7395977411859619892.post-65058836674382162552014-01-21T17:10:59.943+01:002014-01-21T17:10:59.943+01:00The "connect by no filtering" sorts 1000...The "connect by no filtering" sorts 100000 rows, because the filter is on connect by, and not global. Try this:<br /><br /> select /*+ no_connect_by_filtering */ id<br /> , parent_id<br /> , sys_connect_by_path(id,'->') scbp<br /> from (select * from t where indicator = 'N' or parent_id is null)<br />connect by parent_id = prior id<br /> start with mikhttps://www.blogger.com/profile/15793606978181369736noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-16820739540424921822013-07-20T10:46:49.383+02:002013-07-20T10:46:49.383+02:00Rajji,
This is a blog. Please post your questions...Rajji,<br /><br />This is a blog. Please post your questions on a forum like OTN or Stack Overflow.<br /><br />Thanks,<br />Rob.Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-68950975024755587532013-07-16T17:03:05.777+02:002013-07-16T17:03:05.777+02:00Hi ,
Can some one please explain me how to com...Hi ,<br /><br /> <br /><br />Can some one please explain me how to comprehend/understand multiple conditions in connect by prior conditions with some example data.<br /><br /> <br /><br />I am creating a table like this<br /><br /> <br /><br />CREATE TABLE FAMiLY_TREE<br /><br />(<br /><br />GRAND_FATHERID number,<br /><br />FATHER_ID number,<br /><br />SON_ID number,<br /><br />person_name Rajjyhttps://www.blogger.com/profile/16728253507266985920noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-41025424786069190862012-12-14T17:36:32.454+01:002012-12-14T17:36:32.454+01:00Rob,
Most excellent!
Just one thing though - in...Rob,<br /><br />Most excellent! <br /><br />Just one thing though - in my case, for a similar query [without adding the CONNECT_BY_NO_FILTERING hint] my Oracle Database 11.2.0.3.0 optimizer randomly chooses one plan, then the other plan - there seems to be no pattern. I will investigate - but your post is very helpful. Thanks.<br /><br />Regards,<br />ArunVidhya Arunhttps://www.blogger.com/profile/18011295253501566376noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-11366192547349335562012-03-27T21:25:41.629+02:002012-03-27T21:25:41.629+02:00Thanks for your comment, Dominic.
I did a 10032 t...Thanks for your comment, Dominic.<br /><br />I did a 10032 trace for the first time ever. It's a great way to see how individual sort operations performed when your query performs more than one sort at the same time. I saw a similar number of bytes: 7724032. And approximately 1,5M comparisons performed.<br /><br />I reached the same conclusion as you. And it's way too early for me to Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-8757773047492903052012-03-26T20:43:06.700+02:002012-03-26T20:43:06.700+02:00Rob,
Interesting article.
Event 10032 confirms t...Rob,<br /><br />Interesting article.<br /><br />Event 10032 confirms that DBMS_XPLAN is being economical with the truth about “sorting”.<br /><br />As you mentioned, the runstats stats show that the NO_CONNECT_BY_FILTERING did 2 in-memory sorts (compared to the 8 for the other plan). This is backed up by the 10032 trace which, in my case, indicated that one of these 2 sorts in memory sorted DomBrookshttps://www.blogger.com/profile/02598622186013843759noreply@blogger.com