Forum OpenACS Development: concatenated indices in ACS4Tcl (in PG?)
While trying to understand some of the permissions implementation, I was re-reading the CONNECT BY stuff in SQL for Web Nerds, and there is a comment from Robert Xu about Phil's suggestion to use a concatenated index to help the performance of hierarchical queries.
The gist (of Robert's comment) is that it never makes sense to include a primary key (which is already indexed) in a concatenated index, and if a field is a foreign key, it always makes sense to have an index, but not a composite one.
My questions about this are:
- The comment says oracle will eventually dump core if this is screwed up. Has anyone seen this? If so, did concatenated indices have anything to do with it?
- I confirmed another of this guy's comments that bookmarks-4.1.1b does have this index structure, although it does not currently appear in the file-storage module. Do other packages exhibit this design anomaly?
- I have read some posts here about the non-portability (to PG) of CONNECT BY, et.al. Does this index construct also have challenges (of either syntax or semantics) in the PG world?
PG doesn't have CONNECT BY and we do our tree-ordering sorts using generated sort keys that are stored in the table. The sort key has a simple b-tree index on it. This code's undoubtably more portable than the CONNECT BY code. It would be interesting to benchmark CONNECT BY and the sort key approach in Oracle to see which is faster ...