Implementing Index Compression (and other Physical Storage Options) via Application Designer

There are some performance improvements that require physical storage options to be set on tables or indexes.
One particular technique that I will take as an example for this article is index compression.  A good example in PeopleSoft is the tree node table, PSTREENODE.  It drives many security and hierarchical queries.  It is not updated very frequently, only as new trees are brought on.  Many of the indexes are good candidates for compression.
This compression works by storing repeated column values only one per index leaf block.  Each distinct set of values in the columns up to the prefix length are stored in a symbol table.  The choice of prefix length can significantly affect compression.  Oracle can calculate the optimal prefix length using

ANALYZE INDEX … VALIDATE STRUCTURE

I have written this script to make it slightly easier, calc_opt_comp.sql.  This is the output on my demo database, but I get similar results on production systems.

                                          Optimal
                                      Compression                       Weighted
                                           Prefix               Current  Average
Table Name         Index Name              Length FREQ PARTS     Blocks Saving %
------------------ ------------------ ----------- ---- ----- ---------- --------
PSTREENODE         PSAPSTREENODE                4    1     0        280     39.0
                   PSBPSTREENODE                3    1     0        264     30.0
                   PSCPSTREENODE                1    1     0        120      7.0
                   PSDPSTREENODE                4    1     0        256     61.0
                   PSFPSTREENODE                2    1     0        256     67.0
                   PSGPSTREENODE                3    1     0        400     49.0
                   PS_PSTREENODE                4    1     0        256     44.0 

However, I want to make sure that should the table need to be rebuilt in the future, PeopleTools will generate the DDL with the appropriate settings.  The same principle would also apply to any other physical storage option.  I would always recommend that the compression prefix lengths be incorporated into the PeopleTools DDL override in Application Designer (figure 1).  While you could extend the DDL model and add another override for compression, I just append it to the PCTFREE setting.

Figure 1. Index DDL Override

However, there is a catch.  PeopleTools has never examined DDL overrides when determining whether there is a difference between the PeopleSoft and database data dictionaries, even though that comparison must be platform-specific.  DDL overrides and DDL models are just strings held in the PeopleTools tables.  They can be extended (or even removed) by customers.  I assume this is the reason; it was not felt possible to reliably check them,
So, if the build settings (figure 2) are 'recreate index only if modified', which is the default, Application Designer will not generate a DDL script, nor execute any DDL.

Figure 2. Build Settings

The workaround has always been to set the index creation option in the build settings to 'recreate index if it already exists'.  However, we then discover the override doesn't appear in the DDL.  As Application Designer has not detected a difference between PeopleTools and the database, it has instead used the Oracle DBMS_METADATA package to generate the storage clause from the index that exists in the database.  Hence the DDL contains additional keywords, not in the PeopleSoft DDL model.

CREATE UNIQUE  INDEX PS_PSTREENODE ON PSTREENODE (SETID,
   SETCNTRLVALUE,
   TREE_NAME,
   EFFDT,
   TREE_NODE_NUM,
   TREE_NODE,
   TREE_BRANCH) 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSINDEX"
/
ALTER INDEX PS_PSTREENODE NOPARALLEL LOGGING
/

I have only checked this behaviour on PeopleTools 8.54, but the use of DBMS_METADATA was introduced in PeopleTools 8.51, so this problem has probably existed since then.

SELECT dbms_metadata.get_ddl('INDEX','PS_PSTREENODE')
FROM dual

DBMS_METADATA.GET_DDL('INDEX','PS_PSTREENODE')
--------------------------------------------------------------------------------
  CREATE UNIQUE INDEX "SYSADM"."PS_PSTREENODE" ON "SYSADM"."PSTREENODE" ("SETID"
, "SETCNTRLVALUE", "TREE_NAME", "EFFDT", "TREE_NODE_NUM", "TREE_NODE", "TREE_BRANCH")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "PSINDEX"

However, if I drop the index and then regenerate the DDL script in Application Designer,

DROP INDEX ps_pstreenode
/

PeopleTools generates the create index with the compression specified in the PeopleTools table.

…
CREATE UNIQUE  INDEX PS_PSTREENODE ON PSTREENODE (SETID,
   SETCNTRLVALUE,
   TREE_NAME,
   EFFDT,
   TREE_NODE_NUM,
   TREE_NODE,
   TREE_BRANCH) TABLESPACE PSINDEX STORAGE (INITIAL 40000 NEXT 100000
 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 0 COMPRESS 4 PARALLEL
 NOLOGGING
/
ALTER INDEX PS_PSTREENODE NOPARALLEL LOGGING
/
…

Rather than go through the business of dropping the index so you can then generate the correct script to then recreate the index, I would suggest just implementing the change manually by rebuilding the indexes.

ALTER INDEX PSAPSTREENODE REBUILD TABLESPACE PSINDEX COMPRESS 4;
ALTER INDEX PSBPSTREENODE REBUILD TABLESPACE PSINDEX COMPRESS 3;
ALTER INDEX PSCPSTREENODE REBUILD TABLESPACE PSINDEX COMPRESS 1;
ALTER INDEX PSDPSTREENODE REBUILD TABLESPACE PSINDEX COMPRESS 4;
ALTER INDEX PSFPSTREENODE REBUILD TABLESPACE PSINDEX COMPRESS 2;
ALTER INDEX PSGPSTREENODE REBUILD TABLESPACE PSINDEX COMPRESS 3;
ALTER INDEX PS_PSTREENODE REBUILD TABLESPACE PSINDEX COMPRESS 4;

Conclusion

This makes the business of implementing physical attributes through Application Designer much more complicated.  I would still recommend recording the settings in Application Designer, if only because it provides documentation, but then it may be easier to implement the changes manually.