Hi, excellent post, I have taken it and put it into practice, and it works very well for me to redo the whole nested set, But I want to ask if you have a way (store procedure or function) for when a new record is created in the adjacent list, and avoid rebuilding the whole set every time a new record is added in the adjacent list. Thanks in advance
Thanks for the feedback. Yes, there is a way to update a "single" new row but it's pretty nasty because you have to add 1 to some Bowers and 2 to the Bowers on ALL the nodes to the logical "right of the new node.
IIRC, Joe Celko covers the method for inserting, deleting, and moving a single node in Nested Sets in his book "Trees and Hierarchies in SQL for Smarties" but you can also find how to do it by search for the task on Google. I only studied the method long enough to know that I didn't want to ever have to do something like that. I'm not sure that there would be a time savings but I've never tested such a thing for performance.
I did rerun all the tests from my original article on newer 64 bit equipment rather than the old 32 bit equipment I originally tested with for the article. The full up million row hierarchy conversion, which leaves you with the original Adjacency list intact, creates a Hierarchical Path hierarchy, and creates the Nested Sets only took 19 seconds instead of the original 54 with no changes to the code. That retest was about 5 or 6 years ago, IIRC. Should be even better on more recent machines.
Also, in case you've not seen it, you may be interested in Part 2 of that short series where most calculations that people would do on a hierarchy are already done and stored in a table. Here's the link for that article.
Thank you again for the feedback.
Oh... and you don't have to drop the original tables to update them in either case so that any "downtime" is limited to the amount of time (low milliseconds close to zero) to simply repoint synonyms to the ONLINE table and the OFFLINE table. Build the new hierarchy in an offline table and when it's done, do the synonym repointing. Just reverse the synonyms the next time. I lovingly call it the "Swap'n'Drop" method. I'm not the original inventor of that method but I use it for a whole lot of things. For example, I have a nightly reload of about 30 tables (funny that I never actually counted how many) that uses the method. As they say in the Navy... "It works fine, fails safe, and drains to the bilge". 😀