sql - 带有流水线的 Oracle 查询 - 性能问题

Question

我想根据以下查询创建一个 View :

create or replace view MY_VIEW as
select A.*
from MY_TABLE A, table(MY_PACKAGE.PIPELINED_FUNCTION(A.MY_DATE)) P
where A.MY_FIELD1 = P.MY_FIELD1
  and ...

View 应该这样使用:

select *
from MY_VIEW V
where V.MY_DATE = trunc(sysdate)

如您所见，我的请求有点棘手，因为 PIPELINED_FUNCTION 是使用 A 中的字段调用的!而且我的请求真的很慢，我怀疑是因为 Oracle 试图用所有可能的日期计算 P。当然，如果我确定日期(例如 MY_PACKAGE.PIPELINED_FUNCTION(trunc(sysdate)))，答案是立竿见影的。

这里是执行计划(MY_TABLE其实是一个 View :FO.V_TRADING_POOL_INOUT_PRD，管线名称其实是最下面的POOL_INOUT_ELIG_BEST_RANK ):

SELECT STATEMENT, GOAL = ALL_ROWS           211 2   16576   191
 VIEW   FO  V_TRADING_POOL_INOUT_CANDIDATE  211 2   16576   191
  NESTED LOOPS          130 1   8290    115
   VIEW FO  V_TRADING_POOL_INOUT_PRD    63  1   8288    56
    SORT GROUP BY           63  1   941 56
     NESTED LOOPS OUTER         62  1   941 56
      NESTED LOOPS OUTER            61  1   928 55
       NESTED LOOPS         60  1   894 54
        NESTED LOOPS            59  1   884 53
         NESTED LOOPS           58  1   874 52
          NESTED LOOPS OUTER            57  1   832 51
           NESTED LOOPS OUTER           56  1   794 50
            NESTED LOOPS OUTER          55  1   757 49
             NESTED LOOPS OUTER         54  1   720 48
              NESTED LOOPS OUTER            53  1   683 47
               NESTED LOOPS OUTER           52  1   670 46
                HASH JOIN           51  1   657 45
                 INDEX FULL SCAN    FO  TRADING_POOL_GROUP_PK   1   113 678 1
                 HASH JOIN RIGHT OUTER          49  18  11718   44
                  VIEW  FO      16  63  4221    13
                   HASH JOIN            16  63  4536    13
                    HASH JOIN OUTER         13  63  3843    11
                     FILTER                     
                      HASH JOIN OUTER           9   63  3276    8
                       HASH JOIN            3   81  2025    2
                        INDEX FULL SCAN FO  TRADING_POOL_GROUP_PK   1   113 678 1
                        INDEX RANGE SCAN    FO  TRADING_POOL_IO_EQT_IDX1    1   157 2983    1
                       TABLE ACCESS BY INDEX ROWID  FO  PRODUCT_BLACKLIST   6   33  891 6
                        INDEX RANGE SCAN    FO  PRODUCT_BLACKLIST_IDX2  2   33      2
                     TABLE ACCESS FULL  FO  TRADING_POOL_GROUP_TRANS    3   6   54  3
                    VIEW    FO  index$_join$_024    3   96  1056    2
                     HASH JOIN                      
                      INDEX FAST FULL SCAN  FO  SYS_C0018504    1   96  1056    1
                      INDEX FAST FULL SCAN  FO  TRADING_POOL_DEF_PK 1   96  1056    1
                  NESTED LOOPS OUTER            33  18  10512   31
                   NESTED LOOPS OUTER           30  18  9882    29
                    FILTER                      
                     HASH JOIN RIGHT OUTER          29  18  9666    28
                      TABLE ACCESS BY INDEX ROWID   FO  PRODUCT_VALIDATION  1   13  988 1
                       INDEX RANGE SCAN FO  PRODUCT_VALIDATION_IDX1 1   13      1
                      NESTED LOOPS                      
                       NESTED LOOPS         28  18  8298    26
                        NESTED LOOPS            25  18  7956    24
                         TABLE ACCESS BY INDEX ROWID    FO  TRADING_POOL_CONTEXT    1   1   9   1
                          INDEX UNIQUE SCAN FO  TRADING_POOL_CTXT_PK    1   1       1
                         VIEW   FO      24  18  7794    23
                          SORT GROUP BY         24  18  4950    23
                           NESTED LOOPS OUTER           23  18  4950    23
                            TABLE ACCESS BY INDEX ROWID FO  TRADING_POOL_INOUT_PRD  18  18  3510    18
                             INDEX RANGE SCAN   FO  TRADING_POOL_IO_EQT_IDX1    1   157     1
                            TABLE ACCESS BY INDEX ROWID FO  PRODUCT_BLACKLIST   1   1   80  1
                             INDEX RANGE SCAN   FO  PRODUCT_BLACKLIST_IDX2  1   1       1
                        INDEX UNIQUE SCAN   FO  TRADING_POOL_PROCESS_TYPE_PK    1   1       1
                       TABLE ACCESS BY INDEX ROWID  FO  TRADING_POOL_PROCESS_TYPE   1   1   19  1
                    TABLE ACCESS BY INDEX ROWID FO  TRADING_POOL_RULE_DEF   1   1   12  1
                     INDEX UNIQUE SCAN  FO  TRADING_POOL_RULE_DEF_PK    1   1       1
                   TABLE ACCESS BY INDEX ROWID  FO  TRADING_POOL    1   1   35  1
                    INDEX RANGE SCAN    FO  TRADING_POOL_IDX3   1   1       1
                TABLE ACCESS BY INDEX ROWID CFMLOG  INTRANET_USERS  1   1   13  1
                 INDEX UNIQUE SCAN  CFMLOG  PK_INTRANET_USERS   1   1       1
               TABLE ACCESS BY INDEX ROWID  CFMLOG  INTRANET_USERS  1   1   13  1
                INDEX UNIQUE SCAN   CFMLOG  PK_INTRANET_USERS   1   1       1
              TABLE ACCESS BY INDEX ROWID   DATA    PRODUCT_DICTIONARY  1   1   37  1
               INDEX RANGE SCAN DATA    PRODUCT_DICTIONARY_PK   1   1       1
             TABLE ACCESS BY INDEX ROWID    DATA    PRODUCT_DICTIONARY  1   1   37  1
              INDEX RANGE SCAN  DATA    PRODUCT_DICTIONARY_PK   1   1       1
            TABLE ACCESS BY INDEX ROWID DATA    PRODUCT_DICTIONARY  1   1   37  1
             INDEX RANGE SCAN   DATA    PRODUCT_DICTIONARY_PK   1   1       1
           TABLE ACCESS BY INDEX ROWID  DATA    MARKETS 1   1   38  1
            INDEX UNIQUE SCAN   DATA    MARKETS_MKT_ID_PK   1   1       1
          TABLE ACCESS BY INDEX ROWID   FO  TRADING_POOL_DEF    1   1   42  1
           INDEX UNIQUE SCAN    FO  TRADING_POOL_DEF_PK 1   1       1
         TABLE ACCESS BY INDEX ROWID    FO  STRATEGY    1   1   10  1
          INDEX UNIQUE SCAN FO  STRAT_PK    1   1       1
        TABLE ACCESS BY INDEX ROWID DATA    CURRENCY    1   1   10  1
         INDEX UNIQUE SCAN  DATA    CURRENCY_PK 1   1       1
       TABLE ACCESS BY INDEX ROWID  DATA    REFERENTIAL_DICTIONARY  1   1   34  1
        INDEX RANGE SCAN    DATA    REFERENTIAL_DICTIONARY_PK   1   1       1
      TABLE ACCESS BY INDEX ROWID   CFMLOG  INTRANET_USERS  1   1   13  1
       INDEX UNIQUE SCAN    CFMLOG  PK_INTRANET_USERS   1   1       1
   COLLECTION ITERATOR PICKLER FETCH    POOL_RULE_COMMON    POOL_INOUT_ELIG_BEST_RANK   67  1   2   59

以及使用的真实查询:

select *
from fo.v_trading_pool_inout_candidate c
where c.POOL_DATE = trunc(sysdate)
  and c.STRATEGY_ID = 2;

有没有办法(重构？提示？)提高我的观点的表现？非常感谢!

Answer 1

我的第一个想法是:你没有显示所有的连接，所以我们无法真正看到你有那个糟糕计划的所有原因。似乎有外部连接 - 也许在我们不知道的函数中......

将隐式连接更改为显式内部连接。加入 where 子句已弃用。

流水线函数本身可能不是最快的答案。我怀疑不需要查看其中的内容。