At the risk of rambling on and on it appears that using "in" with a list of constant tree sortkeys will indeed use the index on tree_sortkey. So I now have a couple of approaches in mind which will make selecting the ancestors of a node every bit as efficient as the select of a node's children.
Meaning we're home free with just a bit more work. Cool...
These queries should really be faster than the equivalent "connect by" queries in Oracle. The penalty we pay, though, is a slight increase in storage and an increase in update/insert/delete times (the time required to calculate and update new tree_sortkeys). Since these DML operations are much less frequent that selects I really don't care.
As far as the space required for the new tree_sortkeys, after loading up all the openacs.org users, forums, categories and messages the maximum tree_sortkey for acs_objects is 8 bytes + 4 bytes for the variable length counter (text, bit varying, etc are all stored in counter + data form), for a key that's at the 7th level in the object tree.
Not bad.
Of course, more complex sites will have deeper trees with correspondingly longer keys but most nodes won't have more than 128 immediate children, thus taking a byte for each level in the tree.
