Oracle:极大地提高查询性能

Question

我有下一个查询，我正在尝试提高性能:

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);

我已经运行并检查了解释计划，总成本为 7388。其中，成本最高的部分是与 journal 表的连接，其成本为 6319。

该表有大约 160 万行和 87 个分区，其中只有两个包含行(分区 6 有 140 万行，分区 12 有大约剩余的 20 万行)。

我尝试的第一件事是重写查询以避免在将实际的 journal_type_id 匹配到 6 时进行全扫描，但我想我的理解是不正确的，因为成本仍然是 7388。

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);

我确实查找了很多资源，而决定我重新编写查询的原因之一是 this video .

我仍在积极寻找提高性能的方法，但我想我应该在这里提出一个问题以获得一些提示。

我认为视频中的人所说的关于应该做的第一件事是确定哪个是您的“驱动表”(确定要选择哪些行的表 - 基于键)，所以我'我目前正在寻找一种重写查询的方法，以尽可能多地识别和使用这个驱动表及其索引。

我不知道我是否走在正确的轨道上，但如果您认为我不应该继续前进，请阻止我。另外，请注意，我是性能调优的新手，实际上这是我的第一次。

感谢任何帮助。

更新:

一些包含查询中使用的列的索引是:

╔════════════╦═══════════════╦════════════╦═══════════╦═════════════╦═══════════════════════════════════╗
║   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            ║
╚════════════╩═══════════════╩════════════╩═══════════╩═════════════╩═══════════════════════════════════╝

如果您需要查看更多索引或有关其他表的详细信息，请告诉我。

样本解释计划:

-------------------------------------------------------------------------------------------------------------------------------
| 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 |       |       |
-------------------------------------------------------------------------------------------------------------------------------

Answer 1

首先检查您的统计信息是否已更新:优化器在很大程度上取决于统计信息!其次，您应该说明通过此查询获得的行数:根据每个条件选择的行数，完全扫描可能比索引搜索更好。