Forum OpenACS Development: concatenated indices in ACS4Tcl (in PG?)

Collapse
1: concatenated indices in ACS4Tcl (in PG?)
Posted by Roger Williams on 07/28/01 06:49 PM

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?

TIA..Regards..

Collapse
2: Response to concatenated indices in ACS4Tcl (in PG?) (response to 1)
Posted by Don Baccus on 07/28/01 06:58 PM
I haven't seen core dump, but I have seen referential integrity errors  that were "false negatives", i.e. the foreign key reference didn't "see" the row with the key even though it clearly existed and "select"  would find it.  Dumping the two indices in the stock file storage package and replacing with two indices on the individual keys made that go away (this was Oracle).  I remember another thread about this somewhere on web/db.

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 ...