The optimizer might compute an incorrect cardinality estimate
for a join operation in an access plan for a SQL statement on
which a general statistical view is eligible, if multiple
overlapping column group statistics are collected on that
statview.
For example, consider the following query and general
statistical view defined on the web_sales star of a retail
product supplier business model described by the TPC decision
support benchmark:
-- query
select w_warehouse_name ,w_warehouse_sq_ft ,w_city ,w_county
,w_state, w_country, d_year,
avg(ws_sales_price* ws_quantity)
from tpcds.web_sales , tpcds.warehouse , tpcds.date_dim ,
tpcds.customer, tpcds.customer_address
where ws_warehouse_sk = w_warehouse_sk
and ws_sold_date_sk = d_date_sk
and ws_bill_customer_sk = c_customer_sk
and c_current_addr_sk = ca_address_sk
and d_year in (1998,1999,2000,2001,2002)
and ca_city = 'Lakeside' and ca_zip = '69532'
group by w_warehouse_name ,w_warehouse_sq_ft ,w_city ,w_county
,w_state , w_country ,d_year
-- statistical view
create view tpcds.ws_general_sv as
(select ws.*, d.*, i.*, sm.*, c.*, ca.*, p.*, t.*, cd.*, hd.*,
ib.*, w.*, wes.*
from tpcds.web_sales ws, tpcds.date_dim d, tpcds.item i,
tpcds.ship_mode sm,
tpcds.customer c, tpcds.customer_address ca, tpcds.promotion p,
tpcds.time_dim t, tpcds.customer_demographics cd,
tpcds.household_demographics hd, tpcds.income_band ib,
tpcds.warehouse w, tpcds.web_site wes
where ws.ws_sold_date_sk = d.d_date_sk and
ws.ws_item_sk = i.i_item_sk and
ws.ws_ship_mode_sk = sm.sm_ship_mode_sk and
ws.ws_bill_customer_sk=c.c_customer_sk and
c.c_current_addr_sk = ca.ca_address_sk and
ws.ws_promo_sk = p.p_promo_sk and
ws.ws_sold_time_sk = t.t_time_sk and
ws.ws_bill_cdemo_sk = cd.cd_demo_sk and
ws.ws_bill_hdemo_sk = hd.hd_demo_sk and
hd.hd_income_band_sk = ib.ib_income_band_sk and
ws.ws_warehouse_sk = w.w_warehouse_sk and
ws.ws_web_site_sk = wes.web_site_sk)
alter view tpcds.ws_general_sv enable query optimization
If the appropriate referential integrity constraints are defined
to allow the use of this general statistical view for the
example query, then the optimizer might compute an incorrect
cardinality estimate if the following statistics are collected
where the two column groups specify overlap:
runstats on view tpcds.ws_general_sv on all columns and columns
((d_year,ca_city,ca_zip), (ca_city,ca_zip)) with distribution
The incorrect cardinality estimate could result in the optimizer
producing a sub-optimal query access plan. |
