Prev: re sql result set where value increments
Next: Different content types
From: --CELKO-- on 12 Dec 2009 23:33
>>  You may find the difficulties with maintaining a nested sets hierarchy is not worth it. An interesting read are the following articles by Vadim Tropashko .. <<

I love Vadim's work and he has a lot of good articles in the Oracle
world. But the trade off in a Nested Intervals model is that you have
to build a library of rational number functions. It is a good bit of
overhead, cannot tell the optimizer anything, etc.

Since Nested Sets are manipulated by stored procedures copied from my
books once and the data integrity is maintained by declarative code,
none of my clients have found it to be difficult. At least one
corporate level portal control package uses it and tested it for a
100K node load in DB2. The product went from ~60 tables to 6 tables
(in fairness, it had gotten out of hand over the years).

What I have found is that Adjacency List Models lack data integrity
constraints and wind up with cycles, separate trees and orphans in the
real world.


From: Plamen Ratchev on 13 Dec 2009 11:56
I normally have to deal with hierarchies with more than 100k nodes that very frequent updates, deleted, inserts, and
structure changes. With that in hand it is almost impossible to estimate and leave sufficient gaps in a nested sets
model to accommodate for structure changes. This makes the model practically unusable when expectations are to have
adequate response time. I still like the nested sets model but use it with mostly static hierarchies.

--
Plamen Ratchev
http://www.SQLStudio.com
From: --CELKO-- on 13 Dec 2009 13:50
>> I normally have to deal with hierarchies with more than 100k nodes that very frequent updates, deleted, inserts, and structure changes. <<

The tree structure is two integers (lft, rgt) plus the size of the
foreign key to reference the nodes, so a lot of rows fit onto a data
page. Declare the table with a high percentage of free space so the
structure changes have room and I have not seen any problems.
First  |  Prev  | 
Pages: 1 2 3
Prev: re sql result set where value increments
Next: Different content types