table() /kokbf$- functions limitations and workarounds

lateral.sql

select t.*
from   table(sys.ku$_objnumset(1)) v
       ,lateral(select/*+ no_merge index(t) */ * from vtest t where t.a=v.column_value) t
/
/*
Plan hash value: 345808962

------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |       |  8168K|   218M|   204K  (1)| 00:40:53 |
|   1 |  NESTED LOOPS                          |       |  8168K|   218M|   204K  (1)| 00:40:53 |
|   2 |   COLLECTION ITERATOR CONSTRUCTOR FETCH|       |  8168 | 16336 |    29   (0)| 00:00:01 |
|   3 |   VIEW                                 |       |  1000 | 26000 |    25   (0)| 00:00:01 |
|   4 |    SORT GROUP BY                       |       |  1000 | 26000 |    25   (0)| 00:00:01 |
|   5 |     TABLE ACCESS BY INDEX ROWID        | XTEST |  1000 | 26000 |    25   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN                  | ITEST |  1000 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   6 - access("A"=VALUE(KOKBF$))
*/

materialize.sql

with v(a) as (select/*+ materialize */ * from table(sys.odcinumberlist(1)))
select/*+ leading(v t) use_nl(t) index(t) */ t.*
from   vtest t
where  exists(select * from v where t.a=v.a);
/*
                                                                       PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3760184782

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                             |  1000 | 39000 |    58   (4)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION              |                             |       |       |            |          |
|   2 |   LOAD AS SELECT                        | SYS_TEMP_0FD9D7150_4E96F434 |       |       |            |          |
|   3 |    COLLECTION ITERATOR CONSTRUCTOR FETCH|                             |  8168 | 16336 |    29   (0)| 00:00:01 |
|   4 |   HASH GROUP BY                         |                             |  1000 | 39000 |    29   (7)| 00:00:01 |
|   5 |    NESTED LOOPS                         |                             |       |       |            |          |
|   6 |     NESTED LOOPS                        |                             |  1000 | 39000 |    28   (4)| 00:00:01 |
|   7 |      VIEW                               |                             |  8168 |   103K|     3   (0)| 00:00:01 |
|   8 |       HASH UNIQUE                       |                             |     1 | 16336 |            |          |
|   9 |        TABLE ACCESS FULL                | SYS_TEMP_0FD9D7150_4E96F434 |  8168 | 16336 |     3   (0)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN                   | ITEST                       |  1000 |       |     2   (0)| 00:00:01 |
|  11 |     TABLE ACCESS BY INDEX ROWID         | XTEST                       |  1000 | 26000 |    24   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$F5BB74E1" "KOKBF$"@"SEL$2")
      USE_HASH_AGGREGATION(@"SEL$AE95C8DF")
      FULL(@"SEL$AE95C8DF" "T1"@"SEL$AE95C8DF")
      USE_HASH_AGGREGATION(@"SEL$FB0FE72C")
      NLJ_BATCHING(@"SEL$FB0FE72C" "XTEST"@"SEL$4")
      USE_NL(@"SEL$FB0FE72C" "XTEST"@"SEL$4")
      LEADING(@"SEL$FB0FE72C" "V"@"SEL$5" "XTEST"@"SEL$4")
      INDEX(@"SEL$FB0FE72C" "XTEST"@"SEL$4" ("XTEST"."A"))
      NO_ACCESS(@"SEL$FB0FE72C" "V"@"SEL$5")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$5")
      MERGE(@"SEL$4")
      OUTLINE(@"SEL$07BDC5B4")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$F5BB74E1")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$1")
      UNNEST(@"SEL$5")
      OUTLINE_LEAF(@"SEL$FB0FE72C")
      OUTLINE_LEAF(@"SEL$AE95C8DF")
      MERGE(@"SEL$2")
      OUTLINE_LEAF(@"SEL$F5BB74E1")
      NO_COST_XML_QUERY_REWRITE
      XMLINDEX_REWRITE_IN_SELECT
      XMLINDEX_REWRITE
      XML_DML_RWT_STMT
      FORCE_XML_QUERY_REWRITE
      ALL_ROWS
      DB_VERSION('11.2.0.2')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

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

  10 - access("A"="V"."A")
*/

precompute_subquery.sql

select/*+ index(t) */ t.*
from   vtest t 
where a in (select/*+ precompute_subquery */ * from table(sys.odcinumberlist(1)) v);
/*
Plan hash value: 2009697289

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |  1000 | 26000 |    25   (0)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT        |       |  1000 | 26000 |    25   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| XTEST |  1000 | 26000 |    25   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | ITEST |  1000 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   3 - access("A"=1)
                                                  
*/

test-tables.sql

create table xtest(a, b, c) as
select mod(level,10),level,level
from dual
connect by level<=1e4
/
create index itest on xtest(a)
/
create view vtest as
select a,count(b) cnt
from xtest
group by a
/

"Materialize" solution for Oracle 18.5:

with z(a) as (select/*+ materialize */ * from table(sys.odcinumberlist(1)))
    ,v(a) as (select/*+ materialize */ * from z where 1=1)
select/*+ leading(v t) use_nl(t) index(t) */ t.*
from   vtest t
where  exists(select * from v where t.a=v.a)

Plan:

Plan hash value: 434338359

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |     1 |    20 |    58   (4)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION               |                            |       |       |            |          |
|   2 |   LOAD AS SELECT (CURSOR DURATION MEMORY)| SYS_TEMP_0FD9D67BE_327432A |       |       |            |          |
|   3 |    COLLECTION ITERATOR CONSTRUCTOR FETCH |                            |  8168 | 16336 |    29   (0)| 00:00:01 |
|   4 |   HASH GROUP BY                          |                            |     1 |    20 |    29   (7)| 00:00:01 |
|   5 |    NESTED LOOPS                          |                            |  1000 | 20000 |    28   (4)| 00:00:01 |
|   6 |     NESTED LOOPS                         |                            |  1000 | 20000 |    28   (4)| 00:00:01 |
|   7 |      VIEW                                |                            |  8168 |   103K|     3   (0)| 00:00:01 |
|   8 |       HASH UNIQUE                        |                            |     1 | 16336 |            |          |
|   9 |        TABLE ACCESS FULL                 | SYS_TEMP_0FD9D67BE_327432A |  8168 | 16336 |     3   (0)| 00:00:01 |
|* 10 |      INDEX RANGE SCAN                    | ITEST                      |  1000 |       |     2   (0)| 00:00:01 |
|  11 |     TABLE ACCESS BY INDEX ROWID          | XTEST                      |  1000 |  7000 |    24   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

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

  10 - access("A"="V"."A")

Unfortunately we need to wrap table() function twice on 18.5. I haven't tried it on 19.3 yet, will do later.