ANALYZE FORMAT=JSON SELECT ps.recid psRecId, CONCAT_WS('-', NULLIF(DATE_FORMAT(ps.recdt, '%m/%d/%y'), ''), NULLIF(TIME_FORMAT(ps.recdt, '%H:%i:%s'), '')) recDt, c.name compName,ps.distproductid, CONCAT_WS(' - ', NULLIF(l.storenbr, ''), NULLIF(l.sdesc, ''), NULLIF(a.address1, '')) locDesc, p.ldesc prodDesc, DATE_FORMAT(ps.saledate, '%m/%d%/%y') saleDate, ps.specificproductid spId, SUM(ps.quantity) soldQty, ps.unitprice unitPrice , ps.procflag FROM company c, product p, payonscan ps LEFT JOIN companymap cm ON cm.twcerecid = ps.customerlocid AND cm.clientid = 446385 AND fieldcode = 'LO' , location l LEFT JOIN address a ON a.recid = l.addressid WHERE ps.clientlocid = 708557 AND ps.customerlocid = l.recid AND ps.companyid = c.recid AND ps.distproductid = p.recid AND ps.saledate <= '2023-03-19' AND ps.saledate >= '2023-03-13' AND ( ( ps.companyid = 0 OR ps.companyid IS NULL) OR ( ps.companylocid = 0 OR ps.companylocid IS NULL) OR ( ps.pubproductid = 0 OR ps.pubproductid IS NULL) OR ( ps.distproductid = 0 OR ps.distproductid IS NULL) OR ( ps.specificproductid = 0 OR ps.specificproductid IS NULL) OR (cm.recid = 0 OR cm.recid IS NULL)) AND ps.procflag = 'X' GROUP BY ps.recid HAVING soldQty !=0 ORDER BY ps.saledate { "steps": [ { "join_preparation": { "select_id": 1, "steps": [ { "expanded_query": "select ps.recid AS psRecId,concat_ws('-',nullif(date_format(ps.recdt,'%m/%d/%y'),''),nullif(time_format(ps.recdt,'%H:%i:%s'),'')) AS recDt,c.`name` AS compName,ps.distproductid AS distproductid,concat_ws(' - ',nullif(l.storenbr,''),nullif(l.sdesc,''),nullif(a.address1,'')) AS locDesc,p.ldesc AS prodDesc,date_format(ps.saledate,'%m/%d%/%y') AS saleDate,ps.specificproductid AS spId,sum(ps.quantity) AS soldQty,ps.unitprice AS unitPrice,ps.procflag AS procflag from company c join product p join (payonscan ps left join companymap cm on(cm.twcerecid = ps.customerlocid and cm.clientid = 446385 and cm.fieldcode = 'LO')) join (location l left join address a on(a.recid = l.addressid)) where ps.clientlocid = 708557 and ps.customerlocid = l.recid and ps.companyid = c.recid and ps.distproductid = p.recid and ps.saledate <= '2023-03-19' and ps.saledate >= '2023-03-13' and (ps.companyid = 0 or ps.companyid is null or ps.companylocid = 0 or ps.companylocid is null or ps.pubproductid = 0 or ps.pubproductid is null or ps.distproductid = 0 or ps.distproductid is null or ps.specificproductid = 0 or ps.specificproductid is null or cm.recid = 0 or cm.recid is null) and ps.procflag = 'X' group by ps.recid having soldQty <> 0 order by ps.saledate" } ] } }, { "join_optimization": { "select_id": 1, "steps": [ { "condition_processing": { "condition": "WHERE", "original_condition": "ps.clientlocid = 708557 and ps.customerlocid = l.recid and ps.companyid = c.recid and ps.distproductid = p.recid and ps.saledate <= '2023-03-19' and ps.saledate >= '2023-03-13' and (ps.companyid = 0 or ps.companyid is null or ps.companylocid = 0 or ps.companylocid is null or ps.pubproductid = 0 or ps.pubproductid is null or ps.distproductid = 0 or ps.distproductid is null or ps.specificproductid = 0 or ps.specificproductid is null or cm.recid = 0 or cm.recid is null) and ps.procflag = 'X'", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "ps.saledate <= '2023-03-19' and ps.saledate >= '2023-03-13' and (multiple equal(0, ps.companyid, c.recid) or ps.companyid is null or multiple equal(0, ps.companylocid) or ps.companylocid is null or multiple equal(0, ps.pubproductid) or ps.pubproductid is null or multiple equal(0, ps.distproductid, p.recid) or ps.distproductid is null or multiple equal(0, ps.specificproductid) or ps.specificproductid is null or multiple equal(0, cm.recid) or cm.recid is null) and multiple equal(708557, ps.clientlocid) and multiple equal(ps.customerlocid, l.recid) and multiple equal(ps.companyid, c.recid) and multiple equal(ps.distproductid, p.recid) and multiple equal('X', ps.procflag)" }, { "transformation": "constant_propagation", "resulting_condition": "ps.saledate <= '2023-03-19' and ps.saledate >= '2023-03-13' and (multiple equal(0, ps.companyid, c.recid) or ps.companyid is null or multiple equal(0, ps.companylocid) or ps.companylocid is null or multiple equal(0, ps.pubproductid) or ps.pubproductid is null or multiple equal(0, ps.distproductid, p.recid) or ps.distproductid is null or multiple equal(0, ps.specificproductid) or ps.specificproductid is null or multiple equal(0, cm.recid) or cm.recid is null) and multiple equal(708557, ps.clientlocid) and multiple equal(ps.customerlocid, l.recid) and multiple equal(ps.companyid, c.recid) and multiple equal(ps.distproductid, p.recid) and multiple equal('X', ps.procflag)" }, { "transformation": "trivial_condition_removal", "resulting_condition": "ps.saledate <= '2023-03-19' and ps.saledate >= '2023-03-13' and (multiple equal(0, ps.companyid, c.recid) or ps.companyid is null or multiple equal(0, ps.companylocid) or ps.companylocid is null or multiple equal(0, ps.pubproductid) or ps.pubproductid is null or multiple equal(0, ps.distproductid, p.recid) or ps.distproductid is null or multiple equal(0, ps.specificproductid) or ps.specificproductid is null or multiple equal(0, cm.recid) or cm.recid is null) and multiple equal(708557, ps.clientlocid) and multiple equal(ps.customerlocid, l.recid) and multiple equal(ps.companyid, c.recid) and multiple equal(ps.distproductid, p.recid) and multiple equal('X', ps.procflag)" } ] } }, { "condition_processing": { "condition": "HAVING", "original_condition": "soldQty <> 0", "steps": [ { "transformation": "equality_propagation", "resulting_condition": "soldQty <> 0" }, { "transformation": "constant_propagation", "resulting_condition": "soldQty <> 0" }, { "transformation": "trivial_condition_removal", "resulting_condition": "soldQty <> 0" } ] } }, { "table_dependencies": [ { "table": "c", "row_may_be_null": false, "map_bit": 0, "depends_on_map_bits": [] }, { "table": "p", "row_may_be_null": false, "map_bit": 1, "depends_on_map_bits": [] }, { "table": "ps", "row_may_be_null": false, "map_bit": 2, "depends_on_map_bits": [] }, { "table": "cm", "row_may_be_null": true, "map_bit": 3, "depends_on_map_bits": ["2"] }, { "table": "l", "row_may_be_null": false, "map_bit": 4, "depends_on_map_bits": [] }, { "table": "a", "row_may_be_null": true, "map_bit": 5, "depends_on_map_bits": ["4"] } ] }, { "ref_optimizer_key_uses": [ { "table": "c", "field": "recid", "equals": "ps.companyid", "null_rejecting": true }, { "table": "p", "field": "recid", "equals": "ps.distproductid", "null_rejecting": true }, { "table": "ps", "field": "distproductid", "equals": "p.recid", "null_rejecting": true }, { "table": "ps", "field": "procflag", "equals": "'X'", "null_rejecting": false }, { "table": "ps", "field": "clientlocid", "equals": "708557", "null_rejecting": true }, { "table": "ps", "field": "customerlocid", "equals": "l.recid", "null_rejecting": true }, { "table": "ps", "field": "clientlocid", "equals": "708557", "null_rejecting": true }, { "table": "ps", "field": "companyid", "equals": "c.recid", "null_rejecting": true }, { "table": "ps", "field": "clientlocid", "equals": "708557", "null_rejecting": true }, { "table": "cm", "field": "clientid", "equals": "446385", "null_rejecting": true }, { "table": "cm", "field": "fieldcode", "equals": "'LO'", "null_rejecting": false }, { "table": "cm", "field": "twcerecid", "equals": "ps.customerlocid", "null_rejecting": true }, { "table": "cm", "field": "twcerecid", "equals": "l.recid", "null_rejecting": false }, { "table": "l", "field": "recid", "equals": "ps.customerlocid", "null_rejecting": true }, { "table": "l", "field": "recid", "equals": "ps.customerlocid", "null_rejecting": true }, { "table": "a", "field": "recid", "equals": "l.addressid", "null_rejecting": true } ] }, { "eliminated_tables": [] }, { "rows_estimation": [ { "table": "c", "range_analysis": { "table_scan": { "rows": 124578, "cost": 25782.6 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": ["recid"] }, { "index": "name", "usable": true, "key_parts": ["name", "recid"] }, { "index": "personid", "usable": true, "key_parts": ["personid", "recid"] } ], "best_covering_index_scan": { "index": "name", "cost": 25625.35872, "chosen": true }, "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "p", "range_analysis": { "table_scan": { "rows": 8425, "cost": 1848 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": ["recid"] }, { "index": "clientid", "usable": true, "key_parts": ["clientid", "vendorlocid", "recid"] }, { "index": "sdesc", "usable": true, "key_parts": ["sdesc", "recid"] }, { "index": "sortseqnbr", "usable": true, "key_parts": ["sortseqnbr", "recid"] }, { "index": "daycodeid", "usable": true, "key_parts": ["daycodeid", "recid"] }, { "index": "seasonid", "usable": true, "key_parts": ["seasonid", "recid"] }, { "index": "endeffdt", "usable": true, "key_parts": ["endeffdt", "recid"] }, { "index": "producttype", "usable": true, "key_parts": ["producttype", "recid"] }, { "index": "holidaysetid", "usable": true, "key_parts": ["holidaysetid", "recid"] }, { "index": "payablesdaycodeid", "usable": true, "key_parts": ["payablesdaycodeid", "recid"] }, { "index": "billingperioddaycodeid", "usable": true, "key_parts": ["billingperioddaycodeid", "recid"] }, { "index": "billingperiod", "usable": true, "key_parts": ["billingperiod", "recid"] }, { "index": "feedaycodeid", "usable": true, "key_parts": ["feedaycodeid", "recid"] }, { "index": "feeperioddaycodeid", "usable": true, "key_parts": ["feeperioddaycodeid", "recid"] }, { "index": "returnsbasis", "usable": true, "key_parts": ["returnsbasis", "recid"] }, { "index": "returnsexception", "usable": true, "key_parts": ["returnsexception", "recid"] }, { "index": "pickupcode", "usable": true, "key_parts": ["pickupcode", "recid"] }, { "index": "pubgroupmethod", "usable": false, "cause": "not applicable" }, { "index": "drawrounding", "usable": false, "cause": "not applicable" }, { "index": "returnspct", "usable": false, "cause": "not applicable" }, { "index": "producttypeid", "usable": false, "cause": "not applicable" }, { "index": "effdt", "usable": false, "cause": "not applicable" } ], "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "ps", "range_analysis": { "table_scan": { "rows": 2194876, "cost": 469888.2 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": ["recid"] }, { "index": "storenumber", "usable": true, "key_parts": ["storenumber", "recid"] }, { "index": "specificproductid", "usable": true, "key_parts": ["specificproductid", "recid"] }, { "index": "chain", "usable": true, "key_parts": ["chain", "recid"] }, { "index": "distributor", "usable": true, "key_parts": ["distributor", "recid"] }, { "index": "distproductid", "usable": true, "key_parts": ["distproductid", "recid"] }, { "index": "procflag", "usable": true, "key_parts": ["procflag", "recid"] }, { "index": "cp29", "usable": true, "key_parts": [ "processorid", "chain", "distributor", "storenumber", "upc", "saledate", "recid" ] }, { "index": "customerid", "usable": true, "key_parts": [ "clientlocid", "customerid", "companyid", "recid" ] }, { "index": "acctgprocflag", "usable": true, "key_parts": ["acctgprocflag", "periodenddt", "recid"] }, { "index": "customerlocid", "usable": true, "key_parts": ["customerlocid", "recid"] }, { "index": "reportdate", "usable": true, "key_parts": ["reportdate", "recid"] }, { "index": "clientlocid", "usable": true, "key_parts": ["clientlocid", "recid"] }, { "index": "periodenddt", "usable": true, "key_parts": ["periodenddt", "recid"] }, { "index": "remitdate", "usable": true, "key_parts": ["remitdate", "recid"] }, { "index": "companyid", "usable": true, "key_parts": ["companyid", "recid"] }, { "index": "clientlocid_2", "usable": true, "key_parts": ["clientlocid", "saledate", "procflag", "recid"] } ], "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "procflag", "ranges": ["(X) <= (procflag) <= (X)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 106898, "cost": 128366.4884, "chosen": true }, { "index": "customerid", "ranges": ["(708557) <= (clientlocid) <= (708557)"], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 858958, "cost": 1033555.709, "chosen": false, "cause": "cost" }, { "index": "clientlocid", "ranges": ["(708557) <= (clientlocid) <= (708557)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 858804, "cost": 1031929.153, "chosen": false, "cause": "cost" }, { "index": "clientlocid_2", "ranges": [ "(708557,2023-03-13,X) <= (clientlocid,saledate,procflag) <= (708557,2023-03-19,X)" ], "rowid_ordered": false, "using_mrr": false, "index_only": false, "rows": 20520, "cost": 24664.65541, "chosen": true } ], "analyzing_roworder_intersect": { "intersecting_indexes": [ { "index": "procflag", "index_scan_cost": 90.74344828, "cumulated_index_scan_cost": 90.74344828, "disk_sweep_cost": 107452.1687, "cumulative_total_cost": 107542.9122, "usable": true, "matching_rows_now": 106898, "intersect_covering_with_this_index": false, "chosen": true }, { "index": "clientlocid", "index_scan_cost": 1366.207727, "cumulated_index_scan_cost": 1456.951175, "disk_sweep_cost": 42042.8297, "cumulative_total_cost": 43499.78087, "usable": true, "matching_rows_now": 41826.70456, "intersect_covering_with_this_index": false, "chosen": true } ], "clustered_pk": { "clustered_pk_added_to_intersect": false, "cause": "no clustered pk index" }, "chosen": false, "cause": "cost" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "not single_table" }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "clientlocid_2", "rows": 20520, "ranges": [ "(708557,2023-03-13,X) <= (clientlocid,saledate,procflag) <= (708557,2023-03-19,X)" ] }, "rows_for_plan": 20520, "cost_for_plan": 24664.65541, "chosen": true } } }, { "selectivity_for_indexes": [ { "index_name": "clientlocid_2", "selectivity_from_index": 0.009349048 } ], "selectivity_for_columns": [], "cond_selectivity": 0.009349048 }, { "table": "cm", "range_analysis": { "table_scan": { "rows": 294209, "cost": 60414.8 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": ["recid"] }, { "index": "companycode", "usable": true, "key_parts": [ "companycode", "fieldcode", "companyvalue", "twcerecid", "recid" ] }, { "index": "companyid", "usable": true, "key_parts": ["companyid", "recid"] }, { "index": "clientid", "usable": true, "key_parts": ["clientid", "recid"] }, { "index": "pubsubgroupid", "usable": true, "key_parts": ["pubsubgroupid", "recid"] }, { "index": "fieldcode", "usable": true, "key_parts": ["fieldcode", "recid"] }, { "index": "companyvalue", "usable": true, "key_parts": ["companyvalue", "recid"] }, { "index": "twcerecid", "usable": true, "key_parts": ["twcerecid", "recid"] }, { "index": "chaingroupid", "usable": true, "key_parts": ["chaingroupid", "recid"] }, { "index": "clientlocid", "usable": true, "key_parts": ["clientlocid", "recid"] }, { "index": "effdt", "usable": true, "key_parts": ["effdt", "recid"] }, { "index": "endeffdt", "usable": true, "key_parts": ["endeffdt", "recid"] } ], "setup_range_conditions": [], "analyzing_range_alternatives": { "range_scan_alternatives": [ { "index": "clientid", "ranges": ["(446385) <= (clientid) <= (446385)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 17690, "cost": 21254.8158, "chosen": true }, { "index": "fieldcode", "ranges": ["(LO) <= (fieldcode) <= (LO)"], "rowid_ordered": true, "using_mrr": false, "index_only": false, "rows": 3, "cost": 3.745366189, "chosen": true } ], "analyzing_roworder_intersect": { "intersecting_indexes": [ { "index": "fieldcode", "index_scan_cost": 1.000366189, "cumulated_index_scan_cost": 1.000366189, "disk_sweep_cost": 3.010650053, "cumulative_total_cost": 4.011016242, "usable": true, "matching_rows_now": 3, "intersect_covering_with_this_index": false, "chosen": true }, { "index": "clientid", "index_scan_cost": 28.67079646, "cumulated_index_scan_cost": 29.67116265, "disk_sweep_cost": 0, "cumulative_total_cost": 29.67116265, "usable": true, "matching_rows_now": 0.180381973, "intersect_covering_with_this_index": false, "chosen": false, "cause": "does not reduce cost" } ], "clustered_pk": { "clustered_pk_added_to_intersect": false, "cause": "no clustered pk index" }, "chosen": false, "cause": "cost" }, "analyzing_index_merge_union": [] }, "group_index_range": { "chosen": false, "cause": "not single_table" }, "chosen_range_access_summary": { "range_access_plan": { "type": "range_scan", "index": "fieldcode", "rows": 3, "ranges": ["(LO) <= (fieldcode) <= (LO)"] }, "rows_for_plan": 3, "cost_for_plan": 3.745366189, "chosen": true } } }, { "table": "cm", "rowid_filters": [ { "key": "fieldcode", "build_cost": 0.173324557, "rows": 3 } ] }, { "selectivity_for_indexes": [ { "index_name": "fieldcode", "selectivity_from_index": 1.019683e-5 }, { "index_name": "clientid", "selectivity_from_index": 0.060127324 } ], "selectivity_for_columns": [], "cond_selectivity": 6.131083e-7 }, { "table": "l", "range_analysis": { "table_scan": { "rows": 265610, "cost": 54375 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": ["recid"] }, { "index": "storenbr", "usable": true, "key_parts": ["storenbr", "recid"] }, { "index": "effdate", "usable": true, "key_parts": ["effdate", "recid"] }, { "index": "buildingid", "usable": true, "key_parts": ["buildingid", "recid"] }, { "index": "recid", "usable": true, "key_parts": ["recid", "endeffdate", "effdate"] }, { "index": "addressid", "usable": true, "key_parts": ["addressid", "recid"] } ], "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 }, { "table": "a", "range_analysis": { "table_scan": { "rows": 493922, "cost": 101639.4 }, "potential_range_indexes": [ { "index": "PRIMARY", "usable": true, "key_parts": ["recid"] }, { "index": "postalcode", "usable": true, "key_parts": ["postalcode", "recid"] }, { "index": "phone", "usable": true, "key_parts": ["phone", "recid"] }, { "index": "latitude", "usable": true, "key_parts": ["latitude", "recid"] }, { "index": "longitude", "usable": true, "key_parts": ["longitude", "recid"] }, { "index": "buildingid", "usable": true, "key_parts": ["buildingid", "recid"] } ], "setup_range_conditions": [], "group_index_range": { "chosen": false, "cause": "not single_table" } } }, { "selectivity_for_indexes": [], "selectivity_for_columns": [], "cond_selectivity": 1 } ] }, { "considered_execution_plans": [ { "plan_prefix": [], "table": "p", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 8425, "cost": 161, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 8425, "cost": 161, "uses_join_buffering": false } }, "rows_for_plan": 8425, "cost_for_plan": 1846, "rest_of_plan": [ { "plan_prefix": ["p"], "table": "ps", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "distproductid", "rows": 4534, "cost": 38215301.54, "chosen": true }, { "access_type": "ref", "index": "procflag", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 106898, "cost": 901364366.7, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "customerid", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 858958, "cost": 7260362450, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "clientlocid", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 858804, "cost": 7246918203, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "clientlocid_2", "rows": 52258, "cost": 440400305.3, "chosen": false, "cause": "cost" }, { "access_type": "range", "resulting_rows": 20520, "cost": 207799721.8, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 4534, "cost": 38215301.54, "uses_join_buffering": false } }, "rows_for_plan": 38198950, "cost_for_plan": 45856937.54, "selectivity": 0.009349048, "rest_of_plan": [ { "plan_prefix": ["p", "ps"], "table": "cm", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "clientid", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 17690, "cost": 6327089409, "chosen": true }, { "access_type": "ref", "index": "fieldcode", "used_range_estimates": true, "rows": 3, "cost": 1116142.644, "chosen": true }, { "access_type": "ref", "index": "twcerecid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 714317.3432, "chosen": true }, { "access_type": "range", "resulting_rows": 0.180381973, "cost": 1538949.939, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 714317.3432, "uses_join_buffering": false } }, "rows_for_plan": 357123.7983, "cost_for_plan": 46642679.65, "selectivity": 6.131083e-7, "rest_of_plan": [ { "plan_prefix": ["p", "ps", "cm"], "table": "c", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 357123.7983, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 357123.7983, "uses_join_buffering": false } }, "rows_for_plan": 0.218955557, "cost_for_plan": 46999803.49, "rest_of_plan": [ { "plan_prefix": ["p", "ps", "cm", "c"], "table": "l", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 357123.7983, "chosen": true }, { "access_type": "ref", "index": "recid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 0.437975257, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 0.437975257, "uses_join_buffering": false } }, "rows_for_plan": 0.218955557, "cost_for_plan": 46999803.97, "rest_of_plan": [ { "plan_prefix": ["p", "ps", "cm", "c", "l"], "table": "a", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 357123.7983, "chosen": true }, { "access_type": "scan", "resulting_rows": 493922, "cost": 624.6802042, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 493922, "cost": 624.6802042, "uses_join_buffering": false } }, "rows_for_plan": 108146.9666, "cost_for_plan": 47022058.04, "estimated_join_cardinality": 108146.9666 } ] } ] }, { "plan_prefix": ["p", "ps", "cm"], "table": "l", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 357123.7983, "chosen": true }, { "access_type": "ref", "index": "recid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 0.437975257, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 0.437975257, "uses_join_buffering": false } }, "rows_for_plan": 0.218955557, "cost_for_plan": 46642680.13, "rest_of_plan": [ { "plan_prefix": ["p", "ps", "cm", "l"], "table": "c", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 357123.7983, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 357123.7983, "uses_join_buffering": false } }, "rows_for_plan": 0.218955557, "cost_for_plan": 46999803.97, "rest_of_plan": [ { "plan_prefix": ["p", "ps", "cm", "l", "c"], "table": "a", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 357123.7983, "chosen": true }, { "access_type": "scan", "resulting_rows": 493922, "cost": 624.6802042, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 493922, "cost": 624.6802042, "uses_join_buffering": false } }, "rows_for_plan": 108146.9666, "cost_for_plan": 47022058.04, "pruned_by_cost": true } ] }, { "plan_prefix": ["p", "ps", "cm", "l"], "table": "a", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 357123.7983, "chosen": true }, { "access_type": "scan", "resulting_rows": 493922, "cost": 624.6802042, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 493922, "cost": 624.6802042, "uses_join_buffering": false } }, "rows_for_plan": 108146.9666, "cost_for_plan": 46664934.2, "rest_of_plan": [ { "plan_prefix": ["p", "ps", "cm", "l", "a"], "table": "c", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 357123.7983, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 357123.7983, "uses_join_buffering": false } }, "rows_for_plan": 108146.9666, "cost_for_plan": 47043687.39, "pruned_by_cost": true } ] } ] } ] }, { "plan_prefix": ["p", "ps"], "table": "c", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 357123.7983, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 357123.7983, "uses_join_buffering": false } }, "rows_for_plan": 357123.7983, "cost_for_plan": 46285486.1, "rest_of_plan": [ { "plan_prefix": ["p", "ps", "c"], "table": "cm", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "clientid", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 17690, "cost": 6327089409, "chosen": true }, { "access_type": "ref", "index": "fieldcode", "used_range_estimates": true, "rows": 3, "cost": 1116142.644, "chosen": true }, { "access_type": "ref", "index": "twcerecid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 714317.3432, "chosen": true }, { "access_type": "range", "resulting_rows": 0.180381973, "cost": 1538949.939, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 714317.3432, "uses_join_buffering": false } }, "rows_for_plan": 357123.7983, "cost_for_plan": 47071228.21, "pruned_by_cost": true }, { "plan_prefix": ["p", "ps", "c"], "table": "l", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 357123.7983, "chosen": true }, { "access_type": "ref", "index": "recid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 714352.2164, "chosen": false, "cause": "cost" }, { "access_type": "scan", "resulting_rows": 265610, "cost": 22518, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 357123.7983, "uses_join_buffering": false } }, "rows_for_plan": 357123.7983, "cost_for_plan": 46714034.66, "rest_of_plan": [ { "plan_prefix": ["p", "ps", "c", "l"], "table": "cm", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "clientid", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 17690, "cost": 6327089409, "chosen": true }, { "access_type": "ref", "index": "fieldcode", "used_range_estimates": true, "rows": 3, "cost": 1116142.644, "chosen": true }, { "access_type": "ref", "index": "twcerecid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 714317.3432, "chosen": true }, { "access_type": "range", "resulting_rows": 0.180381973, "cost": 1538949.939, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 714317.3432, "uses_join_buffering": false } }, "rows_for_plan": 357123.7983, "cost_for_plan": 47499776.76, "pruned_by_cost": true }, { "plan_prefix": ["p", "ps", "c", "l"], "table": "a", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 357123.7983, "chosen": true }, { "access_type": "scan", "resulting_rows": 493922, "cost": 1018874197, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 357123.7983, "uses_join_buffering": false } }, "rows_for_plan": 357123.7983, "cost_for_plan": 47142583.22, "pruned_by_cost": true } ] } ] }, { "plan_prefix": ["p", "ps"], "table": "l", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 357123.7983, "chosen": true }, { "access_type": "ref", "index": "recid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 714352.2164, "chosen": false, "cause": "cost" }, { "access_type": "scan", "resulting_rows": 265610, "cost": 18765, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 357123.7983, "uses_join_buffering": false } }, "rows_for_plan": 357123.7983, "cost_for_plan": 46285486.1, "pruned_by_heuristic": true } ] }, { "plan_prefix": ["p"], "table": "c", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 124578, "cost": 865, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 124578, "cost": 865, "uses_join_buffering": true } }, "rows_for_plan": 1049569650, "cost_for_plan": 209916641, "pruned_by_cost": true }, { "plan_prefix": ["p"], "table": "l", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 265610, "cost": 1251, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 265610, "cost": 1251, "uses_join_buffering": true } }, "rows_for_plan": 2237764250, "cost_for_plan": 447555947, "pruned_by_cost": true } ] }, { "plan_prefix": [], "table": "ps", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "procflag", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 106898, "cost": 106986.8684, "chosen": true }, { "access_type": "ref", "index": "customerid", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 858958, "cost": 861764.0891, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "clientlocid", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 858804, "cost": 860168.3327, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "clientlocid_2", "rows": 52258, "cost": 52273.03327, "chosen": true }, { "access_type": "range", "resulting_rows": 20520, "cost": 24664.65541, "chosen": true } ], "chosen_access_method": { "type": "range", "records": 20520, "cost": 24664.65541, "uses_join_buffering": false } }, "rows_for_plan": 20520, "cost_for_plan": 28768.65541, "rest_of_plan": [ { "plan_prefix": ["ps"], "table": "p", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20520, "chosen": true }, { "access_type": "scan", "resulting_rows": 8425, "cost": 161, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 20520, "uses_join_buffering": false } }, "rows_for_plan": 20520, "cost_for_plan": 53392.65541, "rest_of_plan": [ { "plan_prefix": ["ps", "p"], "table": "cm", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "clientid", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 17690, "cost": 363548649.7, "chosen": true }, { "access_type": "ref", "index": "fieldcode", "used_range_estimates": true, "rows": 3, "cost": 64132.51419, "chosen": true }, { "access_type": "ref", "index": "twcerecid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 41044.00757, "chosen": true }, { "access_type": "range", "resulting_rows": 0.180381973, "cost": 88426.62657, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 41044.00757, "uses_join_buffering": false } }, "rows_for_plan": 20520, "cost_for_plan": 98540.66297, "selectivity": 6.131083e-7, "rest_of_plan": [ { "plan_prefix": ["ps", "p", "cm"], "table": "c", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20520, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 20520, "uses_join_buffering": false } }, "rows_for_plan": 0.012580982, "cost_for_plan": 119060.6655, "rest_of_plan": [ { "plan_prefix": ["ps", "p", "cm", "c"], "table": "l", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20520, "chosen": true }, { "access_type": "ref", "index": "recid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 0.025165649, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 0.025165649, "uses_join_buffering": false } }, "rows_for_plan": 0.012580982, "cost_for_plan": 119060.6932, "rest_of_plan": [ { "plan_prefix": ["ps", "p", "cm", "c", "l"], "table": "a", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20520, "chosen": true }, { "access_type": "scan", "resulting_rows": 493922, "cost": 35.89354126, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 493922, "cost": 35.89354126, "uses_join_buffering": false } }, "rows_for_plan": 6214.023724, "cost_for_plan": 120339.3915, "estimated_join_cardinality": 6214.023724 } ] } ] }, { "plan_prefix": ["ps", "p", "cm"], "table": "l", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20520, "chosen": true }, { "access_type": "ref", "index": "recid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 0.025165649, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 0.025165649, "uses_join_buffering": false } }, "rows_for_plan": 0.012580982, "cost_for_plan": 98540.69066, "rest_of_plan": [ { "plan_prefix": ["ps", "p", "cm", "l"], "table": "c", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20520, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 20520, "uses_join_buffering": false } }, "rows_for_plan": 0.012580982, "cost_for_plan": 119060.6932, "rest_of_plan": [ { "plan_prefix": ["ps", "p", "cm", "l", "c"], "table": "a", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20520, "chosen": true }, { "access_type": "scan", "resulting_rows": 493922, "cost": 35.89354126, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 493922, "cost": 35.89354126, "uses_join_buffering": false } }, "rows_for_plan": 6214.023724, "cost_for_plan": 120339.3915, "pruned_by_cost": true } ] }, { "plan_prefix": ["ps", "p", "cm", "l"], "table": "a", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20520, "chosen": true }, { "access_type": "scan", "resulting_rows": 493922, "cost": 35.89354126, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 493922, "cost": 35.89354126, "uses_join_buffering": false } }, "rows_for_plan": 6214.023724, "cost_for_plan": 99819.38894, "rest_of_plan": [ { "plan_prefix": ["ps", "p", "cm", "l", "a"], "table": "c", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20520, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 20520, "uses_join_buffering": false } }, "rows_for_plan": 6214.023724, "cost_for_plan": 121582.1937, "pruned_by_cost": true } ] } ] } ] }, { "plan_prefix": ["ps", "p"], "table": "c", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20520, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 20520, "uses_join_buffering": false } }, "rows_for_plan": 20520, "cost_for_plan": 78016.65541, "rest_of_plan": [ { "plan_prefix": ["ps", "p", "c"], "table": "cm", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "clientid", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 17690, "cost": 363548649.7, "chosen": true }, { "access_type": "ref", "index": "fieldcode", "used_range_estimates": true, "rows": 3, "cost": 64132.51419, "chosen": true }, { "access_type": "ref", "index": "twcerecid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 41044.00757, "chosen": true }, { "access_type": "range", "resulting_rows": 0.180381973, "cost": 88426.62657, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 41044.00757, "uses_join_buffering": false } }, "rows_for_plan": 20520, "cost_for_plan": 123164.663, "pruned_by_cost": true }, { "plan_prefix": ["ps", "p", "c"], "table": "l", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20520, "chosen": true }, { "access_type": "ref", "index": "recid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 41046.01135, "chosen": false, "cause": "cost" }, { "access_type": "scan", "resulting_rows": 265610, "cost": 1251, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 20520, "uses_join_buffering": false } }, "rows_for_plan": 20520, "cost_for_plan": 102640.6554, "rest_of_plan": [ { "plan_prefix": ["ps", "p", "c", "l"], "table": "cm", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "clientid", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 17690, "cost": 363548649.7, "chosen": true }, { "access_type": "ref", "index": "fieldcode", "used_range_estimates": true, "rows": 3, "cost": 64132.51419, "chosen": true }, { "access_type": "ref", "index": "twcerecid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 41044.00757, "chosen": true }, { "access_type": "range", "resulting_rows": 0.180381973, "cost": 88426.62657, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 41044.00757, "uses_join_buffering": false } }, "rows_for_plan": 20520, "cost_for_plan": 147788.663, "pruned_by_cost": true }, { "plan_prefix": ["ps", "p", "c", "l"], "table": "a", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20520, "chosen": true }, { "access_type": "scan", "resulting_rows": 493922, "cost": 58543560, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 20520, "uses_join_buffering": false } }, "rows_for_plan": 20520, "cost_for_plan": 127264.6554, "pruned_by_cost": true } ] } ] }, { "plan_prefix": ["ps", "p"], "table": "l", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20520, "chosen": true }, { "access_type": "ref", "index": "recid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 41046.01135, "chosen": false, "cause": "cost" }, { "access_type": "scan", "resulting_rows": 265610, "cost": 1251, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 20520, "uses_join_buffering": false } }, "rows_for_plan": 20520, "cost_for_plan": 78016.65541, "pruned_by_heuristic": true } ] }, { "plan_prefix": ["ps"], "table": "cm", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "clientid", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 17690, "cost": 363548649.7, "chosen": true }, { "access_type": "ref", "index": "fieldcode", "used_range_estimates": true, "rows": 3, "cost": 64132.51419, "chosen": true }, { "access_type": "ref", "index": "twcerecid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 41044.00757, "chosen": true }, { "access_type": "range", "resulting_rows": 0.180381973, "cost": 88426.62657, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 1, "cost": 41044.00757, "uses_join_buffering": false } }, "rows_for_plan": 20520, "cost_for_plan": 73916.66297, "pruned_by_heuristic": true }, { "plan_prefix": ["ps"], "table": "c", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20520, "chosen": true }, { "type": "scan", "chosen": false, "cause": "cost" } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 20520, "uses_join_buffering": false } }, "rows_for_plan": 20520, "cost_for_plan": 53392.65541, "pruned_by_heuristic": true }, { "plan_prefix": ["ps"], "table": "l", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 20520, "chosen": true }, { "access_type": "ref", "index": "recid", "rowid_filter_skipped": "cost_factor <= 0", "rows": 1, "cost": 41046.01135, "chosen": false, "cause": "cost" }, { "access_type": "scan", "resulting_rows": 265610, "cost": 1251, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 20520, "uses_join_buffering": false } }, "rows_for_plan": 20520, "cost_for_plan": 53392.65541, "pruned_by_heuristic": true } ] }, { "plan_prefix": [], "table": "c", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 124578, "cost": 865, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 124578, "cost": 865, "uses_join_buffering": false } }, "rows_for_plan": 124578, "cost_for_plan": 25780.6, "rest_of_plan": [ { "plan_prefix": ["c"], "table": "ps", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "procflag", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 106898, "cost": 13328210098, "chosen": true }, { "access_type": "ref", "index": "customerid", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 858958, "cost": 1.073568e11, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "clientlocid", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 858804, "cost": 1.071581e11, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "companyid", "rows": 18921, "cost": 2357754039, "chosen": true }, { "access_type": "ref", "index": "clientlocid_2", "rows": 52258, "cost": 6512069939, "chosen": false, "cause": "cost" }, { "access_type": "range", "resulting_rows": 20520, "cost": 3072673441, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 18921, "cost": 2357754039, "uses_join_buffering": false } }, "rows_for_plan": 2357140338, "cost_for_plan": 2829207887, "pruned_by_cost": true }, { "plan_prefix": ["c"], "table": "p", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 8425, "cost": 161, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 8425, "cost": 161, "uses_join_buffering": true } }, "rows_for_plan": 1049569650, "cost_for_plan": 209939871.6, "pruned_by_cost": true }, { "plan_prefix": ["c"], "table": "l", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 265610, "cost": 1251, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 265610, "cost": 1251, "uses_join_buffering": true } }, "rows_for_plan": 33089162580, "cost_for_plan": 6617859548, "pruned_by_cost": true } ] }, { "plan_prefix": [], "table": "l", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 265610, "cost": 1251, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 265610, "cost": 1251, "uses_join_buffering": false } }, "rows_for_plan": 265610, "cost_for_plan": 54373, "rest_of_plan": [ { "plan_prefix": ["l"], "table": "ps", "best_access_path": { "considered_access_paths": [ { "access_type": "ref", "index": "procflag", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 106898, "cost": 28416782129, "chosen": true }, { "access_type": "ref", "index": "customerid", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 858958, "cost": 2.288932e11, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "customerlocid", "rows": 380, "cost": 101218354, "chosen": true }, { "access_type": "ref", "index": "clientlocid", "used_range_estimates": true, "rowid_filter_skipped": "worst/max seeks clipping", "rows": 858804, "cost": 2.284693e11, "chosen": false, "cause": "cost" }, { "access_type": "ref", "index": "clientlocid_2", "rows": 52258, "cost": 13884240368, "chosen": false, "cause": "cost" }, { "access_type": "range", "resulting_rows": 20520, "cost": 6551179122, "chosen": false } ], "chosen_access_method": { "type": "ref", "records": 380, "cost": 101218354, "uses_join_buffering": false } }, "rows_for_plan": 100931800, "cost_for_plan": 121459087, "pruned_by_cost": true }, { "plan_prefix": ["l"], "table": "c", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 124578, "cost": 2595, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 124578, "cost": 2595, "uses_join_buffering": true } }, "rows_for_plan": 33089162580, "cost_for_plan": 6617889484, "pruned_by_cost": true }, { "plan_prefix": ["l"], "table": "p", "best_access_path": { "considered_access_paths": [ { "access_type": "scan", "resulting_rows": 8425, "cost": 483, "chosen": true } ], "chosen_access_method": { "type": "scan", "records": 8425, "cost": 483, "uses_join_buffering": true } }, "rows_for_plan": 2237764250, "cost_for_plan": 447607706, "pruned_by_cost": true }, { "plan_prefix": ["l"], "table": "a", "best_access_path": { "considered_access_paths": [ { "access_type": "eq_ref", "index": "PRIMARY", "rows": 1, "cost": 265610, "chosen": true }, { "access_type": "scan", "resulting_rows": 493922, "cost": 757785330, "chosen": false } ], "chosen_access_method": { "type": "eq_ref", "records": 1, "cost": 265610, "uses_join_buffering": false } }, "rows_for_plan": 265610, "cost_for_plan": 373105, "pruned_by_cost": true } ] } ] }, { "best_join_order": ["ps", "p", "cm", "c", "l", "a"] }, { "attaching_conditions_to_tables": { "original_condition": "ps.clientlocid = 708557 and l.recid = ps.customerlocid and c.recid = ps.companyid and p.recid = ps.distproductid and ps.procflag = 'X' and ps.saledate <= '2023-03-19' and ps.saledate >= '2023-03-13' and (ps.companyid = 0 or ps.companylocid = 0 or ps.companylocid is null or ps.pubproductid = 0 or ps.pubproductid is null or ps.distproductid = 0 or ps.distproductid is null or ps.specificproductid = 0 or ps.specificproductid is null or cm.recid = 0 or cm.recid is null)", "attached_conditions_computation": [], "attached_conditions_summary": [ { "table": "ps", "attached": "ps.clientlocid = 708557 and ps.procflag = 'X' and ps.saledate <= '2023-03-19' and ps.saledate >= '2023-03-13' and ps.distproductid is not null and ps.companyid is not null and ps.customerlocid is not null" }, { "table": "p", "attached": null }, { "table": "cm", "attached": "trigcond(ps.companyid = 0 or ps.companylocid = 0 or ps.companylocid is null or ps.pubproductid = 0 or ps.pubproductid is null or ps.distproductid = 0 or ps.distproductid is null or ps.specificproductid = 0 or ps.specificproductid is null or cm.recid = 0 or cm.recid is null) and trigcond(cm.clientid = 446385 and cm.fieldcode = 'LO' and trigcond(ps.customerlocid is not null))" }, { "table": "c", "attached": null }, { "table": "l", "attached": null }, { "table": "a", "attached": "trigcond(a.recid = l.addressid)" } ] } } ] } }, { "join_execution": { "select_id": 1, "steps": [] } } ] }