HomeНаука и техникаRelated VideosMore From: ORACLE-BASE.com

Hierarchical Queries in Oracle (CONNECT BY)

119 ratings | 25962 views
This video demonstrates one of the ways to query hierarchical data from Oracle. For more information see: https://oracle-base.com/articles/misc/hierarchical-queries Website: https://oracle-base.com Blog: https://oracle-base.com/blog Twitter: https://twitter.com/oraclebase Cameo by Arjen Visser Blog: http://blog.dbvisit.com/ Twitter: https://twitter.com/dbvisit Website: http://www.dbvisit.com/ Cameo appearances are for fun, not an endorsement of the content of this video.
Html code for embedding videos on your blog
Text Comments (13)
Aman Garg (1 year ago)
Succinct and done with incredible brevity. Cheers!
OracleCoder (2 years ago)
Hello Tim. Thank you for sharing your video ...last year :-) I been writing about hierarchical queries on my blog earlier, but lately I have been working with a table of about 9 million records. I just find it to be waaaaay too slow. I know I could probably put the data in a memory database or something, but I need to access the hierarchical data and be able to join the resultset with other data in the database...like customer info, etc. Do you have any tips and tricks on how to speed up the CONNECT BY queries? I even added (as a test) a column holding the parent's ROWID...and did a "CONNECT BY PRIOR ROWID = CHARTOROWID(dt.parent_rowid)", but it did not help much. All the right indexes should be there, and up-to-date as well...
OracleCoder (2 years ago)
Ok. Thank you for your feedback. It is a binary MLM tree, so I cannot really filter out anything. Will put my thinking cap on to see if I can figure out something else. :-)
Tim Hall (2 years ago)
Doing a tree walk of 9 million rows is likely to always be an issue. Normal rules apply. Filter early: remove rows you don't care before worrying about the tree walk. Trace the query to look at where the time is being used.
pratap rana (2 years ago)
sir,can u plz explain, what excatly what u write RPAD function
Ash Parmar (2 years ago)
would there by any chance to see this in practice in SQL DEV, over several columns to show manager name aside of the employee name etc?
Tim Hall (2 years ago)
If you don't want duplicates, I would suggest you remove duplicate rows first with a query in an inline view (or WITH clause), then perform the the tree walk on the resulting data. Always try to filter early. :)
Ash Parmar (2 years ago)
thanks Tim. I've had a look, however this table does not have duplicate parent ID, like the one i've been tasked with. If I followed this method, most likely i will produce duplicate rows. is there a way i can prevent this, or will i be better off doing a 'self join', with a partition by clause?
Tim Hall (2 years ago)
You could do that yourself. You can get the SQL from the linked article. Add some more columns and try it for yourself. The examples in the videos are purposely kept small and simple. Best way for you to learn is to try for yourself. :) Cheers Tim...
Baljeet Degun (2 years ago)
A tricky topic very well explained. Thanks Tim!
Kylie Duthie (3 years ago)
Tim. I didn't know you can move the PRIOR keyword in the CONNECT BY section. 90% of the time I use CONNECT BY PRIOR table.column = table.column and I get them around the wrong way and it's so frustrating. Moving the PRIOR keyword makes so much sense. Thanks for sharing!
ORACLE-BASE.com (3 years ago)
+Kylie Payne No problem. You should put PRIOR on the side that make the line scan well for you. I think "CONNECT BY fk_column = PRIOR primary_key" reads better than "CONNECT BY PRIOR primary_key = fk_column", but they do the same thing, so you should pick the one which reads better to you. :)
ORACLE-BASE.com (3 years ago)
Video : Hierarchical Queries in Oracle (CONNECT BY) : https://www.youtube.com/watch?v=HXFUXYFdB-Y @dbvisit 

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.