Oracle 使用DB Link时不使用Push谓词

大家好，
我在执行以下查询时遇到性能问题 (Q1):

select
    z_out。*,
    a_out。id
from orders a_out, test z_out
哪里a_out。id=z_out。id and a_out。created>trunc(sysdate) and rownum<10
复制

表orders包含数百万行;orders。id是主键和orders。created被索引。

观点是:

create or replace view test as 
select/*+qb_name(q_outer)*/
    id,
    min(value) keep (dense_rank first order by id) as value
from (
    select/*+qb_name(q_inner)*/
        id, 
        case
            when substr(id, -1)<'5' 
                --and exists(select 1 from dual@db2)
                then 'YYY'
        end as attr_1
    from orders a1
) a2, small_table b2
哪里b2。attr_1 in (nvl(a2。attr_1, '#'), '*')
group by id
复制

哪里small_table b2包含大约200条记录 (所有列都是varchar2)。

执行Q1有出色的表现和以下的执行计划:

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                     |      1 |        |      9 |00:00:00。01 |     223 |
|*  1 |  COUNT STOPKEY                        |                     |      1 |        |      9 |00:00:00。01 |     223 |
|   2 |   NESTED LOOPS                        |                     |      1 |      1 |      9 |00:00:00。01 |     223 |
|   3 |    PARTITION LIST ALL                 |                     |      1 |      1 |      9 |00:00:00。01 |      41 |
|   4 |     PARTITION RANGE ALL               |                     |      1 |      1 |      9 |00:00:00。01 |      41 |
|   5 |      TABLE ACCESS BY LOCAL INDEX ROWID| ORDERS              |     14 |      1 |      9 |00:00:00。01 |      41 |
|*  6 |       INDEX RANGE SCAN                | IDX_CREATED         |     12 |      1 |      9 |00:00:00。01 |      33 |
|   7 |    VIEW PUSHED PREDICATE              | TEST                |      9 |      1 |      9 |00:00:00。01 |     182 |
|*  8 |     FILTER                            |                     |      9 |        |      9 |00:00:00。01 |     182 |
|   9 |      SORT AGGREGATE                   |                     |      9 |      1 |      9 |00:00:00。01 |     182 |
|  10 |       NESTED LOOPS                    |                     |      9 |    259 |   2376 |00:00:00。01 |     182 |
|* 11 |        INDEX UNIQUE SCAN              | PK_ID               |      9 |      1 |      9 |00:00:00。01 |      20 |
|* 12 |        INDEX STORAGE FAST FULL SCAN   | IDX_MN_AN_AD_ALL    |      9 |    259 |   2376 |00:00:00。01 |     162 |
-----------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<10)                                                                                               
   6 - access("A_OUT"。"CREATED">TRUNC(SYSDATE@!))                                                               
   8 - filter(COUNT(*)>0)                                                                                              
  11 - access("ID"="A_OUT"。"ID")                                                                                 
  12 - storage(("B2"。"ATTR_1"=NVL(CASE  WHEN SUBSTR("ID",(-1))<'5' THEN 'YYY' END ,'#') OR                          
              "B2"。"ATTR_1"='*'))                                                                                      
       filter(("B2"。"ATTR_1"=NVL(CASE  WHEN SUBSTR("ID",(-1))<'5' THEN 'YYY' END ,'#') OR                           
              "B2"。"ATTR_1"='*'))
复制

Q1当线路发生性能问题时--and exists(select 1 from dual@db2)在视图中没有评论。Q1新的执行计划是:

