Team,
could you please help us to understand why the plan changes when json_arrayagg function got added?
demo@PDB1> create table t as select * from all_objects;
Table created.
demo@PDB1> alter table t add constraint t_pk primary key(object_id);
Table altered.
demo@PDB1> set autotrace traceonly explain
demo@PDB1> select /*+ first_rows(25) */ *
2 from t
3 where owner ='SYS'
4 or object_type ='TABLE'
5 order by object_id
6 offset 0 rows fetch next 5 rows only;
Execution Plan
----------------------------------------------------------
Plan hash value: 2292013027
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 12675 | 11 (0)| 00:00:01 |
|* 1 | VIEW | | 25 | 12675 | 11 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 25 | 3325 | 11 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T | 4338 | 563K| 11 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | T_PK | 418 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE WHEN
(0>=0) THEN 0 ELSE 0 END +5 AND "from$_subquery$_002"."rowlimit_$$_rownumber">
0)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "T"."OBJECT_ID")<=CASE WHEN
(0>=0) THEN 0 ELSE 0 END +5)
3 - filter("OWNER"='SYS' OR "OBJECT_TYPE"='TABLE')
demo@PDB1> select /*+ first_rows(25) */ json_object(
2 'DATA_OBJECT_ID' value DATA_OBJECT_ID ,
3 'OBJECT_TYPE' value OBJECT_TYPE ,
4 'CREATED' value CREATED ,
5 'LAST_DDL_TIME' value LAST_DDL_TIME ,
6 'TIMESTAMP' value TIMESTAMP ,
7 'STATUS' value STATUS ,
8 'TEMPORARY' value TEMPORARY ,
9 'GENERATED' value GENERATED ,
10 'SECONDARY' value SECONDARY ,
11 'NAMESPACE' value NAMESPACE ,
12 'EDITION_NAME' value EDITION_NAME ,
13 'SHARING' value SHARING ,
14 'EDITIONABLE' value EDITIONABLE ,
15 'ORACLE_MAINTAINED' value ORACLE_MAINTAINED ,
16 'APPLICATION' value APPLICATION ,
17 'DEFAULT_COLLATION' value DEFAULT_COLLATION ,
18 'DUPLICATED' value DUPLICATED ,
19 'SHARDED' value SHARDED ,
20 'CREATED_APPID' value CREATED_APPID ,
21 'CREATED_VSNID' value CREATED_VSNID ,
22 'MODIFIED_APPID' value MODIFIED_APPID ,
23 'MODIFIED_VSNID' value MODIFIED_VSNID ,
24 'OWNER' value OWNER ,
25 'OBJECT_NAME' value OBJECT_NAME ,
26 'SUBOBJECT_NAME' value SUBOBJECT_NAME ,
27 'OBJECT_ID' value OBJECT_ID ) as x1
28 from t
29 where owner ='SYS'
30 or object_type ='TABLE'
31 order by object_id
32 offset 0 rows fetch next 5 rows only;
Execution Plan
----------------------------------------------------------
Plan hash value: 2292013027
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25 | 50700 | 11 (0)| 00:00:01 |
|* 1 | VIEW | | 25 | 50700 | 11 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 25 | 3325 | 11 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T | 4338 | 563K| 11 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | T_PK | 418 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=0+5 AND
"from$_subquery$_002"."rowlimit_$$_rownumber">0)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_ID")<=0+5)
3 - filter("OWNER"='SYS' OR "OBJECT_TYPE"='TABLE')
demo@PDB1> select json_arrayagg( x1 returning clob ) x2
2 from (
3 select /*+ first_rows(25) */ json_object(
4 'DATA_OBJECT_ID' value DATA_OBJECT_ID ,
5 'OBJECT_TYPE' value OBJECT_TYPE ,
6 'CREATED' value CREATED ,
7 'LAST_DDL_TIME' value LAST_DDL_TIME ,
8 'TIMESTAMP' value TIMESTAMP ,
9 'STATUS' value STATUS ,
10 'TEMPORARY' value TEMPORARY ,
11 'GENERATED' value GENERATED ,
12 'SECONDARY' value SECONDARY ,
13 'NAMESPACE' value NAMESPACE ,
14 'EDITION_NAME' value EDITION_NAME ,
15 'SHARING' value SHARING ,
16 'EDITIONABLE' value EDITIONABLE ,
17 'ORACLE_MAINTAINED' value ORACLE_MAINTAINED ,
18 'APPLICATION' value APPLICATION ,
19 'DEFAULT_COLLATION' value DEFAULT_COLLATION ,
20 'DUPLICATED' value DUPLICATED ,
21 'SHARDED' value SHARDED ,
22 'CREATED_APPID' value CREATED_APPID ,
23 'CREATED_VSNID' value CREATED_VSNID ,
24 'MODIFIED_APPID' value MODIFIED_APPID ,
25 'MODIFIED_VSNID' value MODIFIED_VSNID ,
26 'OWNER' value OWNER ,
27 'OBJECT_NAME' value OBJECT_NAME ,
28 'SUBOBJECT_NAME' value SUBOBJECT_NAME ,
29 'OBJECT_ID' value OBJECT_ID ) as x1
30 from t
31 where owner ='SYS'
32 or object_type ='TABLE'
33 order by object_id
34 offset 0 rows fetch next 5 rows only
35 ) ;
Execution Plan
----------------------------------------------------------
Plan hash value: 2774900652
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2015 | 396 (1)| 00:00:01 |
| 1 | SORT GROUP BY | | 1 | 2015 | | |
|* 2 | VIEW | | 4338 | 8536K| 396 (1)| 00:00:01 |
|* 3 | WINDOW SORT PUSHED RANK | | 4338 | 563K| 396 (1)| 00:00:01 |
|* 4 | TABLE ACCESS STORAGE FULL| T | 4338 | 563K| 395 (1)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=0+5 AND
"from$_subquery$_003"."rowlimit_$$_rownumber">0)
3 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_ID")<=0+5)
4 - storage("OWNER"='SYS' OR "OBJECT_TYPE"='TABLE')
filter("OWNER"='SYS' OR "OBJECT_TYPE"='TABLE')
demo@PDB1> set autotrace off
demo@PDB1>
demo@PDB1>
demo@PDB1> select banner from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
demo@PDB1>
If in case, execution plan is needed - here it is.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
SQL_ID 00wv80w0nct8x, child number 1
-------------------------------------
select /*+ first_rows(25) */ * from t where owner ='SYS' or object_type
='TABLE' order by object_id offset 0 rows fetch next 5 rows only
Plan hash value: 2292013027
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.01 | 5 | 1 |
|* 1 | VIEW | | 1 | 25 | 5 |00:00:00.01 | 5 | 1 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 25 | 5 |00:00:00.01 | 5 | 1 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 4338 | 6 |00:00:00.01 | 5 | 1 |
| 4 | INDEX FULL SCAN | T_PK | 1 | 418 | 6 |00:00:00.01 | 3 | 1 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE WHEN (0>=0) THEN 0 ELSE 0
END +5 AND "from$_subquery$_002"."rowlimit_$$_rownumber">0))
2 - filter(ROW_NUMBER() OVER ( ORDER BY "T"."OBJECT_ID")<=CASE WHEN (0>=0) THEN 0 ELSE 0 END
+5)
3 - filter(("OWNER"='SYS' OR "OBJECT_TYPE"='TABLE'))
26 rows selected.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
SQL_ID 366afr8j6fufp, child number 1
-------------------------------------
select /*+ first_rows(25) */ json_object( 'DATA_OBJECT_ID' value
DATA_OBJECT_ID , 'OBJECT_TYPE' value OBJECT_TYPE , 'CREATED'
value CREATED , 'LAST_DDL_TIME' value LAST_DDL_TIME , 'TIMESTAMP'
value TIMESTAMP , 'STATUS' value STATUS , 'TEMPORARY' value
TEMPORARY , 'GENERATED' value GENERATED , 'SECONDARY' value
SECONDARY , 'NAMESPACE' value NAMESPACE , 'EDITION_NAME' value
EDITION_NAME , 'SHARING' value SHARING , 'EDITIONABLE' value
EDITIONABLE , 'ORACLE_MAINTAINED' value ORACLE_MAINTAINED ,
'APPLICATION' value APPLICATION , 'DEFAULT_COLLATION' value
DEFAULT_COLLATION , 'DUPLICATED' value DUPLICATED , 'SHARDED'
value SHARDED , 'CREATED_APPID' value CREATED_APPID ,
'CREATED_VSNID' value CREATED_VSNID , 'MODIFIED_APPID' value
MODIFIED_APPID , 'MODIFIED_VSNID' value MODIFIED_VSNID , 'OWNER'
value OWNER , 'OBJECT_NAME' value OBJECT_NAME , 'SUBOBJECT_NAME'
value SUBOBJECT_NAME , 'OBJECT_ID' value OBJECT_I
Plan hash value: 2292013027
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.01 | 5 |
|* 1 | VIEW | | 1 | 25 | 5 |00:00:00.01 | 5 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 25 | 5 |00:00:00.01 | 5 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T | 1 | 4338 | 6 |00:00:00.01 | 5 |
| 4 | INDEX FULL SCAN | T_PK | 1 | 418 | 6 |00:00:00.01 | 3 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber"<=0+5 AND
"from$_subquery$_002"."rowlimit_$$_rownumber">0))
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_ID")<=0+5)
3 - filter(("OWNER"='SYS' OR "OBJECT_TYPE"='TABLE'))
38 rows selected.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
SQL_ID c88mx17xkq6mf, child number 0
-------------------------------------
select json_arrayagg( x1 returning clob) as x2 from ( select /*+
first_rows(25) */ json_object( 'DATA_OBJECT_ID' value DATA_OBJECT_ID
, 'OBJECT_TYPE' value OBJECT_TYPE , 'CREATED' value CREATED ,
'LAST_DDL_TIME' value LAST_DDL_TIME , 'TIMESTAMP' value TIMESTAMP
, 'STATUS' value STATUS , 'TEMPORARY' value TEMPORARY ,
'GENERATED' value GENERATED , 'SECONDARY' value SECONDARY ,
'NAMESPACE' value NAMESPACE , 'EDITION_NAME' value EDITION_NAME
, 'SHARING' value SHARING , 'EDITIONABLE' value EDITIONABLE ,
'ORACLE_MAINTAINED' value ORACLE_MAINTAINED , 'APPLICATION' value
APPLICATION , 'DEFAULT_COLLATION' value DEFAULT_COLLATION ,
'DUPLICATED' value DUPLICATED , 'SHARDED' value SHARDED ,
'CREATED_APPID' value CREATED_APPID , 'CREATED_VSNID' value
CREATED_VSNID , 'MODIFIED_APPID' value MODIFIED_APPID ,
'MODIFIED_VSNID' value MODIFIED_VSNID , 'OWNER' value OWNER ,
'OBJECT_NAME' value OBJECT_NAME , 'SUBOBJECT_NAME
Plan hash value: 2774900652
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.03 | 1423 | | | |
| 1 | SORT GROUP BY | | 1 | 1 | 1 |00:00:00.03 | 1423 | 73728 | 73728 | |
|* 2 | VIEW | | 1 | 4338 | 5 |00:00:00.03 | 1423 | | | |
|* 3 | WINDOW SORT PUSHED RANK | | 1 | 4338 | 5 |00:00:00.03 | 1423 | 2048 | 2048 | 2048 (0)|
|* 4 | TABLE ACCESS STORAGE FULL| T | 1 | 4338 | 48945 |00:00:00.02 | 1423 | 1025K| 1025K| |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("from$_subquery$_003"."rowlimit_$$_rownumber"<=0+5 AND
"from$_subquery$_003"."rowlimit_$$_rownumber">0))
3 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_ID")<=0+5)
4 - storage(("OWNER"='SYS' OR "OBJECT_TYPE"='TABLE'))
filter(("OWNER"='SYS' OR "OBJECT_TYPE"='TABLE'))
39 rows selected.
That is not a JSON issue per se, but more an aggregation issue when combined with the ansi top-n clauses, eg
SQL> create table t as
2 select rownum r, d.* from dba_objects d;
Table created.
SQL>
SQL> alter table t add primary key ( r );
Table altered.
SQL>
SQL> set autotrace traceonly explain
SQL> select /*+ first_rows(25) */ *
2 from t
3 where owner ='SYS'
4 or object_type ='TABLE'
5 order by r
6 offset 0 rows fetch next 5 rows only;
Execution Plan
----------------------------------------------------------
Plan hash value: 3307248566
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 13520 | 15 (0)| 00:00:01 |
|* 1 | VIEW | | 26 | 13520 | 15 (0)| 00:00:01 |
|* 2 | WINDOW NOSORT STOPKEY | | 26 | 3562 | 15 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T | 3848 | 514K| 15 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | SYS_C0093514 | 560 | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CASE WHEN (0>=0) THEN 0
ELSE 0 END +5 AND "from$_subquery$_002"."rowlimit_$$_rownumber">0)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "T"."R")<=CASE WHEN (0>=0) THEN 0 ELSE 0
END +5)
3 - filter("OWNER"='SYS' OR "OBJECT_TYPE"='TABLE')
SQL>
SQL> select sum(object_id)
2 from (
3 select /*+ first_rows(25) */ *
4 from t
5 where owner ='SYS'
6 or object_type ='TABLE'
7 order by r
8 offset 0 rows fetch next 5 rows only
9 );
Execution Plan
----------------------------------------------------------
Plan hash value: 12776055
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 463 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 26 | | |
|* 2 | VIEW | | 3848 | 97K| 463 (1)| 00:00:01 |
|* 3 | WINDOW SORT PUSHED RANK| | 3848 | 97K| 463 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T | 3848 | 97K| 462 (1)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=CASE WHEN
(0>=0) THEN 0 ELSE 0 END +5 AND "from$_subquery$_003"."rowlimit_$$_rownumb
er">0)
3 - filter(ROW_NUMBER() OVER ( ORDER BY "T"."R")<=CASE WHEN (0>=0)
THEN 0 ELSE 0 END +5)
4 - filter("OWNER"='SYS' OR "OBJECT_TYPE"='TABLE')
Somewhere in the query transformation, we're losing sight of the fact that we only want 'n' rows.
You can workaround it by using the non-ansi syntax
select sum(object_id)
from
(
select * from
(
select *
from t
where owner ='SYS'
or object_type ='TABLE'
order by r
)
where rownum <= 5
);