What's the performance characteristics of the CONNECT BY hierarchical query clause? More specifically, is it optimized to connect on a specific datatype? Are there performance differences between connecting using VARCHAR2 or NUMBER?
asked
Performance of CONNECT BY?
Coop,
I can't answer in specifics because I've only used CONNECT BY a bit recently. But I can say I found the performance fairly good so long as you're connecting to an indexed field, preferably a primary key.
If I had this questions, I'd be searching asktom.oracle.com for his thoughts. It'll take you time to wade through the search results and I find the discussions usually pretty interesting, so read more than I need to for just a quick answer. But I usually learn a lot in the process.
I did a quick search at AskTom for you, but didn't find anything.
I did however find a useful discussion in an Oracle forum.
Good luck,
Stew
As long as the data is indexed appropriately, CONNECT BY is just as fast (if not faster) than doing it by hand. While CONNECT BY is simpler, you should also look into the newer common table expression method (i.e. WITH/recursive WITH) as it is more portable. Dan Morgan has some good examples here.
