title: Oracle query with pipelined - performance issue
body: <p>I'd like to create a view based on the following query:</p>
  
  <pre><code>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 ...
  </code></pre>
  
  <p>And the view should be used this way:</p>
  
  <pre><code>select *
  from MY_VIEW V
  where V.MY_DATE = trunc(sysdate)
  </code></pre>
  
  <p>As you can see, my request is a bit tricky since <code>PIPELINED_FUNCTION</code> is called with fields from <code>A</code> ! And my request is really slow, I suspect because Oracle tries to compute <code>P</code> with all possible dates. Of course If I fix the date (for instance <code>MY_PACKAGE.PIPELINED_FUNCTION(trunc(sysdate))</code>), the answer is immediate.</p>
  
  <p>Here is the execution plan (<code>MY_TABLE</code> is actually a view: <code>FO.V_TRADING_POOL_INOUT_PRD</code>, and the pipelined name is actually <code>POOL_INOUT_ELIG_BEST_RANK</code> at the bottom):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And the real query used:</p>
  
  <pre><code>select *
  from fo.v_trading_pool_inout_candidate c
  where c.POOL_DATE = trunc(sysdate)
    and c.STRATEGY_ID = 2;
  </code></pre>
  
  <p>Is there a way (refactoring ? hints ?) to improve the performance of my view ?
  Thanks a lot !</p>