Performance tuning of a query processing millions of rows
<p>I've got a query processing 2 tables with over 61 million records each. </p>

<ul>
<li><strong>WB_YH_BCUPDATE_FULL_BASE:</strong> table containing customers and all the months they were active. (from 2014 till now)</li>
</ul>

<blockquote>
  <p>CUSTOMERNUMBER | CAR MONTH  </p>
  
  <hr>
  
  <p>99999 | 201401<br>
  99999 | 201402<br>
  99999 | 201403<br>
  ....</p>
</blockquote>

<ul>
<li><strong>WB_YH_BCUPDATE_MATCH_MONTH:</strong> table containing customers and all the months that were active with + a fictive extra field containing the CAR_MONTH + 6 months.</li>
</ul>

<blockquote>
  <p>CUSTOMERNUMBER    | CAR_MONTH | MATCH_MONTH_6</p>
  
  <hr>
  
  <p>99999 | 201401 | 201407<br>
  99999 | 201402 | 201408<br>
  99999 | 201403 | 201409<br>
  ...</p>
</blockquote>

<p>Now I want to check for all customers and all of their corresponding CAR_MONTHS if they were still active (= they appear in the table) after 6 months. For this I need to use the field I created, being MATCH_MONTH_6.</p>

<p>I'm using the following query: </p>

<pre><code>select distinct a.CUSTOMERNUMBER
   , a.CAR_MONTH
   , b.MATCH_MONTH_6
   , CASE WHEN b.CUSTOMERNUMBER is null then 0
          ELSE 1
     END FL_MATCH_6         
from WB_YH_BCUPDATE_FULL_BASE a left join WB_YH_BCUPDATE_MATCH_MONTH b
                                on a.CUSTOMERNUMBER =  b.CUSTOMERNUMBER      
                                and a.CAR_MONTH = b.CAR_MONTH 
                                and b.MATCH_MONTH_6 in (
                                select CAR_MONTH
                                from   WB_YH_BCUPDATE_FULL_BASE
                                where  customernumber = a.customernumber
                                );
</code></pre>

<p>The performance of my query is really poor as you can see from the following Execution Plan:</p>

<pre><code> Plan Hash Value  : 3376431373 

-----------------------------------------------------------------------------------------------------------------------------
| Id   | Operation                          | Name                           | Rows     | Bytes      | Cost      | Time     |
-----------------------------------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                   |                                | 25897713 |  673340538 | 371846479 | 02:56:04 |
|    1 |   HASH UNIQUE                      |                                | 25897713 |  673340538 | 371846479 | 02:56:04 |
|    2 |    NESTED LOOPS OUTER              |                                | 61874441 | 1608735466 | 371674345 | 02:55:59 |
|    3 |     TABLE ACCESS STORAGE FULL      | WB_YH_BCUPDATE_FULL_BASE       | 61874441 |  742493292 |      3225 | 00:00:01 |
|    4 |     VIEW                           |                                |        1 |         14 |         6 | 00:00:01 |
|    5 |      NESTED LOOPS                  |                                |        1 |         31 |         6 | 00:00:01 |
|    6 |       NESTED LOOPS                 |                                |       24 |         31 |         6 | 00:00:01 |
|  * 7 |        TABLE ACCESS BY INDEX ROWID | WB_YH_BCUPDATE_MATCH_MONTH     |        1 |         19 |         3 | 00:00:01 |
|  * 8 |         INDEX RANGE SCAN           | WB_YH_BCUPDATE_MATCH_MONTH_IND |       24 |            |         2 | 00:00:01 |
|  * 9 |        INDEX RANGE SCAN            | WB_YH_BCUPDATE_FULL_BASE_IND   |       24 |            |         2 | 00:00:01 |
| * 10 |       TABLE ACCESS BY INDEX ROWID  | WB_YH_BCUPDATE_FULL_BASE       |        1 |         12 |         3 | 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 7 - filter("A"."CAR_MONTH"="B"."CAR_MONTH")
* 8 - access("A"."CUSTOMERNUMBER"="B"."CUSTOMERNUMBER")
* 9 - access("CUSTOMERNUMBER"="A"."CUSTOMERNUMBER")
* 10 - filter("CAR_MONTH"=TO_NUMBER("B"."MATCH_MONTH_6"))
</code></pre>

<p>Do you guys have any idea on how I can optimize this query or how I can rewrite this query to be more performant?</p>

<p>Kind regards,</p>