Results 1 to 4 of 4
  1. #1
    Join Date
    May 2003
    Location
    San Juan, PR
    Posts
    18

    Unanswered: index for a range of values

    DB2 OS/390 V7

    I want to create an index whose entries correspond just a selected values (range) of one of the column used to define the index.
    In my case, I have the tbl ORDER, with the column ORDER_ST (status). The values for status are: 1=Pending, 2=In progress, 3=Delivered.
    Frequenly, I want to retrieve all rows pending or in progress (less than 10% of total of tbl's rows).
    What is the better way to solve this requirement?

    Thanks, ALOZ.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The main consideration is whether DB2 will even use such an index. DB2 will use an index if it can reduce the number of pages (typically 4K in size) that need to be accessed.

    If the values for ORDER_ST are randomly distributed throughout the table, and the row size is 400 bytes or less, then on average there is at least one qualifying row on each 4K page (since you said pending and in progress orders are 10% of the total). DB2 will not likely use an index on ORDER_ST in this situation. This scenario is more likely if the table is clustered by customer number, or some other similar column.

    However, if the pending and in progress orders tend to be physically at the end of the table (cluster ratio of index on ORDER_ST is relatively high), then DB2 would be more inclined to use the index since it can reduce the number of 4K pages accessed. If the table was physical clustered by an index on ORDER, and ORDER was based on a next available number (or append was used to insert new rows), the cluster ratio for ORDER_ST might be coincidently high even though it is not the clustering index because more recent orders tend to be the ones not shipped yet. You can check the cluster ratio of the index in the catalog after running runstats.

    For queries, I would probably try “ORDER_ST < ‘3’ ” as the predicate instead of “ORDER_ST in (‘1’,’2’)”.

    Make sure you capture detail runstats on column distribution (otherwise DB2 will assume each of the three values occupies 33.3% of the table). For example:

    RUNSTATS INDEX (index-qualifier.index-name)
    KEYCARD
    FREQVAL NUMCOLS 1 COUNT 3

    Using the explain will tell you whether the index on ORDER_ST will be used for the SQL statements in your application. But if in spite of all your attempts to make adjustments, the index is never used, you would be better off just dropping the index.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2003
    Posts
    6
    Depending upon the table size and key structures, you might want to consider spliting up the table into (3) partitions based on order_status, that way you can address rows for each order_status separately going by partition.

  4. #4
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    Originally posted by cdhebar
    Depending upon the table size and key structures, you might want to consider spliting up the table into (3) partitions based on order_status, that way you can address rows for each order_status separately going by partition.
    ...or you can go even further. Create 3 tables and then create a view containing UNION, that links them together! Good thing is, that you can have very different indexes in each 'partition' this way.

    Cheers, Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •