Examining Your Data and Queries

To determine a fragmentation strategy, you must know how the data in a table is used. Take the following steps to gather information about a table that you might fragment.

To gather information about your table

Identify the queries that are critical to performance to determine if the queries are online transaction processing (OLTP) or decision-support system (DSS) queries.
Use the SET EXPLAIN statement to determine how the data is being accessed.
For information on the output of the SET EXPLAIN statement, see Query Plan Report. To determine how the data is accessed, you can sometimes simply review the SELECT statements along with the table schema.
Determine what portion of the data each query examines.
For example, if certain rows in the table are read most of the time, you can isolate them in a small fragment to reduce I/O contention for other fragments.
Determine which statements create temporary files.
Decision-support queries typically create and access large temporary files, and placement of temporary dbspaces can be critical to performance.
If particular tables are always joined together in a decision-support query, spread fragments for these tables across different disks.
Examine the columns in the table to determine which fragmentation scheme would keep each scan thread equally busy for the decision-support queries.
To see how the column values are distributed, create a distribution on the column with the UPDATE STATISTICS statement and examine the distribution with dbschema.
```
dbschema -d database -hd table
```