http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query90.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/query90.q b/ql/src/test/queries/clientpositive/perf/query90.q new file mode 100644 index 0000000..515da06 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/query90.q @@ -0,0 +1,2 @@ +set hive.mapred.mode=nonstrict; +explain select cast(amc as decimal(15,4))/cast(pmc as decimal(15,4)) am_pm_ratio from ( select count(*) amc from web_sales, household_demographics , time_dim, web_page where ws_sold_time_sk = time_dim.t_time_sk and ws_ship_hdemo_sk = household_demographics.hd_demo_sk and ws_web_page_sk = web_page.wp_web_page_sk and time_dim.t_hour between 6 and 6+1 and household_demographics.hd_dep_count = 8 and web_page.wp_char_count between 5000 and 5200) at, ( select count(*) pmc from web_sales, household_demographics , time_dim, web_page where ws_sold_time_sk = time_dim.t_time_sk and ws_ship_hdemo_sk = household_demographics.hd_demo_sk and ws_web_page_sk = web_page.wp_web_page_sk and time_dim.t_hour between 14 and 14+1 and household_demographics.hd_dep_count = 8 and web_page.wp_char_count between 5000 and 5200) pt order by am_pm_ratio limit 100;
http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query91.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/query91.q b/ql/src/test/queries/clientpositive/perf/query91.q new file mode 100644 index 0000000..9458c0c --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/query91.q @@ -0,0 +1,2 @@ +set hive.mapred.mode=nonstrict; +explain select cc_call_center_id Call_Center, cc_name Call_Center_Name, cc_manager Manager, sum(cr_net_loss) Returns_Loss from call_center, catalog_returns, date_dim, customer, customer_address, customer_demographics, household_demographics where catalog_returns.cr_call_center_sk = call_center.cc_call_center_sk and catalog_returns.cr_returned_date_sk = date_dim.d_date_sk and catalog_returns.cr_returning_customer_sk= customer.c_customer_sk and customer_demographics.cd_demo_sk = customer.c_current_cdemo_sk and household_demographics.hd_demo_sk = customer.c_current_hdemo_sk and customer_address.ca_address_sk = customer.c_current_addr_sk and d_year = 1999 and d_moy = 11 and ( (cd_marital_status = 'M' and cd_education_status = 'Unknown') or(cd_marital_status = 'W' and cd_education_status = 'Advanced Degree')) and hd_buy_potential like '0-500%' and ca_gmt_offset = -7 group by cc_call_center_id,cc_name,cc_manager,cd_marital_status,cd_education_status order by Returns_Loss desc; http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query92.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/query92.q b/ql/src/test/queries/clientpositive/perf/query92.q new file mode 100644 index 0000000..625e99f --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/query92.q @@ -0,0 +1 @@ +explain SELECT sum(case when ssci.customer_sk is not null and csci.customer_sk is null then 1 else 0 end) as store_only, sum(case when ssci.customer_sk is null and csci.customer_sk is not null then 1 else 0 end) as catalog_only, sum(case when ssci.customer_sk is not null and csci.customer_sk is not null then 1 else 0 end) as store_and_catalog FROM (SELECT ss.ss_customer_sk as customer_sk, ss.ss_item_sk as item_sk FROM store_sales ss JOIN date_dim d1 ON (ss.ss_sold_date_sk = d1.d_date_sk) WHERE d1.d_month_seq >= 1206 and d1.d_month_seq <= 1217 GROUP BY ss.ss_customer_sk, ss.ss_item_sk) ssci FULL OUTER JOIN (SELECT cs.cs_bill_customer_sk as customer_sk, cs.cs_item_sk as item_sk FROM catalog_sales cs JOIN date_dim d2 ON (cs.cs_sold_date_sk = d2.d_date_sk) WHERE d2.d_month_seq >= 1206 and d2.d_month_seq <= 1217 GROUP BY cs.cs_bill_customer_sk, cs.cs_item_sk) csci ON (ssci.customer_sk=csci.customer_sk and ssci.item_sk = csci.item_sk); http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query93.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/query93.q b/ql/src/test/queries/clientpositive/perf/query93.q new file mode 100644 index 0000000..b60b041 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/query93.q @@ -0,0 +1 @@ +explain select ss_customer_sk ,sum(act_sales) sumsales from (select ss_item_sk ,ss_ticket_number ,ss_customer_sk ,case when sr_return_quantity is not null then (ss_quantity-sr_return_quantity)*ss_sales_price else (ss_quantity*ss_sales_price) end act_sales from store_sales left outer join store_returns on (store_returns.sr_item_sk = store_sales.ss_item_sk and store_returns.sr_ticket_number = store_sales.ss_ticket_number) ,reason where store_returns.sr_reason_sk = reason.r_reason_sk and r_reason_desc = 'Did not like the warranty') t group by ss_customer_sk order by sumsales, ss_customer_sk limit 100; http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query94.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/query94.q b/ql/src/test/queries/clientpositive/perf/query94.q new file mode 100644 index 0000000..f9f4bc1 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/query94.q @@ -0,0 +1 @@ +explain SELECT count(distinct ws_order_number) as order_count, sum(ws_ext_ship_cost) as total_shipping_cost, sum(ws_net_profit) as total_net_profit FROM web_sales ws1 JOIN customer_address ca ON (ws1.ws_ship_addr_sk = ca.ca_address_sk) JOIN web_site s ON (ws1.ws_web_site_sk = s.web_site_sk) JOIN date_dim d ON (ws1.ws_ship_date_sk = d.d_date_sk) LEFT SEMI JOIN (SELECT ws2.ws_order_number as ws_order_number FROM web_sales ws2 JOIN web_sales ws3 ON (ws2.ws_order_number = ws3.ws_order_number) WHERE ws2.ws_warehouse_sk <> ws3.ws_warehouse_sk ) ws_wh1 ON (ws1.ws_order_number = ws_wh1.ws_order_number) LEFT OUTER JOIN web_returns wr1 ON (ws1.ws_order_number = wr1.wr_order_number) WHERE d.d_date between '1999-05-01' and '1999-07-01' and ca.ca_state = 'TX' and s.web_company_name = 'pri' and wr1.wr_order_number is null limit 100; http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query95.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/query95.q b/ql/src/test/queries/clientpositive/perf/query95.q new file mode 100644 index 0000000..fbd2d47 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/query95.q @@ -0,0 +1 @@ +explain SELECT count(distinct ws1.ws_order_number) as order_count, sum(ws1.ws_ext_ship_cost) as total_shipping_cost, sum(ws1.ws_net_profit) as total_net_profit FROM web_sales ws1 JOIN customer_address ca ON (ws1.ws_ship_addr_sk = ca.ca_address_sk) JOIN web_site s ON (ws1.ws_web_site_sk = s.web_site_sk) JOIN date_dim d ON (ws1.ws_ship_date_sk = d.d_date_sk) LEFT SEMI JOIN (SELECT ws2.ws_order_number as ws_order_number FROM web_sales ws2 JOIN web_sales ws3 ON (ws2.ws_order_number = ws3.ws_order_number) WHERE ws2.ws_warehouse_sk <> ws3.ws_warehouse_sk ) ws_wh1 ON (ws1.ws_order_number = ws_wh1.ws_order_number) LEFT SEMI JOIN (SELECT wr_order_number FROM web_returns wr JOIN (SELECT ws4.ws_order_number as ws_order_number FROM web_sales ws4 JOIN web_sales ws5 ON (ws4.ws_order_number = ws5.ws_order_number) WHERE ws4.ws_warehouse_sk <> ws5.ws_warehouse_sk ) ws_wh2 ON (wr.wr_order_number = ws_wh2.ws_order_number)) tmp1 ON (ws1.ws_order_number = tmp1.wr_order_number) WHERE d.d_date between '20 02-05-01' and '2002-06-30' and ca.ca_state = 'GA' and s.web_company_name = 'pri'; http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query96.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/query96.q b/ql/src/test/queries/clientpositive/perf/query96.q new file mode 100644 index 0000000..29265ed --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/query96.q @@ -0,0 +1 @@ +explain select count(*) as c from store_sales ,household_demographics ,time_dim, store where store_sales.ss_sold_time_sk = time_dim.t_time_sk and store_sales.ss_hdemo_sk = household_demographics.hd_demo_sk and store_sales.ss_store_sk = store.s_store_sk and time_dim.t_hour = 8 and time_dim.t_minute >= 30 and household_demographics.hd_dep_count = 5 and store.s_store_name = 'ese' order by c limit 100; http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query97.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/query97.q b/ql/src/test/queries/clientpositive/perf/query97.q new file mode 100644 index 0000000..4995309 --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/query97.q @@ -0,0 +1 @@ +explain select sum(case when ssci.customer_sk is not null and csci.customer_sk is null then 1 else 0 end) store_only ,sum(case when ssci.customer_sk is null and csci.customer_sk is not null then 1 else 0 end) catalog_only ,sum(case when ssci.customer_sk is not null and csci.customer_sk is not null then 1 else 0 end) store_and_catalog from ( select ss_customer_sk customer_sk ,ss_item_sk item_sk from store_sales JOIN date_dim ON store_sales.ss_sold_date_sk = date_dim.d_date_sk where d_month_seq between 1193 and 1193 + 11 group by ss_customer_sk ,ss_item_sk) ssci full outer join ( select cs_bill_customer_sk customer_sk ,cs_item_sk item_sk from catalog_sales JOIN date_dim ON catalog_sales.cs_sold_date_sk = date_dim.d_date_sk where d_month_seq between 1193 and 1193 + 11 group by cs_bill_customer_sk ,cs_item_sk) csci on (ssci.customer_sk=csci.customer_sk and ssci.item_sk = csci.item_sk) limit 100; http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/queries/clientpositive/perf/query98.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/perf/query98.q b/ql/src/test/queries/clientpositive/perf/query98.q new file mode 100644 index 0000000..930b7ef --- /dev/null +++ b/ql/src/test/queries/clientpositive/perf/query98.q @@ -0,0 +1,2 @@ +set hive.mapred.mode=nonstrict; +explain select i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(ss_ext_sales_price) as itemrevenue ,sum(ss_ext_sales_price)*100/sum(sum(ss_ext_sales_price)) over (partition by i_class) as revenueratio from store_sales ,item ,date_dim where store_sales.ss_item_sk = item.i_item_sk and i_category in ('Jewelry', 'Sports', 'Books') and store_sales.ss_sold_date_sk = date_dim.d_date_sk and d_date between cast('2001-01-12' as date) and (cast('2001-02-11' as date)) group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio; http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/results/clientpositive/perf/query12.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query12.q.out b/ql/src/test/results/clientpositive/perf/query12.q.out new file mode 100644 index 0000000..648d64d --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/query12.q.out @@ -0,0 +1,129 @@ +PREHOOK: query: explain select i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(ws_ext_sales_price) as itemrevenue ,sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) over (partition by i_class) as revenueratio from web_sales ,item ,date_dim where web_sales.ws_item_sk = item.i_item_sk and item.i_category in ('Jewelry', 'Sports', 'Books') and web_sales.ws_sold_date_sk = date_dim.d_date_sk and date_dim.d_date between '2001-01-12' and '2001-02-11' group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio limit 100 +PREHOOK: type: QUERY +POSTHOOK: query: explain select i_item_desc ,i_category ,i_class ,i_current_price ,i_item_id ,sum(ws_ext_sales_price) as itemrevenue ,sum(ws_ext_sales_price)*100/sum(sum(ws_ext_sales_price)) over (partition by i_class) as revenueratio from web_sales ,item ,date_dim where web_sales.ws_item_sk = item.i_item_sk and item.i_category in ('Jewelry', 'Sports', 'Books') and web_sales.ws_sold_date_sk = date_dim.d_date_sk and date_dim.d_date between '2001-01-12' and '2001-02-11' group by i_item_id ,i_item_desc ,i_category ,i_class ,i_current_price order by i_category ,i_class ,i_item_id ,i_item_desc ,revenueratio limit 100 +POSTHOOK: type: QUERY +Plan not optimized by CBO. + +Vertex dependency in root stage +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE) +Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Reducer 4 (SIMPLE_EDGE) +Reducer 6 <- Reducer 5 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 6 + File Output Operator [FS_26] + compressed:false + Statistics:Num rows: 100 Data size: 143600 Basic stats: COMPLETE Column stats: NONE + table:{"input format:":"org.apache.hadoop.mapred.TextInputFormat","output format:":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat","serde:":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"} + Limit [LIM_25] + Number of rows:100 + Statistics:Num rows: 100 Data size: 143600 Basic stats: COMPLETE Column stats: NONE + Select Operator [SEL_24] + | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"] + | Statistics:Num rows: 34938 Data size: 50180683 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 5 [SIMPLE_EDGE] + Reduce Output Operator [RS_23] + key expressions:_col1 (type: string), _col2 (type: string), _col4 (type: string), _col0 (type: string), _col6 (type: decimal(38,23)) + sort order:+++++ + Statistics:Num rows: 34938 Data size: 50180683 Basic stats: COMPLETE Column stats: NONE + value expressions:_col3 (type: decimal(7,2)), _col5 (type: decimal(17,2)) + Select Operator [SEL_21] + outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6"] + Statistics:Num rows: 34938 Data size: 50180683 Basic stats: COMPLETE Column stats: NONE + PTF Operator [PTF_20] + Function definitions:[{"Input definition":{"type:":"WINDOWING"}},{"name:":"windowingtablefunction","order by:":"_col3","partition by:":"_col3"}] + Statistics:Num rows: 34938 Data size: 50180683 Basic stats: COMPLETE Column stats: NONE + Select Operator [SEL_19] + | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5"] + | Statistics:Num rows: 34938 Data size: 50180683 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 4 [SIMPLE_EDGE] + Reduce Output Operator [RS_18] + key expressions:_col3 (type: string) + Map-reduce partition columns:_col3 (type: string) + sort order:+ + Statistics:Num rows: 34938 Data size: 50180683 Basic stats: COMPLETE Column stats: NONE + value expressions:_col0 (type: string), _col1 (type: string), _col2 (type: string), _col4 (type: decimal(7,2)), _col5 (type: decimal(17,2)) + Group By Operator [GBY_17] + | aggregations:["sum(VALUE._col0)"] + | keys:KEY._col0 (type: string), KEY._col1 (type: string), KEY._col2 (type: string), KEY._col3 (type: string), KEY._col4 (type: decimal(7,2)) + | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5"] + | Statistics:Num rows: 34938 Data size: 50180683 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 3 [SIMPLE_EDGE] + Reduce Output Operator [RS_16] + key expressions:_col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: decimal(7,2)) + Map-reduce partition columns:_col0 (type: string), _col1 (type: string), _col2 (type: string), _col3 (type: string), _col4 (type: decimal(7,2)) + sort order:+++++ + Statistics:Num rows: 69877 Data size: 100362804 Basic stats: COMPLETE Column stats: NONE + value expressions:_col5 (type: decimal(17,2)) + Group By Operator [GBY_15] + aggregations:["sum(_col23)"] + keys:_col38 (type: string), _col41 (type: string), _col49 (type: string), _col47 (type: string), _col42 (type: decimal(7,2)) + outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5"] + Statistics:Num rows: 69877 Data size: 100362804 Basic stats: COMPLETE Column stats: NONE + Select Operator [SEL_14] + outputColumnNames:["_col38","_col41","_col49","_col47","_col42","_col23"] + Statistics:Num rows: 69877 Data size: 100362804 Basic stats: COMPLETE Column stats: NONE + Filter Operator [FIL_32] + predicate:((_col3 = _col37) and (_col0 = _col62)) (type: boolean) + Statistics:Num rows: 69877 Data size: 100362804 Basic stats: COMPLETE Column stats: NONE + Merge Join Operator [MERGEJOIN_37] + | condition map:[{"":"Inner Join 0 to 1"}] + | keys:{"0":"_col0 (type: int)","1":"d_date_sk (type: int)"} + | outputColumnNames:["_col0","_col3","_col23","_col37","_col38","_col41","_col42","_col47","_col49","_col62"] + | Statistics:Num rows: 279510 Data size: 401454092 Basic stats: COMPLETE Column stats: NONE + |<-Map 8 [SIMPLE_EDGE] + | Reduce Output Operator [RS_11] + | key expressions:d_date_sk (type: int) + | Map-reduce partition columns:d_date_sk (type: int) + | sort order:+ + | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_35] + | predicate:(d_date_sk is not null and d_date BETWEEN '2001-01-12' AND '2001-02-11') (type: boolean) + | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_2] + | alias:date_dim + | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 2 [SIMPLE_EDGE] + Reduce Output Operator [RS_9] + key expressions:_col0 (type: int) + Map-reduce partition columns:_col0 (type: int) + sort order:+ + Statistics:Num rows: 254100 Data size: 364958258 Basic stats: COMPLETE Column stats: NONE + value expressions:_col3 (type: int), _col23 (type: decimal(7,2)), _col37 (type: int), _col38 (type: string), _col41 (type: string), _col42 (type: decimal(7,2)), _col47 (type: string), _col49 (type: string) + Merge Join Operator [MERGEJOIN_36] + | condition map:[{"":"Inner Join 0 to 1"}] + | keys:{"0":"ws_item_sk (type: int)","1":"i_item_sk (type: int)"} + | outputColumnNames:["_col0","_col3","_col23","_col37","_col38","_col41","_col42","_col47","_col49"] + | Statistics:Num rows: 254100 Data size: 364958258 Basic stats: COMPLETE Column stats: NONE + |<-Map 1 [SIMPLE_EDGE] + | Reduce Output Operator [RS_4] + | key expressions:ws_item_sk (type: int) + | Map-reduce partition columns:ws_item_sk (type: int) + | sort order:+ + | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | value expressions:ws_sold_date_sk (type: int), ws_ext_sales_price (type: decimal(7,2)) + | Filter Operator [FIL_33] + | predicate:(ws_item_sk is not null and ws_sold_date_sk is not null) (type: boolean) + | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | TableScan [TS_0] + | alias:web_sales + | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + |<-Map 7 [SIMPLE_EDGE] + Reduce Output Operator [RS_6] + key expressions:i_item_sk (type: int) + Map-reduce partition columns:i_item_sk (type: int) + sort order:+ + Statistics:Num rows: 231000 Data size: 331780228 Basic stats: COMPLETE Column stats: NONE + value expressions:i_item_id (type: string), i_item_desc (type: string), i_current_price (type: decimal(7,2)), i_class (type: string), i_category (type: string) + Filter Operator [FIL_34] + predicate:(i_item_sk is not null and (i_category) IN ('Jewelry', 'Sports', 'Books')) (type: boolean) + Statistics:Num rows: 231000 Data size: 331780228 Basic stats: COMPLETE Column stats: NONE + TableScan [TS_1] + alias:item + Statistics:Num rows: 462000 Data size: 663560457 Basic stats: COMPLETE Column stats: NONE + http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/results/clientpositive/perf/query13.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query13.q.out b/ql/src/test/results/clientpositive/perf/query13.q.out new file mode 100644 index 0000000..0e59b0d --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/query13.q.out @@ -0,0 +1,300 @@ +PREHOOK: query: EXPLAIN SELECT Avg(ss_quantity) , + Avg(ss_ext_sales_price) , + Avg(ss_ext_wholesale_cost) , + Sum(ss_ext_wholesale_cost) +FROM store_sales , + store , + customer_demographics , + household_demographics , + customer_address , + date_dim +WHERE store.s_store_sk = store_sales.ss_store_sk +AND store_sales.ss_sold_date_sk = date_dim.d_date_sk +AND date_dim.d_year = 2001 +AND (( + store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk + AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk + AND customer_demographics.cd_marital_status = 'M' + AND customer_demographics.cd_education_status = '4 yr Degree' + AND store_sales.ss_sales_price BETWEEN 100.00 AND 150.00 + AND household_demographics.hd_dep_count = 3 ) + OR ( + store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk + AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk + AND customer_demographics.cd_marital_status = 'D' + AND customer_demographics.cd_education_status = 'Primary' + AND store_sales.ss_sales_price BETWEEN 50.00 AND 100.00 + AND household_demographics.hd_dep_count = 1 ) + OR ( + store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk + AND customer_demographics.cd_demo_sk = ss_cdemo_sk + AND customer_demographics.cd_marital_status = 'U' + AND customer_demographics.cd_education_status = 'Advanced Degree' + AND store_sales.ss_sales_price BETWEEN 150.00 AND 200.00 + AND household_demographics.hd_dep_count = 1 )) +AND (( + store_sales.ss_addr_sk = customer_address.ca_address_sk + AND customer_address.ca_country = 'United States' + AND customer_address.ca_state IN ('KY', + 'GA', + 'NM') + AND store_sales.ss_net_profit BETWEEN 100 AND 200 ) + OR ( + store_sales.ss_addr_sk = customer_address.ca_address_sk + AND customer_address.ca_country = 'United States' + AND customer_address.ca_state IN ('MT', + 'OR', + 'IN') + AND store_sales.ss_net_profit BETWEEN 150 AND 300 ) + OR ( + store_sales.ss_addr_sk = customer_address.ca_address_sk + AND customer_address.ca_country = 'United States' + AND customer_address.ca_state IN ('WI', 'MO', 'WV') + AND store_sales.ss_net_profit BETWEEN 50 AND 250 )) +PREHOOK: type: QUERY +POSTHOOK: query: EXPLAIN SELECT Avg(ss_quantity) , + Avg(ss_ext_sales_price) , + Avg(ss_ext_wholesale_cost) , + Sum(ss_ext_wholesale_cost) +FROM store_sales , + store , + customer_demographics , + household_demographics , + customer_address , + date_dim +WHERE store.s_store_sk = store_sales.ss_store_sk +AND store_sales.ss_sold_date_sk = date_dim.d_date_sk +AND date_dim.d_year = 2001 +AND (( + store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk + AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk + AND customer_demographics.cd_marital_status = 'M' + AND customer_demographics.cd_education_status = '4 yr Degree' + AND store_sales.ss_sales_price BETWEEN 100.00 AND 150.00 + AND household_demographics.hd_dep_count = 3 ) + OR ( + store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk + AND customer_demographics.cd_demo_sk = store_sales.ss_cdemo_sk + AND customer_demographics.cd_marital_status = 'D' + AND customer_demographics.cd_education_status = 'Primary' + AND store_sales.ss_sales_price BETWEEN 50.00 AND 100.00 + AND household_demographics.hd_dep_count = 1 ) + OR ( + store_sales.ss_hdemo_sk=household_demographics.hd_demo_sk + AND customer_demographics.cd_demo_sk = ss_cdemo_sk + AND customer_demographics.cd_marital_status = 'U' + AND customer_demographics.cd_education_status = 'Advanced Degree' + AND store_sales.ss_sales_price BETWEEN 150.00 AND 200.00 + AND household_demographics.hd_dep_count = 1 )) +AND (( + store_sales.ss_addr_sk = customer_address.ca_address_sk + AND customer_address.ca_country = 'United States' + AND customer_address.ca_state IN ('KY', + 'GA', + 'NM') + AND store_sales.ss_net_profit BETWEEN 100 AND 200 ) + OR ( + store_sales.ss_addr_sk = customer_address.ca_address_sk + AND customer_address.ca_country = 'United States' + AND customer_address.ca_state IN ('MT', + 'OR', + 'IN') + AND store_sales.ss_net_profit BETWEEN 150 AND 300 ) + OR ( + store_sales.ss_addr_sk = customer_address.ca_address_sk + AND customer_address.ca_country = 'United States' + AND customer_address.ca_state IN ('WI', 'MO', 'WV') + AND store_sales.ss_net_profit BETWEEN 50 AND 250 )) +POSTHOOK: type: QUERY +Plan optimized by CBO. + +Vertex dependency in root stage +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 8 (SIMPLE_EDGE) +Reducer 3 <- Map 9 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Map 10 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Map 11 (SIMPLE_EDGE), Reducer 4 (SIMPLE_EDGE) +Reducer 6 <- Map 12 (SIMPLE_EDGE), Reducer 5 (SIMPLE_EDGE) +Reducer 7 <- Reducer 6 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:-1 + Stage-1 + Reducer 7 + File Output Operator [FS_52] + compressed:false + Statistics:Num rows: 1 Data size: 344 Basic stats: COMPLETE Column stats: NONE + table:{"input format:":"org.apache.hadoop.mapred.TextInputFormat","output format:":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat","serde:":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"} + Group By Operator [GBY_50] + | aggregations:["avg(VALUE._col0)","avg(VALUE._col1)","avg(VALUE._col2)","sum(VALUE._col3)"] + | outputColumnNames:["_col0","_col1","_col2","_col3"] + | Statistics:Num rows: 1 Data size: 344 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 6 [SIMPLE_EDGE] + Reduce Output Operator [RS_49] + sort order: + Statistics:Num rows: 1 Data size: 112 Basic stats: COMPLETE Column stats: NONE + value expressions:_col0 (type: struct<count:bigint,sum:double,input:int>), _col1 (type: struct<count:bigint,sum:decimal(17,2),input:decimal(7,2)>), _col2 (type: struct<count:bigint,sum:decimal(17,2),input:decimal(7,2)>), _col3 (type: decimal(17,2)) + Group By Operator [GBY_48] + aggregations:["avg(_col5)","avg(_col7)","avg(_col8)","sum(_col8)"] + outputColumnNames:["_col0","_col1","_col2","_col3"] + Statistics:Num rows: 1 Data size: 112 Basic stats: COMPLETE Column stats: NONE + Merge Join Operator [MERGEJOIN_85] + | condition map:[{"":"Inner Join 0 to 1"}] + | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"} + | outputColumnNames:["_col5","_col7","_col8"] + | Statistics:Num rows: 18150000 Data size: 18420070657 Basic stats: COMPLETE Column stats: NONE + |<-Map 12 [SIMPLE_EDGE] + | Reduce Output Operator [RS_45] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE + | Select Operator [SEL_41] + | outputColumnNames:["_col0"] + | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_80] + | predicate:(d_date_sk is not null and (d_year = 2001)) (type: boolean) + | Statistics:Num rows: 36524 Data size: 40870356 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_39] + | alias:date_dim + | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 5 [SIMPLE_EDGE] + Reduce Output Operator [RS_43] + key expressions:_col0 (type: int) + Map-reduce partition columns:_col0 (type: int) + sort order:+ + Statistics:Num rows: 16500000 Data size: 16745518417 Basic stats: COMPLETE Column stats: NONE + value expressions:_col5 (type: int), _col7 (type: decimal(7,2)), _col8 (type: decimal(7,2)) + Select Operator [SEL_38] + outputColumnNames:["_col0","_col5","_col7","_col8"] + Statistics:Num rows: 16500000 Data size: 16745518417 Basic stats: COMPLETE Column stats: NONE + Filter Operator [FIL_73] + predicate:(((_col17) IN ('KY', 'GA', 'NM') and _col9 BETWEEN 100 AND 200) or ((_col17) IN ('MT', 'OR', 'IN') and _col9 BETWEEN 150 AND 300) or ((_col17) IN ('WI', 'MO', 'WV') and _col9 BETWEEN 50 AND 250)) (type: boolean) + Statistics:Num rows: 16500000 Data size: 16745518417 Basic stats: COMPLETE Column stats: NONE + Merge Join Operator [MERGEJOIN_84] + | condition map:[{"":"Inner Join 0 to 1"}] + | keys:{"0":"_col3 (type: int)","1":"_col0 (type: int)"} + | outputColumnNames:["_col0","_col5","_col7","_col8","_col9","_col17"] + | Statistics:Num rows: 22000000 Data size: 22327357890 Basic stats: COMPLETE Column stats: NONE + |<-Map 11 [SIMPLE_EDGE] + | Reduce Output Operator [RS_35] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 20000000 Data size: 20297597642 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col1 (type: string) + | Select Operator [SEL_31] + | outputColumnNames:["_col0","_col1"] + | Statistics:Num rows: 20000000 Data size: 20297597642 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_79] + | predicate:((((ca_state) IN ('KY', 'GA', 'NM') or (ca_state) IN ('MT', 'OR', 'IN') or (ca_state) IN ('WI', 'MO', 'WV')) and (ca_country = 'United States')) and ca_address_sk is not null) (type: boolean) + | Statistics:Num rows: 20000000 Data size: 20297597642 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_29] + | alias:customer_address + | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 4 [SIMPLE_EDGE] + Reduce Output Operator [RS_33] + key expressions:_col3 (type: int) + Map-reduce partition columns:_col3 (type: int) + sort order:+ + Statistics:Num rows: 4491 Data size: 1626526 Basic stats: COMPLETE Column stats: NONE + value expressions:_col0 (type: int), _col5 (type: int), _col7 (type: decimal(7,2)), _col8 (type: decimal(7,2)), _col9 (type: decimal(7,2)) + Select Operator [SEL_28] + outputColumnNames:["_col0","_col3","_col5","_col7","_col8","_col9"] + Statistics:Num rows: 4491 Data size: 1626526 Basic stats: COMPLETE Column stats: NONE + Filter Operator [FIL_74] + predicate:(((_col12 = 'M') and (_col13 = '4 yr Degree') and _col6 BETWEEN 100.0 AND 150.0 and (_col15 = 3)) or ((_col12 = 'D') and (_col13 = 'Primary') and _col6 BETWEEN 50.0 AND 100.0 and (_col15 = 1)) or ((_col12 = 'U') and (_col13 = 'Advanced Degree') and _col6 BETWEEN 150.0 AND 200.0 and (_col15 = 1))) (type: boolean) + Statistics:Num rows: 4491 Data size: 1626526 Basic stats: COMPLETE Column stats: NONE + Merge Join Operator [MERGEJOIN_83] + | condition map:[{"":"Inner Join 0 to 1"}] + | keys:{"0":"_col2 (type: int)","1":"_col0 (type: int)"} + | outputColumnNames:["_col0","_col3","_col5","_col6","_col7","_col8","_col9","_col12","_col13","_col15"] + | Statistics:Num rows: 23958 Data size: 8676981 Basic stats: COMPLETE Column stats: NONE + |<-Map 10 [SIMPLE_EDGE] + | Reduce Output Operator [RS_25] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 7200 Data size: 770400 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col1 (type: int) + | Select Operator [SEL_11] + | outputColumnNames:["_col0","_col1"] + | Statistics:Num rows: 7200 Data size: 770400 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_78] + | predicate:(((hd_dep_count = 3) or (hd_dep_count = 1)) and hd_demo_sk is not null) (type: boolean) + | Statistics:Num rows: 7200 Data size: 770400 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_9] + | alias:household_demographics + | Statistics:Num rows: 7200 Data size: 770400 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 3 [SIMPLE_EDGE] + Reduce Output Operator [RS_23] + key expressions:_col2 (type: int) + Map-reduce partition columns:_col2 (type: int) + sort order:+ + Statistics:Num rows: 21780 Data size: 7888165 Basic stats: COMPLETE Column stats: NONE + value expressions:_col0 (type: int), _col3 (type: int), _col5 (type: int), _col6 (type: decimal(7,2)), _col7 (type: decimal(7,2)), _col8 (type: decimal(7,2)), _col9 (type: decimal(7,2)), _col12 (type: string), _col13 (type: string) + Merge Join Operator [MERGEJOIN_82] + | condition map:[{"":"Inner Join 0 to 1"}] + | keys:{"0":"_col1 (type: int)","1":"_col0 (type: int)"} + | outputColumnNames:["_col0","_col2","_col3","_col5","_col6","_col7","_col8","_col9","_col12","_col13"] + | Statistics:Num rows: 21780 Data size: 7888165 Basic stats: COMPLETE Column stats: NONE + |<-Map 9 [SIMPLE_EDGE] + | Reduce Output Operator [RS_20] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 19800 Data size: 7171059 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col1 (type: string), _col2 (type: string) + | Select Operator [SEL_8] + | outputColumnNames:["_col0","_col1","_col2"] + | Statistics:Num rows: 19800 Data size: 7171059 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_77] + | predicate:((((cd_marital_status = 'M') or (cd_marital_status = 'D') or (cd_marital_status = 'U')) and ((cd_education_status = '4 yr Degree') or (cd_education_status = 'Primary') or (cd_education_status = 'Advanced Degree'))) and cd_demo_sk is not null) (type: boolean) + | Statistics:Num rows: 19800 Data size: 7171059 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_6] + | alias:customer_demographics + | Statistics:Num rows: 19800 Data size: 7171059 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 2 [SIMPLE_EDGE] + Reduce Output Operator [RS_18] + key expressions:_col1 (type: int) + Map-reduce partition columns:_col1 (type: int) + sort order:+ + Statistics:Num rows: 1874 Data size: 3581903 Basic stats: COMPLETE Column stats: NONE + value expressions:_col0 (type: int), _col2 (type: int), _col3 (type: int), _col5 (type: int), _col6 (type: decimal(7,2)), _col7 (type: decimal(7,2)), _col8 (type: decimal(7,2)), _col9 (type: decimal(7,2)) + Merge Join Operator [MERGEJOIN_81] + | condition map:[{"":"Inner Join 0 to 1"}] + | keys:{"0":"_col4 (type: int)","1":"_col0 (type: int)"} + | outputColumnNames:["_col0","_col1","_col2","_col3","_col5","_col6","_col7","_col8","_col9"] + | Statistics:Num rows: 1874 Data size: 3581903 Basic stats: COMPLETE Column stats: NONE + |<-Map 1 [SIMPLE_EDGE] + | Reduce Output Operator [RS_13] + | key expressions:_col4 (type: int) + | Map-reduce partition columns:_col4 (type: int) + | sort order:+ + | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | value expressions:_col0 (type: int), _col1 (type: int), _col2 (type: int), _col3 (type: int), _col5 (type: int), _col6 (type: decimal(7,2)), _col7 (type: decimal(7,2)), _col8 (type: decimal(7,2)), _col9 (type: decimal(7,2)) + | Select Operator [SEL_2] + | outputColumnNames:["_col0","_col1","_col2","_col3","_col4","_col5","_col6","_col7","_col8","_col9"] + | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | Filter Operator [FIL_75] + | predicate:(((((((ss_net_profit BETWEEN 100 AND 200 or ss_net_profit BETWEEN 150 AND 300 or ss_net_profit BETWEEN 50 AND 250) and ss_store_sk is not null) and (ss_sales_price BETWEEN 100.0 AND 150.0 or ss_sales_price BETWEEN 50.0 AND 100.0 or ss_sales_price BETWEEN 150.0 AND 200.0)) and ss_cdemo_sk is not null) and ss_hdemo_sk is not null) and ss_addr_sk is not null) and ss_sold_date_sk is not null) (type: boolean) + | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | TableScan [TS_0] + | alias:store_sales + | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + |<-Map 8 [SIMPLE_EDGE] + Reduce Output Operator [RS_15] + key expressions:_col0 (type: int) + Map-reduce partition columns:_col0 (type: int) + sort order:+ + Statistics:Num rows: 1704 Data size: 3256276 Basic stats: COMPLETE Column stats: NONE + Select Operator [SEL_5] + outputColumnNames:["_col0"] + Statistics:Num rows: 1704 Data size: 3256276 Basic stats: COMPLETE Column stats: NONE + Filter Operator [FIL_76] + predicate:s_store_sk is not null (type: boolean) + Statistics:Num rows: 1704 Data size: 3256276 Basic stats: COMPLETE Column stats: NONE + TableScan [TS_3] + alias:store + Statistics:Num rows: 1704 Data size: 3256276 Basic stats: COMPLETE Column stats: NONE + http://git-wip-us.apache.org/repos/asf/hive/blob/09b6f9a3/ql/src/test/results/clientpositive/perf/query15.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/perf/query15.q.out b/ql/src/test/results/clientpositive/perf/query15.q.out new file mode 100644 index 0000000..0ce4e52 --- /dev/null +++ b/ql/src/test/results/clientpositive/perf/query15.q.out @@ -0,0 +1,153 @@ +PREHOOK: query: explain select ca_zip ,sum(cs_sales_price) from catalog_sales ,customer ,customer_address ,date_dim where catalog_sales.cs_bill_customer_sk = customer.c_customer_sk and customer.c_current_addr_sk = customer_address.ca_address_sk and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792') or customer_address.ca_state in ('CA','WA','GA') or catalog_sales.cs_sales_price > 500) and catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and date_dim.d_qoy = 2 and date_dim.d_year = 2000 group by ca_zip order by ca_zip limit 100 +PREHOOK: type: QUERY +POSTHOOK: query: explain select ca_zip ,sum(cs_sales_price) from catalog_sales ,customer ,customer_address ,date_dim where catalog_sales.cs_bill_customer_sk = customer.c_customer_sk and customer.c_current_addr_sk = customer_address.ca_address_sk and ( substr(ca_zip,1,5) in ('85669', '86197','88274','83405','86475', '85392', '85460', '80348', '81792') or customer_address.ca_state in ('CA','WA','GA') or catalog_sales.cs_sales_price > 500) and catalog_sales.cs_sold_date_sk = date_dim.d_date_sk and date_dim.d_qoy = 2 and date_dim.d_year = 2000 group by ca_zip order by ca_zip limit 100 +POSTHOOK: type: QUERY +Plan optimized by CBO. + +Vertex dependency in root stage +Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE) +Reducer 3 <- Map 8 (SIMPLE_EDGE), Reducer 2 (SIMPLE_EDGE) +Reducer 4 <- Map 9 (SIMPLE_EDGE), Reducer 3 (SIMPLE_EDGE) +Reducer 5 <- Reducer 4 (SIMPLE_EDGE) +Reducer 6 <- Reducer 5 (SIMPLE_EDGE) + +Stage-0 + Fetch Operator + limit:100 + Stage-1 + Reducer 6 + File Output Operator [FS_37] + compressed:false + Statistics:Num rows: 100 Data size: 86000 Basic stats: COMPLETE Column stats: NONE + table:{"input format:":"org.apache.hadoop.mapred.TextInputFormat","output format:":"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat","serde:":"org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe"} + Limit [LIM_36] + Number of rows:100 + Statistics:Num rows: 100 Data size: 86000 Basic stats: COMPLETE Column stats: NONE + Select Operator [SEL_35] + | outputColumnNames:["_col0","_col1"] + | Statistics:Num rows: 53240002 Data size: 45787477895 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 5 [SIMPLE_EDGE] + Reduce Output Operator [RS_34] + key expressions:_col0 (type: string) + sort order:+ + Statistics:Num rows: 53240002 Data size: 45787477895 Basic stats: COMPLETE Column stats: NONE + value expressions:_col1 (type: decimal(17,2)) + Group By Operator [GBY_32] + | aggregations:["sum(VALUE._col0)"] + | keys:KEY._col0 (type: string) + | outputColumnNames:["_col0","_col1"] + | Statistics:Num rows: 53240002 Data size: 45787477895 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 4 [SIMPLE_EDGE] + Reduce Output Operator [RS_31] + key expressions:_col0 (type: string) + Map-reduce partition columns:_col0 (type: string) + sort order:+ + Statistics:Num rows: 106480005 Data size: 91574956652 Basic stats: COMPLETE Column stats: NONE + value expressions:_col1 (type: decimal(17,2)) + Group By Operator [GBY_30] + aggregations:["sum(_col2)"] + keys:_col7 (type: string) + outputColumnNames:["_col0","_col1"] + Statistics:Num rows: 106480005 Data size: 91574956652 Basic stats: COMPLETE Column stats: NONE + Select Operator [SEL_29] + outputColumnNames:["_col7","_col2"] + Statistics:Num rows: 106480005 Data size: 91574956652 Basic stats: COMPLETE Column stats: NONE + Merge Join Operator [MERGEJOIN_54] + | condition map:[{"":"Inner Join 0 to 1"}] + | keys:{"0":"_col0 (type: int)","1":"_col0 (type: int)"} + | outputColumnNames:["_col2","_col7"] + | Statistics:Num rows: 106480005 Data size: 91574956652 Basic stats: COMPLETE Column stats: NONE + |<-Map 9 [SIMPLE_EDGE] + | Reduce Output Operator [RS_27] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | Select Operator [SEL_23] + | outputColumnNames:["_col0"] + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_51] + | predicate:(((d_qoy = 2) and (d_year = 2000)) and d_date_sk is not null) (type: boolean) + | Statistics:Num rows: 18262 Data size: 20435178 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_21] + | alias:date_dim + | Statistics:Num rows: 73049 Data size: 81741831 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 3 [SIMPLE_EDGE] + Reduce Output Operator [RS_25] + key expressions:_col0 (type: int) + Map-reduce partition columns:_col0 (type: int) + sort order:+ + Statistics:Num rows: 96800003 Data size: 83249958789 Basic stats: COMPLETE Column stats: NONE + value expressions:_col2 (type: decimal(7,2)), _col7 (type: string) + Select Operator [SEL_20] + outputColumnNames:["_col0","_col2","_col7"] + Statistics:Num rows: 96800003 Data size: 83249958789 Basic stats: COMPLETE Column stats: NONE + Filter Operator [FIL_47] + predicate:((substr(_col7, 1, 5)) IN ('85669', '86197', '88274', '83405', '86475', '85392', '85460', '80348', '81792') or (_col6) IN ('CA', 'WA', 'GA') or (_col2 > 500)) (type: boolean) + Statistics:Num rows: 96800003 Data size: 83249958789 Basic stats: COMPLETE Column stats: NONE + Merge Join Operator [MERGEJOIN_53] + | condition map:[{"":"Inner Join 0 to 1"}] + | keys:{"0":"_col4 (type: int)","1":"_col0 (type: int)"} + | outputColumnNames:["_col0","_col2","_col6","_col7"] + | Statistics:Num rows: 96800003 Data size: 83249958789 Basic stats: COMPLETE Column stats: NONE + |<-Map 8 [SIMPLE_EDGE] + | Reduce Output Operator [RS_17] + | key expressions:_col0 (type: int) + | Map-reduce partition columns:_col0 (type: int) + | sort order:+ + | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | value expressions:_col1 (type: string), _col2 (type: string) + | Select Operator [SEL_8] + | outputColumnNames:["_col0","_col1","_col2"] + | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | Filter Operator [FIL_50] + | predicate:ca_address_sk is not null (type: boolean) + | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + | TableScan [TS_6] + | alias:customer_address + | Statistics:Num rows: 40000000 Data size: 40595195284 Basic stats: COMPLETE Column stats: NONE + |<-Reducer 2 [SIMPLE_EDGE] + Reduce Output Operator [RS_15] + key expressions:_col4 (type: int) + Map-reduce partition columns:_col4 (type: int) + sort order:+ + Statistics:Num rows: 88000001 Data size: 75681779077 Basic stats: COMPLETE Column stats: NONE + value expressions:_col0 (type: int), _col2 (type: decimal(7,2)) + Merge Join Operator [MERGEJOIN_52] + | condition map:[{"":"Inner Join 0 to 1"}] + | keys:{"0":"_col1 (type: int)","1":"_col0 (type: int)"} + | outputColumnNames:["_col0","_col2","_col4"] + | Statistics:Num rows: 88000001 Data size: 75681779077 Basic stats: COMPLETE Column stats: NONE + |<-Map 1 [SIMPLE_EDGE] + | Reduce Output Operator [RS_10] + | key expressions:_col1 (type: int) + | Map-reduce partition columns:_col1 (type: int) + | sort order:+ + | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | value expressions:_col0 (type: int), _col2 (type: decimal(7,2)) + | Select Operator [SEL_2] + | outputColumnNames:["_col0","_col1","_col2"] + | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | Filter Operator [FIL_48] + | predicate:(cs_bill_customer_sk is not null and cs_sold_date_sk is not null) (type: boolean) + | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + | TableScan [TS_0] + | alias:catalog_sales + | Statistics:Num rows: 1 Data size: 0 Basic stats: PARTIAL Column stats: NONE + |<-Map 7 [SIMPLE_EDGE] + Reduce Output Operator [RS_12] + key expressions:_col0 (type: int) + Map-reduce partition columns:_col0 (type: int) + sort order:+ + Statistics:Num rows: 80000000 Data size: 68801615852 Basic stats: COMPLETE Column stats: NONE + value expressions:_col1 (type: int) + Select Operator [SEL_5] + outputColumnNames:["_col0","_col1"] + Statistics:Num rows: 80000000 Data size: 68801615852 Basic stats: COMPLETE Column stats: NONE + Filter Operator [FIL_49] + predicate:(c_customer_sk is not null and c_current_addr_sk is not null) (type: boolean) + Statistics:Num rows: 80000000 Data size: 68801615852 Basic stats: COMPLETE Column stats: NONE + TableScan [TS_3] + alias:customer + Statistics:Num rows: 80000000 Data size: 68801615852 Basic stats: COMPLETE Column stats: NONE +
