Oracle index compression and PeopleTools

[blog note: PowerShell has dropped to the bottom of my priority list for now. I hope to get back to it eventually. In the meantime, here's a PeopleSoft-related post that has nothing to do with PowerShell.]

I've been having great success with using Oracle's index compression with our PeopleSoft database. It saves a lot of space, and speeds up index range scans. The problem is that it's not supported by PeopleTools.

It's not appropriate for every index, but that's beyond the scope of this discussion. For some background on Oracle index compression, Jonathan Lewis describes the basics here, and Richard Foote goes into detail here, here, here, and here.

When an index is built or rebuilt with the PeopleTools App Designer, it uses the DDL [data definition language] model for your particular platform. In the case of Oracle, you can have default parameters like initial and max extents that you can override per index. Compression isn't one of those parameters, so I've been compressing selected indexes outside of PeopleTools. Usually, I'll just do an "alter index ps_some_huge_table rebuild compress X" with X being some number of columns.

This is not at all optimal. It's not reflected in the PeopleTools metadata, so if anybody does a build with PeopleTools, the compression is lost. In the case of an application upgrade, this is likely to undo compression on a large number of indexes.

What I'd like to do is modify the DDL model SQL, have the default be no compression, and override the compression for those indexes that I've already compressed. For future index compression, I can just use PeopleTools from the get-go.

You can change the DDL model SQL in PeopleTools/Utilities/Administration/DDL Model Defaults.

Here's the problem I've run into: the delivered DDL Model SQL for a create index command for Oracle looks like this, with anything in double-asterisks being a parameter that's either defaulted or overridden:

CREATE [UNIQUE] **BITMAP** INDEX [IDXNAME] ON [TBNAME] ([IDXCOLLIST]) TABLESPACE **INDEXSPC** STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PARALLEL NOLOGGING;

I've modified it to look like this:

CREATE [UNIQUE] **BITMAP** INDEX [IDXNAME] ON [TBNAME] ([IDXCOLLIST]) TABLESPACE **INDEXSPC** STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PARALLEL NOLOGGING COMPRESS **COMPRESS**;

and set up an additional parameter COMPRESS with a default value of 0. I then go into a record definition for some table with an index I want to compress and override that default value with 3. I generate a CREATE INDEX script, and everything looks good -- the index with the overridden COMPRESS parameter says COMPRESS 3, and the other indexes say COMPRESS 0.

Alas, COMPRESS 0 is not legal syntax, at least in Oracle 10.2. The syntax for no compression is either NOCOMPRESS or just no compress clause at all. I can't have the default be blank, since COMPRESS on its own with no integer means compress every column in a non-unique index and compress all but the last in a unique index.

As I'm typing this, another idea occurs to me. Back in a bit... [type type type test test test...]

OK! That's better. Instead of using the parameter just as the number of columns, with the default being 0, I made the default value "NOCOMPRESS" and the override now has to be "COMPRESS X" with X being some positive integer. I no longer include the keyword "COMPRESS" in the model DDL; I just include the parameter value itself. So now the model SQL is this:

CREATE [UNIQUE] **BITMAP** INDEX [IDXNAME] ON [TBNAME] ([IDXCOLLIST]) TABLESPACE **INDEXSPC** STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PARALLEL NOLOGGING **COMPRESS**;

One oddity: I had to exit and restart the app designer to get it to use the new default value.

I have it only in a development database. I'll have to think, read, and tinker a bit before I put it in production. Upgrades are by far my biggest worry.

UPDATE: I heard from a couple of PeopleSoft experts, by email and in the comments, and there's an even simpler way to achieve this. It's based on the fact that PeopleTools does no validation on the model SQL or parameters. One can just piggyback the compress syntax onto one of the existing parameters. Says David Kurtz:

I tend to put the COMPRESS keyword and value into the PCTFREE DDL override. These are just strings that are used to build up the DDL command. The PCTFREE override defaults to "10". I might, for example, set it to "10 COMPRESS 1".

The advantage is that when upgrade time comes, and the DDL script runs (in this case, DDLORA.DMS), nothing is lost. I don't have to remember to redo the custom model SQL.

UPDATE: David Kurtz discusses this topic on his own blog.