Oracle 12c introduced Partial indexing, which works well for simple partitioned tables with literals. However, it has several significant issues:
Continue readingTag 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 readingJust 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.
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
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…
Top-N again: fetch first N rows only vs rownum
Three interesting myths about rowlimiting clause vs rownum have recently been posted on our Russian forum:
- TopN query with rownum<=N is always faster than "fetch first N rows only" (ie. row_number()over(order by ...)<=N)
- “fetch first N rows only” is always faster than rownum<=N
- “SORT ORDER BY STOPKEY” stores just N top records during sorting, while “WINDOW SORT PUSHED RANK” sorts all input and stores all records sorted in memory.
Interestingly that after Vyacheslav posted first statement as an axiom and someone posted old tests(from 2009) and few people made own tests which showed that “fetch first N rows” is about 2-3 times faster than the query with rownum, the final decision was that “fetch first” is always faster.
First of all I want to show that statement #3 is wrong and “WINDOW SORT PUSHED RANK” with row_number works similarly as “SORT ORDER BY STOPKEY”:
It’s pretty easy to show using sort trace:
Let’s create simple small table Tests1 with 1000 rows where A is in range 1-1000 (just 1 block):
create table test1(a not null, b) as select level, level from dual connect by level<=1000; alter session set max_dump_file_size=unlimited; ALTER SESSION SET EVENTS '10032 trace name context forever, level 10'; ALTER SESSION SET tracefile_identifier = 'rownum'; select * from (select * from test1 order by a) where rownum<=10; ALTER SESSION SET tracefile_identifier = 'rownumber'; select * from test1 order by a fetch first 10 rows only;
And we can see from the trace files that both queries did the same number of comparisons:
rownum:
----- Current SQL Statement for this session (sql_id=bbg66rcbt76zt) ----- select * from (select * from test1 order by a) where rownum<=10 ---- Sort Statistics ------------------------------ Input records 1000 Output records 10 Total number of comparisons performed 999 Comparisons performed by in-memory sort 999 Total amount of memory used 2048 Uses version 1 sort ---- End of Sort Statistics -----------------------
[collapse]
row_number
----- Current SQL Statement for this session (sql_id=duuy4bvaz3d0q) ----- select * from test1 order by a fetch first 10 rows only ---- Sort Statistics ------------------------------ Input records 1000 Output records 10 Total number of comparisons performed 999 Comparisons performed by in-memory sort 999 Total amount of memory used 2048 Uses version 1 sort ---- End of Sort Statistics -----------------------
[collapse]
Ie. each row (except first one) was compared with the biggest value from top 10 values and since they were bigger than top 10 value, oracle doesn’t compare it with other TopN values.
And if we change the order of rows in the table both of these queries will do the same number of comparisons again:
from 999 to 0
create table test1(a not null, b) as select 1000-level, level from dual connect by level<=1000; alter session set max_dump_file_size=unlimited; ALTER SESSION SET EVENTS '10032 trace name context forever, level 10'; ALTER SESSION SET tracefile_identifier = 'rownum'; select * from (select * from test1 order by a) where rownum<=10; ALTER SESSION SET tracefile_identifier = 'rownumber'; select * from test1 order by a fetch first 10 rows only;
[collapse]
rownum
----- Current SQL Statement for this session (sql_id=bbg66rcbt76zt) ----- select * from (select * from test1 order by a) where rownum<=10 ---- Sort Statistics ------------------------------ Input records 1000 Output records 1000 Total number of comparisons performed 4976 Comparisons performed by in-memory sort 4976 Total amount of memory used 2048 Uses version 1 sort ---- End of Sort Statistics -----------------------
[collapse]
row_number
----- Current SQL Statement for this session (sql_id=duuy4bvaz3d0q) ----- select * from test1 order by a fetch first 10 rows only ---- Sort Statistics ------------------------------ Input records 1000 Output records 1000 Total number of comparisons performed 4976 Comparisons performed by in-memory sort 4976 Total amount of memory used 2048 Uses version 1 sort ---- End of Sort Statistics -----------------------
[collapse]
We can see that both queries required much more comparisons(4976) here, that’s because each new value is smaller than the biggest value from the topN and even smaller than lowest value, so oracle should get right position for it and it requires 5 comparisons for that (it compares with 10th value, then with 6th, 3rd, 2nd and 1st values from top10). Obviously it makes less comparisons for the first 10 rows.
Now let’s talk about statements #1 and #2:
We know that rownum forces optimizer_mode to switch to “first K rows”, because of the parameter “_optimizer_rownum_pred_based_fkr”
SQL> @param_ rownum NAME VALUE DEFLT TYPE DESCRIPTION ---------------------------------- ------ ------ --------- ------------------------------------------------------ _optimizer_rownum_bind_default 10 TRUE number Default value to use for rownum bind _optimizer_rownum_pred_based_fkr TRUE TRUE boolean enable the use of first K rows due to rownum predicate _px_rownum_pd TRUE TRUE boolean turn off/on parallel rownum pushdown optimization
while fetch first/row_number doesn’t (it will be changed after the patch #22174392) and it leads to the following consequences:
1. first_rows disables serial direct reads optimization(or smartscan on Exadata), that’s why the tests with big tables showed that “fetch first” were much faster than the query with rownum.
So if we set “_serial_direct_read”=always, we get the same performance in both tests (within the margin of error).
2. In cases when index access (index full scan/index range scan) is better, CBO differently calculates the cardinality of underlying INDEX FULL(range) SCAN:
the query with rownum is optimized for first_k_rows and the cardinality of index access is equal to K rows, but CBO doesn’t reduce cardinality for “fetch first”, so the cost of index access is much higher, compare them:
rownum
SQL> explain plan for 2 select * 3 from (select * from test order by a,b) 4 where rownum<=10; -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 10 | 390 | 4 (0)| 00:00:01 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 10 | 390 | 4 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| TEST | 1000K| 12M| 4 (0)| 00:00:01 | | 4 | INDEX FULL SCAN | IX_TEST_AB | 10 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=10)
[collapse]
fetch first
SQL> explain plan for
2 select *
3 from test
4 order by a,b
5 fetch first 10 rows only;
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 780 | | 5438 (1)| 00:00:01 |
|* 1 | VIEW | | 10 | 780 | | 5438 (1)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 1000K| 12M| 22M| 5438 (1)| 00:00:01 |
| 3 | TABLE ACCESS FULL | TEST | 1000K| 12M| | 690 (1)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "TEST"."A","TEST"."B")<=10)
[collapse]
fetch first + first_rows
SQL> explain plan for
2 select/*+ first_rows */ *
3 from test
4 order by a,b
5 fetch first 10 rows only;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 780 | 27376 (1)| 00:00:02 |
|* 1 | VIEW | | 10 | 780 | 27376 (1)| 00:00:02 |
|* 2 | WINDOW NOSORT STOPKEY | | 1000K| 12M| 27376 (1)| 00:00:02 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST | 1000K| 12M| 27376 (1)| 00:00:02 |
| 4 | INDEX FULL SCAN | IX_TEST_AB | 1000K| | 2637 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "TEST"."A","TEST"."B")<=10)
[collapse]
fetch first + index
SQL> explain plan for
2 select/*+ index(test (a,b)) */ *
3 from test
4 order by a,b
5 fetch first 10 rows only;
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 780 | 27376 (1)| 00:00:02 |
|* 1 | VIEW | | 10 | 780 | 27376 (1)| 00:00:02 |
|* 2 | WINDOW NOSORT STOPKEY | | 1000K| 12M| 27376 (1)| 00:00:02 |
| 3 | TABLE ACCESS BY INDEX ROWID| TEST | 1000K| 12M| 27376 (1)| 00:00:02 |
| 4 | INDEX FULL SCAN | IX_TEST_AB | 1000K| | 2637 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "TEST"."A","TEST"."B")<=10)
[collapse]
So in this case we can add hints “first_rows” or “index”, or install the patch #22174392.
ps. I thought to post this note later, since I hadn’t time enough to add other interesting details about the different TopN variants, including “with tie”, rank(), etc, so I’ll post another note with more details later.
WINDOW NOSORT STOPKEY + RANK()
Recently I found that WINDOW NOSORT STOPKEY with RANK()OVER() works very inefficiently: http://www.freelists.org/post/oracle-l/RANKWINDOW-NOSORT-STOPKEY-stopkey-doesnt-work
The root cause of this behaviour is that Oracle optimizes WINDOW NOSORT STOPKEY with RANK the same way as with DENSE_RANK:
rnk1
create table test(n not null) as
with gen as (select level n from dual connect by level<=100)
select g2.n as n
from gen g1, gen g2
where g1.n<=10
/
create index ix_test on test(n)
/
exec dbms_stats.gather_table_stats('','TEST');
select/*+ gather_plan_statistics */ n
from (select rank()over(order by n) rnk
,n
from test)
where rnk<=3
/
select * from table(dbms_xplan.display_cursor('','','allstats last'));
drop table test purge;
[collapse]
Output
N
----------
1
1
1
1
1
1
1
1
1
1
10 rows selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID 8tbq95dpw0gw7, child number 0
-------------------------------------
select/*+ gather_plan_statistics */ n from (select rank()over(order by
n) rnk ,n from test) where rnk<=3
Plan hash value: 1892911073
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 3 | | | |
|* 1 | VIEW | | 1 | 1000 | 10 |00:00:00.01 | 3 | | | |
|* 2 | WINDOW NOSORT STOPKEY| | 1 | 1000 | 30 |00:00:00.01 | 3 | 73728 | 73728 | |
| 3 | INDEX FULL SCAN | IX_TEST | 1 | 1000 | 31 |00:00:00.01 | 3 | | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNK"<=3)
2 - filter(RANK() OVER ( ORDER BY "N")<=3)
[collapse]
As you can see, A-Rows in plan step 2 = 30 – ie, that is the number of rows where
DENSE_RANK<=3
but not
RANK<=3
The more effective way will be to stop after first 10 rows, because 11th row already has RANK more than 3!
But we can create own STOPKEY version with PL/SQL:
PLSQL STOPKEY version
create or replace type rowids_table is table of varchar2(18);
/
create or replace function get_rowids_by_rank(
n int
,max_rank int
)
return rowids_table pipelined
as
begin
for r in (
select/*+ index_rs_asc(t (n)) */ rowidtochar(rowid) chr_rowid, rank()over(order by n) rnk
from test t
where t.n > get_rowids_by_rank.n
order by n
)
loop
if r.rnk <= max_rank then
pipe row (r.chr_rowid);
else
exit;
end if;
end loop;
return;
end;
/
select/*+ leading(r t) use_nl(t) */
t.*
from table(get_rowids_by_rank(1, 3)) r
,test t
where t.rowid = chartorowid(r.column_value)
/
[collapse]
12c: New SQL PLAN OPERATIONS and HINTS
This post is just a compilation of the links to other people’s articles and short descriptions about new SQL PLAN OPERATIONS and HINTS with a couple little additions from me.
Continue reading