Dominique Devienne escribió:
I'm currently using hashed indices only. Does the technique of using a compatible key extend to them? (I suspect not in this case, but I thought I'd ask, just in case I'm misunderstanding.)
No, it doesn't extend to hashed indices, you have to stick with ordered ones. The crucial fact upon which the parent/children sorting criteria relies is that sorting on scenario_t induces a lexicographical order that lists subtrees together (if you think of paths as forming a directory tree, lexicographical order is equivalent to inorder traversal).
What is this? parent_t is just a sort of alias to scenario_t that allows us to differentiate between a "terminal" scenario and a scenario used for purposes of recursive lookup. children is a compatible (with the order of entry_container_t) compare predicate that levels off scenario paths in a way that, for instance, if we have parent "a" then "a/b", "a/d", "a/b/e" etc. are deemed equivalent to the parent.
I was thinking about such a Materialized Path impl, after reading http://www.dbazine.com/oracle/or-articles/tropashko4 recently. But I would not have been able to express it in an example as easily as you could. I appreciate the leg up.
I've only taken a cursory look at the reference you provide, but seems indeed that my proposal basically implements the materialized path idea.
Is this of any help to your particular problem?
I think it does. I'll need time to digest and adapt it, but it sounds like Materialized Path with an ordered index will avoid a full scan.
Good luck with your project, Joaquín M López Muñoz Telefónica, Investigación y Desarrollo