SQL & PL/SQL

hi experts, here's a test case on 19c (19.28):

drop table t1;

create table t1 as select * from all_objects;

create index t1x1 on t1(owner);

drop table t2;

create table t2 as select * from all_objects;

create index t2x on t2(object_id);


select t1.owner,t1.object_id,t1.object_type,t1.object_name,v2.cnt
  from t1
       LEFT join lateral (
         select t2.*
               ,count(*) over(partition by t2.owner) cnt
           from t2
          where t2.object_id=t1.object_id
       ) v2 on t1.object_id=v2.object_id
where t1.owner='SJAMES';

Plan hash value: 3054039542
 
------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |  3337 |   211K| 10164  (33)| 00:00:01 |
|   1 |  MERGE JOIN OUTER                        |                 |  3337 |   211K| 10164  (33)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED    | T1              |  3337 |   169K|   151   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                      | T1X1            |  3337 |       |    12   (0)| 00:00:01 |
|   4 |   BUFFER SORT                            |                 |     1 |    13 | 10013  (34)| 00:00:01 |
|   5 |    VIEW                                  | VW_LAT_D4FD8C38 |     1 |    13 |     3  (34)| 00:00:01 |
|*  6 |     VIEW                                 | VW_LAT_A18161FF |     1 |    26 |     3  (34)| 00:00:01 |
|   7 |      WINDOW SORT                         |                 |     1 |    16 |     3  (34)| 00:00:01 |
|   8 |       TABLE ACCESS BY INDEX ROWID BATCHED| T2              |     1 |    16 |     2   (0)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN                  | T2X             |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T1"."OWNER"='SJAMES')
   6 - filter("T1"."OBJECT_ID"="V2"."OBJECT_ID")
   9 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID")

Why are there two lateral views in the sql plan? On our production, this plan is very slow, much slower than select count(*) within a scalar subquery.

If I change it to inner join, then there's only lateral view and it's a lot faster. Unfortunately I need outer join in our use case, and the code would look better without an extra scalar subquery.

select t1.owner,t1.object_id,t1.object_type,t1.object_name,v2.cnt
  from t1
       INNER join lateral (
         select t2.*
               ,count(*) over(partition by t2.owner) cnt
           from t2
          where t2.object_id=t1.object_id
       ) v2 on t1.object_id=v2.object_id
where t1.owner='SJAMES';


Plan hash value: 3712667785
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |     1 |    78 | 10164  (33)| 00:00:01 |
|   1 |  NESTED LOOPS                          |                 |     1 |    78 | 10164  (33)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED  | T1              |  3337 |   169K|   151   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                    | T1X1            |  3337 |       |    12   (0)| 00:00:01 |
|*  4 |   VIEW                                 | VW_LAT_535DE542 |     1 |    26 |     3  (34)| 00:00:01 |
|   5 |    WINDOW SORT                         |                 |     1 |    16 |     3  (34)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2              |     1 |    16 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN                  | T2X             |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T1"."OWNER"='SJAMES')
   4 - filter("T1"."OBJECT_ID"="V2"."OBJECT_ID")
   7 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID")