Here’s More Help For A Huge Hardship

June 25, 2014 Ted Holt

In the April 16, 2014, issue of this august publication, I shared how encoded vector indexes can help with the task of summarizing data from large tables. Tom McKinley of IBM graciously emailed me with more information about that technique, and it is my pleasure to pass Tom’s wisdom along to you.

Assume two tables–one of items, one of sales.

create table Items
  ( Item        char (12) not null,
    Description char (25),
    Cost        dec  (7,2),
    Price       dec  (7,2),
  primary key (Item))

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))

Here’s what Tom had to say:

“If a query summarizes by a column in another table, you can exploit this include technology by creating the EVI over the join column in the table that has the measures you want to summarize. The query below groups by a column in the Items table but sums the sale amount in the Sales table. The implementation ends up joining the Items file to the SalesByItem symbol table, not to the big Sales table.”

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

select i.item, sum(s.quantity * s.price)
  from Items as i
  join Sales as s
    on i.item = s.item
 group by i.item
 order by 2 desc

Tom adds, “I think that this capability has huge potential that is not being fully exploited.”

I have to agree, Tom.

Certainly I–and probably a lot of other IBM i professionals as well–need to become experts in the use of encoded vector indexes.

Here’s Help For A Huge Hardship

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