Home | Previous Page | Next Page   Planning Table and Index Fragmentation > Planning a Fragmentation Strategy >

Examining Your Data and Queries

To design the fragmentation strategy for all tables in a database, you must understand how the tables are used and the kinds of data that they contain.

The Table-Access Pattern

The table-access pattern for a database helps determine how individual tables should be fragmented and how the fragments should be placed on the disk system.

To create a table-access chart
  1. Print the schema of each table that transactions and queries access.
  2. Print the SQL code for a selection of representative queries and transactions. You might be able to print the SQL code for all major transactions executed by an OLTP client.
  3. Create a table that contains the number of rows in each table and estimated table size as well as the following information for each query:
    • Which tables are accessed by simple select statements
    • Which tables are updated by insert or delete statements
    • Which tables are updated by add statements
    • Which tables are joined

    The table might look like the following small sample.

    Table 1. Sample Table-Access Table
    Query/Transaction customers
    40M rows
    8 GB
    states
    50 rows
    1024 KB
    orders
    80M rows
    15 GB
    query 1 I J
    query 2 J J A
    query 3 S J J

    In the table, S stands for Select, D stands for Delete, I stands for Insert, A stands for Add, and J stands for join, based on a WHERE clause in the query.

    You might expand this simple table or create two more detailed tables. One detailed table might contain query information, such as a description of the query or the query SQL code, the filter columns, the frequency with which the query is run, and similar information. The second table might list detailed information about each table, including the columns most often used as filters and similar information that helps you determine fragmentation possibilities.

The sample information in Table 1 shows the following access pattern:

If you are planning a database, you might not have exact numbers for the table size and rows. If you know what columns each table will contain and approximately how many rows you will have initially, use the sizing calculators in Appendix A. Estimating Table and Index Size, to estimate table size. If you are migrating a database from a different database server, use the table and row size information from the original database.

The Data-Distribution Pattern

The number of different values and duplicate values in keys and join columns in a table also affects your fragmentation decision. If you are planning a database, estimate the number of values and duplicated keys or join column values in tables as well as you can.

If you are migrating a database, you might know or be able to find out the data distribution for table data.

To create and examine data distributions for an existing table
  1. Load the table into an Informix database.
  2. Run UPDATE STATISTICS HIGH or run UPDATE STATISTICS MEDIUM with a RESOLUTION clause such as RESOLUTION 0.05 0.98 to create data-distribution information.
  3. Run dbschema -d database -hd tablename to examine the data-distribution information for each column in the table, where database is the database name and tablename is the table name.

Data-distribution information lets you see how fragments would be balanced if you used an expression fragmentation scheme based on any column in the table.

Using Data-Distribution Pattern Information

After you have evaluated the table access pattern and data distribution, you can begin to decide on table fragmentation and fragmentation schemes. Consider the following issues:

Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]