Search

Top 60 Oracle Blogs

Recent comments

DBMS_METADATA

DDL gets my goat

First things first. If you are not familiar with the term “gets my goat” then you can find a definition here Smile. But put simply, it means things that you find frustrating or bothersome.

What is getting my goat today is the time it takes to get the DDL for a database object using the DBMS_METADATA package. It always seem slow to me. Now I must admit, this is very much me exhibiting “never happy” syndrome, because before DBMS_METADATA came along, generating DDL from the database was an absolute pain in the proverbial. Your choices were either:

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