Here’s Help For A Huge Hardship

Multimillion-row tables are more and more common in IBM i shops these days. Querying those monsters can be a strain on the system. Fortunately, IBM gave us some help in DB2 for i 7.1.

The help comes in the form of a new wrinkle in indexing. The smart people who produce the world’s greatest relational database management system found a way to store aggregate (summary) information in encoded vector indexes. Here’s an example.

Assume a table (physical file) of sales history, such as this one:

create table Sales
  ( ID                integer as identity,
    Invoice           dec  (9,0),
    Line              dec  (3,0),
    InvoiceDate       date,
    Customer          dec  (7,0),
    Item              char (12),
    Quantity          dec  (3,0),
    Price             dec  (5,2),
  primary key (ID));

Suppose that users commonly want to know how many of each item was sold and how much money each item placed into the company bank account. You might use a query like this one:

select item,
       sum(quantity) as Qty,
       sum(quantity * price) as Extended
  from sales
 group by item
 order by 1

Each time the query runs, the system has to rebuild the summary figures.

You might improve performance by creating the following encoded vector index:

create encoded vector index SalesByItem
 on sales (item)
 include (sum(quantity), sum(quantity * price))

This index includes two summary figures for each item: the sum of the quantity and the sum of the quantity multiplied by the price. As rows are added to, deleted from, or changed in the table, the system adjusts the summary figures. When a query needs these figures, it can read them from the index instead of rebuilding them.

My thanks to Tom McKinley, of IBM, who reminded me of this new feature in his informative presentations at the recent RPG & DB2 Summit in Texas. I had forgotten that this feature existed.

RELATED STORY

Enter the Vector, Victor!

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot