Re: Using Materialized Path to create a paths table

>> I had a look at nested sets, but it seems that for large trees (maybe 100,000 nodes), the cost of renumbering the left and right pointers would be expensive if an insert required complete tree renumbering. <<

I have tested it on 150,000 rows and it is not a problem. The rows are short (2 intergers and a FK to the nodes table) so a lot of data sits on a data page. You have a clustered index on (lft) or (lft,rgt) so you use a table scan, which has to touch the root but after that, it can jump to the "cut point" and unlock all the pages to the lft of that point. Draw out a diagram and you will see what I mean.

Also, how often to you change a hierarchy as opposed to the nodes in a business app? Personnel come and go, but job titles stay.

As for path enumerations, try to write the constraints to prevent cycles and orphans. It is a lot of string manipulations. Received on Wed May 31 2006 - 01:34:27 CEST