Examining your data and queries

To determine a fragmentation strategy, you must gather information about the table that you might fragment. You must also know how the data in the table is used.

Procedure

To gather information about your table:

  1. Identify the queries that are critical to performance to determine if the queries are online transaction processing (OLTP) or decision-support system (DSS) queries.
  2. Use the SET EXPLAIN statement to determine how the data is being accessed.

    For information about the output of the SET EXPLAIN statement, see Report that shows the query plan chosen by the optimizer. To determine how the data is accessed, you can sometimes simply review the SELECT statements along with the table schema.

  3. 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.

  4. 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.

  5. If particular tables are always joined together in a decision-support query, spread fragments for these tables across different disks.
  6. 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