{ "query_optimization": { "r_total_time_ms": 0.632113456 }, "query_block": { "select_id": 1, "cost": 191.2655859, "r_loops": 1, "r_total_time_ms": 80877.31723, "nested_loop": [ { "table": { "table_name": "a", "access_type": "ALL", "possible_keys": ["idx_sp_patient_id", "idx01"], "loops": 1, "r_loops": 1, "rows": 8935, "r_rows": 8935, "cost": 1.634451875, "r_table_time_ms": 17.25423444, "r_other_time_ms": 10.94020979, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "attached_condition": "STG_CVS.a.sp_patient_id is not null and STG_CVS.a.status_date is not null and STG_CVS.a.record_id is not null" } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "130", "used_key_parts": ["sp_patient_id", "status_date", "record_id"], "ref": [ "STG_CVS.a.sp_patient_id", "STG_CVS.a.status_date", "STG_CVS.a.record_id" ], "loops": 8935, "r_loops": 8935, "rows": 1, "r_rows": 0.313150532, "cost": 2.43652089, "r_table_time_ms": 4.847699847, "r_other_time_ms": 4.937194588, "filtered": 100, "r_filtered": 100, "materialized": { "lateral": 1, "query_block": { "select_id": 3, "cost": 0.001865065, "r_loops": 8935, "r_total_time_ms": 80564.24882, "outer_ref_condition": "STG_CVS.a.sp_patient_id is not null and STG_CVS.a.status_date is not null", "nested_loop": [ { "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": ["STG_CVS.a.sp_patient_id", "STG_CVS.a.status_date"], "loops": 1, "r_loops": 8935, "rows": 1, "r_rows": 1.0691662, "cost": 9.299576e-4, "r_table_time_ms": 56.24085779, "r_other_time_ms": 16.665884, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "attached_condition": "STG_CVS.a.sp_patient_id is not null and STG_CVS.a.status_date is not null", "using_index": true } }, { "table": { "table_name": "", "access_type": "ref", "possible_keys": ["key0"], "key": "key0", "key_length": "27", "used_key_parts": ["sp_patient_id", "status_date"], "ref": ["STG_CVS.a.sp_patient_id", "STG_CVS.a.status_date"], "loops": 1.0358, "r_loops": 9553, "rows": 1, "r_rows": 0.311525175, "cost": 2.824564e-4, "r_table_time_ms": 3458.099826, "r_other_time_ms": 4667.675054, "filtered": 100, "r_filtered": 100, "materialized": { "query_block": { "select_id": 4, "cost": 0.006587546, "r_loops": 9553, "r_total_time_ms": 80454.05574, "filesort": { "sort_key": "STG_CVS.a.sp_patient_id", "r_loops": 9553, "r_total_time_ms": 3672.150794, "r_used_priority_queue": false, "r_output_rows": 2709, "r_buffer_size": "(varied across executions)", "r_sort_mode": "sort_key,rowid", "temporary_table": { "nested_loop": [ { "table": { "table_name": "a", "access_type": "index", "possible_keys": ["idx_sp_patient_id", "idx01"], "key": "idx01", "key_length": "130", "used_key_parts": [ "sp_patient_id", "status_date", "record_id" ], "loops": 1, "r_loops": 9553, "rows": 8935, "r_rows": 8935, "cost": 1.336190992, "r_table_time_ms": 25822.99687, "r_other_time_ms": 42830.2773, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "using_index": true } } ] } } } } } } ] } } } }, { "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": 8935, "r_loops": 2798, "rows": 1, "r_rows": 0.997140815, "cost": 18.08811229, "r_table_time_ms": 64.53270021, "r_other_time_ms": 5.466674373, "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": 8935, "r_loops": 2798, "rows": 1, "r_rows": 0.993924232, "cost": 17.22365149, "r_table_time_ms": 29.83695953, "r_other_time_ms": 2.456369712, "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": 8935, "r_loops": 2798, "rows": 1, "r_rows": 0.997140815, "cost": 18.08811229, "r_table_time_ms": 27.95569628, "r_other_time_ms": 1.800227348, "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": 8935, "r_loops": 2798, "rows": 1, "r_rows": 0.997140815, "cost": 17.59966428, "r_table_time_ms": 25.1739805, "r_other_time_ms": 1.481109039, "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": 8935, "r_loops": 2798, "rows": 4, "r_rows": 4.070407434, "cost": 51.16197083, "r_table_time_ms": 67.85125369, "r_other_time_ms": 6.223805536, "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": 35740, "r_loops": 2798, "rows": 1, "r_rows": 0.997140815, "cost": 65.03310194, "r_table_time_ms": 23.47930436, "r_other_time_ms": 15.5702321, "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.44394686, "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.340799766, "r_other_time_ms": 0.051280442, "r_engine_stats": {}, "filtered": 100, "r_filtered": 100, "index_condition": "STG_ANALYTICS.ref_status.status_code = STG_CVS.a.status_reason_code" } } ] } } } ] } }