------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                     |      1 |        |      0 |00:00:00。01 |       0 |      0 |       |       |          |
|*  1 |  COUNT STOPKEY                         |                     |      1 |        |      0 |00:00:00。01 |       0 |      0 |       |       |          |
|*  2 |   HASH JOIN                            |                     |      1 |      1 |      0 |00:00:00。01 |       0 |      0 |  3789K|  3789K| 1078K (0)|
|   3 |    JOIN FILTER CREATE                  | :BF0000             |      1 |      1 |  25602 |00:00:00。22 |   23345 |    161 |       |       |          |
|   4 |     PARTITION LIST ALL                 |                     |      1 |      1 |  25602 |00:00:00。21 |   23345 |    161 |       |       |          |
|   5 |      PARTITION RANGE ALL               |                     |      2 |      1 |  25602 |00:00:00。21 |   23345 |    161 |       |       |          |
|   6 |       TABLE ACCESS BY LOCAL INDEX ROWID| ORDERS              |     29 |      1 |  25602 |00:00:00。20 |   23345 |    161 |       |       |          |
|*  7 |        INDEX RANGE SCAN                | IDX_CREATED         |     13 |      1 |  25602 |00:00:00。12 |     474 |    161 |  1025K|  1025K|          |
|   8 |    VIEW                                | TEST                |      1 |   3820K|      0 |00:00:00。01 |       0 |      0 |       |       |          |
|   9 |     SORT GROUP BY                      |                     |      1 |   3820K|      0 |00:00:00。01 |       0 |      0 | 73728 | 73728 |          |
|  10 |      JOIN FILTER USE                   | :BF0000             |      1 |    989M|    106M|00:03:38。87 |      60M|  52960 |       |       |          |
|  11 |       NESTED LOOPS                     |                     |      1 |    989M|    328M|00:03:04。11 |      60M|  52960 |       |       |          |
|  12 |        INDEX FULL SCAN                 | PK_ID               |      1 |   3820K|   1245K|00:00:21。04 |     200K|  52959 |  1025K|  1025K|          |
|* 13 |        INDEX STORAGE FAST FULL SCAN    | IDX_MN_AN_AD_ALL    |   1245K|    259 |    328M|00:02:12。09 |      60M|      1 |  1025K|  1025K|          |
|  14 |         REMOTE                         |                     |      1 |        |      1 |00:00:00。01 |       0 |      0 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM<10)                                                                                                                                    
   2 - access("A_OUT"。"ID"="Z_OUT"。"ID")                                                                                                              
   7 - access("A_OUT"。"CREATED">TRUNC(SYSDATE@!))                                                                                                    
  13 - filter(("B2"。"ATTR_1"=NVL(CASE  WHEN (SUBSTR("ID",(-1))<'5' AND  IS NOT NULL) THEN 'YYY' END ,'#') OR "B2"。"ATTR_1"='*'))
复制

注意:Q1性能阻止查询完成，如果and exists(select 1 from dual@db2)在视图中没有评论。To get the previous execution plan I had to execute Q1,停止Q1(大约4分钟后)，然后制定了计划。

and exists(select 1 from dual@db2)用于表明即使使用与该条件一样简单的条件 (我正在使用的 “真实” 视图访问DB2 for some good reasons)。

我希望可以访问该视图n时代，就像在第一个场景中。我尝试使用提示，但没有成功。

可能有用地说，即使有了线和exists(select 1 from dual@db2)在视图中，以下查询具有出色的性能 (我知道这与Q1)。

select
    (select value from test z_out 哪里a_out。id=z_out。id) as value,
    a_out。id
from orders a_out
哪里a_out。created>trunc(sysdate) and rownum<10
复制

所以，我想当它被访问时，它工作正常n即使行and exists(select 1 from dual@db2)没有评论。但是我不能强迫执行计划朝那个方向发展Q1。

If hints are necessary, I'd like to add them inside the view DDL only (if possible) so that the view users won't have to worry about it。

谢谢

一些观察结果:

-Q1使用嵌套循环将订单连接到视图。Q2使用哈希连接。哈希联接不能将第一个表的谓词应用于第二个表

-第一个 (快速) 查询仅从orders返回9行。第二个超过25,000。行数的增加可能足以使自适应计划从NL -> HJ切换。

-远程查询在您的select子句中。所以它对每一行q_inner返回执行一次。这是1.2米和计数!将其转换为外部联接可能会有所帮助

-查询似乎访问订单两次。加入ID，所以两者都得到同一行!在我看来，您可以简化查询以避免这些访问之一

因此，我首先将查询更改为仅访问一次订单。所以你今天只从订单中获取行。这本身可能足以解决问题!

我不明白视图做得足够好，无法说明。如果您需要帮助，请提交一个新问题:

-创建表格
-以插入intos的形式采样数据
-预期查询结果