SELECT TAX_CODE , SUM (AMOUNT_DEFERRED) "AMOUNT" FROM (SELECT /*+USE_CONCAT */ EB.RESOURCE_ID , EB.TAX_CODE , EB.AMOUNT_DEFERRED FROM ITEM_T I , EVENT_BAL_IMPACTS_T EB WHERE ( ( EB.OBJ_ID0 > :b1 AND EB.OBJ_ID0 <= :b2 ) OR ( EB.OBJ_ID0 > :b3 AND EB.OBJ_ID0 <= :b4) OR EB.OBJ_ID0 <= :b5) AND EB.AMOUNT_DEFERRED != 0 AND EB.RESOURCE_ID = :b6 AND I.POID_DB IS NOT NULL AND I.POID_ID0 = EB.ITEM_OBJ_ID0 AND I.AR_BILLINFO_OBJ_ID0 = :b7 AND EB.OBJ_ID0 =( CASE WHEN BITAND(EB.OBJ_ID0 , 1152921504606846976 ) != 0 THEN (CASE WHEN NOT EXISTS (SELECT START_POID_ID0 FROM TMP_UNPROCESSED_EVENTS_T WHERE EB.OBJ_ID0 BETWEEN START_POID_ID0 AND END_POID_ID0 ) THEN EB.OBJ_ID0 ELSE 0 END) ELSE EB.OBJ_ID0 END) AND ( ( ( ( I.EFFECTIVE_T = 0 ) OR ( I.EFFECTIVE_T <= :b8 AND I.STATUS = 1 ) ) AND ( I.BILL_OBJ_ID0 = (SELECT /*+ push_subq no_merge */ DECODE(PAY_TYPE , 10007 , LAST_BILL_OBJ_ID0 ,BILL_OBJ_ID0 ) FROM BILLINFO_T WHERE POID_ID0 = I.BILLINFO_OBJ_ID0 PLAN TABLE OPUTPUT Plan hash value: 336913729 ---------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1395 (100)| | | | | 1 | HASH GROUP BY | | 1 | 79 | | | | | | 2 | CONCATENATION | | | | | | | | | 3 | FILTER | | | | | | | | | 4 | NESTED LOOPS | | | | | | | | | 5 | NESTED LOOPS | | 1 | 74 | 47 (0)| 00:00:01 | | | | 6 | PARTITION RANGE ALL | | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 7 | INDEX RANGE SCAN | I_ITEM_AR_BNFO_OBJ__ID | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 8 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 9 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 10 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 11 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 12 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 13 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 14 | PARTITION RANGE ITERATOR | | 1 | | 1 (0)| 00:00:01 | 1 | KEY | | 15 | INDEX RANGE SCAN | I_EVENT_BI_ITEM_OBJ__ID | 1 | | 1 (0)| 00:00:01 | 1 | KEY | | 16 | TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T | 1 | 26 | 1 (0)| 00:00:01 | 1 | 1 | | 17 | INDEX RANGE SCAN | I_UNPR_EVENTS_ST_END__ID | 1 | 26 | 1 (0)| 00:00:01 | | | | 18 | FILTER | | | | | | | | | 19 | NESTED LOOPS | | | | | | | | | 20 | NESTED LOOPS | | 1 | 74 | 47 (0)| 00:00:01 | | | | 21 | PARTITION RANGE ALL | | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 22 | INDEX RANGE SCAN | I_ITEM_AR_BNFO_OBJ__ID | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 23 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 24 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 25 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 26 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 27 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 28 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 29 | PARTITION RANGE ITERATOR | | 1 | | 1 (0)| 00:00:01 | 1 | KEY | | 30 | INDEX RANGE SCAN | I_EVENT_BI_ITEM_OBJ__ID | 1 | | 1 (0)| 00:00:01 | 1 | KEY | | 31 | TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T | 1 | 26 | 1 (0)| 00:00:01 | 1 | 1 | | 32 | INDEX RANGE SCAN | I_UNPR_EVENTS_ST_END__ID | 1 | 26 | 1 (0)| 00:00:01 | | | | 33 | FILTER | | | | | | | | | 34 | NESTED LOOPS | | | | | | | | | 35 | NESTED LOOPS | | 1 | 74 | 47 (0)| 00:00:01 | | | | 36 | PARTITION RANGE ALL | | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 37 | INDEX RANGE SCAN | I_ITEM_AR_BNFO_OBJ__ID | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 38 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 39 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 40 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 41 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 42 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 43 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 44 | PARTITION RANGE ITERATOR | | 1 | | 1 (0)| 00:00:01 | 1 | KEY | | 45 | INDEX RANGE SCAN | I_EVENT_BI_ITEM_OBJ__ID | 1 | | 1 (0)| 00:00:01 | 1 | KEY | | 46 | TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T | 1 | 26 | 1 (0)| 00:00:01 | 1 | 1 | | 47 | INDEX RANGE SCAN | I_UNPR_EVENTS_ST_END__ID | 1 | 26 | 1 (0)| 00:00:01 | | | | 48 | FILTER | | | | | | | | | 49 | NESTED LOOPS | | | | | | | | | 50 | NESTED LOOPS | | 1 | 79 | 279 (1)| 00:00:04 | | | | 51 | PARTITION RANGE ALL | | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 52 | INDEX RANGE SCAN | I_ITEM_AR_BNFO_OBJ__ID | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 53 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 54 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 55 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 56 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 57 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 58 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 59 | PARTITION RANGE ITERATOR | | 360 | | 167 (1)| 00:00:03 | KEY | KEY | | 60 | INDEX RANGE SCAN | I_EVENT_BI_ITEM_OBJ__ID | 360 | | 167 (1)| 00:00:03 | KEY | KEY | | 61 | TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T | 1 | 31 | 233 (1)| 00:00:03 | 1 | 1 | | 62 | INDEX RANGE SCAN | I_UNPR_EVENTS_ST_END__ID | 1 | 26 | 1 (0)| 00:00:01 | | | | 63 | FILTER | | | | | | | | | 64 | NESTED LOOPS | | | | | | | | | 65 | NESTED LOOPS | | 1 | 79 | 279 (1)| 00:00:04 | | | | 66 | PARTITION RANGE ALL | | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 67 | INDEX RANGE SCAN | I_ITEM_AR_BNFO_OBJ__ID | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 68 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 69 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 70 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 71 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 72 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 73 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 74 | PARTITION RANGE ITERATOR | | 360 | | 167 (1)| 00:00:03 | KEY | KEY | | 75 | INDEX RANGE SCAN | I_EVENT_BI_ITEM_OBJ__ID | 360 | | 167 (1)| 00:00:03 | KEY | KEY | | 76 | TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T | 1 | 31 | 233 (1)| 00:00:03 | 1 | 1 | | 77 | INDEX RANGE SCAN | I_UNPR_EVENTS_ST_END__ID | 1 | 26 | 1 (0)| 00:00:01 | | | | 78 | FILTER | | | | | | | | | 79 | NESTED LOOPS | | | | | | | | | 80 | NESTED LOOPS | | 1 | 79 | 279 (1)| 00:00:04 | | | | 81 | PARTITION RANGE ALL | | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 82 | INDEX RANGE SCAN | I_ITEM_AR_BNFO_OBJ__ID | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 83 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 84 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 85 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 86 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 87 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 88 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 89 | PARTITION RANGE ITERATOR | | 360 | | 167 (1)| 00:00:03 | KEY | KEY | | 90 | INDEX RANGE SCAN | I_EVENT_BI_ITEM_OBJ__ID | 360 | | 167 (1)| 00:00:03 | KEY | KEY | | 91 | TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T | 1 | 31 | 233 (1)| 00:00:03 | 1 | 1 | | 92 | INDEX RANGE SCAN | I_UNPR_EVENTS_ST_END__ID | 1 | 26 | 1 (0)| 00:00:01 | | | | 93 | FILTER | | | | | | | | | 94 | NESTED LOOPS | | | | | | | | | 95 | NESTED LOOPS | | 1 | 79 | 136 (1)| 00:00:02 | | | | 96 | PARTITION RANGE ALL | | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 97 | INDEX RANGE SCAN | I_ITEM_AR_BNFO_OBJ__ID | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 98 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 99 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 100 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 101 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 102 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 103 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 104 | PARTITION RANGE ITERATOR | | 360 | | 24 (0)| 00:00:01 | KEY | KEY | | 105 | INDEX RANGE SCAN | I_EVENT_BI_ITEM_OBJ__ID | 360 | | 24 (0)| 00:00:01 | KEY | KEY | | 106 | TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T | 1 | 31 | 90 (2)| 00:00:02 | 1 | 1 | | 107 | INDEX RANGE SCAN | I_UNPR_EVENTS_ST_END__ID | 1 | 26 | 1 (0)| 00:00:01 | | | | 108 | FILTER | | | | | | | | | 109 | NESTED LOOPS | | | | | | | | | 110 | NESTED LOOPS | | 1 | 79 | 136 (1)| 00:00:02 | | | | 111 | PARTITION RANGE ALL | | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 112 | INDEX RANGE SCAN | I_ITEM_AR_BNFO_OBJ__ID | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 113 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 114 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 115 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 116 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 117 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 118 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 119 | PARTITION RANGE ITERATOR | | 360 | | 24 (0)| 00:00:01 | KEY | KEY | | 120 | INDEX RANGE SCAN | I_EVENT_BI_ITEM_OBJ__ID | 360 | | 24 (0)| 00:00:01 | KEY | KEY | | 121 | TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T | 1 | 31 | 90 (2)| 00:00:02 | 1 | 1 | | 122 | INDEX RANGE SCAN | I_UNPR_EVENTS_ST_END__ID | 1 | 26 | 1 (0)| 00:00:01 | | | | 123 | FILTER | | | | | | | | | 124 | NESTED LOOPS | | | | | | | | | 125 | NESTED LOOPS | | 1 | 79 | 136 (1)| 00:00:02 | | | | 126 | PARTITION RANGE ALL | | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 127 | INDEX RANGE SCAN | I_ITEM_AR_BNFO_OBJ__ID | 1 | 48 | 46 (0)| 00:00:01 | 1 | 61 | | 128 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 129 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 130 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 131 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 132 | TABLE ACCESS BY INDEX ROWID | BILLINFO_T | 1 | 16 | 1 (0)| 00:00:01 | | | | 133 | INDEX UNIQUE SCAN | I_BILLINFO__ID | 1 | | 1 (0)| 00:00:01 | | | | 134 | PARTITION RANGE ITERATOR | | 360 | | 24 (0)| 00:00:01 | KEY | KEY | | 135 | INDEX RANGE SCAN | I_EVENT_BI_ITEM_OBJ__ID | 360 | | 24 (0)| 00:00:01 | KEY | KEY | | 136 | TABLE ACCESS BY LOCAL INDEX ROWID| EVENT_BAL_IMPACTS_T | 1 | 31 | 90 (2)| 00:00:02 | 1 | 1 | | 137 | INDEX RANGE SCAN | I_UNPR_EVENTS_ST_END__ID | 1 | 26 | 1 (0)| 00:00:01 | | |