Skip to content

Instantly share code, notes, and snippets.

@xtender

xtender/lateral.sql

Last active May 16, 2019 17:55
Download ZIP
table() /kokbf$- functions limitations and workarounds
Raw
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$))
*/
Raw
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")
*/
Raw
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)
*/
Raw
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
/
@xtender
Copy link
Author

xtender commented May 16, 2019

"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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment