Oracle Live SQL - Script: Tuning scalar subqueries

Statement 1

create table t as  
  select level c1, lpad ( 'x', 100, 'x' ) stuff from dual 
  connect by level <= 1000

Table created.

Statement 2

create table tchild as 
  select c1, c2, c3,  
         lpad ( 'x', 500, 'x' ) stuff  
  from   t  
  cross join (  
    select mod ( level, 17 ) c2, round ( dbms_random.value ( 0, 100 ) ) c3 
    from   dual 
    connect by level <= 10 
  ) 
  order by dbms_random.value

Table created.

Statement 3

create index ichild  
  on tchild ( c1 )

Index created.

Statement 4

exec dbms_stats.gather_table_stats ( null, 't' )

Statement processed.

Statement 5

exec dbms_stats.gather_table_stats ( null, 'tchild' )

Statement processed.

Statement 6

This query has two scalar subqueries to aggregate rows in TCHILD

Scalar subqueries

select /*+ gather_plan_statistics */c1,   
       ( select count ( distinct c2 )   
         from   tchild c  
         where  t.c1 = c.c1 ) dist_c2,   
       ( select avg ( c3 )   
         from   tchild c  
         where  t.c1 = c.c1 ) mean_c3  
from   t  
where  c1 <= 10


C1 DIST_C2 MEAN_C3
8 10 64.3
5 10 64.3
1 10 64.3
6 10 64.3
4 10 64.3
7 10 64.3
9 10 64.3
2 10 64.3
10 10 64.3
3 10 64.3

10 rows selected.

C1	DIST_C2	MEAN_C3
8	10	64.3
5	10	64.3
1	10	64.3
6	10	64.3
4	10	64.3
7	10	64.3
9	10	64.3
2	10	64.3
10	10	64.3
3	10	64.3

Statement 7

The optimizer was able to convert one of the queries of TCHILD to a join, the other remains a scalar subquery. As operations 1 & 2 in the plan shows, this means it ran the query 10 times (once for each row from T). The total work for the query is 239 gets - to get this you need to sum the total for the SELECT at ID 0 (125) with the total for the scalar subquery (114 at operation 1).

Scalar subqueries performance

select *   
from   dbms_xplan.display_cursor( '5kps8u21y44an', format => 'IOSTATS LAST')


PLAN_TABLE_OUTPUT
SQL_ID  5kps8u21y44an, child number 0
-------------------------------------
select /*+ gather_plan_statistics */c1,          ( select count ( 
distinct c2 )            from   tchild c           where  t.c1 = c.c1 ) 
dist_c2,          ( select avg ( c3 )            from   tchild c        
   where  t.c1 = c.c1 ) mean_c3  from   t  where  c1 <= 10
 
Plan hash value: 3954871576
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |        |      1 |        |     10 |00:00:00.01 |     125 |
|   1 |  SORT GROUP BY                        |        |     10 |      1 |     10 |00:00:00.01 |     114 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | TCHILD |     10 |     10 |    100 |00:00:00.01 |     114 |
|*  3 |    INDEX RANGE SCAN                   | ICHILD |     10 |     10 |    100 |00:00:00.01 |      15 |
|   4 |  HASH GROUP BY                        |        |      1 |     10 |     10 |00:00:00.01 |     125 |
|   5 |   NESTED LOOPS OUTER                  |        |      1 |     10 |    100 |00:00:00.01 |     125 |
|*  6 |    TABLE ACCESS FULL                  | T      |      1 |     10 |     10 |00:00:00.01 |      18 |
|   7 |    TABLE ACCESS BY INDEX ROWID BATCHED| TCHILD |     10 |      1 |    100 |00:00:00.01 |     107 |
|*  8 |     INDEX RANGE SCAN                  | ICHILD |     10 |      1 |    100 |00:00:00.01 |       8 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("C"."C1"=:B1)
   6 - filter("C1"<=10)
   8 - access("C1"="C"."C1")
       filter("C"."C1"<=10)
 
Note
-----
   - this is an adaptive plan
 

35 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 5kps8u21y44an, child number 0
-------------------------------------
select /+ gather_plan_statistics /c1, ( select count (
distinct c2 ) from tchild c where t.c1 = c.c1 )
dist_c2, ( select avg ( c3 ) from tchild c
where t.c1 = c.c1 ) mean_c3 from t where c1 <= 10

