SELECT DISTINCT FILL_NUMBER,PICKUP_DATE, FACILITY_ID,RX_NUMBER,PICKUP_ID,PICKUP_ID_QUAL,CTT_LEGACY_NUMBER,JURISDICTION_ID, RELATIONSHIP_CODE,PICKUP_PATIENT_ID_INDICATOR,RXC_FILL_SOLD_MARK_DATE,TIMEZONE_ID, STATE_CODE,PRESCRIPTION_FILL_ID,PICKUP_FIRST_NAME,PICKUP_LAST_NAME,PICKUP_STREET_ADDRESS, PICKUP_ZIPCODE,PICKUP_CITY,PICKUP_STATE,PICKUP_DATE_OF_BIRTH,PATIENT_ID_QUALIFIER_VALUE,PATIENT_QUALIFIER_TYPE, PATIENT_ID_JURISDICTION_CODE,PATIENT_ID_EXP_DATE,RFUFN,LAST_NAME,PATIENT_FIRST_NAME,PATIENT_LAST_NAME,PPI_MESSAGE_ID,DRUG_DB_CODE_QUALIFIER, DRUG_DB_CODE,PRESCRIBER_ORDER_NUM,PET_OWNER_FIRST_NAME,PET_OWNER_LAST_NAME,SUFFIX,ADDRESS_LINE2,SPECIES_CODE,PARTIAL_FILL_SEQUENCE_NUMBER, Acquired_Id,facility_num,IS_GCN_SRD,DRUG_SCHEDULE,PRODUCT_NUM,RTFN,SIG,SUBOXONE_DEA,USER_CREDENTIALS,SRD_ATTRIBUTE_SQ_ID,TREATMENT_TYPE,EXEMPTION_CODE, MME,DIAGNOSIS_CODE_QUALIFIER,DIAGNOSIS_CODE,CREATED_DATE,INTK_MESSAGE_ID FROM (SELECT DISTINCT RXP_PRESCRIPTION_FILL.FILL_NUMBER, RXP_PRESCRIPTION_FILL.PICKUP_DATE, RXP_PRESCRIPTION_FILL.FACILITY_ID, RXP_PRESCRIPTION_FILL.RX_NUMBER, RXP_PRESCRIPTION_FILL.PICKUP_ID, RXP_PRESCRIPTION_FILL.PICKUP_ID_QUAL, RXP_PRESCRIPTION_FILL.CTT_LEGACY_NUMBER, RXP_PRESCRIPTION_FILL.JURISDICTION_ID, RXP_PRESCRIPTION_FILL.RELATIONSHIP_CODE, RXP_PRESCRIPTION_FILL.PICKUP_PATIENT_ID_INDICATOR, RXP_PRESCRIPTION_FILL.RXC_FILL_SOLD_MARK_DATE, FACILITY.TIMEZONE_ID, (SELECT ZIP.STATE_CODE FROM ADDRESS ADS, ADDRESS_LINK ADL, ZIPCODE ZIP WHERE ADL.ADDRESS_NUM = ADS.ADDRESS_NUM AND ADL.OWNER_TYPE_CODE = 'C' AND ADS.CSZ_NUM = ZIP.CSZ_NUM AND ADL.OWNER = FACILITY.FACILITY_NUM AND ADL.USAGE = 8 AND ROWNUM < 2) STATE_CODE, RXP_PRESCRIPTION_FILL.PRESCRIPTION_FILL_ID, RXP_PRESCRIPTION_FILL.PICKUP_LAST_NAME, RXP_PRESCRIPTION_FILL.PICKUP_FIRST_NAME, RXP_PRESCRIPTION_FILL.PICKUP_STREET_ADDRESS, RXP_PRESCRIPTION_FILL.PICKUP_ZIPCODE, RXP_PRESCRIPTION_FILL.PICKUP_CITY, RXP_PRESCRIPTION_FILL.PICKUP_STATE, RXP_PRESCRIPTION_FILL.PICKUP_DATE_OF_BIRTH, RXP_PRESCRIPTION_FILL.PATIENT_ID_QUALIFIER_VALUE, RXP_PRESCRIPTION_FILL.PATIENT_QUALIFIER_TYPE, RXP_PRESCRIPTION_FILL.PATIENT_ID_JURISDICTION_CODE, RXP_PRESCRIPTION_FILL.PATIENT_ID_EXP_DATE, RXP_FILL_USER.FIRST_NAME AS RFUFN, RXP_FILL_USER.LAST_NAME, RXP_PRESCRIPTION_FILL.PATIENT_LAST_NAME, RXP_PRESCRIPTION_FILL.PATIENT_FIRST_NAME, RXP_PRESCRIPTION.PPI_MESSAGE_ID, RXP_PRESCRIPTION.DRUG_DB_CODE_QUALIFIER, RXP_PRESCRIPTION.DRUG_DB_CODE, RXP_PRESCRIPTION.PRESCRIBER_ORDER_NUM, RXP_PATIENT.PET_OWNER_LAST_NAME, RXP_PATIENT.PET_OWNER_FIRST_NAME, RXP_PATIENT.SUFFIX, RXP_PATIENT_ADDRESS.ADDRESS_LINE2, RXP_PATIENT.SPECIES_CODE, RXP_PRESCRIPTION_FILL.PARTIAL_FILL_SEQUENCE_NUMBER , RXP_PRESCRIPTION.Acquired_Id , FACILITY.FACILITY_NUM, RXP_PRESCRIPTION_FILL.IS_GCN_SRD, RXP_PRESCRIPTION_FILL.DRUG_SCHEDULE, RXP_PRESCRIPTION_FILL.PRODUCT_NUM, RXP_PATIENT.FIRST_NAME AS RTFN, RXP_PRESCRIPTION.SIG, RXP_PRESCRIPTION_FILL.SUBOXONE_DEA, RXP_FILL_USER.USER_CREDENTIALS, SRD_ATTRIBUTE_VALUE.SRD_ATTRIBUTE_SQ_ID, SRD_ATTRIBUTE_VALUE.TREATMENT_TYPE, SRD_ATTRIBUTE_VALUE.EXEMPTION_CODE, SRD_ATTRIBUTE_VALUE.MME, SRD_ATTRIBUTE_VALUE.DIAGNOSIS_CODE_QUALIFIER, SRD_ATTRIBUTE_VALUE.DIAGNOSIS_CODE, RXP_PRESCRIPTION_FILL.CREATED_DATE, RXC_INTK_CROSS_REF.MESSAGE_ID AS INTK_MESSAGE_ID FROM RXP_PRESCRIPTION_FILL SUBPARTITION (RPF_20221225_RPF_SP_14), FACILITY, RXP_FILL_USER, RXP_PRESCRIPTION, RXP_PATIENT, RXP_PATIENT_ADDRESS, SRD_ATTRIBUTE_VALUE, RXC_INTK_CROSS_REF WHERE RXP_PRESCRIPTION_FILL.FILL_STATUS = 7 AND RXP_PRESCRIPTION_FILL.RXC_FILL_SOLD_MARK_DATE BETWEEN TO_DATE('20221223' || '230000', 'YYYYMMDDHH24MISS') AND TO_DATE('20221225' || '055959', 'YYYYMMDDHH24MISS') AND RXP_PRESCRIPTION_FILL.LAST_UPDATED_DATE BETWEEN TO_DATE('20221224' || '000000', 'YYYYMMDDHH24MISS') AND TO_DATE('20221224' || '235959', 'YYYYMMDDHH24MISS') AND RXP_PRESCRIPTION_FILL.FACILITY_ID = FACILITY.FACILITY_ID AND RXP_PRESCRIPTION_FILL.PRESCRIPTION_FILL_ID = RXP_FILL_USER.PRESCRIPTION_FILL_ID AND RXP_FILL_USER.ACTIVITY = 4 AND RXP_PRESCRIPTION_FILL.PRESCRIPTION_ID = RXP_PRESCRIPTION.PRESCRIPTION_ID AND RXP_PRESCRIPTION_FILL.PATIENT_ID = RXP_PATIENT.PATIENT_ID AND RXP_PRESCRIPTION.PATIENT_ADDRESS_ID = RXP_PATIENT_ADDRESS.PATIENT_ADDRESS_ID AND SRD_ATTRIBUTE_VALUE.PRESCRIPTION_FILL_ID(+) = RXP_PRESCRIPTION_FILL.PRESCRIPTION_FILL_ID AND RXP_PRESCRIPTION.PPI_MESSAGE_ID = RXC_INTK_CROSS_REF.PPI_MESSAGE_NUM(+)) T1, (SELECT DISTINCT CV.VALUE AS VALUE,TEMP_C.DATA_GROUP FROM (SELECT CV.CONFIG_PROPERTY_NUM, MIN (TEMP_A.DISTANCE_TO_PARENT) MIN_DISTANCE_TO_PARENT FROM CONFIG_VALUE CV, (SELECT DGL.PARENT_DATA_GROUP DATA_GROUP, DGL.DISTANCE_TO_PARENT FROM data_group_link DGL WHERE data_group IN (SELECT f.facility_num FROM facility f)) TEMP_A WHERE CV.DATA_GROUP = TEMP_A.DATA_GROUP GROUP BY CV.CONFIG_PROPERTY_NUM) TEMP_B, CONFIG_VALUE CV, (SELECT DGL.PARENT_DATA_GROUP DATA_GROUP, DGL.DISTANCE_TO_PARENT FROM data_group_link DGL WHERE data_group IN (SELECT f.facility_num FROM facility f)) TEMP_C, CONFIG_PROPERTY CP WHERE CV.CONFIG_PROPERTY_NUM = TEMP_B.CONFIG_PROPERTY_NUM AND CV.DATA_GROUP = TEMP_C.DATA_GROUP AND TEMP_C.DISTANCE_TO_PARENT = TEMP_B.MIN_DISTANCE_TO_PARENT AND CP.CONFIG_PROPERTY_NUM = CV.CONFIG_PROPERTY_NUM AND CP.NAME = 'InternalApp.EnableSRDDataModelChangeforGGLagImprovement') T2 WHERE T1.FACILITY_NUM = T2.DATA_GROUP (+) AND NVL(T2.VALUE,'N')='N' Plan hash value: 1897663655 ---------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 7412 (100)| | | | | 1 | COUNT STOPKEY | | | | | | | | | 2 | NESTED LOOPS | | 1 | 37 | 10 (0)| 00:00:01 | | | | 3 | NESTED LOOPS | | 1 | 37 | 10 (0)| 00:00:01 | | | | 4 | NESTED LOOPS | | 1 | 28 | 9 (0)| 00:00:01 | | | | 5 | TABLE ACCESS BY INDEX ROWID BATCHED | ADDRESS_LINK | 1 | 17 | 7 (0)| 00:00:01 | | | | 6 | INDEX RANGE SCAN | ADDRESS_LINK_IN1 | 16 | | 3 (0)| 00:00:01 | | | | 7 | TABLE ACCESS BY INDEX ROWID | ADDRESS | 1 | 11 | 2 (0)| 00:00:01 | | | | 8 | INDEX UNIQUE SCAN | ADDRESS_PK | 1 | | 1 (0)| 00:00:01 | | | | 9 | INDEX UNIQUE SCAN | ZIPCODE_PK | 1 | | 0 (0)| | | | | 10 | TABLE ACCESS BY INDEX ROWID | ZIPCODE | 1 | 9 | 1 (0)| 00:00:01 | | | | 11 | HASH UNIQUE | | 1 | 1858 | 7412 (2)| 00:00:01 | | | | 12 | FILTER | | | | | | | | | 13 | NESTED LOOPS OUTER | | 1 | 1858 | 7401 (2)| 00:00:01 | | | | 14 | NESTED LOOPS | | 1 | 354 | 5547 (1)| 00:00:01 | | | | 15 | NESTED LOOPS | | 1 | 322 | 5538 (1)| 00:00:01 | | | | 16 | NESTED LOOPS OUTER | | 1 | 313 | 5535 (1)| 00:00:01 | | | | 17 | NESTED LOOPS OUTER | | 1 | 284 | 5532 (1)| 00:00:01 | | | | 18 | NESTED LOOPS | | 1 | 255 | 5529 (1)| 00:00:01 | | | | 19 | NESTED LOOPS | | 1 | 180 | 5526 (1)| 00:00:01 | | | | 20 | NESTED LOOPS | | 1 | 156 | 5523 (1)| 00:00:01 | | | | 21 | PARTITION COMBINED ITERATOR | | 1 | 133 | 5521 (1)| 00:00:01 | KEY | KEY | | 22 | TABLE ACCESS FULL | RXP_PRESCRIPTION_FILL | 1 | 133 | 5521 (1)| 00:00:01 | 3854 | 3854 | | 23 | TABLE ACCESS BY INDEX ROWID BATCHED | FACILITY | 1 | 23 | 2 (0)| 00:00:01 | | | | 24 | INDEX RANGE SCAN | FACILITY_IN5 | 1 | | 1 (0)| 00:00:01 | | | | 25 | TABLE ACCESS BY INDEX ROWID | RXP_PATIENT | 1 | 24 | 3 (0)| 00:00:01 | | | | 26 | INDEX UNIQUE SCAN | RXP_PATIENT_PK | 1 | | 2 (0)| 00:00:01 | | | | 27 | TABLE ACCESS BY GLOBAL INDEX ROWID | RXP_PRESCRIPTION | 1 | 75 | 3 (0)| 00:00:01 | ROWID | ROWID | | 28 | INDEX UNIQUE SCAN | RXP_PRESCRIPTION_PK | 1 | | 2 (0)| 00:00:01 | | | | 29 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| RXC_INTK_CROSS_REF | 1 | 29 | 3 (0)| 00:00:01 | ROWID | ROWID | | 30 | INDEX RANGE SCAN | RXC_INTK_CROSS_REF_IN1 | 1 | | 3 (0)| 00:00:01 | | | | 31 | TABLE ACCESS BY INDEX ROWID BATCHED | SRD_ATTRIBUTE_VALUE | 1 | 29 | 3 (0)| 00:00:01 | | | | 32 | INDEX RANGE SCAN | SRD_ATTRIBUTE_VALUE_IN1 | 1 | | 2 (0)| 00:00:01 | | | | 33 | TABLE ACCESS BY INDEX ROWID | RXP_PATIENT_ADDRESS | 1 | 9 | 3 (0)| 00:00:01 | | | | 34 | INDEX UNIQUE SCAN | RXP_PATIENT_ADDRESS_PK | 1 | | 2 (0)| 00:00:01 | | | | 35 | PARTITION RANGE ITERATOR | | 1 | 32 | 9 (0)| 00:00:01 | KEY | KEY | | 36 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED | RXP_FILL_USER | 1 | 32 | 9 (0)| 00:00:01 | ROWID | ROWID | | 37 | INDEX RANGE SCAN | RXP_FILL_USER_IN1 | 5 | | 4 (0)| 00:00:01 | KEY | KEY | | 38 | VIEW PUSHED PREDICATE | | 1 | 1504 | 1853 (6)| 00:00:01 | | | | 39 | SORT UNIQUE | | 1 | 102 | 1853 (6)| 00:00:01 | | | | 40 | NESTED LOOPS SEMI | | 1 | 102 | 1852 (6)| 00:00:01 | | | | 41 | NESTED LOOPS SEMI | | 1 | 96 | 1852 (6)| 00:00:01 | | | | 42 | HASH JOIN SEMI | | 1 | 48 | 1851 (6)| 00:00:01 | | | | 43 | NESTED LOOPS | | 9 | 261 | 28 (0)| 00:00:01 | | | | 44 | NESTED LOOPS | | 9 | 261 | 28 (0)| 00:00:01 | | | | 45 | INDEX RANGE SCAN | DATA_GROUP_LINK_IN1 | 9 | 126 | 3 (0)| 00:00:01 | | | | 46 | INDEX RANGE SCAN | CONFIG_VALUE_FK2 | 1 | | 2 (0)| 00:00:01 | | | | 47 | TABLE ACCESS BY INDEX ROWID | CONFIG_VALUE | 1 | 15 | 3 (0)| 00:00:01 | | | | 48 | VIEW | | 2432 | 46208 | 1823 (6)| 00:00:01 | | | | 49 | SORT GROUP BY | | 2432 | 72960 | 1823 (6)| 00:00:01 | | | | 50 | HASH JOIN | | 1847K| 52M| 1759 (2)| 00:00:01 | | | | 51 | VIEW | VW_GBC_11 | 14989 | 263K| 141 (10)| 00:00:01 | | | | 52 | SORT GROUP BY | | 14989 | 292K| 141 (10)| 00:00:01 | | | | 53 | NESTED LOOPS SEMI | | 97439 | 1903K| 138 (8)| 00:00:01 | | | | 54 | INDEX FAST FULL SCAN | DATA_GROUP_LINK_PK | 128K| 1760K| 129 (1)| 00:00:01 | | | | 55 | INDEX UNIQUE SCAN | FACILITY_PK | 8578 | 51468 | 0 (0)| | | | | 56 | INDEX FAST FULL SCAN | CONFIG_VALUE_PK | 1847K| 21M| 1610 (1)| 00:00:01 | | | | 57 | TABLE ACCESS BY INDEX ROWID | CONFIG_PROPERTY | 1 | 48 | 1 (0)| 00:00:01 | | | | 58 | INDEX UNIQUE SCAN | CONFIG_PROPERTY_PK | 1 | | 0 (0)| | | | | 59 | INDEX UNIQUE SCAN | FACILITY_PK | 11339 | 68034 | 0 (0)| | | | ---------------------------------------------------------------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$3 5 - SEL$3 / ADL@SEL$3 6 - SEL$3 / ADL@SEL$3 7 - SEL$3 / ADS@SEL$3 8 - SEL$3 / ADS@SEL$3 9 - SEL$3 / ZIP@SEL$3 10 - SEL$3 / ZIP@SEL$3 11 - SEL$F5BB74E1 22 - SEL$F5BB74E1 / RXP_PRESCRIPTION_FILL@SEL$2 23 - SEL$F5BB74E1 / FACILITY@SEL$2 24 - SEL$F5BB74E1 / FACILITY@SEL$2 25 - SEL$F5BB74E1 / RXP_PATIENT@SEL$2 26 - SEL$F5BB74E1 / RXP_PATIENT@SEL$2 27 - SEL$F5BB74E1 / RXP_PRESCRIPTION@SEL$2 28 - SEL$F5BB74E1 / RXP_PRESCRIPTION@SEL$2 29 - SEL$F5BB74E1 / RXC_INTK_CROSS_REF@SEL$2 30 - SEL$F5BB74E1 / RXC_INTK_CROSS_REF@SEL$2 31 - SEL$F5BB74E1 / SRD_ATTRIBUTE_VALUE@SEL$2 32 - SEL$F5BB74E1 / SRD_ATTRIBUTE_VALUE@SEL$2 33 - SEL$F5BB74E1 / RXP_PATIENT_ADDRESS@SEL$2 34 - SEL$F5BB74E1 / RXP_PATIENT_ADDRESS@SEL$2 36 - SEL$F5BB74E1 / RXP_FILL_USER@SEL$2 37 - SEL$F5BB74E1 / RXP_FILL_USER@SEL$2 38 - SEL$8F6A6034 / T2@SEL$1 39 - SEL$8F6A6034 45 - SEL$8F6A6034 / DGL@SEL$8 46 - SEL$8F6A6034 / CV@SEL$4 47 - SEL$8F6A6034 / CV@SEL$4 48 - SEL$97FDD4B0 / TEMP_B@SEL$4 49 - SEL$97FDD4B0 51 - SEL$B80E8442 / VW_GBC_11@SEL$D20BBA32 52 - SEL$B80E8442 54 - SEL$B80E8442 / DGL@SEL$6 55 - SEL$B80E8442 / F@SEL$7 56 - SEL$97FDD4B0 / CV@SEL$5 57 - SEL$8F6A6034 / CP@SEL$4 58 - SEL$8F6A6034 / CP@SEL$4 59 - SEL$8F6A6034 / F@SEL$9 Note ----- - Warning: basic plan statistics not available. These are only collected when: * hint 'gather_plan_statistics' is used for the statement or * parameter 'statistics_level' is set to 'ALL', at session or system level