--- In firebird-support@yahoogroups.com, Harriv wrote:

> > Looking at the interesting Modified Preorder Tree Traversal, I'd > > say your query can even be written simply using JOINs:
> >
> > SELECT T.*
> > FROM POSITION_SCREEN_MAP PSM
> > JOIN POSITION_SCREEN_MAP PSM2
> > ON PSM2.LFT BETWEEN PSM.LFT AND PSM.RGHT
> > JOIN TEXT_DATA T
> > ON T.POSITION_TAG = PSM2.POSITION_TAG
> > WHERE PSM.POSITION_TAG = 'AREA1'
>
> Yes, this works, I can understand how and it is more efficient,
> thank you.
>
> > Although there are some clear advantages in the ability of this
> > method to quickly calculate the number of child rows and probably
> > a few other benefits as well, I'd say it is mainly useful for
> > read-only databases or databases you update rarely with only
> > batch updates. It has a huge cost of updating lots of rows
> > whenever you insert a new value to the tree (choose between
> > updating on average half the rows in the table for a single
> > insert or do the inserts and then recalculate the entire table!)
> > that goes against the very idea of a multiuser environment.
> > Firebirds beautiful multiuser abilities are gone when you use
> > this method.
>
> Yes, I found out this as well. Luckily this particular application
> is almost read-only for the tree data.

I thought a bit more about it, and if you initially leave some extra space, a variant of the Modified Preorder Tree Traversal could work nicely. If you leave a larger gap, say 9 rather than 1 between each node (I chose a random number, I have no idea whether this is better than other numbers divisible by 3), then you would not have to reorder the tree each time. Even you had to reorder it, you would normally have to reorder a much smaller part. Counting records could no longer be done as easily as you can in the original version, but that's a smaller price to pay than to have to reorder large parts of the table.

> Are there any good articles about recursive queries and trees?

Beyond reading the release notes on recursive common table expression in Firebird 2.1 (I think), I haven't read any. Though that doesn't mean those articles don't exist, I've just never needed them, so I haven't looked.

HTH,
Set