Plan hash value: 3954871576

----------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
----------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 10 \|00:00:00.01 \| 125 \|
\| 1 \| SORT GROUP BY \| \| 10 \| 1 \| 10 \|00:00:00.01 \| 114 \|
\| 2 \| TABLE ACCESS BY INDEX ROWID BATCHED \| TCHILD \| 10 \| 10 \| 100 \|00:00:00.01 \| 114 \|
\|* 3 \| INDEX RANGE SCAN \| ICHILD \| 10 \| 10 \| 100 \|00:00:00.01 \| 15 \|
\| 4 \| HASH GROUP BY \| \| 1 \| 10 \| 10 \|00:00:00.01 \| 125 \|
\| 5 \| NESTED LOOPS OUTER \| \| 1 \| 10 \| 100 \|00:00:00.01 \| 125 \|
\|* 6 \| TABLE ACCESS FULL \| T \| 1 \| 10 \| 10 \|00:00:00.01 \| 18 \|
\| 7 \| TABLE ACCESS BY INDEX ROWID BATCHED\| TCHILD \| 10 \| 1 \| 100 \|00:00:00.01 \| 107 \|
\|* 8 \| INDEX RANGE SCAN \| ICHILD \| 10 \| 1 \| 100 \|00:00:00.01 \| 8 \|
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("C"."C1"=:B1)
6 - filter("C1"<=10)
8 - access("C1"="C"."C1")
filter("C"."C1"<=10)

Note
-----
- this is an adaptive plan

Statement 8

Instead of using scalar subqueries, you can compute all the totals using CROSS APPLY. This allows you to join tables from the outer query within the subquery. You can also do this with LATERAL.

Cross apply

select /*+ gather_plan_statistics */t.c1,   
       dist_c2,  
       mean_c3  
from   t  
cross apply (  
  select avg ( c3 ) mean_c3,   
         count ( distinct c2 ) dist_c2  
  from   tchild c  
  where  t.c1 = c.c1  
) sub  
where  c1 <= 10


C1 DIST_C2 MEAN_C3
1 10 64.3
2 10 64.3
3 10 64.3
4 10 64.3
5 10 64.3
6 10 64.3
7 10 64.3
8 10 64.3
9 10 64.3
10 10 64.3

10 rows selected.

C1	DIST_C2	MEAN_C3
1	10	64.3
2	10	64.3
3	10	64.3
4	10	64.3
5	10	64.3
6	10	64.3
7	10	64.3
8	10	64.3
9	10	64.3
10	10	64.3

Statement 9

