[Deploying Sakai] anyone using Lesson Builder in Oracle; please check your indices

Charles Hedrick hedrick at rutgers.edu
Thu Dec 1 08:42:20 PST 2011

For anyone using recent versions of Lesson Builder on Oracle;

Some problems turned up with indices. Oracle doesn't allow names longer than 31 characters. That meant that the script creating indices would fail. If you weren't watching carefully you wouldn't notice and of course performance problems wouldn't show up until the tables start getting large.

It's hard to know exactly how to fix this up, since I can't tell the state of your indices now. The file components/src/ddl/oracle/simplepage.sql will create the current indices. To keep the names reasonable, I've changed the names from lessonbuilder_* to lb_*. The script components/src/ddl/oracle/convert-index-names.sql will convert old names to new ones, but of course it will fail on any indices that don't currently exist.

Because hibernate doesn't seem to create indices automatically, schema maintainenace is split for Lesson Builder.

* I depend upon hibernate to create and update tables. It seems to do that fairly well, although it won't necessarily change definitions of fields if the field already exists. Fortunately that hasn't been an issue in production, though developers need to be aware of it.

* Hibernate doesn't reliably create indices, so those are done by the script components/src/ddl/oracle/simplepage.sql. That script is run at startup, by a call that aborts if there is an error in the first line. I add new indices at the beginning of the script. So it should run once each time there's a change. Thereafter the first line will produce "index already present" and the file will be aborted.

Of course you will want auto.ddl to be on when you install Lesson Builder or move to a new version. Otherwise the changes won't happen.

More information about the production mailing list