sql - 强制 Oracle 数据库不实现 CTE

Question

我对 cte 有一些问题，如果我使用这个查询 oracle 实现 cte1 View 并且查询会很慢

with cte1 as (..),
    cte2 as ( ... use cte1 ...),
    cte3 as ( ... use cte1 ...)
select * from  cte2  join cte3
  on ...

在以下查询中，Oracle 没有实现 cte1，并且查询比以前快 20 倍:

with cte1 as (..),
    cte2 as ( ... use cte1 ...)
select * from cte2 on ....

还有

with cte1 as (..),
    cte3 as ( ... use cte1 ...)
select * from cte3 on ....

是否可以强制 Oracle 不实现 CTE，以便使用 idex？

查询 1 的执行计划:

Plan hash value: 1038428573

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                             |   126K|   104M|  1753   (1)| 00:00:22 |
|   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |            |          |
|   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9DC639_11293183 |       |       |            |          |
|*  3 |    HASH JOIN                   |                             | 39285 |  1726K|  1618   (1)| 00:00:20 |
|*  4 |     HASH JOIN                  |                             | 31724 |   650K|   863   (1)| 00:00:11 |
|*  5 |      INDEX RANGE SCAN          | UQ1_xxxxxxx                 | 31724 |   402K|    23   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL         | xxxx                        |   384K|  3005K|   837   (1)| 00:00:11 |
|   7 |     TABLE ACCESS FULL          | xxxxxxxxx                   |   481K|    11M|   753   (1)| 00:00:10 |
|*  8 |   HASH JOIN                    |                             |   126K|   104M|   136   (3)| 00:00:02 |
|*  9 |    HASH JOIN                   |                             |     3 |  1314 |    68   (2)| 00:00:01 |
|  10 |     TABLE ACCESS BY INDEX ROWID| xxxxxxxxxxxxxxxx            |     2 |    20 |     1   (0)| 00:00:01 |
|* 11 |      INDEX RANGE SCAN          | FK2_xxxxxxxxxxxxxxxx        |     2 |       |     1   (0)| 00:00:01 |
|* 12 |     VIEW                       |                             | 39285 |    16M|    66   (0)| 00:00:01 |
|  13 |      TABLE ACCESS FULL         | SYS_TEMP_0FD9DC639_11293183 | 39285 |  1035K|    66   (0)| 00:00:01 |
|* 14 |    VIEW                        |                             | 39285 |    16M|    66   (0)| 00:00:01 |
|  15 |     TABLE ACCESS FULL          | SYS_TEMP_0FD9DC639_11293183 | 39285 |  1035K|    66   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ES"."xxxxxxxxxxxxx"="E"."xxxxxxxxxxxxxx")
   4 - access("CR"."xxxxxxxxxxxxxx"="E"."xxxxxxxxxxxxID")
   5 - access("CR"."xxxxxxxID"=TO_NUMBER(:xxxxxxxID))
   8 - access("EV"."xxxxxxxxxxxx_ID"="LA"."xxxxxx_ID")
   9 - access("LA"."xxxxxxxxxxxxx_ID"="EV2"."xxxxxxxxxxxx_ID")
  11 - access("LA"."xxxxxxxID"=359134)
  12 - filter("EV2"."xxxxxxxxxxxxxxxxID"=4)
  14 - filter("EV"."xxxxxxxxxxxxxxx_ID"=3 AND "EV"."xxxxxxxxxxxx_ID"=359134)

查询 2 的执行计划:

计划哈希值:1937334873

----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                        |     1 |    55 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                   |                        |       |       |            |          |
|   2 |   NESTED LOOPS                  |                        |     1 |    55 |     4   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                 |                        |     1 |    31 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                        |     2 |    46 |     2   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| xxxxxxxxxxxxxxxx       |     2 |    20 |     1   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | FK2_xxxxxxxxxxxxxxxx   |     2 |       |     1   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| xxxxxxxxxxxx           |     1 |    13 |     1   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN          | FK2_xxxxxxxxxxxx       |     4 |       |     1   (0)| 00:00:01 |
|*  9 |     TABLE ACCESS BY INDEX ROWID | xxxxxxxxxxxxx          |     1 |     8 |     1   (0)| 00:00:01 |
|* 10 |      INDEX UNIQUE SCAN          | PK_xxxxxxxxxxxxx       |     1 |       |     1   (0)| 00:00:01 |
|* 11 |    INDEX RANGE SCAN             | UQ1_xxxxxxxxxxxxxxxxxx |     1 |       |     1   (0)| 00:00:01 |
|  12 |   TABLE ACCESS BY INDEX ROWID   | xxxxxxxxxxxxxxxxxx     |     1 |    24 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access("LA"."xxxxxx_ID"=359134)
   7 - filter("CR"."xxxxxxxID"=TO_NUMBER(:xxxxxxxID))
   8 - access("LA"."xxxxxxxxxxxxx_ID"="CR"."xxxxxxxxxxxx_ID")
   9 - filter("E"."xxxxxxxxxxxxxxx_ID"=4)
  10 - access("CR"."xxxxxxxxxxxxID"="E"."xxxxxxxxxxxxID")
  11 - access("ES"."xxxxxxxxxxxID"="E"."xxxxxxxxxxxxID")

Answer 1

有一个未记录的 /*+ inline */提示应该阻止优化器实现 CTE。应该放在 select 之后在 CTE 本身。
/*+ materialize */提示与此相反，即请求实现 View 。

据我所知，这些都没有正式记录，因此请谨慎使用。在这种情况下，通过 Oracle 支持打开 SR 以获得一些建议是一个好主意，他们可以“批准”使用提示或提供替代方案(包括可以解决问题的潜在补丁/错误修正)。