Converting the scalar subqueries to a lateral view means the query only accesses TCHILD once. This gives a good performance boost over the previous method, doing nearly half the work (142 total gets, instead of

Cross apply performance

select *   
from   dbms_xplan.display_cursor( '7bmgkc8pyhkda', format => 'IOSTATS LAST')


PLAN_TABLE_OUTPUT
SQL_ID  7bmgkc8pyhkda, child number 0
-------------------------------------
select /*+ gather_plan_statistics */t.c1,          dist_c2,         
mean_c3  from   t  cross apply (    select avg ( c3 ) mean_c3,          
  count ( distinct c2 ) dist_c2    from   tchild c    where  t.c1 = 
c.c1  ) sub  where  c1 <= 10
 
Plan hash value: 4203483510
 
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |      1 |        |     10 |00:00:00.01 |     142 |
|   1 |  NESTED LOOPS                          |                 |      1 |     10 |     10 |00:00:00.01 |     142 |
|*  2 |   TABLE ACCESS FULL                    | T               |      1 |     10 |     10 |00:00:00.01 |      28 |
|   3 |   VIEW                                 | VW_LAT_535DE542 |     10 |      1 |     10 |00:00:00.01 |     114 |
|   4 |    SORT GROUP BY                       |                 |     10 |      1 |     10 |00:00:00.01 |     114 |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| TCHILD          |     10 |     10 |    100 |00:00:00.01 |     114 |
|*  6 |      INDEX RANGE SCAN                  | ICHILD          |     10 |     10 |    100 |00:00:00.01 |      15 |
--------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("T"."C1"<=10)
   6 - access("T"."C1"="C"."C1")
 

27 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 7bmgkc8pyhkda, child number 0
-------------------------------------
select /+ gather_plan_statistics /t.c1, dist_c2,
mean_c3 from t cross apply ( select avg ( c3 ) mean_c3,
count ( distinct c2 ) dist_c2 from tchild c where t.c1 =
c.c1 ) sub where c1 <= 10

Plan hash value: 4203483510

--------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
--------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 10 \|00:00:00.01 \| 142 \|
\| 1 \| NESTED LOOPS \| \| 1 \| 10 \| 10 \|00:00:00.01 \| 142 \|
\|* 2 \| TABLE ACCESS FULL \| T \| 1 \| 10 \| 10 \|00:00:00.01 \| 28 \|
\| 3 \| VIEW \| VW_LAT_535DE542 \| 10 \| 1 \| 10 \|00:00:00.01 \| 114 \|
\| 4 \| SORT GROUP BY \| \| 10 \| 1 \| 10 \|00:00:00.01 \| 114 \|
\| 5 \| TABLE ACCESS BY INDEX ROWID BATCHED\| TCHILD \| 10 \| 10 \| 100 \|00:00:00.01 \| 114 \|
\|* 6 \| INDEX RANGE SCAN \| ICHILD \| 10 \| 10 \| 100 \|00:00:00.01 \| 15 \|
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("T"."C1"<=10)
6 - access("T"."C1"="C"."C1")

Statement 10

Another way to write this query is to join the tables and group the result set.

Join

select /*+ gather_plan_statistics  */c1,   
       count ( distinct c2 ) dist_c2,  
       avg ( c3 ) mean_c3  
from   t  
join   tchild  
using  ( c1 )  
where  c1 <= 10  
group  by c1


C1 DIST_C2 MEAN_C3
6 10 64.3
1 10 64.3
7 10 64.3
2 10 64.3
8 10 64.3
4 10 64.3
10 10 64.3
5 10 64.3
3 10 64.3
9 10 64.3

10 rows selected.

C1	DIST_C2	MEAN_C3
6	10	64.3
1	10	64.3
7	10	64.3
2	10	64.3
8	10	64.3
4	10	64.3
10	10	64.3
5	10	64.3
3	10	64.3
9	10	64.3

Statement 11

This does the least work (125 gets) of the three queries, so is the best method overall. When tuning a large select with many scalar subqueries it can be fiddly to convert these to a join. In these cases CROSS APPLY/LATERAL can be easier to slip into the main query, while still giving considerable performance gains.

Join performance

select *   
from   dbms_xplan.display_cursor( '1grsg6z21dwhs', format => 'IOSTATS LAST')


PLAN_TABLE_OUTPUT
SQL_ID  1grsg6z21dwhs, child number 0
-------------------------------------
select /*+ gather_plan_statistics  */c1,          count ( distinct c2 ) 
dist_c2,         avg ( c3 ) mean_c3  from   t  join   tchild  using  ( 
c1 )  where  c1 <= 10  group  by c1
 
Plan hash value: 2854331822
 
------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |      1 |        |     10 |00:00:00.01 |     125 |
|   1 |  HASH GROUP BY                  |          |      1 |     10 |     10 |00:00:00.01 |     125 |
|   2 |   VIEW                          | VW_DAG_0 |      1 |     10 |    100 |00:00:00.01 |     125 |
|   3 |    HASH GROUP BY                |          |      1 |     10 |    100 |00:00:00.01 |     125 |
|   4 |     NESTED LOOPS                |          |      1 |     10 |    100 |00:00:00.01 |     125 |
|   5 |      NESTED LOOPS               |          |      1 |     10 |    100 |00:00:00.01 |      26 |
|*  6 |       TABLE ACCESS FULL         | T        |      1 |     10 |     10 |00:00:00.01 |      18 |
|*  7 |       INDEX RANGE SCAN          | ICHILD   |     10 |      1 |    100 |00:00:00.01 |       8 |
|   8 |      TABLE ACCESS BY INDEX ROWID| TCHILD   |    100 |      1 |    100 |00:00:00.01 |      99 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - filter("T"."C1"<=10)
   7 - access("T"."C1"="TCHILD"."C1")
       filter("TCHILD"."C1"<=10)
 

29 rows selected.

Tuning scalar subqueries

Additional Information