SQL> SELECT CLM.ID CLM_ID 2 ,DECODE(CLM.multi_source_cd,'Y',CLM.id,NULL) gen_ind 3 ,CLM.DATE_FILLED DATE_FILLED 4 ,CLM.RX_NUMBER RX_NUMBER 5 ,DECODE(NVL(CLM.NEW_REFILL_CD,0), 0, 'N', 'R') NEW_REFILL 6 ,CLM.METRIC_QTY_ALLOWED METRIC_QTY_ALLOWED 7 ,CLM.DAYS_SUPPLY_ALLOWED DAYS_SUPPLY_ALLOWED 8 ,CLM.BILL_BASIS CLM_BILL_BASIS 9 ,(NVL(CLM.BILL_IC_SUBMITTED,0) + NVL(CLM.BILL_IC_ADJ,0)) CLM_BILL_IC 10 ,CLM.BILL_DF CLM_BILL_DF 11 ,CLM.TAX_PAID TAX_PAID 12 ,CLM.PAT_TOTAL CLM_PAT_TOTAL 13 ,DECODE(CLM.TRANSACTION_TYPE, 1, CLM.PAT_TOTAL, 0) REV_PAT_TOTAL 14 ,DECODE(CLM.TRANSACTION_TYPE, 1, NVL(CLM.BILL_IC_SUBMITTED,0) + NVL(CLM.BILL_IC_ADJ,0) + NVL(CLM.BILL_DF, 0) + 15 NVL(CLM.TAX_PAID, 0) - NVL(CLM.PAT_TOTAL, 0) , 0) REV_NET_TOTAL 16 ,CLM.PAT_DEDUCTIBLE CLM_PAT_DEDUCTIBLE 17 ,DECODE(CLM.DAW_ALLOWED,'1','1','2','2',NULL) CLM_DAW 18 ,PHR.ID PHR_ID 19 ,PHR.NAME PHR_NAME 20 ,PHR.ADDR1 PHR_ADDR1 21 ,PHR.CITY || ', ' || PHR.STATE || ' ' || PHR.ZIPCODE PHR_CITY_ST 22 ,CLM.GROUP_ID CLM_GROUP_ID 23 ,MBR.ID MBR_ID 24 ,MBR.FIRST_NAME MBR_FIRST_NAME 25 ,MBR.LAST_NAME MBR_LAST_NAME 26 ,MBR.PERSON_CODE MBR_PERSON_CODE 27 ,DRG.DESCRIPTION DRG_DESCRIPTION 28 ,DRG.ID_NUM_FORM_CD DRG_ID_FORMAT 29 ,DECODE(DRG.MULTI_SOURCE_CD, 'Y', 'G', 'M', 'C', DRG.MULTI_SOURCE_CD) DRUG_MULTI_SOURCE 30 ,CLM.DRUG_NDC_ALLOWED 31 ,CLM.PAYEE_TYPE CLM_PAYEE_TYPE 32 ,PHY.DEA PHY_DEA 33 ,PHY.FIRST_NAME PHY_FIRST_NAME 34 ,PHY.LAST_NAME PHY_LAST_NAME 35 ,CLM.CAPTURE_STATUS CAPTURE_STATUS 36 ,DECODE(CLM.TRANSACTION_TYPE, 0, 1, 0) CLAIM_COUNT 37 ,DECODE(CLM.TRANSACTION_TYPE, 1, 1, 0) REV_CLAIM 38 FROM PBM_CLAIM CLM 39 ,PBM_PHARMACY PHR 40 ,PBM_MEMBER MBR 41 ,PBM_DRUG DRG 42 ,PBM_PHYSICIAN PHY 43 , pbm_pharmacy_network pn 44 WHERE 45 CLM.phrnet_pk = pn.pk AND 46 pn.service_type = decode(NVL('&P_NETWORK_TYPE', 'A'),'RETAIL','R','MAIL','M',pn.service_type ) AND 47 PHR.PK = CLM.PHARMACY_PK AND 48 MBR.PK = CLM.MEMBER_PK AND 49 MBR.ID = DECODE(NVL('&P_MEMBER_ID','ALL'), 50 'ALL',MBR.ID,'All',MBR.ID, 51 '&P_MEMBER_ID') AND 52 MBR.PERSON_CODE = DECODE(NVL('&P_PERSON_CODE', 'ALL'), 53 'ALL', MBR.PERSON_CODE, 54 '&P_PERSON_CODE') AND 55 PHY.PK = CLM.PHYSICIAN_PK AND 56 CLM.GROUP_ID = &P_GROUP_ID AND 57 DRG.NDC = CLM.DRUG_NDC_ALLOWED AND 58 DATE_FILLED BETWEEN NVL('&P_FROM_DATE', DATE_FILLED) AND NVL('&P_TO_DATE', DATE_FILLED) 59 ORDER BY 60 MBR.PERSON_CODE 61 ,CLM.DATE_FILLED 62 ,CLM.ID 63 / ------------------------------------------- SCENARIO # 1 ------------ Enter value for p_network_type: ALL old 46: pn.service_type = decode(NVL('&P_NETWORK_TYPE', 'A'),'RETAIL','R','MAIL','M',pn.service_ new 46: pn.service_type = decode(NVL('ALL', 'A'),'RETAIL','R','MAIL','M',pn.service_type) AND Enter value for p_member_id: 3961309900 old 49: MBR.ID = DECODE(NVL('&P_MEMBER_ID','ALL'), new 49: MBR.ID = DECODE(NVL('3961309900','ALL'), Enter value for p_member_id: 3961309900 old 51: '&P_MEMBER_ID') AND new 51: '3961309900') AND Enter value for p_person_code: ALL old 52: MBR.PERSON_CODE = DECODE(NVL('&P_PERSON_CODE', 'ALL'), new 52: MBR.PERSON_CODE = DECODE(NVL('ALL', 'ALL'), Enter value for p_person_code: ALL old 54: '&P_PERSON_CODE') AND new 54: 'ALL') AND Enter value for p_group_id: 7701 old 56: CLM.GROUP_ID = &P_GROUP_ID AND new 56: CLM.GROUP_ID = 7701 AND Enter value for p_from_date: 01-DEC-07 Enter value for p_to_date: 31-DEC-07 old 58: DATE_FILLED BETWEEN NVL('&P_FROM_DATE', DATE_FILLED) AND NVL('&P_TO_DATE', DATE_FILLED new 58: DATE_FILLED BETWEEN NVL('01-DEC-07', DATE_FILLED) AND NVL('31-DEC-07', DATE_FILLED) Elapsed: 00:05:328.22 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3074 Card=1 Bytes=24 4) 1 0 SORT (ORDER BY) (Cost=3074 Card=1 Bytes=244) 2 1 NESTED LOOPS (Cost=3072 Card=1 Bytes=244) 3 2 NESTED LOOPS (Cost=3070 Card=1 Bytes=212) 4 3 NESTED LOOPS (Cost=3068 Card=1 Bytes=177) 5 4 NESTED LOOPS (Cost=3067 Card=1 Bytes=114) 6 5 NESTED LOOPS (Cost=3066 Card=1 Bytes=110) 7 6 TABLE ACCESS (BY INDEX ROWID) OF 'PBM_CLAIM' (Cost=3028 Card=19 Bytes=1482) 8 7 INDEX (RANGE SCAN) OF 'CLAIM_GROUP_FK_I' (NON-UNIQUE) (Cost=45 Card=19) 9 6 TABLE ACCESS (BY INDEX ROWID) OF 'PBM_MEMBER'(Cost=2 Card=1 Bytes=32) 10 9 INDEX (UNIQUE SCAN) OF 'MEMBER_PK' (UNIQUE) (Cost=1 Card=1) 11 5 TABLE ACCESS (BY INDEX ROWID) OF 'PBM_PHARMACY_NETWORK' (Cost=1 Card=44 Bytes=176) 12 11 INDEX (UNIQUE SCAN) OF 'PHRNET_PK' (UNIQUE) 13 4 TABLE ACCESS (BY INDEX ROWID) OF 'PBM_PHARMACY' (Cost=1 Card=101750 Bytes=6410250) 14 13 INDEX (UNIQUE SCAN) OF 'PHARMACY_PK' (UNIQUE) 15 3 TABLE ACCESS (BY INDEX ROWID) OF 'PBM_DRUG' (Cost=2 Card=200344 Bytes=7012040) 16 15 INDEX (UNIQUE SCAN) OF 'DRUG_PK' (UNIQUE) (Cost=1 Card=200344) 17 2 TABLE ACCESS (BY INDEX ROWID) OF 'PBM_PHYSICIAN' (Cost=2 Card=1924035 Bytes=61569120) 18 17 INDEX (UNIQUE SCAN) OF 'PHYSICIAN_PK' (UNIQUE) (Cost=1 Card=1924035) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 732146 consistent gets 532602 physical reads 0 redo size 1592 bytes sent via SQL*Net to client 1050 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 1 rows processed ======================================================================================================================== SCENARIO # 2 ------------- Enter value for p_network_type: ALL old 46: pn.service_type = decode(NVL('&P_NETWORK_TYPE', 'A'),'RETAIL','R','MAIL','M',pn.service new 46: pn.service_type = decode(NVL('ALL', 'A'),'RETAIL','R','MAIL','M',pn.service_type) AND Enter value for p_member_id: 3961309900 old 49: MBR.ID = DECODE(NVL('&P_MEMBER_ID','ALL'), new 49: MBR.ID = DECODE(NVL('3961309900','ALL'), Enter value for p_member_id: 3961309900 old 51: '&P_MEMBER_ID') AND new 51: '3961309900') AND Enter value for p_person_code: ALL old 52: MBR.PERSON_CODE = DECODE(NVL('&P_PERSON_CODE', 'ALL'), new 52: MBR.PERSON_CODE = DECODE(NVL('ALL', 'ALL'), Enter value for p_person_code: ALL old 54: '&P_PERSON_CODE') AND new 54: 'ALL') AND Enter value for p_group_id: 7703 old 56: CLM.GROUP_ID = &P_GROUP_ID AND new 56: CLM.GROUP_ID = 7703 AND Enter value for p_from_date: 01-DEC-07 Enter value for p_to_date: 31-DEC-07 old 58: DATE_FILLED BETWEEN NVL('&P_FROM_DATE', DATE_FILLED) AND NVL('&P_TO_DATE', DATE_FILLE new 58: DATE_FILLED BETWEEN NVL('01-DEC-07', DATE_FILLED) AND NVL('31-DEC-07', DATE_FILLED) no rows selected Elapsed: 00:05:311.19 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3074 Card=1 Bytes=24 4) 1 0 SORT (ORDER BY) (Cost=3074 Card=1 Bytes=244) 2 1 NESTED LOOPS (Cost=3072 Card=1 Bytes=244) 3 2 NESTED LOOPS (Cost=3070 Card=1 Bytes=212) 4 3 NESTED LOOPS (Cost=3068 Card=1 Bytes=177) 5 4 NESTED LOOPS (Cost=3067 Card=1 Bytes=114) 6 5 NESTED LOOPS (Cost=3066 Card=1 Bytes=110) 7 6 TABLE ACCESS (BY INDEX ROWID) OF 'PBM_CLAIM' ( Cost=3028 Card=19 Bytes=1482) 8 7 INDEX (RANGE SCAN) OF 'CLAIM_GROUP_FK_I' (NO N-UNIQUE) (Cost=45 Card=19) 9 6 TABLE ACCESS (BY INDEX ROWID) OF 'PBM_MEMBER' (Cost=2 Card=1 Bytes=32) 10 9 INDEX (UNIQUE SCAN) OF 'MEMBER_PK' (UNIQUE) (Cost=1 Card=1) 11 5 TABLE ACCESS (BY INDEX ROWID) OF 'PBM_PHARMACY_N ETWORK' (Cost=1 Card=44 Bytes=176) 12 11 INDEX (UNIQUE SCAN) OF 'PHRNET_PK' (UNIQUE) 13 4 TABLE ACCESS (BY INDEX ROWID) OF 'PBM_PHARMACY' (C ost=1 Card=101750 Bytes=6410250) 14 13 INDEX (UNIQUE SCAN) OF 'PHARMACY_PK' (UNIQUE) 15 3 TABLE ACCESS (BY INDEX ROWID) OF 'PBM_DRUG' (Cost=2 Card=200344 Bytes=7012040) 16 15 INDEX (UNIQUE SCAN) OF 'DRUG_PK' (UNIQUE) (Cost=1 Card=200344) 17 2 TABLE ACCESS (BY INDEX ROWID) OF 'PBM_PHYSICIAN' (Cost =2 Card=1924035 Bytes=61569120) 18 17 INDEX (UNIQUE SCAN) OF 'PHYSICIAN_PK' (UNIQUE) (Cost =1 Card=1924035) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 308069 consistent gets 259415 physical reads 960 redo size 1239 bytes sent via SQL*Net to client 1011 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 7 sorts (memory) 0 sorts (disk) 0 rows processed