Skip to Main Content
  • Questions
  • Plan changes when json_arrayagg function got added

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Rajeshwaran.

Asked: November 28, 2019 - 10:17 am UTC

Answered by: Connor McDonald - Last updated: November 29, 2019 - 4:45 am UTC

Category: Database Development - Version: 18.0

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: 12c Statistics on load-special cases

You Asked

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.

and we said...

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
);


and you rated our response

  (1 rating)

Reviews

on row limiting clause

November 29, 2019 - 2:51 am UTC

Reviewer: Rajeshwaran Jeyabal

Thanks for the details.

By any chance do you know if any bug related to this that has a patch available?

we are on 18.0 - is any patch available on PSU/RUR available related to this?
Connor McDonald

Followup  

November 29, 2019 - 4:45 am UTC

I've passed it onto the optimizer PM, but I don't think its a "bug" as such. The optimizer can never be perfect in an infinite number of cases

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.