title: Oracle: hugely improve query performance
body: <p>I have this next query for which I'm trying to improve performance:</p>
  
  <pre><code>select atx.journal_id
      ,ab.c_date
  from acct_batch ab 
      join acct_tx atx on ab.acct_id = atx.acct_id 
        and ab.batch_id = atx.batch_id
      join journal j on j.journal_id = atx.journal_id
        and j.journal_type_id = 6
      join acct a on a.acct_id = atx.acct_id 
        and a.acct_type_id = 32
      join payments p on p.payment_id = j.payment_id
      join routing r on r.route_id = p.route_id 
        and r.acq_code = 'RZ_NS'
      join acq_acct aa on aa.acq_code = r.acq_code
        and aa.acq_acct_code = r.acq_acct_code
        and aa.slc = 'MXM'
  where ab.c_date between to_date(to_char('01-JUL-2015')) and  last_day(sysdate);
  </code></pre>
  
  <p>I've ran and reviewed the explain plan and the total cost is 7388. Out of this, the most expensive part is the join with the <code>journal</code> table, which has a cost of 6319.</p>
  
  <p><a href="https://i.stack.imgur.com/ofxiX.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ofxiX.png" alt="Part_of_explain_plan"></a></p>
  
  <p>The table has approximately 1.6 million rows with 87 partitions, out of which only two contain the rows (partition 6 with 1.4 million and partition 12 with the approximate rest of 200k rows).</p>
  
  <p>The first thing I've tried is to re-write the query to avoid the full-scan on matching the actual journal_type_id to 6, but I guess my understanding was incorrect, because the cost remained 7388.</p>
  
  <pre><code>select atx.journal_id
      ,ab.c_date
  from acct_batch ab 
      join acct_tx atx on ab.acct_id = atx.acct_id 
        and ab.batch_id = atx.batch_id
      join (select 
                journal_id
                , payment_id 
            from journal 
            where journal_type_id = 6) j on j.journal_id = atx.journal_id
      join acct a on a.acct_id = atx.acct_id 
        and a.acct_type_id = 32
      join payments p on p.payment_id = j.payment_id
      join routing r on r.route_id = p.route_id 
        and r.acq_code = 'RZ_NS'
      join acq_acct aa on aa.acq_code = r.acq_code
        and aa.acq_acct_code = r.acq_acct_code
        and aa.slc = 'MXM'
  where ab.c_date between to_date(to_char('01-JUL-2015')) and  last_day(sysdate);
  </code></pre>
  
  <p>I did look for a lot of resources and one of the reason which determined me to re-write the query was <a href="https://www.youtube.com/watch?v=ZVisY-fEoMw" rel="nofollow noreferrer">this video</a>.</p>
  
  <p>I'm still actively looking for ways of improving performance, but I thought I'd shoot a question here to maybe get some hints.</p>
  
  <p>I think what the person from the video says about the first thing that should be done is to identify which is your "driving table" (the one which determines what rows are being selected - based on the key), so I'm currently looking for a way of re-writing the query to identify and use this driving table and its indexes as much as possible.</p>
  
  <p>I don't know if I'm on the right track yet, but please stop me if you think I should proceed otherwise. Also, please note that I am a total beginner in performance tuning, actually this being my first.</p>
  
  <p>Any help is appreciated.</p>
  
  <p><strong>Update:</strong></p>
  
  <p>Some of the indexes which contain the columns used in the queries are:</p>
  
  <pre><code>╔════════════╦═══════════════╦════════════╦═══════════╦═════════════╦═══════════════════════════════════╗
  ║   Table    ║   IndexName   ║ Uniqueness ║ IndexType ║ Partitioned ║              Columns              ║
  ╠════════════╬═══════════════╬════════════╬═══════════╬═════════════╬═══════════════════════════════════╣
  ║ Acct_Batch ║ Acct_Batch_PK ║ UNIQUE     ║ NORMAL    ║ NO          ║ Acct_ID, Batch_ID                 ║
  ║ Acct_TX    ║ Acct_TX_IDX   ║ NONUNIQUE  ║ NORMAL    ║ YES         ║ Acct_ID, Batch_ID                 ║
  ║ Acct_TX    ║ Acct_TX_BIDX  ║ NONUNIQUE  ║ NORMAL    ║ YES         ║ Journal_ID, Acct_ID               ║
  ║ Journal    ║ Journal_PK    ║ UNIQUE     ║ NORMAL    ║ YES         ║ Journal_ID                        ║
  ║ Journal    ║ JType_BIDX    ║ NONUNIQUE  ║ NORMAL    ║ YES         ║ Journal_Type_ID, Book_Date        ║
  ║ Journal    ║ JType_BIDX_2  ║ NONUNIQUE  ║ NORMAL    ║ YES         ║ MCODE, Journal_Type_ID, Book_Date ║
  ║ Journal    ║ JPay_BIDX     ║ NONUNIQUE  ║ NORMAL    ║ YES         ║ Payment_ID, Journal_ID            ║
  ╚════════════╩═══════════════╩════════════╩═══════════╩═════════════╩═══════════════════════════════════╝
  </code></pre>
  
  <p>Let me know if you need to see more indexes or details regarding the other tables.</p>
  
  <p>Sample explain plan:</p>
  
  <pre><code>-------------------------------------------------------------------------------------------------------------------------------
  | Id  | Operation                                 | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
  -------------------------------------------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT                          |                   |     1 |   160 |  7388   (1)| 00:01:29 |       |       |
  |*  1 |  FILTER                                   |                   |       |       |            |          |       |       |
  |   2 |   NESTED LOOPS                            |                   |       |       |            |          |       |       |
  |   3 |    NESTED LOOPS                           |                   |     1 |   160 |  7388   (1)| 00:01:29 |       |       |
  |*  4 |     HASH JOIN                             |                   |     4 |   604 |  7380   (1)| 00:01:29 |       |       |
  |   5 |      NESTED LOOPS                         |                   |       |       |            |          |       |       |
  |   6 |       NESTED LOOPS                        |                   |   107 | 14338 |  7372   (1)| 00:01:29 |       |       |
  |*  7 |        HASH JOIN                          |                   |    27 |  3186 |  7298   (1)| 00:01:28 |       |       |
  |   8 |         NESTED LOOPS                      |                   |       |       |            |          |       |       |
  |   9 |          NESTED LOOPS                     |                   |   102 | 10302 |   978   (0)| 00:00:12 |       |       |
  |  10 |           NESTED LOOPS                    |                   |    11 |   638 |    37   (0)| 00:00:01 |       |       |
  |* 11 |            TABLE ACCESS BY INDEX ROWID    | ACQ_ACCT          |    11 |   253 |     4   (0)| 00:00:01 |       |       |
  |* 12 |             INDEX RANGE SCAN              | AA_PK             |    16 |       |     2   (0)| 00:00:01 |       |       |
  |  13 |            TABLE ACCESS BY INDEX ROWID    | ROUTES            |     1 |    35 |     3   (0)| 00:00:01 |       |       |
  |* 14 |             INDEX RANGE SCAN              | R_A_BIDX          |     1 |       |     2   (0)| 00:00:01 |       |       |
  |  15 |           PARTITION RANGE ALL             |                   |    95 |       |    84   (0)| 00:00:02 |     1 |    84 |
  |* 16 |            INDEX RANGE SCAN               | P_R_ID_BIDX       |    95 |       |    84   (0)| 00:00:02 |     1 |    84 |
  |  17 |          TABLE ACCESS BY LOCAL INDEX ROWID| PAYMENTS          |     9 |   387 |   100   (0)| 00:00:02 |     1 |     1 |
  |  18 |         PARTITION RANGE ALL               |                   |   107K|  1782K|  6319   (1)| 00:01:16 |     1 |    87 |
  |* 19 |          TABLE ACCESS FULL                | JOURNAL           |   107K|  1782K|  6319   (1)| 00:01:16 |     1 |    87 |
  |  20 |        PARTITION RANGE ITERATOR           |                   |     4 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
  |* 21 |         INDEX RANGE SCAN                  | ATX_A_IDX         |     4 |       |     2   (0)| 00:00:01 |   KEY |   KEY |
  |  22 |       TABLE ACCESS BY LOCAL INDEX ROWID   | ACCT_TX           |     4 |    64 |     3   (0)| 00:00:01 |     1 |     1 |
  |* 23 |      INDEX RANGE SCAN                     | AB_B_A_IDX        |  5006 | 85102 |     8   (0)| 00:00:01 |       |       |
  |* 24 |     INDEX UNIQUE SCAN                     | ACC_PK            |     1 |       |     1   (0)| 00:00:01 |       |       |
  |* 25 |    TABLE ACCESS BY INDEX ROWID            | ACCT              |     1 |     9 |     2   (0)| 00:00:01 |       |       |
  -------------------------------------------------------------------------------------------------------------------------------
  </code></pre>