Search

Top 60 Oracle Blogs

Recent comments

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 effect the compression.  Oracle can calculate the optimal prefix length using

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ANALYZE INDEX … VALIDATE STRUCTURE

I have written 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.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">                                          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 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.

Index DDL Overrides
Figure 1. Index DDL Overide

However, there is 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.

Build Settings
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 from the index that exists in the database.  Hence the DDL contains additional keywords not in the PeopleSoft DDL model.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">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 use of DBMS_METADATA was introduced in PeopleTools 8.51, so this problem has probably existed since then.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">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,

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">DROP INDEX ps_pstreenode
/

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

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">
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.

#eeeeee; border: 0px solid rgb(0, 0, 0); overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">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.