QUERY: (OPTIMIZATION TIMESTAMP: 11-07-2022 11:26:51) ------ create view "informix".v_los_fineable_dt (vendor_fines_id,po_ostat_sent_dt,init_ord_stat_dt,ship_ord_stat_dt) as select x0.vendor_fines_id ,min(x0.po_ostat_sent_dt ) ,min(x0.init_ord_stat_dt ) ,min(x0.ship_ord_stat_dt ) from vog_db:"informix".vendor_fineable_date x0 group by x0.vendor_fines_id; Estimated Cost: 3096317 Estimated # of Rows Returned: 49164244 1) informix.d: INDEX PATH (1) Index Name: informix.ix_vendor_fineable_date_1 Index Keys: vendor_fines_id (Serial, fragments: ALL) QUERY: (OPTIMIZATION TIMESTAMP: 11-07-2022 11:26:51) ------ create view "informix".v_ahi_approval (invoice_nbr,dc_code,ahi_creator_name,ahi_created_on_dt,ahi_rec_sup_name,ahi_rec_sup_ts,ahi_mgr_name,ahi_mgr_ts,ahi_agm_name,ahi_agm_ts) as select x0.invoice_nbr ,x0.dc_code ,x0.ahi_creator_name ,x0.ahi_created_on_dt ,x0.ahi_rec_sup_name ,x0.ahi_rec_sup_ts ,x0.ahi_mgr_name ,x0.ahi_mgr_ts ,x0.ahi_agm_name ,x0.ahi_agm_ts from vog_db:"informix".ahi_approval x0; Estimated Cost: 5341 Estimated # of Rows Returned: 128824 1) informix.c: SEQUENTIAL SCAN QUERY: (OPTIMIZATION TIMESTAMP: 11-07-2022 11:26:51) ------ create view "informix".v_vendor_fines_exemption (fine_type_id,vendor_id) as select x0.fine_type_id ,x0.vendor_id from vog_db:"informix".vendor_fines_exemption x0; Estimated Cost: 188 Estimated # of Rows Returned: 5511 1) informix.b: SEQUENTIAL SCAN QUERY: (OPTIMIZATION TIMESTAMP: 11-07-2022 11:26:51) ------ select a11.fine_type_id fine_type_id, max(TRIM(TRAILING ' ' FROM a14.fine_type_desc)) fine_type_desc, a11.fine_status_id fine_status_id, max(a11.fine_status_desc) fine_status_desc, a11.generated_date generated_date, a11.sent_to_ap_date sent_to_ap_date, a11.exemption_status_id exemption_status_id, max(a11.exemption_status_desc) exemption_status_desc, a11.invoice_nbr invoice_nbr, a11.vendor_cd vendor_cd, a11.ahi_asn_nbr ahi_asn_nbr, a11.dc_code dc_code, a11.yard yard, max(a13.yard_name) yard_name, a11.department_nbr store_department_id, max(a15.store_department_name) store_department_name, sum(a11.Fine_Amt) WJXBFS1 from (SELECT source_table_id , CASE WHEN source_table_id = 1 THEN 'v_vendor_fines' WHEN source_table_id = 2 THEN 'v_hist_vendor_fines' ELSE 'Unknown' END AS source_table_name , CASE WHEN process_flg = 'Y' and sent_to_ap_flg IN ('D', 'N') THEN 1 WHEN process_flg = 'Y' and sent_to_ap_flg = 'Y' THEN 2 WHEN process_flg = 'N' and sent_to_ap_flg = 'N' THEN 3 ELSE 0 END AS fine_status_id , CASE WHEN process_flg = 'Y' and sent_to_ap_flg IN ('D', 'N') THEN 'Pending' WHEN process_flg = 'Y' and sent_to_ap_flg = 'Y' THEN 'Processed' WHEN process_flg = 'N' and sent_to_ap_flg = 'N' THEN 'Unprocessed' ELSE 'Unknown' END AS fine_status_desc , a.vendor_fines_id , a.invoice_nbr , a.department_nbr AS department_nbr , a.vendor_cd , a.po_nbr , a.yard , a.sku , a.sequence_nbr , a.ordered_qty , a.item_cost_amt , a.early_shipped_qty , a.ontime_shipped_qty , a.late_shipped_qty , a.received_qty , a.receiver_date , a.due_date , a.late_date , a.early_date , a.last_recv_date , a.add_grace_days_nbr , a.fill_rate_amt , a.qty_ordered_value , a.recvbyduedateqty , a.qty_received_value , a.fine_amt , a.fine_type_id , a.generated_date , a.user_name , a.updated_date , a.process_flg , a.sent_to_ap_flg , a.sent_to_ap_date , a.fine_pct , nvl(b.fine_type_id, 0) AS exemption_status_id , CASE WHEN nvl(b.fine_type_id, 0) = 0 THEN 'Apply' ELSE 'Exempt' END AS exemption_status_desc , a.ahi_asn_nbr AS ahi_asn_nbr , nvl(c.dc_code, 0) AS dc_code -- , a.ahi_approved_flg AS ahi_approved_flg , nvl(a.ahi_approved_flg, 'N') AS ahi_approved_flg , d.po_ostat_sent_dt , d.init_ord_stat_dt , d.ship_ord_stat_dt , CASE WHEN process_flg = 'Y' and sent_to_ap_flg = 'Y' THEN a.invoice_nbr ELSE ' ' END AS deduction_invoice_nbr from (SELECT 1 AS source_table_id , vendor_fines_id , a.invoice_nbr , department_nbr , vendor_cd , po_nbr , yard , sku , sequence_nbr , ordered_qty , item_cost_amt , early_shipped_qty , ontime_shipped_qty , late_shipped_qty , received_qty , receiver_date , due_date , late_date , early_date , last_recv_date , add_grace_days_nbr , fill_rate_amt , qty_ordered_value , recvbyduedateqty , qty_received_value , fine_amt , a.fine_type_id AS fine_type_id , date(generated_date) AS generated_date , user_name , updated_date , process_flg , sent_to_ap_flg , sent_to_ap_date , fine_pct , a.ahi_asn_nbr AS ahi_asn_nbr , a.ahi_approved_flg AS ahi_approved_flg FROM v_vendor_fines A where date(generated_date) >= '01/01/2010' UNION ALL SELECT 2 AS source_table_id , vendor_fines_id , a.invoice_nbr , department_nbr , vendor_cd , po_nbr , yard , sku , sequence_nbr , ordered_qty , item_cost_amt , early_shipped_qty , ontime_shipped_qty , late_shipped_qty , received_qty , receiver_date , due_date , late_date , early_date , last_recv_date , add_grace_days_nbr , fill_rate_amt , qty_ordered_value , recvbyduedateqty , qty_received_value , fine_amt , a.fine_type_id AS fine_type_id , date(generated_date) AS generated_date , user_name , updated_date , process_flg , sent_to_ap_flg , sent_to_ap_date , fine_pct , a.ahi_asn_nbr AS ahi_asn_nbr , a.ahi_approved_flg AS ahi_approved_flg FROM v_hist_vendor_fines A where date(generated_date) >= '01/01/2010' ) A left join v_vendor_fines_exemption B ON (a.vendor_cd = b.vendor_id AND a.fine_type_id = b.fine_type_id) left join v_ahi_approval C ON (a.invoice_nbr = c.invoice_nbr) left join v_los_fineable_dt D ON (a. vendor_fines_id = d. vendor_fines_id) ) a11, informix.v_date_dimension a12, informix.v_yardship a13, informix.v_fine_type_cd a14, (SELECT merch_manager, merch_manager_name, merch_manager_id, cast(CASE WHEN merch_manager_id = 1101 THEN 100 WHEN merch_manager_id = 1202 THEN 200 WHEN merch_manager_id = 1303 THEN 300 WHEN merch_manager_id = 1405 THEN 401 WHEN merch_manager_id = 1505 THEN 500 WHEN merch_manager_id = 1506 THEN 501 WHEN merch_manager_id = 1606 THEN 600 WHEN merch_manager_id = 1707 THEN 700 WHEN merch_manager_id IN (1900,1909) THEN 900 ELSE merch_manager_id END AS INT) AS store_department_id, CASE WHEN merch_manager_id = 1101 THEN 'Building Materials and Millwork' WHEN merch_manager_id = 1202 THEN 'Hardware' WHEN merch_manager_id = 1303 THEN 'Electrical' WHEN merch_manager_id = 1405 THEN 'Cabinets and Appliances' WHEN merch_manager_id = 1505 THEN 'Wallcoverings' WHEN merch_manager_id = 1506 THEN 'Grocery and Pet' WHEN merch_manager_id = 1606 THEN 'Plumbing' WHEN merch_manager_id = 1707 THEN 'Floorcoverings' WHEN merch_manager_id IN (1900,1909) THEN 'Unknown' ELSE 'None' END AS store_department_name FROM msi_db:merch_managerv) a15 where a11.generated_date = a12.the_date and a11.yard = a13.yard and a11.fine_type_id = a14.fine_type_id and a11.department_nbr = a15.store_department_id and (a11.department_nbr in (100) and (a12.calendar_year_nbr = 2022 and a12.calendar_month_nbr = 6) and (a13.location_type_id in (1, 2) or a11.yard = 3598) and a11.fine_type_id in (6, 7)) group by a11.fine_type_id, a11.fine_status_id, a11.generated_date, a11.sent_to_ap_date, a11.exemption_status_id, a11.invoice_nbr, a11.vendor_cd, a11.ahi_asn_nbr, a11.dc_code, a11.yard, a11.department_nbr Estimated Cost: 475 Estimated # of Rows Returned: 796 1) informix.a: INDEX PATH Filters: (informix.a.department_nbr = 100 AND DATE (informix.a.generated_date ) >= 01/01/10 ) (1) Index Name: informix.ix_vendor_fines8 Index Keys: fine_type_id (Serial, fragments: ALL) Lower Index Filter: informix.a.fine_type_id = 6 (2) Index Name: informix.ix_vendor_fines8 Index Keys: fine_type_id (Serial, fragments: ALL) Lower Index Filter: informix.a.fine_type_id = 7 Union Query: ------------ 1) informix.a: INDEX PATH Filters: (informix.a.department_nbr = 100 AND DATE (informix.a.generated_date ) >= 01/01/10 ) (1) Index Name: informix.ix_hist_vendor_fines8 Index Keys: fine_type_id (Serial, fragments: ALL) Lower Index Filter: informix.a.fine_type_id = 6 (2) Index Name: informix.ix_hist_vendor_fines8 Index Keys: fine_type_id (Serial, fragments: ALL) Lower Index Filter: informix.a.fine_type_id = 7 QUERY: (OPTIMIZATION TIMESTAMP: 11-07-2022 11:26:51) ------ select a11.fine_type_id fine_type_id, max(TRIM(TRAILING ' ' FROM a14.fine_type_desc)) fine_type_desc, a11.fine_status_id fine_status_id, max(a11.fine_status_desc) fine_status_desc, a11.generated_date generated_date, a11.sent_to_ap_date sent_to_ap_date, a11.exemption_status_id exemption_status_id, max(a11.exemption_status_desc) exemption_status_desc, a11.invoice_nbr invoice_nbr, a11.vendor_cd vendor_cd, a11.ahi_asn_nbr ahi_asn_nbr, a11.dc_code dc_code, a11.yard yard, max(a13.yard_name) yard_name, a11.department_nbr store_department_id, max(a15.store_department_name) store_department_name, sum(a11.Fine_Amt) WJXBFS1 from (SELECT source_table_id , CASE WHEN source_table_id = 1 THEN 'v_vendor_fines' WHEN source_table_id = 2 THEN 'v_hist_vendor_fines' ELSE 'Unknown' END AS source_table_name , CASE WHEN process_flg = 'Y' and sent_to_ap_flg IN ('D', 'N') THEN 1 WHEN process_flg = 'Y' and sent_to_ap_flg = 'Y' THEN 2 WHEN process_flg = 'N' and sent_to_ap_flg = 'N' THEN 3 ELSE 0 END AS fine_status_id , CASE WHEN process_flg = 'Y' and sent_to_ap_flg IN ('D', 'N') THEN 'Pending' WHEN process_flg = 'Y' and sent_to_ap_flg = 'Y' THEN 'Processed' WHEN process_flg = 'N' and sent_to_ap_flg = 'N' THEN 'Unprocessed' ELSE 'Unknown' END AS fine_status_desc , a.vendor_fines_id , a.invoice_nbr , a.department_nbr AS department_nbr , a.vendor_cd , a.po_nbr , a.yard , a.sku , a.sequence_nbr , a.ordered_qty , a.item_cost_amt , a.early_shipped_qty , a.ontime_shipped_qty , a.late_shipped_qty , a.received_qty , a.receiver_date , a.due_date , a.late_date , a.early_date , a.last_recv_date , a.add_grace_days_nbr , a.fill_rate_amt , a.qty_ordered_value , a.recvbyduedateqty , a.qty_received_value , a.fine_amt , a.fine_type_id , a.generated_date , a.user_name , a.updated_date , a.process_flg , a.sent_to_ap_flg , a.sent_to_ap_date , a.fine_pct , nvl(b.fine_type_id, 0) AS exemption_status_id , CASE WHEN nvl(b.fine_type_id, 0) = 0 THEN 'Apply' ELSE 'Exempt' END AS exemption_status_desc , a.ahi_asn_nbr AS ahi_asn_nbr , nvl(c.dc_code, 0) AS dc_code -- , a.ahi_approved_flg AS ahi_approved_flg , nvl(a.ahi_approved_flg, 'N') AS ahi_approved_flg , d.po_ostat_sent_dt , d.init_ord_stat_dt , d.ship_ord_stat_dt , CASE WHEN process_flg = 'Y' and sent_to_ap_flg = 'Y' THEN a.invoice_nbr ELSE ' ' END AS deduction_invoice_nbr from (SELECT 1 AS source_table_id , vendor_fines_id , a.invoice_nbr , department_nbr , vendor_cd , po_nbr , yard , sku , sequence_nbr , ordered_qty , item_cost_amt , early_shipped_qty , ontime_shipped_qty , late_shipped_qty , received_qty , receiver_date , due_date , late_date , early_date , last_recv_date , add_grace_days_nbr , fill_rate_amt , qty_ordered_value , recvbyduedateqty , qty_received_value , fine_amt , a.fine_type_id AS fine_type_id , date(generated_date) AS generated_date , user_name , updated_date , process_flg , sent_to_ap_flg , sent_to_ap_date , fine_pct , a.ahi_asn_nbr AS ahi_asn_nbr , a.ahi_approved_flg AS ahi_approved_flg FROM v_vendor_fines A where date(generated_date) >= '01/01/2010' UNION ALL SELECT 2 AS source_table_id , vendor_fines_id , a.invoice_nbr , department_nbr , vendor_cd , po_nbr , yard , sku , sequence_nbr , ordered_qty , item_cost_amt , early_shipped_qty , ontime_shipped_qty , late_shipped_qty , received_qty , receiver_date , due_date , late_date , early_date , last_recv_date , add_grace_days_nbr , fill_rate_amt , qty_ordered_value , recvbyduedateqty , qty_received_value , fine_amt , a.fine_type_id AS fine_type_id , date(generated_date) AS generated_date , user_name , updated_date , process_flg , sent_to_ap_flg , sent_to_ap_date , fine_pct , a.ahi_asn_nbr AS ahi_asn_nbr , a.ahi_approved_flg AS ahi_approved_flg FROM v_hist_vendor_fines A where date(generated_date) >= '01/01/2010' ) A left join v_vendor_fines_exemption B ON (a.vendor_cd = b.vendor_id AND a.fine_type_id = b.fine_type_id) left join v_ahi_approval C ON (a.invoice_nbr = c.invoice_nbr) left join v_los_fineable_dt D ON (a. vendor_fines_id = d. vendor_fines_id) ) a11, informix.v_date_dimension a12, informix.v_yardship a13, informix.v_fine_type_cd a14, (SELECT merch_manager, merch_manager_name, merch_manager_id, cast(CASE WHEN merch_manager_id = 1101 THEN 100 WHEN merch_manager_id = 1202 THEN 200 WHEN merch_manager_id = 1303 THEN 300 WHEN merch_manager_id = 1405 THEN 401 WHEN merch_manager_id = 1505 THEN 500 WHEN merch_manager_id = 1506 THEN 501 WHEN merch_manager_id = 1606 THEN 600 WHEN merch_manager_id = 1707 THEN 700 WHEN merch_manager_id IN (1900,1909) THEN 900 ELSE merch_manager_id END AS INT) AS store_department_id, CASE WHEN merch_manager_id = 1101 THEN 'Building Materials and Millwork' WHEN merch_manager_id = 1202 THEN 'Hardware' WHEN merch_manager_id = 1303 THEN 'Electrical' WHEN merch_manager_id = 1405 THEN 'Cabinets and Appliances' WHEN merch_manager_id = 1505 THEN 'Wallcoverings' WHEN merch_manager_id = 1506 THEN 'Grocery and Pet' WHEN merch_manager_id = 1606 THEN 'Plumbing' WHEN merch_manager_id = 1707 THEN 'Floorcoverings' WHEN merch_manager_id IN (1900,1909) THEN 'Unknown' ELSE 'None' END AS store_department_name FROM msi_db:merch_managerv) a15 where a11.generated_date = a12.the_date and a11.yard = a13.yard and a11.fine_type_id = a14.fine_type_id and a11.department_nbr = a15.store_department_id and (a11.department_nbr in (100) and (a12.calendar_year_nbr = 2022 and a12.calendar_month_nbr = 6) and (a13.location_type_id in (1, 2) or a11.yard = 3598) and a11.fine_type_id in (6, 7)) group by a11.fine_type_id, a11.fine_status_id, a11.generated_date, a11.sent_to_ap_date, a11.exemption_status_id, a11.invoice_nbr, a11.vendor_cd, a11.ahi_asn_nbr, a11.dc_code, a11.yard, a11.department_nbr Estimated Cost: 39394052 Estimated # of Rows Returned: 504149606400 1) (Temp Table For Collection Subquery): SEQUENTIAL SCAN 2) (Temp Table For View): SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: ((Temp Table For Collection Subquery).vendor_cd = (Temp Table For View).vendor_id AND (Temp Table For Collection Subquery).fine_type_id = (Temp Table For View).fine_type_id ) 3) (Temp Table For View): SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: (Temp Table For Collection Subquery).invoice_nbr = (Temp Table For View).invoice_nbr 4) (Temp Table For View): SEQUENTIAL SCAN DYNAMIC HASH JOIN Dynamic Hash Filters: (Temp Table For Collection Subquery).vendor_fines_id = (Temp Table For View).vendor_fines_id QUERY: (OPTIMIZATION TIMESTAMP: 11-07-2022 11:26:51) ------ select a11.fine_type_id fine_type_id, max(TRIM(TRAILING ' ' FROM a14.fine_type_desc)) fine_type_desc, a11.fine_status_id fine_status_id, max(a11.fine_status_desc) fine_status_desc, a11.generated_date generated_date, a11.sent_to_ap_date sent_to_ap_date, a11.exemption_status_id exemption_status_id, max(a11.exemption_status_desc) exemption_status_desc, a11.invoice_nbr invoice_nbr, a11.vendor_cd vendor_cd, a11.ahi_asn_nbr ahi_asn_nbr, a11.dc_code dc_code, a11.yard yard, max(a13.yard_name) yard_name, a11.department_nbr store_department_id, max(a15.store_department_name) store_department_name, sum(a11.Fine_Amt) WJXBFS1 from (SELECT source_table_id , CASE WHEN source_table_id = 1 THEN 'v_vendor_fines' WHEN source_table_id = 2 THEN 'v_hist_vendor_fines' ELSE 'Unknown' END AS source_table_name , CASE WHEN process_flg = 'Y' and sent_to_ap_flg IN ('D', 'N') THEN 1 WHEN process_flg = 'Y' and sent_to_ap_flg = 'Y' THEN 2 WHEN process_flg = 'N' and sent_to_ap_flg = 'N' THEN 3 ELSE 0 END AS fine_status_id , CASE WHEN process_flg = 'Y' and sent_to_ap_flg IN ('D', 'N') THEN 'Pending' WHEN process_flg = 'Y' and sent_to_ap_flg = 'Y' THEN 'Processed' WHEN process_flg = 'N' and sent_to_ap_flg = 'N' THEN 'Unprocessed' ELSE 'Unknown' END AS fine_status_desc , a.vendor_fines_id , a.invoice_nbr , a.department_nbr AS department_nbr , a.vendor_cd , a.po_nbr , a.yard , a.sku , a.sequence_nbr , a.ordered_qty , a.item_cost_amt , a.early_shipped_qty , a.ontime_shipped_qty , a.late_shipped_qty , a.received_qty , a.receiver_date , a.due_date , a.late_date , a.early_date , a.last_recv_date , a.add_grace_days_nbr , a.fill_rate_amt , a.qty_ordered_value , a.recvbyduedateqty , a.qty_received_value , a.fine_amt , a.fine_type_id , a.generated_date , a.user_name , a.updated_date , a.process_flg , a.sent_to_ap_flg , a.sent_to_ap_date , a.fine_pct , nvl(b.fine_type_id, 0) AS exemption_status_id , CASE WHEN nvl(b.fine_type_id, 0) = 0 THEN 'Apply' ELSE 'Exempt' END AS exemption_status_desc , a.ahi_asn_nbr AS ahi_asn_nbr , nvl(c.dc_code, 0) AS dc_code -- , a.ahi_approved_flg AS ahi_approved_flg , nvl(a.ahi_approved_flg, 'N') AS ahi_approved_flg , d.po_ostat_sent_dt , d.init_ord_stat_dt , d.ship_ord_stat_dt , CASE WHEN process_flg = 'Y' and sent_to_ap_flg = 'Y' THEN a.invoice_nbr ELSE ' ' END AS deduction_invoice_nbr from (SELECT 1 AS source_table_id , vendor_fines_id , a.invoice_nbr , department_nbr , vendor_cd , po_nbr , yard , sku , sequence_nbr , ordered_qty , item_cost_amt , early_shipped_qty , ontime_shipped_qty , late_shipped_qty , received_qty , receiver_date , due_date , late_date , early_date , last_recv_date , add_grace_days_nbr , fill_rate_amt , qty_ordered_value , recvbyduedateqty , qty_received_value , fine_amt , a.fine_type_id AS fine_type_id , date(generated_date) AS generated_date , user_name , updated_date , process_flg , sent_to_ap_flg , sent_to_ap_date , fine_pct , a.ahi_asn_nbr AS ahi_asn_nbr , a.ahi_approved_flg AS ahi_approved_flg FROM v_vendor_fines A where date(generated_date) >= '01/01/2010' UNION ALL SELECT 2 AS source_table_id , vendor_fines_id , a.invoice_nbr , department_nbr , vendor_cd , po_nbr , yard , sku , sequence_nbr , ordered_qty , item_cost_amt , early_shipped_qty , ontime_shipped_qty , late_shipped_qty , received_qty , receiver_date , due_date , late_date , early_date , last_recv_date , add_grace_days_nbr , fill_rate_amt , qty_ordered_value , recvbyduedateqty , qty_received_value , fine_amt , a.fine_type_id AS fine_type_id , date(generated_date) AS generated_date , user_name , updated_date , process_flg , sent_to_ap_flg , sent_to_ap_date , fine_pct , a.ahi_asn_nbr AS ahi_asn_nbr , a.ahi_approved_flg AS ahi_approved_flg FROM v_hist_vendor_fines A where date(generated_date) >= '01/01/2010' ) A left join v_vendor_fines_exemption B ON (a.vendor_cd = b.vendor_id AND a.fine_type_id = b.fine_type_id) left join v_ahi_approval C ON (a.invoice_nbr = c.invoice_nbr) left join v_los_fineable_dt D ON (a. vendor_fines_id = d. vendor_fines_id) ) a11, informix.v_date_dimension a12, informix.v_yardship a13, informix.v_fine_type_cd a14, (SELECT merch_manager, merch_manager_name, merch_manager_id, cast(CASE WHEN merch_manager_id = 1101 THEN 100 WHEN merch_manager_id = 1202 THEN 200 WHEN merch_manager_id = 1303 THEN 300 WHEN merch_manager_id = 1405 THEN 401 WHEN merch_manager_id = 1505 THEN 500 WHEN merch_manager_id = 1506 THEN 501 WHEN merch_manager_id = 1606 THEN 600 WHEN merch_manager_id = 1707 THEN 700 WHEN merch_manager_id IN (1900,1909) THEN 900 ELSE merch_manager_id END AS INT) AS store_department_id, CASE WHEN merch_manager_id = 1101 THEN 'Building Materials and Millwork' WHEN merch_manager_id = 1202 THEN 'Hardware' WHEN merch_manager_id = 1303 THEN 'Electrical' WHEN merch_manager_id = 1405 THEN 'Cabinets and Appliances' WHEN merch_manager_id = 1505 THEN 'Wallcoverings' WHEN merch_manager_id = 1506 THEN 'Grocery and Pet' WHEN merch_manager_id = 1606 THEN 'Plumbing' WHEN merch_manager_id = 1707 THEN 'Floorcoverings' WHEN merch_manager_id IN (1900,1909) THEN 'Unknown' ELSE 'None' END AS store_department_name FROM msi_db:merch_managerv) a15 where a11.generated_date = a12.the_date and a11.yard = a13.yard and a11.fine_type_id = a14.fine_type_id and a11.department_nbr = a15.store_department_id and (a11.department_nbr in (100) and (a12.calendar_year_nbr = 2022 and a12.calendar_month_nbr = 6) and (a13.location_type_id in (1, 2) or a11.yard = 3598) and a11.fine_type_id in (6, 7)) group by a11.fine_type_id, a11.fine_status_id, a11.generated_date, a11.sent_to_ap_date, a11.exemption_status_id, a11.invoice_nbr, a11.vendor_cd, a11.ahi_asn_nbr, a11.dc_code, a11.yard, a11.department_nbr Estimated Cost: 109352878080 Estimated # of Rows Returned: 102768048 Temporary Files Required For: Group By 1) informix.merch_manager: SEQUENTIAL SCAN 2) informix.a14: SEQUENTIAL SCAN NESTED LOOP JOIN 3) informix.a12: SEQUENTIAL SCAN Filters: (informix.a12.calendar_year_nbr = 2022 AND informix.a12.calendar_month_nbr = 6 ) NESTED LOOP JOIN 4) (Temp Table For Collection Subquery): SEQUENTIAL SCAN DYNAMIC HASH JOIN (Build Outer) Dynamic Hash Filters: (((Temp Table For Collection Subquery).generated_date = informix.a12.the_date AND (Temp Table For Collection Subquery).department_nbr = CASE WHEN informix.merch_manager.merch_manager_id = 1101 THEN 100 WHEN informix.merch_manager.merch_manager_id = 1202 THEN 200 WHEN informix.merch_manager.merch_manager_id = 1303 THEN 300 WHEN informix.merch_manager.merch_manager_id = 1405 THEN 401 WHEN informix.merch_manager.merch_manager_id = 1505 THEN 500 WHEN informix.merch_manager.merch_manager_id = 1506 THEN 501 WHEN informix.merch_manager.merch_manager_id = 1606 THEN 600 WHEN informix.merch_manager.merch_manager_id = 1707 THEN 700 WHEN informix.merch_manager.merch_manager_id IN (1900 , 1909 ) THEN 900 ELSE informix.merch_manager.merch_manager_id END::integer) AND (Temp Table For Collection Subquery).fine_type_id = informix.a14.fine_type_id ) 5) informix.a13: INDEX PATH Filters: (informix.a13.location_type_id IN (1 , 2 )OR (Temp Table For Collection Subquery).yard = 3598 ) (1) Index Name: skumoper.yard_no Index Keys: yard (Serial, fragments: ALL) Lower Index Filter: (Temp Table For Collection Subquery).yard = informix.a13.yard NESTED LOOP JOIN Query statistics: ----------------- Table map : ---------------------------- Internal name Table name ---------------------------- t1 merch_manager t2 a14 t3 a12 t4 a t5 a t6 (Temp Table For Collection Subquery) t7 b t8 (Temp Table For View) t9 c t10 (Temp Table For View) t11 d t12 (Temp Table For View) t13 (Temp Table For Collection Subquery) t14 a13 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t1 11 11 11 00:00.00 2 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t2 165 15 165 00:00.00 2 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 165 166 00:00.00 29 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t3 4950 32 1567005 00:00.69 518 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 4950 5296 00:00.69 85743 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t4 3 10 93 00:00.03 9 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t5 360 786 3911 00:00.43 466 type rows_prod rows_cons_1 rows_cons_2 time ------------------------------------------------------ merge 363 3 360 00:00.47 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t6 363 796 363 00:00.00 107 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t7 5511 5511 5511 00:00.00 188 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t8 5511 5511 5511 00:00.00 193 type rows_prod est_rows rows_bld rows_prb novrflo time est_cost ------------------------------------------------------------------------------ hjoin 363 796 5511 363 0 00:00.00 1477 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t9 128824 128824 128824 00:00.09 5342 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t10 128824 128824 128824 00:00.06 4810 type rows_prod est_rows rows_bld rows_prb novrflo time est_cost ------------------------------------------------------------------------------ hjoin 363 1025440 128824 363 0 00:00.18 60723 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t11 82737064 82737064 82737064 02:36.36 3096317 type rows_prod est_rows rows_cons time est_cost ------------------------------------------------------------ group 49164244 49164244 82737064 03:35.80 232785200 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t12 49164244 49164244 49164244 00:19.95 1858837 type rows_prod est_rows rows_bld rows_prb novrflo time est_cost ------------------------------------------------------------------------------ hjoin 363 504149606400 49164244 363 0 02:50.16 39394052 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t13 363 504149606400 363 00:00.00 12977004544 type rows_prod est_rows rows_bld rows_prb novrflo time est_cost ------------------------------------------------------------------------------ hjoin 6 187443648 4950 363 0 00:00.70 108975579136 type table rows_prod est_rows rows_scan time est_cost ------------------------------------------------------------------- scan t14 6 615 6 00:00.00 0 type rows_prod est_rows time est_cost ------------------------------------------------- nljoin 6 102772832 00:00.70 109033119744 type rows_prod est_rows rows_cons time est_cost ------------------------------------------------------------ group 6 102768048 6 00:00.70 319758624