Here’s Help For A Huge Hardship
April 16, 2014 Ted Holt
|
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
|
