{ "query_optimization": { "r_total_time_ms": 0.604557704 }, "query_block": { "select_id": 1, "cost": 217.9088104, "r_loops": 1, "r_total_time_ms": 230.7228514, "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 9254, "r_rows": 2709, "cost": 0.423511288, "r_table_time_ms": 0.340997639, "r_other_time_ms": 2.234179886, "filtered": 100, "r_filtered": 100, "attached_condition": "b.sp_patient_id is not null and b.status_date is not null and b.record_id is not null", "materialized": { "query_block": { "select_id": 3, "cost": 13.10668129, "r_loops": 1, "r_total_time_ms": 20.19887627, "filesort": { "sort_key": "STG_CVS.a.sp_patient_id, STG_CVS.a.status_date", "r_loops": 1, "r_total_time_ms": 0.400790971, "r_used_priority_queue": false, "r_output_rows": 2709, "r_buffer_size": "108Kb", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "", "access_type": "ALL", "loops": 1, "r_loops": 1, "rows": 8935, "r_rows": 2709, "cost": 0.409256901, "r_table_time_ms": 0.195283044, "r_other_time_ms": 0.968075362, "filtered": 100, "r_filtered": 100, "attached_condition": "b.sp_patient_id is not null and b.status_date is not null", "materialized": { "query_block": { "select_id": 4, "cost": 7.259926167, "r_loops": 1, "r_total_time_ms": 5.361947784, "nested_loop": [ { "table": { "table_name": "a", "access_type": "index", "key": "idx01", "key_length": "130", "used_key_parts": [ "sp_patient_id", "status_date", "record_id" ], "loops": 1, "r_loops": 1, "rows": 8935, "r_rows": 8935, "cost": 1.336190992, "r_table_time_ms": 2.98117813, "r_other_time_ms": 1.839928307, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } }, { "table": { "table_name": "a", "access_type": "ref", "possible_keys": ["idx_sp_patient_id", "idx01"], "key": "idx01", "key_length": "27", "used_key_parts": ["sp_patient_id", "status_date"], "ref": ["b.sp_patient_id", "b.status_date"], "loops": 8935, "r_loops": 2709, "rows": 1, "r_rows": 1.032853451, "cost": 6.560383956, "r_table_time_ms": 9.963142547, "r_other_time_ms": 3.095794002, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } } } }, { "table": { "table_name": "a", "access_type": "ref", "possible_keys": ["idx_sp_patient_id", "idx01"], "key": "idx01", "key_length": "130", "used_key_parts": ["sp_patient_id", "status_date", "record_id"], "ref": ["b.sp_patient_id", "b.status_date", "b.record_id"], "loops": 9254, "r_loops": 2709, "rows": 1, "r_rows": 1.032853451, "cost": 16.93023431, "r_table_time_ms": 26.97019765, "r_other_time_ms": 1.046196338, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100 } }, { "table": { "table_name": "x", "access_type": "ref", "possible_keys": ["idx02", "idx04"], "key": "idx02", "key_length": "94", "used_key_parts": ["extnl_id", "extnl_id_type"], "ref": ["STG_CVS.a.hcp_npi", "const"], "loops": 9580.6662, "r_loops": 2798, "rows": 1, "r_rows": 0.997140815, "cost": 19.39520604, "r_table_time_ms": 34.61634226, "r_other_time_ms": 1.854481548, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(ODS_CUST_DAILY.x.extnl_id_type = 'NPI' and STG_CVS.a.hcp_npi = ODS_CUST_DAILY.x.extnl_id and trigcond(STG_CVS.a.hcp_npi is not null))" } }, { "table": { "table_name": "pd", "access_type": "ref", "possible_keys": ["cust_gid"], "key": "cust_gid", "key_length": "4", "used_key_parts": ["cust_gid"], "ref": ["ODS_CUST_DAILY.x.cust_gid"], "loops": 9580.6662, "r_loops": 2798, "rows": 1, "r_rows": 0.993924232, "cost": 18.3985128, "r_table_time_ms": 20.52175067, "r_other_time_ms": 0.925482147, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(trigcond(ODS_CUST_DAILY.x.cust_gid is not null))" } }, { "table": { "table_name": "cct", "access_type": "ref", "possible_keys": ["idx01", "idx03", "idx05", "idx06"], "key": "idx06", "key_length": "8", "used_key_parts": ["cust_gid1", "team_org_gid"], "ref": ["ODS_CUST_DAILY.x.cust_gid", "const"], "loops": 9580.6662, "r_loops": 2798, "rows": 1, "r_rows": 0.997140815, "cost": 19.39520604, "r_table_time_ms": 19.4680818, "r_other_time_ms": 0.919590854, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(trigcond(ODS_CUST_DAILY.x.cust_gid is not null))" } }, { "table": { "table_name": "ca", "access_type": "ref", "possible_keys": ["idx01"], "key": "idx01", "key_length": "4", "used_key_parts": ["cust_gid"], "ref": ["ODS_CUST_DAILY.cct.cust_gid2"], "loops": 9580.6662, "r_loops": 2798, "rows": 1, "r_rows": 0.997140815, "cost": 18.7745256, "r_table_time_ms": 17.90026873, "r_other_time_ms": 0.806985526, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(trigcond(cct.cust_gid2 is not null))" } }, { "table": { "table_name": "co", "access_type": "ref", "possible_keys": ["idx01", "idx03"], "key": "idx01", "key_length": "4", "used_key_parts": ["cust_gid"], "ref": ["ODS_CUST_DAILY.x.cust_gid"], "loops": 9580.6662, "r_loops": 2798, "rows": 4, "r_rows": 4.070407434, "cost": 54.85906711, "r_table_time_ms": 55.36099361, "r_other_time_ms": 3.016905071, "r_engine_stats": {}, "filtered": 100, "r_filtered": 24.49732198, "attached_condition": "trigcond(co.credit_ind = 'y' and co.team_org_gid = 2 and trigcond(ODS_CUST_DAILY.x.cust_gid is not null))" } }, { "table": { "table_name": "oa", "access_type": "ref", "possible_keys": ["idx01"], "key": "idx01", "key_length": "4", "used_key_parts": ["terr_org_gid"], "ref": ["ODS_CUST_DAILY.co.org_gid"], "loops": 38322.6648, "r_loops": 2798, "rows": 1, "r_rows": 0.997140815, "cost": 69.73254722, "r_table_time_ms": 18.15573257, "r_other_time_ms": 6.802137681, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "attached_condition": "trigcond(trigcond(co.org_gid is not null))" } } ], "subqueries": [ { "expression_cache": { "r_loops": 2798, "r_hit_ratio": 98.9992852, "query_block": { "select_id": 2, "cost": 0.001933826, "r_loops": 28, "r_total_time_ms": 0.273862091, "outer_ref_condition": "STG_CVS.a.status_reason_code is not null", "nested_loop": [ { "table": { "table_name": "ref_status", "access_type": "eq_ref", "possible_keys": ["PRIMARY"], "key": "PRIMARY", "key_length": "12", "used_key_parts": ["status_code"], "ref": ["STG_CVS.a.status_reason_code"], "loops": 1, "r_loops": 28, "rows": 1, "r_rows": 1, "cost": 0.001933826, "r_table_time_ms": 0.224797476, "r_other_time_ms": 0.029226775, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "index_condition": "STG_ANALYTICS.ref_status.status_code = STG_CVS.a.status_reason_code" } } ] } } } ] } }