Hi, we are using version 11.2.0.4 Of Oracle Exadata. We are seeing one of the query containing a key transaction table(TXN) and others small tables(<10k rows mostly) in the JOIN condition. We see its running longer (~4hrs) with default plan i.e. an indexed access path for table TXN using bitmap index- TXN_IDX1. And we saw by just having a no_index hint on that key transaction table it does finish in ~14minutes. Wanted to understand what exact stats is influencing it to go for the decision to go for the indexed(TXN_IDX1) access path.
My question is , Is there anything specific to BITMAP index which makes it more obvious choice for optimizer to go for it? Would it been a btree index is it that the case would have been different?
I do see we have below table and the index stats. Its having many distinct values for the BITMAP index but i think the decision has been made to keep that index bitmap because of a fact dimension design.
The table -TXN is range partitioned on column DATE_KEY. This is number column but holding a truncated date value in format YYYMMDD. And current there are total ~1270 partitions in the table. I do agree that this will pose problem for optimizer to estimate properly but that is what currently we have since long back.
Below is the table stats:-
| TABLE_NAME | NUM_ROWS | BLOCKS |
| TXN | 39628824325 | 144926887 |
Below is the index stats:- TXN_IDX1
| INDEX_NAME | BLEVEL | LEAF_BLOCKS | AVG_LEAF_BLOCKS_PER_KEY | AVG_DATA_BLOCKS_PER_KEY | DISTINCT_KEYS | CLUSTERING_FACTOR | NUM_ROWS |
| TXN_IDX1 | 3 | 27620408 | 23 | 575 | 1160128 | 668045267 | 668045267 |
Bitmap Index - TXN_IDX1 is on column USR_KEY. Below is the column stats.
| TABLE_NAME | COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | DENSITY | NUM_NULLS | HISTOGRAM |
| TXN | USR_KEY | 1160128 | C10A | C4071B5F51 | 8.62E-07 | 0 | NONE |
Below is the global stats for the partition key column - DATE_KEY
| TABLE_NAME | COLUMN_NAME | NUM_DISTINCT | LOW_VALUE | HIGH_VALUE | DENSITY | NUM_NULLS | HISTOGRAM |
| TXN | DATE_KEY | 1535 | C4150F0811 | C415150411 | 1.62E-19 | 0 | HEIGHT BALANCED |
Below is the sql monitor for the default plan which keeps running even after ~10+ minutes. And the other execution with NO_INDEX hint which finishes in ~8minutes.
select ....
FROM USER1.TXN,USER1.S_TIN, USER1.S_RTRS_CD,USER1.S_CRNCY,USER1.S_MPCD,USER1.S_CNTRY,USER1.S_PNT,USER2.S_TX_KEY,USER1.S_DATE,USER1.S_CST_TIN
WHERE S_TX_KEY.USR_KEY = TXN.USR_KEY
AND TXN.CRNCY_KEY = S_CRNCY.CRNCY_KEY
AND TXN.MPCD_KEY = S_MPCD.MPCD_KEY
AND TXN.CNTRY_KEY = S_CNTRY.CNTRY_KEY
AND TXN.PNT_KEY = S_PNT.PNT_KEY
AND TXN.IT_KEY = S_RTRS_CD.IT_KEY
AND S_TX_KEY.USR_KEY = S_CST_TIN.USR_KEY
AND S_CST_TIN.TX_KEY = S_TIN.TX_KEY
AND TXN.DATE_KEY >= TO_NUMBER ( TO_CHAR (S_CST_TIN.STRT_DATE, 'YYYYMMDD'))
AND TXN.DATE_KEY < NVL ( TO_NUMBER ( TO_CHAR (S_CST_TIN.END_DATE, 'YYYYMMDD')), 29991231)
AND S_RTRS_CD.ST = S_TIN.ST
AND S_RTRS_CD.ST = 'AB'
AND S_RTRS_CD.YR_KEY = S_DATE.YR
AND S_DATE.YR = TO_CHAR (2019)
AND TXN.DATE_KEY = S_DATE.DATE_KEY;
Global Information
------------------------------
Status : EXECUTING
Instance ID : 2
SQL Execution ID : 33554432
Execution Started : 03/16/2020 11:46:44
First Refresh Time : 03/16/2020 11:46:49
Last Refresh Time : 03/16/2020 11:59:19
Duration : 756s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 190
Global Stats
=========================================================================================
| Elapsed | Cpu | IO | Concurrency | Cluster | Fetch | Buffer | Read | Read |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes |
=========================================================================================
| 776 | 400 | 375 | 0.00 | 1.41 | 190 | 36M | 1M | 12GB |
=========================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3713841281)
========================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | | (%) | (# samples) |
========================================================================================================================================================================================================================
| -> 0 | SELECT STATEMENT | | | | 714 | +42 | 1 | 945K | | | | | |
| -> 1 | NESTED LOOPS | | 1 | 25124 | 714 | +42 | 1 | 945K | | | | 5.87 | Cpu (44) |
| -> 2 | NESTED LOOPS | | 857 | 25124 | 714 | +42 | 1 | 1G | | | | | |
| -> 3 | NESTED LOOPS | | 1 | 25109 | 714 | +42 | 1 | 1M | | | | | |
| -> 4 | NESTED LOOPS | | 1 | 25108 | 714 | +42 | 1 | 1M | | | | | |
| -> 5 | NESTED LOOPS | | 1 | 25107 | 714 | +42 | 1 | 1M | | | | | |
| -> 6 | NESTED LOOPS | | 1 | 25106 | 751 | +5 | 1 | 1M | | | | 0.13 | Cpu (1) |
| -> 7 | NESTED LOOPS | | 6 | 25100 | 751 | +5 | 1 | 4M | | | | | |
| -> 8 | NESTED LOOPS | | 6 | 25094 | 751 | +5 | 1 | 4M | | | | | |
| -> 9 | HASH JOIN | | 1 | 7098 | 751 | +5 | 1 | 90 | | | 1M | | |
| 10 | JOIN FILTER CREATE | :BF0000 | 85 | 7089 | 1 | +5 | 1 | 1806 | | | | | |
| 11 | NESTED LOOPS | | 85 | 7089 | 1 | +5 | 1 | 1806 | | | | | |
| 12 | NESTED LOOPS | | 85 | 7089 | 1 | +5 | 1 | 1806 | | | | | |
| 13 | TABLE ACCESS STORAGE FULL | S_TIN | 34 | 6930 | 6 | +0 | 1 | 80 | | | | 0.13 | Cpu (1) |
| 14 | INDEX RANGE SCAN | S_TIN_IX2 | 2 | 2 | 1 | +5 | 178 | 1806 | | | | | |
| 15 | TABLE ACCESS BY INDEX ROWID | S_CST_TIN | 2 | 5 | 1 | +5 | 3459 | 1806 | | | | | |
| -> 16 | JOIN FILTER USE | :BF0000 | 1664 | 9 | 751 | +5 | 1 | 84 | | | | | |
| -> 17 | TABLE ACCESS STORAGE FULL | S_TX_KEY | 1664 | 9 | 751 | +5 | 1 | 84 | | | | | |
| -> 18 | PARTITION RANGE ITERATOR | | 85 | 25094 | 751 | +5 | 90 | 4M | | | | | |
| -> 19 | TABLE ACCESS BY LOCAL INDEX ROWID | TXN | 85 | 25094 | 754 | +2 | 108K | 4M | 1M | 11GB | | 56.88 | Cpu (82) |
| | | | | | | | | | | | | | cell single block physical read (344) |
| -> 20 | BITMAP CONVERSION TO ROWIDS | | | | 751 | +5 | 108K | 4M | | | | | |
| -> 21 | BITMAP INDEX SINGLE VALUE | TXN_IDX1 | | | 755 | +1 | 108K | 46566 | 43733 | 342MB | | 2.94 | gc cr grant 2-way (2) |
| | | | | | | | | | | | | | Cpu (2) |
| | | | | | | | | | | | | | cell single block physical read (18) |
| -> 22 | TABLE ACCESS BY INDEX ROWID | S_PNT | 1 | 1 | 751 | +5 | 4M | 4M | | | | 0.53 | Cpu (4) |
| -> 23 | INDEX UNIQUE SCAN | S_PNT_Key | 1 | | 751 | +5 | 4M | 4M | | | | 0.27 | Cpu (2) |
| -> 24 | TABLE ACCESS BY INDEX ROWID | S_DATE | 1 | 1 | 751 | +5 | 4M | 1M | 14 | 112KB | | 0.67 | Cpu (5) |
| -> 25 | INDEX UNIQUE SCAN | S_CLNDR_PK | 1 | | 751 | +5 | 4M | 4M | 2 | 16384 | | 0.13 | Cpu (1) |
| -> 26 | TABLE ACCESS BY INDEX ROWID | S_MPCD | 1 | 1 | 714 | +42 | 1M | 1M | | | | 0.40 | Cpu (3) |
| -> 27 | INDEX UNIQUE SCAN | S_MPCD_PK | 1 | | 714 | +42 | 1M | 1M | | | | | |
| -> 28 | TABLE ACCESS BY INDEX ROWID | S_CRNCY | 1 | 1 | 714 | +42 | 1M | 1M | 2 | 16384 | | 0.13 | Cpu (1) |
| -> 29 | INDEX UNIQUE SCAN | S_CRNCY_PK | 1 | | 714 | +42 | 1M | 1M | | | | | |
| -> 30 | TABLE ACCESS BY INDEX ROWID | S_CNTRY | 1 | 1 | 714 | +42 | 1M | 1M | 2 | 16384 | | 0.13 | Cpu (1) |
| -> 31 | INDEX UNIQUE SCAN | S_CNTRY_PK | 1 | | 714 | +42 | 1M | 1M | 1 | 8192 | | 0.13 | Cpu (1) |
| -> 32 | INDEX RANGE SCAN | S_RTRS_CD_PK | 857 | 4 | 714 | +42 | 1M | 1G | 7 | 57344 | | 6.94 | Cpu (52) |
| -> 33 | TABLE ACCESS BY INDEX ROWID | S_RTRS_CD | 1 | 15 | 714 | +42 | 1G | 945K | 13 | 104KB | | 24.70 | Cpu (185) |
========================================================================================================================================================================================================================
Predicate Information (identified by operation id):
---------------------------------------------------
9 - access("S_TX_KEY"."USR_KEY"="S_CST_TIN"."USR_KEY")
13 - storage("S_TIN"."ST"='AB')
filter("S_TIN"."ST"='AB')
14 - access("S_CST_TIN"."TX_KEY"="S_TIN"."TX_KEY")
17 - storage("S_TX_KEY"."USR_KEY" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"S_TX_KEY"."USR_KEY"))
filter("S_TX_KEY"."USR_KEY" IS NOT NULL AND SYS_OP_BLOOM_FILTER(:BF0000,"S_TX_KEY"."USR_KEY"))
19 - filter("TXN"."DATE_KEY">=TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("S_CST_TIN"."STRT_DATE"),'YYYYMMDD')) AND "TXN"."DATE_KEY"<NVL(TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("S_CST_TIN"."END_DATE"),'YYYYMMDD')),29991231))
21 - access("S_TX_KEY"."USR_KEY"="TXN"."USR_KEY")
23 - access("TXN"."PNT_KEY"="S_PNT"."PNT_KEY")
24 - filter("S_DATE"."YR"='2019')
25 - access("TXN"."DATE_KEY"="S_DATE"."DATE_KEY")
27 - access("TXN"."MPCD_KEY"="S_MPCD"."MPCD_KEY")
29 - access("TXN"."CRNCY_KEY"="S_CRNCY"."CRNCY_KEY")
31 - access("TXN"."CNTRY_KEY"="S_CNTRY"."CNTRY_KEY")
32 - access("S_RTRS_CD"."ST"='AB' AND "S_RTRS_CD"."YR_KEY"=TO_NUMBER("S_DATE"."YR"))
33 - filter("TXN"."IT_KEY"="S_RTRS_CD"."IT_KEY")
****************Below is the sql monitor for the NO_INDEX hint plan which finishing in <~8minutes.
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 2
SQL Execution ID : 33554432
Execution Started : 03/16/2020 11:49:29
First Refresh Time : 03/16/2020 11:49:29
Last Refresh Time : 03/16/2020 11:57:01
Duration : 452s
Module/Action : SQL*Plus/-
Program : sqlplus.exe
Fetch Calls : 4145
Global Stats
===================================================================================================
| Elapsed | Cpu | IO | Application | Cluster | Fetch | Buffer | Read | Read | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Offload |
===================================================================================================
| 242 | 202 | 40 | 0.17 | 0.00 | 4145 | 112M | 1M | 852GB | 93.97% |
===================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=590147730)
=====================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Cell | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Offload | (Max) | (%) | (# samples) |
=====================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 423 | +1 | 1 | 21M | | | | | 5.26 | Cpu (10) |
| | | | | | | | | | | | | | | SQL*Net more data to client (2) |
| 1 | HASH JOIN | | 1 | 11M | 423 | +1 | 1 | 21M | | | | 2M | 4.82 | Cpu (11) |
| 2 | NESTED LOOPS | | 1786 | 10297 | 1 | +1 | 1 | 6858 | | | | | | |
| 3 | NESTED LOOPS | | 1786 | 10297 | 1 | +1 | 1 | 6858 | | | | | | |
| 4 | TABLE ACCESS STORAGE FULL | S_TIN | 721 | 6929 | 1 | +1 | 1 | 721 | | | | | | |
| 5 | INDEX RANGE SCAN | S_TIN_IX2 | 2 | 2 | 1 | +1 | 721 | 6858 | | | | | | |
| 6 | TABLE ACCESS BY INDEX ROWID | S_CST_TIN | 2 | 5 | 1 | +1 | 6858 | 6858 | | | | | | |
| 7 | VIEW | VW_JF_SET$1CBDFA29 | 9M | 11M | 423 | +1 | 1 | 21M | | | | | | |
| 8 | UNION-ALL | | | | 423 | +1 | 1 | 21M | | | | | 2.19 | Cpu (5) |
| 9 | HASH JOIN | | 7M | 9M | 423 | +1 | 1 | 21M | | | | 2M | | |
| 10 | VIEW | index$_join$_069 | 264 | 2 | 1 | +1 | 1 | 264 | | | | | | |
| 11 | HASH JOIN | | | | 1 | +1 | 1 | 264 | | | | 2M | | |
| 12 | INDEX STORAGE FAST FULL SCAN | S_CNTRY_AK1 | 264 | 1 | 1 | +1 | 1 | 264 | | | | | | |
| 13 | INDEX STORAGE FAST FULL SCAN | S_CNTRY_PK | 264 | 1 | 1 | +1 | 1 | 264 | | | | | | |
| 14 | HASH JOIN | | 7M | 9M | 423 | +1 | 1 | 21M | | | | 2M | 0.88 | Cpu (2) |
| 15 | VIEW | index$_join$_067 | 193 | 2 | 1 | +1 | 1 | 193 | | | | | | |
| 16 | HASH JOIN | | | | 1 | +1 | 1 | 193 | | | | 2M | | |
| 17 | INDEX STORAGE FAST FULL SCAN | S_CRNCY_PK | 193 | 1 | 1 | +1 | 1 | 193 | | | | | | |
| 18 | INDEX STORAGE FAST FULL SCAN | S_CRNCY_AK1 | 193 | 1 | 1 | +1 | 1 | 193 | | | | | | |
| 19 | HASH JOIN | | 7M | 9M | 423 | +1 | 1 | 21M | | | | 2M | 0.88 | Cpu (2) |
| 20 | TABLE ACCESS STORAGE FULL | S_PNT | 86 | 35 | 1 | +1 | 1 | 86 | | | | | | |
| 21 | HASH JOIN | | 7M | 9M | 423 | +1 | 1 | 21M | | | | 1M | 2.19 | Cpu (5) |
| 22 | JOIN FILTER CREATE | :BF0000 | 165 | 2 | 1 | +1 | 1 | 165 | | | | | | |
| 23 | VIEW | index$_join$_068 | 165 | 2 | 1 | +1 | 1 | 165 | | | | | | |
| 24 | HASH JOIN | | | | 1 | +1 | 1 | 165 | | | | 2M | | |
| 25 | INDEX STORAGE FAST FULL SCAN | S_MPCD_IX2 | 165 | 1 | 1 | +1 | 1 | 165 | 1 | 8192 | | | | |
| 26 | INDEX STORAGE FAST FULL SCAN | S_MPCD_PK | 165 | 1 | 1 | +1 | 1 | 165 | 1 | 16384 | | | | |
| 27 | HASH JOIN | | 7M | 9M | 423 | +1 | 1 | 21M | | | | 2M | 2.19 | Cpu (5) |
| 28 | TABLE ACCESS STORAGE FULL | S_RTRS_CD | 5996 | 22 | 1 | +1 | 1 | 6003 | | | | | | |
| 29 | HASH JOIN | | 9M | 9M | 423 | +1 | 1 | 29M | | | | 1M | 7.02 | Cpu (16) |
| 30 | JOIN FILTER CREATE | :BF0001 | 1664 | 9 | 1 | +1 | 1 | 1665 | | | | | | |
| 31 | TABLE ACCESS STORAGE FULL | S_TX_KEY | 1664 | 9 | 1 | +1 | 1 | 1665 | | | | | | |
| 32 | NESTED LOOPS | | 6G | 9M | 427 | +1 | 1 | 302M | | | | | | |
| 33 | TABLE ACCESS STORAGE FULL | S_DATE | 358 | 69 | 427 | +1 | 1 | 377 | 12 | 776KB | | | | |
| 34 | JOIN FILTER USE | :BF0000 | 17M | 24910 | 423 | +1 | 377 | 302M | | | | | 0.44 | Cpu (1) |
| 35 | JOIN FILTER USE | :BF0001 | 17M | 24910 | 423 | +1 | 377 | 302M | | | | | 0.44 | Cpu (1) |
| 36 | PARTITION RANGE ITERATOR | | 17M | 24910 | 423 | +1 | 377 | 302M | | | | | | |
| 37 | TABLE ACCESS STORAGE FULL | TXN | 17M | 24910 | 425 | +1 | 377 | 302M | 571K | 426GB | 87.95% | 7M | 61.84 | Cpu (120) |
| | | | | | | | | | | | | | | cell smart table scan (21) |
| 38 | HASH JOIN | | 2M | 2M | 1 | +427 | 1 | 0 | | | | 2M | | |
| 39 | VIEW | index$_join$_079 | 264 | 2 | 1 | +427 | 1 | 264 | | | | | | |
| 40 | HASH JOIN | | | | 1 | +427 | 1 | 264 | | | | 2M | | |
| 41 | INDEX STORAGE FAST FULL SCAN | S_CNTRY_AK1 | 264 | 1 | 1 | +427 | 1 | 264 | | | | | | |
| 42 | INDEX STORAGE FAST FULL SCAN | S_CNTRY_PK | 264 | 1 | 1 | +427 | 1 | 264 | | | | | | |
| 43 | HASH JOIN | | 2M | 2M | 1 | +427 | 1 | 0 | | | | 2M | | |
| 44 | VIEW | index$_join$_077 | 193 | 2 | 1 | +427 | 1 | 193 | | | | | | |
| 45 | HASH JOIN | | | | 1 | +427 | 1 | 193 | | | | 2M | | |
| 46 | INDEX STORAGE FAST FULL SCAN | S_CRNCY_PK | 193 | 1 | 1 | +427 | 1 | 193 | | | | | | |
| 47 | INDEX STORAGE FAST FULL SCAN | S_CRNCY_AK1 | 193 | 1 | 1 | +427 | 1 | 193 | | | | | | |
| 48 | HASH JOIN | | 2M | 2M | 1 | +427 | 1 | 0 | | | | 2M | | |
| 49 | TABLE ACCESS STORAGE FULL | S_PNT | 86 | 35 | 1 | +427 | 1 | 86 | | | | | | |
| 50 | HASH JOIN | | 2M | 2M | 1 | +427 | 1 | 0 | | | | 1M | | |
| 51 | JOIN FILTER CREATE | :BF0002 | 165 | 2 | 1 | +427 | 1 | 165 | | | | | | |
| 52 | VIEW | index$_join$_078 | 165 | 2 | 1 | +427 | 1 | 165 | | | | | | |
| 53 | HASH JOIN | | | | 1 | +427 | 1 | 165 | | | | 2M | | |
| 54 | INDEX STORAGE FAST FULL SCAN | S_MPCD_IX2 | 165 | 1 | 1 | +427 | 1 | 165 | | | | | | |
| 55 | INDEX STORAGE FAST FULL SCAN | S_MPCD_PK | 165 | 1 | 1 | +427 | 1 | 165 | | | | | | |
| 56 | HASH JOIN | | 2M | 2M | 1 | +427 | 1 | 0 | | | | 2M | | |
| 57 | TABLE ACCESS STORAGE FULL | S_RTRS_CD | 5996 | 22 | 1 | +427 | 1 | 6003 | | | | | | |
| 58 | HASH JOIN | | 2M | 2M | 1 | +427 | 1 | 0 | | | | 1M | | |
| 59 | JOIN FILTER CREATE | :BF0003 | 1664 | 9 | 1 | +427 | 1 | 1665 | | | | | | |
| 60 | TABLE ACCESS STORAGE FULL | S_TX_KEY | 1664 | 9 | 1 | +427 | 1 | 1665 | | | | | | |
| 61 | NESTED LOOPS | | 2G | 2M | 26 | +427 | 1 | 0 | | | | | | |
| 62 | TABLE ACCESS STORAGE FULL | S_DATE | 358 | 69 | 26 | +427 | 1 | 377 | | | | | | |
| 63 | JOIN FILTER USE | :BF0002 | 5M | 6259 | | | 377 | | | | | | | |
| 64 | JOIN FILTER USE | :BF0003 | 5M | 6259 | | | 377 | | | | | | | |
| 65 | PARTITION RANGE ITERATOR | | 5M | 6259 | | | 377 | | | | | | | |
| 66 | TABLE ACCESS STORAGE FULL | TXN | 5M | 6259 | 27 | +426 | 377 | 0 | 528K | 426GB | 99.99% | 7M | 11.84 | Cpu (2) |
| | | | | | | | | | | | | | | reliable message (2) |
| | | | | | | | | | | | | | | cell smart table scan (23) |
=====================================================================================================================================================================================================================
20716155 rows selected.
Elapsed: 00:07:33.89
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ITEM_4"="S_TIN"."ST" AND "ITEM_1"="S_CST_TIN"."USR_KEY")
filter("ITEM_3"<NVL(TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("S_CST_TIN"."END_DATE"),'YYYYMMDD')),29991231) AND "ITEM_2">=TO_NUMBER(TO_CHAR(INTERNAL_FUNCTION("S_CST_TIN"."STRT_DATE"),'YYYYMMDD')))
4 - storage("S_TIN"."ST" IS NOT NULL)
filter("S_TIN"."ST" IS NOT NULL)
5 - access("S_CST_TIN"."TX_KEY"="S_TIN"."TX_KEY")
9 - access("TXN"."CNTRY_KEY"="S_CNTRY"."CNTRY_KEY")
11 - access(ROWID=ROWID)
14 - access("TXN"."CRNCY_KEY"="S_CRNCY"."CRNCY_KEY")
16 - access(ROWID=ROWID)
19 - access("TXN"."PNT_KEY"="S_PNT"."PNT_KEY")
21 - access("TXN"."MPCD_KEY"="S_MPCD"."MPCD_KEY")
24 - access(ROWID=ROWID)
27 - access("TXN"."IT_KEY"="S_RTRS_CD"."IT_KEY" AND "S_RTRS_CD"."YR_KEY"=TO_NUMBER("S_DATE"."YR"))
28 - storage("S_RTRS_CD"."ST"='AB')
filter("S_RTRS_CD"."ST"='AB')
29 - access("S_TX_KEY"."USR_KEY"="TXN"."USR_KEY")
31 - storage("S_TX_KEY"."USR_KEY" IS NOT NULL)
filter("S_TX_KEY"."USR_KEY" IS NOT NULL)
33 - storage("S_DATE"."YR"='2019')
filter("S_DATE"."YR"='2019')
37 - storage(("TXN"."DATE_KEY"<20181101 AND "TXN"."DATE_KEY">=20160921 OR "TXN"."DATE_KEY">=20181229 AND "TXN"."DATE_KEY"<20200308 OR "TXN"."DATE_KEY">=20181123 AND "TXN"."DATE_KEY"<20181201) AND "TXN"."DATE_KEY"="S_DATE"."DATE_KEY" AND SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0001,"TXN"."USR_KEY"),SYS_OP_BLOOM_FILTER (:BF0000,"TXN"."MPCD_KEY")))
filter(("TXN"."DATE_KEY"<20181101 AND "TXN"."DATE_KEY">=20160921 OR "TXN"."DATE_KEY">=20181229 AND "TXN"."DATE_KEY"<20200308 OR "TXN"."DATE_KEY">=20181123 AND "TXN"."DATE_KEY"<20181201) AND "TXN"."DATE_KEY"="S_DATE"."DATE_KEY" AND
SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0001,"TXN"."USR_KEY"),SYS_OP_BLOOM_FILTER(:BF0000,"TXN"."MPCD_KEY")))
38 - access("TXN"."CNTRY_KEY"="S_CNTRY"."CNTRY_KEY")
40 - access(ROWID=ROWID)
43 - access("TXN"."CRNCY_KEY"="S_CRNCY"."CRNCY_KEY")
45 - access(ROWID=ROWID)
48 - access("TXN"."PNT_KEY"="S_PNT"."PNT_KEY")
50 - access("TXN"."MPCD_KEY"="S_MPCD"."MPCD_KEY")
53 - access(ROWID=ROWID)
56 - access("TXN"."IT_KEY"="S_RTRS_CD"."IT_KEY" AND "S_RTRS_CD"."YR_KEY"=TO_NUMBER("S_DATE"."YR"))
57 - storage("S_RTRS_CD"."ST"='AB')
filter("S_RTRS_CD"."ST"='AB')
58 - access("S_TX_KEY"."USR_KEY"="TXN"."USR_KEY")
60 - storage("S_TX_KEY"."USR_KEY" IS NOT NULL)
filter("S_TX_KEY"."USR_KEY" IS NOT NULL)
62 - storage("S_DATE"."YR"='2019')
filter("S_DATE"."YR"='2019')
66 - storage(("TXN"."DATE_KEY"<20160921 OR "TXN"."DATE_KEY">=20181201 AND "TXN"."DATE_KEY"<20181229 OR "TXN"."DATE_KEY">=20181101 AND "TXN"."DATE_KEY"<20181123 OR "TXN"."DATE_KEY">=20200308 AND "TXN"."DATE_KEY"<20200405)
AND "TXN"."DATE_KEY"="S_DATE"."DATE_KEY" AND SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0003,"TXN"."USR_KEY"),SYS_OP_BLOOM_FILTER (:BF0002,"TXN"."MPCD_KEY")))
filter(("TXN"."DATE_KEY"<20160921 OR "TXN"."DATE_KEY">=20181201 AND "TXN"."DATE_KEY"<20181229 OR "TXN"."DATE_KEY">=20181101 AND
"TXN"."DATE_KEY"<20181123 OR "TXN"."DATE_KEY">=20200308 AND "TXN"."DATE_KEY"<20200405) AND "TXN"."DATE_KEY"="S_DATE"."DATE_KEY"
AND SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0003,"TXN"."USR_KEY"),SYS_OP_BLOOM_FILTER (:BF0002,"TXN"."MPCD_KEY")))
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
111751850 consistent gets
111712712 physical reads
20916 redo size
936887233 bytes sent via SQL*Net to client
46045 bytes received via SQL*Net from client
4145 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20716155 rows processed