sql - UNION ALL 的 Oracle 优化器问题

Question

我对 Oracle 优化器的这种行为感到非常困惑。这与联合来自 CTE 的所有操作有关。如果有人有任何想法，我就是游戏。

 --Relevant data structures:
 --t_positionperf (index: POSITIONPERF_X1 (account_id, hist_date, security_id)
 --t_positionhist (index: POSITIONHIST_X1 (position_id, hist_date, hist_type)
 --                       PK_T_POSITIONHIST (hist_date, position_id, hist_type)
 --v_positiontype (very simple "case-when" translation of a tiny lookup table)

with q as (
select 
pp.position_id, pp.hist_date, pp.account_id, pp.income, pp.expense,
ph.position_type_id, ph.price, ph.quantity, ph.factor, ph.daily_accrual,
n.daily_accrual as new_daily_accrual, nvl(n.is_loan, v.is_loan) as new_is_loan
from
t_positionperf pp
left outer join t_positionhist ph
            on  pp.position_id = ph.position_id
            and pp.hist_date = ph.hist_date
            and ph.hist_type = 'O' --the 'O' join set from t_positionhist
     left outer join v_positiontype v 
                 on  ph.position_type_id = v.position_type_id
     left outer join (
                      select 
                      x.position_id, x.hist_date, x.daily_accrual, v2.is_loan 
                      from t_positionhist x 
                      join v_positiontype v2 on x.position_type_id = v2.position_type_id
                      where x.hist_type = 'N'
                     ) n --the 'N' join set from t_positionhist
                on  ph.position_id = n.position_id 
                and ph.hist_date = n.hist_date
where pp.account_id in (5018,5312)
and pp.hist_date between to_date('01-jan-14') and  to_date('31-jan-14')
)
select 
q.account_id,q.hist_date,q.position_id,q.income,
q.expense,q.position_type_id,q.price,q.quantity,q.factor
from q

这使用了合理的访问路径。

Statistics
----------------------------------------------------------
         29  recursive calls
          0  db block gets
      37640  consistent gets
       5115  physical reads
          0  redo size
     227442  bytes sent via SQL*Net to client
       4952  bytes received via SQL*Net from client
        409  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       6116  rows processed


Execution Plan:
--------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)  | Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |   941 | 88454 |    5283   (1)| 00:01:04 |
|*  1 |  FILTER                          |                   |       |       |              |          |
|   2 |   NESTED LOOPS OUTER             |                   |   941 | 88454 |    5283   (1)| 00:01:04 |
|*  3 |    HASH JOIN RIGHT OUTER         |                   |   941 | 71516 |    2456   (1)| 00:00:30 |
|   4 |     TABLE ACCESS FULL            | T_POSITIONTYPE    |    64 |  1216 |       3   (0)| 00:00:01 |
|   5 |     NESTED LOOPS OUTER           |                   |   941 | 53637 |    2452   (1)| 00:00:30 |
|   6 |      INLIST ITERATOR             |                   |       |       |              |          |
|   7 |       TABLE ACCESS BY INDEX ROWID| T_POSITIONPERF    |   941 | 22584 |     567   (1)| 00:00:07 |
|*  8 |        INDEX RANGE SCAN          | POSITIONPERF_X1   |   941 |       |       6   (0)| 00:00:01 |
|   9 |      TABLE ACCESS BY INDEX ROWID | T_POSITIONHIST    |     1 |    33 |       2   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN          | POSITIONHIST_X1   |     1 |       |       1   (0)| 00:00:01 |
|  11 |    VIEW PUSHED PREDICATE         |                   |     1 |    18 |       3   (0)| 00:00:01 |
|  12 |     NESTED LOOPS                 |                   |     1 |    27 |       4   (0)| 00:00:01 |
|  13 |      TABLE ACCESS BY INDEX ROWID | T_POSITIONHIST    |     1 |    20 |       3   (0)| 00:00:01 |
|* 14 |       INDEX UNIQUE SCAN          | POSITIONHIST_X1   |     1 |       |       2   (0)| 00:00:01 |
|  15 |      TABLE ACCESS BY INDEX ROWID | T_POSITIONTYPE    |     1 |     7 |       1   (0)| 00:00:01 |
|* 16 |       INDEX UNIQUE SCAN          | PK_T_POSITIONTYPE |     1 |       |       0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

但是，如果您从 CTE 中向此选择添加 UNION ALL，则查询如下所示:

with q as (...) 
select q.account_id, q... etc.
UNION ALL
select q.account_id, max(q...) etc.
group by q.account_id

优化器现在想要实现 CTE(TEMP TABLE TRANSFORMATION)。这部分对我来说很有意义，因为来自同一个 CTE 的 UNION ALL。我不明白的是为什么实现 CTE 会导致它选择不同的(而且要差很多)访问路径。在构建“N”连接集时，它似乎失去了将谓词推送到 T_POSITIONHIST 表的能力。您可以看到它现在对 T_POSITIONHIST 进行了全表扫描，因此磁盘 I/O 已经通过了 的屋顶 :

Statistics
----------------------------------------------------------
        362  recursive calls
         53  db block gets
     546116  consistent gets
     521527  physical reads
        688  redo size
     250541  bytes sent via SQL*Net to client
       4952  bytes received via SQL*Net from client
        409  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       6117  rows processed


Execution Plan
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                             |  1882 |   205K|    13  (54)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION         |                             |       |       |            |          |
|   2 |   LOAD AS SELECT                   | SYS_TEMP_0FD9D6CB3_54C7EBF8 |       |       |            |          |
|*  3 |    FILTER                          |                             |       |       |            |          |
|*  4 |     HASH JOIN OUTER                |                             |   941 | 81867 |   147K  (3)| 00:29:32 |
|*  5 |      HASH JOIN RIGHT OUTER         |                             |   941 | 63047 |  2456   (1)| 00:00:30 |
|   6 |       VIEW                         | V_POSITIONTYPE              |    64 |   448 |     3   (0)| 00:00:01 |
|   7 |        TABLE ACCESS FULL           | T_POSITIONTYPE              |    64 |   448 |     3   (0)| 00:00:01 |
|   8 |       NESTED LOOPS OUTER           |                             |   941 | 56460 |  2452   (1)| 00:00:30 |
|   9 |        INLIST ITERATOR             |                             |       |       |            |          |
|  10 |         TABLE ACCESS BY INDEX ROWID| T_POSITIONPERF              |   941 | 22584 |   567   (1)| 00:00:07 |
|* 11 |          INDEX RANGE SCAN          | POSITIONPERF_X1             |   941 |       |     6   (0)| 00:00:01 |
|  12 |        TABLE ACCESS BY INDEX ROWID | T_POSITIONHIST              |     1 |    36 |     2   (0)| 00:00:01 |
|* 13 |         INDEX UNIQUE SCAN          | POSITIONHIST_X1             |     1 |       |     1   (0)| 00:00:01 |
|  14 |      VIEW                          |                             |  2358K|    44M|   145K  (3)| 00:29:02 |
|  15 |       VIEW                         |                             |  2358K|    51M|   145K  (3)| 00:29:02 |
|* 16 |        HASH JOIN                   |                             |  2358K|    67M|   145K  (3)| 00:29:02 |
|  17 |         VIEW                       | V_POSITIONTYPE              |    64 |   448 |     3   (0)| 00:00:01 |
|  18 |          TABLE ACCESS FULL         | T_POSITIONTYPE              |    64 |   448 |     3   (0)| 00:00:01 |
|* 19 |         TABLE ACCESS FULL          | T_POSITIONHIST              |  2358K|    51M|   145K  (3)| 00:29:01 |
|  20 |   UNION-ALL                        |                             |       |       |            |          |
|  21 |    VIEW                            |                             |   941 |   106K|     6   (0)| 00:00:01 |
|  22 |     TABLE ACCESS FULL              | SYS_TEMP_0FD9D6CB3_54C7EBF8 |   941 | 56460 |     6   (0)| 00:00:01 |
|  23 |    HASH GROUP BY                   |                             |   941 |    99K|     7  (15)| 00:00:01 |
|  24 |     VIEW                           |                             |   941 |    99K|     6   (0)| 00:00:01 |
|  25 |      TABLE ACCESS FULL             | SYS_TEMP_0FD9D6CB3_54C7EBF8 |   941 | 56460 |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------

奇怪的是，您可以通过复制 CTE 来部分解决此问题，如下所示:

with q as (...), q2 as (.../*duplicate of q*/)
select q.account_id, q... etc.
UNION ALL
select q2account_id, max(q2...) etc.
group by q2.account_id

因为它不再想要物化结果集，所以它能够使用更智能的访问路径，除非它必须执行两次，因此成本大约翻了一番(但仍远低于它上面使用的真正糟糕的访问路径)。

统计数据

     57  recursive calls
      0  db block gets
  73855  consistent gets
      0  physical reads
      0  redo size
 202937  bytes sent via SQL*Net to client
   4953  bytes received via SQL*Net from client
    409  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
   6117  rows processed

Answer 1

所以最终我找到了答案:显然优化器决定实现 CTE('With' 块)会破坏优化器执行 的能力。基于成本的查询转换 这反过来又破坏了执行某些操作的能力，例如 Join Predicate Pushdown(这是上面看到的解释计划中的 View Pushed Predicate)和 Complex View Merge(一种重写查询块的方法)。这可能会导致优化器在您期望它使用来自附加到查询的谓词的某些索引访问时对大量表进行不必要的全表扫描等可怕的事情。

我在其他几种情况下遇到了这个问题，并最终在进行研究时遇到了以下启发性的博文:
http://oracle-randolf.blogspot.com/2008/01/subquery-factoring-and-cost-based-query.html

似乎最初使用 CTE 禁用了 CBQT，但在 11.2.0.3 中，如果 WITH View 被内联(即未实现)，优化器将使用 CBQT，所以现在只有在选择 WITH View 被实现时才会出现问题。请参阅链接中的评论:
帖木儿·阿赫马杰夫 说过...
只是想注意这个问题在 11.2.0.3 中不再存在。它在“如果所有 WITH View 都内联则允许 CBQT”下进行跟踪 (11740670)

这里看到的两种策略都解释为:(1) 复制 WITH View 和 (2) Yaroslav Shabalin 建议使用 INLINE 提示