Forum OpenACS Development: OpenACS 4.0 Guidelines (aD questions)

Collapse
1: OpenACS 4.0 Guidelines (aD questions)
Posted by Ken Kennedy on 05/10/01 07:53 PM

In the OpenACS 4.0 Guidelines, there are a couple of aD questions, including

  • what's with the disabled constraints, followed by index creation, followed by constraint reenablement?

Please note...I'm NOT an aD employee (nor have I ever been, though I almost was...), but just looking at that, I can guess. Sounds like the SQL was generated a DDL creation tool. I use ERwin and SQL*Navigator at work, and get those kinds of disabled/mucked with/re- enabled constraints sometimes. It could be one of those, or even a perl script for all I know (there's supposedly a pretty cool DDL:Oracle module that I haven't tried, for example). But that's what it looks like to me...

Collapse
2: Response to OpenACS 4.0 Guidelines (aD questions) (response to 1)
Posted by Don Baccus on 05/10/01 08:25 PM
That may be the case, we (Ben and I) never did track this down.  We
were asking, in essence, if there was some Secret Magical Oracle-World
Trick going down that we'd have to worry about in Postgres.  We were
sitting in an aD conference table with our respective laptops reading
datamodels like crazy and pulling out stuff that we didn't immediately
understand into a list that later got stuck into that document.

Anyway, the eventual conclusion was that this was something we could
just ignore in our porting efforts, and thus far we've not been bitten
by any problems that would prove this assumption wrong.

I should filter through that document one more time and pull out some
of the things that aren't relevant at present...

You've made me curious, though ... if any aD'ers in the know read
this, was some sort of tool being used that generated these?

Collapse
3: Response to OpenACS 4.0 Guidelines (aD questions) (response to 1)
Posted by Lars Pind on 05/11/01 05:13 AM
I don't know what the deal is with those, but we don't use any CASE or ER tools to generate the datamodels. We occasionally use such tools to reverse-engineer and generate pretty diagrams, but I'm not aware of anybody here (at aD) ever having done the reverse.
Collapse
4: Response to OpenACS 4.0 Guidelines (aD questions) (response to 1)
Posted by David Eison on 05/11/01 04:18 PM
Was news to me.  Turns out it's an optimization for later disabling/re-enabling.  From Oracle 8i Concepts, Ch. 25 Data Integrity:

"Enabling a primary key or unique key constraint automatically creates a unique index to enforce the constraint. This index is dropped if the constraint is subsequently disabled, causing Oracle to rebuild the index every time the constraint is enabled. To avoid this behavior, create new primary key and unique key constraints initially disabled. Then create nonunique indexes or use existing nonunique indexes to enforce the constraints. "

Collapse
5: Response to OpenACS 4.0 Guidelines (aD questions) (response to 1)
Posted by Don Baccus on 05/11/01 06:15 PM
Hmmm...but the code isn't taking advantage of this.  The only place
the constraint is disabled then reenabled is when
acs-objects-create.sql is run.

I suppose someone may've thought it would be useful for cases where
you copy in tons of data directly into the objects table.  In this
case, though, the cost of dropping and rebuilding the index isn't
really that big a deal.

The code certainly doesn't hurt, but unless the ACS were to start
disabling and reenabling the unique constraint on a frequent basis it
doesn't help, either.

It may actually slow things down a bit.  When a unique index is used
to enforce the constraint, Oracle can throw a constraint error based
on whether or not the insertion into the index succeeds.  Using a
non-unique index to enforce the constraint means that the check has to
be done on the index after insertion.  Of course, the insertion code
may flag dupe entries anyway specifically to optimize this case.  But
then again it may not.

Collapse
6: Response to OpenACS 4.0 Guidelines (aD questions) (response to 1)
Posted by Ken Kennedy on 05/14/01 04:28 PM
Life is weird...I'm actually using this whole thread for a dataload at work today. Now I'm glad I brought it up! *grin*