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.