DB2 Administrative SQL Cookbook: Column Selectivity by Table (BLU)

You may also like...

2 Responses

  1. Good point regarding using the number of columns referenced by a query to understand if using BLU may be a good fit for a workload! There is a small but important detail worth pointing out …

    Take for example the following query that reports sales for a 5-day period where a transaction was over 1000 dollars:
    select * from sales where saleDate > ’10/10/2015′ and saleDate 1000

    If the sales table has 90 columns, avg_cols_ref_per_query would return 90. However query processing in BLU will take advantage of late materialization by first only accessing the saleDate column, applying the date predicate followed by the same for the salePrice column and only for the surviving rows, the remaining 88 columns are accessed (a concept known as late materialization in the industry). Based on the selectivity of the predicates, the sales table may still be a good candidate for being column-organized despite pct_cols reporting 100% of columns are accessed. Another point for readers to consider.

    • Ember Crooks says:

      Agreed. This is by no means the only measure for BLU being successful. I’ve heard that queries that access all columns can run faster on BLU in a number of scenarios. This is just one factor to consider.

Leave a Reply

Your email address will not be published. Required fields are marked *