Oracle 12c introduced Partial indexing, which works well for simple partitioned tables with literals. However, it has several significant issues:
Continue readingCategory Archives: CBO
Slow index access “COL=:N” where :N is NULL
All Oracle specialists know that a predicate X=NULL can never be true and we should use “X is NULL” in such cases. The Oracle optimizer knows about that, so if we create a table like this:
Continue readingSmart Scan and Recursive queries
Since Christmas I have been asked to investigate two different “failures to use Smart Scan”. It turns out they both fell into the same little known restriction on the use of Direct Read. Smart Scan critically depends on Direct Read in order to read the synthetic output blocks into private buffers in PGA so with Direct Read disabled Smart Scan is also disabled. In these two cases the restriction is on using Direct Read on Serial Recursive queries.
Case 1: Materialized View Refresh
A customer asked me to investigate why his MView refresh was running slowly and was failing to use Smart Scan. He had used 'trace[NSMTIO] disk=highest' which showed the cause as:
Direct Read for serial qry: disabled(::recursive_call::kctfsage:::)
Just short note for myself: OJPPD limitations
As of Oracle 19c OJPPD doesn’t support connect-by and TABLE():
OJPPD: OJPPD bypassed: query block contains START WITH/CONNECT BY.
OJPPD: OJPPD bypassed: View contains TABLE expression.
Laterals: is (+) documented for laterals?
I know this syntax for a long time, since when lateral() was not documented yet, but recently I found a bug: the following query successfully returns 1 row:
with a as (select level a from dual connect by level<10)
,b as (select 0 b from dual)
,c as (select 0 c from dual)
select
*
from a,
lateral(select * from b where a.a=b.b)(+) bb
--left outer join c on c.c=bb.b
where a=1;
A B
---------- ----------
1
But doesn’t if we uncomment “left join”:
with a as (select level a from dual connect by level<10)
,b as (select 0 b from dual)
,c as (select 0 c from dual)
select
*
from a,
lateral(select * from b where a.a=b.b)(+) bb
left outer join c on c.c=bb.b
where a=1;
no rows selected
And outer apply works fine:
with a as (select level a from dual connect by level<10)
,b as (select 0 b from dual)
,c as (select 0 c from dual)
select
*
from a
outer apply (select * from b where a.a=b.b) bb
left outer join c on c.c=bb.b
where a=1;
A B C
---------- ---------- ----------
1
Workarounds for JPPD with view and table(kokbf$), xmltable or json_table functions
You may know that table() (kokbf$ collection functions), xmltable and json_table functions block Join-Predicate PushDown(JPPD).
Simple example:
DDL
create table xtest(a, b, c) as
select mod(level,1000),level,rpad('x',100,'x')
from dual
connect by level<=1e4
/
create index itest on xtest(a)
/
create or replace view vtest as
select a,count(b) cnt
from xtest
group by a
/
call dbms_stats.gather_table_stats(user,'xtest');
/
[collapse]
select distinct v.*
from table(sys.odcinumberlist(1,2,3)) c, vtest v
where v.a = c.column_value;
Plan hash value: 699667151
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 80 (4)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 19 | 80 (4)| 00:00:01 |
|* 2 | HASH JOIN | | 1 | 19 | 79 (3)| 00:00:01 |
| 3 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 1 | 2 | 29 (0)| 00:00:01 |
| 4 | VIEW | VTEST | 1000 | 17000 | 49 (3)| 00:00:01 |
| 5 | HASH GROUP BY | | 1000 | 8000 | 49 (3)| 00:00:01 |
| 6 | TABLE ACCESS FULL | XTEST | 10000 | 80000 | 48 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("V"."A"=VALUE(KOKBF$))
same for json_table
select/*+ cardinality(c 1) use_nl(v) push_pred(v) */ *
from json_table('{"a":[1,2,3]}', '$.a[*]' COLUMNS (a int PATH '$')) c
,vtest v
where c.a = v.a;
Plan hash value: 664523328
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 78 (2)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 28 | 78 (2)| 00:00:01 |
| 2 | JSONTABLE EVALUATION | | | | | |
|* 3 | VIEW | VTEST | 1 | 26 | 49 (3)| 00:00:01 |
| 4 | SORT GROUP BY | | 1000 | 8000 | 49 (3)| 00:00:01 |
| 5 | TABLE ACCESS FULL | XTEST | 10000 | 80000 | 48 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("V"."A"="P"."A")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$F534CA49 / V@SEL$1
U - push_pred(v)
[collapse]
same for xmltable
select/*+ leading(c v) cardinality(c 1) use_nl(v) push_pred(v) */ v.*
from xmltable('(1,3)' columns a int path '.') c,vtest v
where c.a = v.a(+);
Plan hash value: 564839666
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 78 (2)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 28 | 78 (2)| 00:00:01 |
| 2 | COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE | 1 | 2 | 29 (0)| 00:00:01 |
|* 3 | VIEW | VTEST | 1 | 26 | 49 (3)| 00:00:01 |
| 4 | SORT GROUP BY | | 1000 | 8000 | 49 (3)| 00:00:01 |
| 5 | TABLE ACCESS FULL | XTEST | 10000 | 80000 | 48 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("V"."A"(+)=CAST(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(VALUE(KOKBF$),0,0,54525952,0),
50,1,2)) AS int ))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$6722A2F6 / V@SEL$1
U - push_pred(v)
[collapse]
And compare with this:
create global temporary table temp_collection(a number);
insert into temp_collection select * from table(sys.odcinumberlist(1,2,3));
select/*+ cardinality(c 1) no_merge(v) */
distinct v.*
from temp_collection c, vtest v
where v.a = c.a;
Plan hash value: 3561835411
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 41 (3)| 00:00:01 |
| 1 | HASH UNIQUE | | 1 | 26 | 41 (3)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 26 | 40 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TEMP_COLLECTION | 1 | 13 | 29 (0)| 00:00:01 |
| 4 | VIEW PUSHED PREDICATE | VTEST | 1 | 13 | 11 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | SORT AGGREGATE | | 1 | 8 | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED| XTEST | 10 | 80 | 11 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | ITEST | 10 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(COUNT(*)>0)
8 - access("A"="C"."A")
You can see that JPPD works fine in case of global temporary tables and, obviously, the first workaround is to avoid such functions with complex views.
But in such simple queries you have 2 other simple options:
1. you can avoid JPPD and get optimal plans using CVM(complex view merge) by just simply rewriting the query using IN or EXISTS:
select *
from vtest v
where v.a in (select/*+ cardinality(c 1) */ c.* from table(sys.odcinumberlist(1,2,3)) c);
Plan hash value: 1474391442
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 100 | 42 (5)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 10 | 100 | 42 (5)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 100 | 41 (3)| 00:00:01 |
| 3 | NESTED LOOPS | | 10 | 100 | 41 (3)| 00:00:01 |
| 4 | SORT UNIQUE | | 1 | 2 | 29 (0)| 00:00:01 |
| 5 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 1 | 2 | 29 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | ITEST | 10 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | XTEST | 10 | 80 | 11 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"=VALUE(KOKBF$))
the same with json_table and xmltable
select *
from vtest t
where t.a in (select/*+ cardinality(v 1) */ v.a from json_table('{"a":[1,2,3]}', '$.a[*]' COLUMNS (a int PATH '$')) v);
Plan hash value: 2910004067
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 100 | 42 (5)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 10 | 100 | 42 (5)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 100 | 41 (3)| 00:00:01 |
| 3 | NESTED LOOPS | | 10 | 100 | 41 (3)| 00:00:01 |
| 4 | SORT UNIQUE | | | | | |
| 5 | JSONTABLE EVALUATION | | | | | |
|* 6 | INDEX RANGE SCAN | ITEST | 10 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| XTEST | 10 | 80 | 11 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"="P"."A")
select v.*
from vtest v
where exists(select/*+ cardinality(c 1) */ 1 from xmltable('(1,3)' columns a int path '.') c where c.a = v.a);
Plan hash value: 1646016183
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 100 | 42 (5)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT | | 10 | 100 | 42 (5)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 100 | 41 (3)| 00:00:01 |
| 3 | NESTED LOOPS | | 10 | 100 | 41 (3)| 00:00:01 |
| 4 | SORT UNIQUE | | 1 | 2 | 29 (0)| 00:00:01 |
| 5 | COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE | 1 | 2 | 29 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | ITEST | 10 | | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | XTEST | 10 | 80 | 11 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"=CAST(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(VALUE(KOKBF$),0,0,54525952,0),50,1,2)) AS int ))
[collapse]
2. Avoid JPPD using lateral():
select/*+ cardinality(c 1) no_merge(lat) */
distinct lat.*
from table(sys.odcinumberlist(1,2,3)) c,
lateral(select * from vtest v where v.a = c.column_value) lat;
Plan hash value: 18036714
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 190 | 41 (3)| 00:00:01 |
| 1 | HASH UNIQUE | | 10 | 190 | 41 (3)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 190 | 40 (0)| 00:00:01 |
| 3 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 1 | 2 | 29 (0)| 00:00:01 |
| 4 | VIEW | VW_LAT_4DB60E85 | 10 | 170 | 11 (0)| 00:00:01 |
| 5 | SORT GROUP BY | | 10 | 80 | 11 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| XTEST | 10 | 80 | 11 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | ITEST | 10 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - access("A"=VALUE(KOKBF$))
Let’s see a bit more complex query:
Test tables 2
create table xtest1(id primary key, a) as
select level,level from dual connect by level<=1000;
create table xtest2(a, b, c) as
select mod(level,1000),level,rpad('x',100,'x')
from dual
connect by level<=1e4
/
create index itest2 on xtest2(a)
/
create or replace view vtest2 as
select a,count(b) cnt
from xtest2
group by a
/
[collapse]
select v.*
from xtest1 t1,
vtest2 v
where t1.id in (select/*+ cardinality(c 1) */ * from table(sys.odcinumberlist(1,2,3)) c)
and v.a = t1.a;
Plan hash value: 4293766070
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 36 | 80 (3)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 36 | 80 (3)| 00:00:01 |
| 2 | JOIN FILTER CREATE | :BF0000 | 1 | 10 | 31 (4)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 10 | 31 (4)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 10 | 31 (4)| 00:00:01 |
| 5 | SORT UNIQUE | | 1 | 2 | 29 (0)| 00:00:01 |
| 6 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 1 | 2 | 29 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_C0026365 | 1 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | XTEST1 | 1 | 8 | 1 (0)| 00:00:01 |
| 9 | VIEW | VTEST2 | 1000 | 26000 | 49 (3)| 00:00:01 |
| 10 | HASH GROUP BY | | 1000 | 8000 | 49 (3)| 00:00:01 |
| 11 | JOIN FILTER USE | :BF0000 | 10000 | 80000 | 48 (0)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | XTEST2 | 10000 | 80000 | 48 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("V"."A"="T1"."A")
7 - access("T1"."ID"=VALUE(KOKBF$))
12 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"A"))
As you see, CVM can’t help in this case, but we can use lateral():
select/*+ no_merge(lat) */ lat.*
from xtest1 t1,
lateral(select * from vtest2 v where v.a = t1.a) lat
where t1.id in (select/*+ cardinality(c 1) */ * from table(sys.odcinumberlist(1,2,3)) c);
Plan hash value: 1798023704
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 360 | 42 (3)| 00:00:01 |
| 1 | NESTED LOOPS | | 10 | 360 | 42 (3)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 10 | 31 (4)| 00:00:01 |
| 3 | SORT UNIQUE | | 1 | 2 | 29 (0)| 00:00:01 |
| 4 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 1 | 2 | 29 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | XTEST1 | 1 | 8 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_C0026365 | 1 | | 0 (0)| 00:00:01 |
| 7 | VIEW | VW_LAT_A18161FF | 10 | 260 | 11 (0)| 00:00:01 |
| 8 | SORT GROUP BY | | 10 | 80 | 11 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED | XTEST2 | 10 | 80 | 11 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | ITEST2 | 10 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("T1"."ID"=VALUE(KOKBF$))
10 - access("A"="T1"."A")
There is also another workaround with non-documented “precompute_subquery” hint:
select v.*
from xtest1 t1,
vtest2 v
where t1.id in (select/*+ precompute_subquery */ * from table(sys.odcinumberlist(1,2,3)) c)
and v.a = t1.a;
Plan hash value: 1964829099
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30 | 480 | 37 (3)| 00:00:01 |
| 1 | HASH GROUP BY | | 30 | 480 | 37 (3)| 00:00:01 |
| 2 | NESTED LOOPS | | 30 | 480 | 36 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 30 | 480 | 36 (0)| 00:00:01 |
| 4 | INLIST ITERATOR | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| XTEST1 | 3 | 24 | 3 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_C0026365 | 3 | | 2 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | ITEST2 | 10 | | 1 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | XTEST2 | 10 | 80 | 11 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("T1"."ID"=1 OR "T1"."ID"=2 OR "T1"."ID"=3)
7 - access("A"="T1"."A")
It can help even in most difficult cases, for example if you can’t rewrite query (in this case you can create sql patch or sql profile with “precompute_subquery”), but I wouldn’t suggest it since “precompute_subquery” is non-documented, it can be used only with simple collections and has limitation in 1000 values.
I’d suggest to use the workaround with lateral, since it’s most reliable and very simple.
v$sql_hint.target_level
Today I wanted to give a link to the description of v$sql_hint.target_level to show that no_parallel can be specified for statement or object, and though it’s pretty obvious, but surprisingly I haven’t found any articles or posts about it, so this short post describes it.
v$sql_hint.target_level is a bitset, where
1st bit set to 1 means that the hint can be specified on statement level,
2nd – on query block level,
3rd – on object level,
4th – on join level(for multiple objects).
Short example:
select name,sql_feature
,class,inverse
,version,version_outline
,target_level
,decode(bitand(target_level,1),0,'no','yes') Statement_level
,decode(bitand(target_level,2),0,'no','yes') Query_block_level
,decode(bitand(target_level,4),0,'no','yes') Object_level
,decode(bitand(target_level,8),0,'no','yes') Join_level
from v$sql_hint h;
with hints as (
select name,sql_feature
,class,inverse
,version,version_outline
,target_level
,decode(bitand(target_level,1),0,'no','yes') Statement_level
,decode(bitand(target_level,2),0,'no','yes') Query_block_level
,decode(bitand(target_level,4),0,'no','yes') Object_level
,decode(bitand(target_level,8),0,'no','yes') Join_level
from v$sql_hint h
)
select *
from hints
where statement_level='yes'
and to_number(regexp_substr(version,'^\d+')) >= 18
order by version;
Result:
NAME SQL_FEATURE CLASS VERSION TARGET_LEVEL STATEMENT_LEVEL QUERY_BLOCK_LEVEL OBJECT_LEVEL JOIN_LEVEL ----------------- --------------- -------------------- -------- ------------ --------------- ----------------- ------------ ---------- PDB_LOCAL_ONLY QKSFM_DML PDB_LOCAL_ONLY 18.1.0 1 yes no no no SUPPRESS_LOAD QKSFM_DDL SUPPRESS_LOAD 18.1.0 1 yes no no no SYSTEM_STATS QKSFM_ALL SYSTEM_STATS 18.1.0 1 yes no no no MEMOPTIMIZE_WRITE QKSFM_EXECUTION MEMOPTIMIZE_WRITE 18.1.0 1 yes no no no SKIP_PROXY QKSFM_ALL SKIP_PROXY 18.1.0 1 yes no no no CURRENT_INSTANCE QKSFM_ALL CURRENT_INSTANCE 18.1.0 1 yes no no no JSON_LENGTH QKSFM_EXECUTION JSON_LENGTH 19.1.0 1 yes no no no QUARANTINE QKSFM_EXECUTION QUARANTINE 19.1.0 1 yes no no no
Correct syntax for the table_stats hint
A friend contacted me to ask why they were having problems using the table_stats hint to influence optimizer decision making and also to influence the decision to use direct read or buffer cache scan so this is just a quick blog post to clarify the syntax as it is not well documented.
table_stats(<table_name> <method> {<keyword>=<value>} )
Method is one of: DEFAULT, SET, SCALE, SAMPLE
Keyword is one of: BLOCKS, ROWS, ROW_LENGTH
Another bug with lateral
Compare the results of the following query with the clause “fetch first 2 rows only”
with
t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(a,b) as (select * from table(ku$_objnumpairlist(
sys.ku$_objnumpair(1,1),
sys.ku$_objnumpair(1,2),
sys.ku$_objnumpair(1,3),
sys.ku$_objnumpair(3,1),
sys.ku$_objnumpair(3,2),
sys.ku$_objnumpair(3,3)
)))
,t(id) as (select * from table(odcinumberlist(1,2,3,4,5,6,7)))
select
*
from t,
lateral(select t1.a,t2.b
from t1,t2
where t1.a = t2.a
and t1.a = t.id
order by t2.b
fetch first 2 rows only
)(+)
order by id;
ID A B
---------- ---------- ----------
1 1 1
1 3 1
2 1 1
2 3 1
3 1 1
3 3 1
4 1 1
4 3 1
5 1 1
5 3 1
6 1 1
6 3 1
7 1 1
7 3 1
14 rows selected.
with this one (i’ve just commented out the line with “fetch-first-rows-only”:
with
t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(a,b) as (select * from table(ku$_objnumpairlist(
sys.ku$_objnumpair(1,1),
sys.ku$_objnumpair(1,2),
sys.ku$_objnumpair(1,3),
sys.ku$_objnumpair(3,1),
sys.ku$_objnumpair(3,2),
sys.ku$_objnumpair(3,3)
)))
,t(id) as (select * from table(odcinumberlist(1,2,3,4,5,6,7)))
select
*
from t,
lateral(select t1.a,t2.b
from t1,t2
where t1.a = t2.a
and t1.a = t.id
order by t2.b
-- fetch first 2 rows only
)(+)
order by id;
ID A B
---------- ---------- ----------
1 1 2
1 1 3
1 1 1
2
3 3 2
3 3 1
3 3 3
4
5
6
7
11 rows selected.
Obviously, the first query should return less rows than second one, but we can see that it returned more rows and join predicate “and t1.a = t.id” was ignored, because A and B are not empty and “A” is not equal to t.ID.
Lateral view decorrelation(VW_DCL) causes wrong results with rownum
Everyone knows that rownum in inline views blocks many query transformations, for example pushing/pulling predicates, scalar subquery unnesting, etc, and many people use it for such purposes as a workaround to avoid unwanted transformations(or even CBO bugs).
Obviously, the main reason of that is different calculation of rownum:
If we pull the predicate “column_value = 3” from the following query to higher level
select *
from (select * from table(odcinumberlist(1,1,1,2,2,2,3,3,3)) order by 1)
where rownum <= 2
and column_value = 3;
COLUMN_VALUE
------------
3
3
we will get different results:
select *
from (select *
from (select * from table(odcinumberlist(1,1,1,2,2,2,3,3,3)) order by 1)
where rownum <= 2
)
where column_value = 3;
no rows selected
Doc ID 62340.1
[collapse]
But we recently encountered a bug with it: lateral view with ROWNUM returns wrong results in case of lateral view decorrelation.
Compare results of this query with and without no_decorrelation hint:
with
t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(b) as (select * from table(odcinumberlist(1,1,3,3)))
,t(id) as (select * from table(odcinumberlist(1,2,3)))
select
*
from t,
lateral(select/*+ no_decorrelate */ rownum rn
from t1,t2
where t1.a=t2.b and t1.a = t.id
)(+)
order by 1,2;
ID RN
---------- ----------
1 1
1 2
2
3 1
3 2
|
with
t1(a) as (select * from table(odcinumberlist(1,3)))
,t2(b) as (select * from table(odcinumberlist(1,1,3,3)))
,t(id) as (select * from table(odcinumberlist(1,2,3)))
select
*
from t,
lateral(select rownum rn
from t1,t2
where t1.a=t2.b and t1.a = t.id
)(+)
order by 1,2;
ID RN
---------- ----------
1 1
1 2
2
3 3
3 4
|
Of course, we can draw conclusions even from these results: we can see that in case of decorrelation(query with hint) rownum was calculated before the join. But to be sure we can check optimizer’s trace 10053:
Final query after transformations:
******* UNPARSED QUERY IS *******
SELECT VALUE(KOKBF$2) "ID", "VW_DCL_76980902"."RN" "RN"
FROM TABLE("ODCINUMBERLIST"(1, 2, 3)) "KOKBF$2",
(SELECT ROWNUM "RN_0", VALUE(KOKBF$0) "ITEM_3"
FROM TABLE("ODCINUMBERLIST"(1, 3)) "KOKBF$0",
TABLE("ODCINUMBERLIST"(1, 1, 3, 3)) "KOKBF$1"
WHERE VALUE(KOKBF$0) = VALUE(KOKBF$1)
) "VW_DCL_76980902"
WHERE "VW_DCL_76980902"."ITEM_3"(+) = VALUE(KOKBF$2)
ORDER BY VALUE(KOKBF$2), "VW_DCL_76980902"."RN"
*************************
[collapse]
I’ll modify it a bit just to make it more readable:
we can see that
select
*
from t,
lateral(select rownum rn
from t1,t2
where t1.a=t2.b and t1.a = t.id)(+)
order by 1,2;
was transformed to
select
t.id, dcl.rn
from t,
(select rownum rn
from t1,t2
where t1.a=t2.b) dcl
where dcl.a(+) = t.id
order by 1,2;
And it confirms that rownum was calculated on the different dataset (t1-t2 join) without join filter by table t.
I created SR with Severity 1 (SR #3-19117219271) more than a month ago, but unfortunately Oracle development doesn’t want to fix this bug and moreover they say that is not a bug. So I think this is a dangerous precedent and probably soon we will not be able to be sure in the calculation of rownum and old fixes…