Fragmentation

In order to determine a fragmentation strategy, you must know how the data in a table is used. In a DSS environment, the best way to do this is to examine how the data is queried.

Before fragmentation can be undertaken a number of tasks must be completed

• The tables accessed in the critical SELECT statements must be identified. These are the key statements that must fully optmised for the system to performance to it's maximum.
  
  
• Which of the critical tables are regularly joined together. These tables should reside on separate disks.
  
  
• The statements that require temporary space must be identfied. Large temporary files are created in most Decision Support Queries. The placement of these temporary dbspaces is a critical factor for performance. Each temporary dbspace should reside on a separate disk and must be created via the onspaces -t command and must be listed in the DBSPACETEMP environment variable. The minimum configuration normal recommended is three temporary dbspaces.
  
  
• Once the critical statements have been determined the filter clauses used to give the required selectivity should be examined and how the data is acccessed. The result of SET EXPLAIN will show how the tables are actually accessed i.e. whole table or just a few rows, a sequential scan or an index read?
  
  
• Are the same rows returned time and time again? Fragmentation is able to create uneven data distributions using expression based fragmentation, not just even data distributions.