Oracle Live SQL - Script: Speeding up scalar subqueries

Statement 1

exec dbms_random.seed ( 0 )

Statement processed.

Statement 2

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

Table created.

Statement 3

create table tchild1 pctfree 0 enable row movement as  
  select c1, c2, round ( dbms_random.value ( 0, 100 ) ) c3,  
         trunc ( sysdate, 'y' ) + ( rownum / 240 ) c4, 
         lpad ( 'x', 10, 'x' ) stuff   
  from   t   
  cross join (   
    select mod ( level, 17 ) c2 
    from   dual  
    connect by level <= 100 
  )  
  order by dbms_random.value

Table created.

Statement 4

create table tchild2 pctfree 0 enable row movement as  
  select c1, c2, round ( dbms_random.value ( 0, 100 ) ) c3,  
         trunc ( sysdate, 'y' ) + ( rownum / 240 ) c4, 
         lpad ( 'x', 10, 'x' ) stuff   
  from   t   
  cross join (   
    select mod ( level, 17 ) c2 
    from   dual  
    connect by level <= 100  
  )  
  order by dbms_random.value

Table created.

Statement 5

alter table tchild2 shrink space

Table altered.

Statement 6

alter table tchild1 shrink space

Table altered.

Statement 7

select count(*) from tchild1


COUNT(*)
100000

COUNT(*)
100000

Statement 8

select count(*) from tchild2


COUNT(*)
100000

COUNT(*)
100000

Statement 9

select count(*) from t


COUNT(*)
1000

COUNT(*)
1000

Statement 10

select * 
from   t   
where  c1 <= 100


C1 STUFF
1 xxxxxxxxxx
2 xxxxxxxxxx
3 xxxxxxxxxx
4 xxxxxxxxxx
5 xxxxxxxxxx
6 xxxxxxxxxx
7 xxxxxxxxxx
8 xxxxxxxxxx
9 xxxxxxxxxx
10 xxxxxxxxxx
11 xxxxxxxxxx
12 xxxxxxxxxx
13 xxxxxxxxxx
14 xxxxxxxxxx
15 xxxxxxxxxx
16 xxxxxxxxxx
17 xxxxxxxxxx
18 xxxxxxxxxx
19 xxxxxxxxxx
20 xxxxxxxxxx
21 xxxxxxxxxx
22 xxxxxxxxxx
23 xxxxxxxxxx
24 xxxxxxxxxx
25 xxxxxxxxxx
26 xxxxxxxxxx
27 xxxxxxxxxx
28 xxxxxxxxxx
29 xxxxxxxxxx
30 xxxxxxxxxx
31 xxxxxxxxxx
32 xxxxxxxxxx
33 xxxxxxxxxx
34 xxxxxxxxxx
35 xxxxxxxxxx
36 xxxxxxxxxx
37 xxxxxxxxxx
38 xxxxxxxxxx
39 xxxxxxxxxx
40 xxxxxxxxxx
41 xxxxxxxxxx
42 xxxxxxxxxx
43 xxxxxxxxxx
44 xxxxxxxxxx
45 xxxxxxxxxx
46 xxxxxxxxxx
47 xxxxxxxxxx
48 xxxxxxxxxx
49 xxxxxxxxxx
50 xxxxxxxxxx

Rows 1 - 50. More rows exist.

C1	STUFF
1	xxxxxxxxxx
2	xxxxxxxxxx
3	xxxxxxxxxx
4	xxxxxxxxxx
5	xxxxxxxxxx
6	xxxxxxxxxx
7	xxxxxxxxxx
8	xxxxxxxxxx
9	xxxxxxxxxx
10	xxxxxxxxxx
11	xxxxxxxxxx
12	xxxxxxxxxx
13	xxxxxxxxxx
14	xxxxxxxxxx
15	xxxxxxxxxx
16	xxxxxxxxxx
17	xxxxxxxxxx
18	xxxxxxxxxx
19	xxxxxxxxxx
20	xxxxxxxxxx
21	xxxxxxxxxx
22	xxxxxxxxxx
23	xxxxxxxxxx
24	xxxxxxxxxx
25	xxxxxxxxxx
26	xxxxxxxxxx
27	xxxxxxxxxx
28	xxxxxxxxxx
29	xxxxxxxxxx
30	xxxxxxxxxx
31	xxxxxxxxxx
32	xxxxxxxxxx
33	xxxxxxxxxx
34	xxxxxxxxxx
35	xxxxxxxxxx
36	xxxxxxxxxx
37	xxxxxxxxxx
38	xxxxxxxxxx
39	xxxxxxxxxx
40	xxxxxxxxxx
41	xxxxxxxxxx
42	xxxxxxxxxx
43	xxxxxxxxxx
44	xxxxxxxxxx
45	xxxxxxxxxx
46	xxxxxxxxxx
47	xxxxxxxxxx
48	xxxxxxxxxx
49	xxxxxxxxxx
50	xxxxxxxxxx

Statement 11

begin  
  for rws in (  
    select /*+ gather_plan_statistics */c1, rownum,  
           ( select count ( distinct c2 )    
             from   tchild1 c   
             where  t.c1 = c.c1 ) dist_c2,    
           ( select avg ( c3 )    
             from   tchild2 c   
             where  t.c1 = c.c1 ) mean_c3,    
           ( select max ( c4 )    
             from   tchild1 c   
             where  t.c1 = c.c1 ) max_c4,    
           ( select min ( c4 )    
             from   tchild2 c   
             where  t.c1 = c.c1 ) min_c4    
    from   t   
    where  c1 <= 100 
  ) loop 
    null; 
  end loop; 
end;

Statement processed.

Statement 12

The database executes the scalar subqueries once for each row fetched from T. So it does 200 full table scan of TCHILD1 & 2. A huge amount of repeated work! You can see this from the starts column for lines 2, 4, 6, and 8

select *  
from   dbms_xplan.display_cursor ( '2wgmsabxdbhby', null, 'IOSTATS LAST')


PLAN_TABLE_OUTPUT
SQL_ID  2wgmsabxdbhby, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */C1, ROWNUM, ( SELECT COUNT ( 
DISTINCT C2 ) FROM TCHILD1 C WHERE T.C1 = C.C1 ) DIST_C2, ( SELECT AVG 
( C3 ) FROM TCHILD2 C WHERE T.C1 = C.C1 ) MEAN_C3, ( SELECT MAX ( C4 ) 
FROM TCHILD1 C WHERE T.C1 = C.C1 ) MAX_C4, ( SELECT MIN ( C4 ) FROM 
TCHILD2 C WHERE T.C1 = C.C1 ) MIN_C4 FROM T WHERE C1 <= 100
 
Plan hash value: 2227323319
 
----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |    100 |00:00:00.01 |       6 |
|   1 |  SORT GROUP BY     |         |    100 |      1 |    100 |00:00:00.55 |   42600 |
|*  2 |   TABLE ACCESS FULL| TCHILD1 |    100 |    100 |  10000 |00:00:00.53 |   42600 |
|   3 |  SORT AGGREGATE    |         |    100 |      1 |    100 |00:00:00.58 |   42600 |
|*  4 |   TABLE ACCESS FULL| TCHILD2 |    100 |    100 |  10000 |00:00:00.61 |   42600 |
|   5 |  SORT AGGREGATE    |         |    100 |      1 |    100 |00:00:00.58 |   42600 |
|*  6 |   TABLE ACCESS FULL| TCHILD1 |    100 |    100 |  10000 |00:00:00.55 |   42600 |
|   7 |  SORT AGGREGATE    |         |    100 |      1 |    100 |00:00:00.58 |   42600 |
|*  8 |   TABLE ACCESS FULL| TCHILD2 |    100 |    100 |  10000 |00:00:00.62 |   42600 |
|   9 |  COUNT             |         |      1 |        |    100 |00:00:00.01 |       6 |
|* 10 |   TABLE ACCESS FULL| T       |      1 |    100 |    100 |00:00:00.01 |       6 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("C"."C1"=:B1)
   4 - filter("C"."C1"=:B1)
   6 - filter("C"."C1"=:B1)
   8 - filter("C"."C1"=:B1)
  10 - filter("C1"<=100)
 

35 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 2wgmsabxdbhby, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics /C1, ROWNUM, ( SELECT COUNT (
DISTINCT C2 ) FROM TCHILD1 C WHERE T.C1 = C.C1 ) DIST_C2, ( SELECT AVG
( C3 ) FROM TCHILD2 C WHERE T.C1 = C.C1 ) MEAN_C3, ( SELECT MAX ( C4 )
FROM TCHILD1 C WHERE T.C1 = C.C1 ) MAX_C4, ( SELECT MIN ( C4 ) FROM
TCHILD2 C WHERE T.C1 = C.C1 ) MIN_C4 FROM T WHERE C1 <= 100

Plan hash value: 2227323319

----------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
----------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 100 \|00:00:00.01 \| 6 \|
\| 1 \| SORT GROUP BY \| \| 100 \| 1 \| 100 \|00:00:00.55 \| 42600 \|
\|* 2 \| TABLE ACCESS FULL\| TCHILD1 \| 100 \| 100 \| 10000 \|00:00:00.53 \| 42600 \|
\| 3 \| SORT AGGREGATE \| \| 100 \| 1 \| 100 \|00:00:00.58 \| 42600 \|
\|* 4 \| TABLE ACCESS FULL\| TCHILD2 \| 100 \| 100 \| 10000 \|00:00:00.61 \| 42600 \|
\| 5 \| SORT AGGREGATE \| \| 100 \| 1 \| 100 \|00:00:00.58 \| 42600 \|
\|* 6 \| TABLE ACCESS FULL\| TCHILD1 \| 100 \| 100 \| 10000 \|00:00:00.55 \| 42600 \|
\| 7 \| SORT AGGREGATE \| \| 100 \| 1 \| 100 \|00:00:00.58 \| 42600 \|
\|* 8 \| TABLE ACCESS FULL\| TCHILD2 \| 100 \| 100 \| 10000 \|00:00:00.62 \| 42600 \|
\| 9 \| COUNT \| \| 1 \| \| 100 \|00:00:00.01 \| 6 \|
\|* 10 \| TABLE ACCESS FULL\| T \| 1 \| 100 \| 100 \|00:00:00.01 \| 6 \|
----------------------------------------------------------------------------------------

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

2 - filter("C"."C1"=:B1)
4 - filter("C"."C1"=:B1)
6 - filter("C"."C1"=:B1)
8 - filter("C"."C1"=:B1)
10 - filter("C1"<=100)

Statement 13

With cross apply you can join the outer table in an inline view. This fetches the three most recent orders for each store

select store_name, o.*  
from   co.stores s 
cross apply ( 
  select * from co.orders o 
  where  s.store_id = o.store_id 
  order by o.order_datetime desc 
  fetch first 3 rows only 
) o 
order by store_name 
fetch first 20 rows only


STORE_NAME ORDER_ID ORDER_DATETIME CUSTOMER_ID ORDER_STATUS STORE_ID
Bejing 1912 30-MAR-19 11.05.48.922577 AM 195 COMPLETE 21
Bejing 1908 29-MAR-19 06.46.08.118387 PM 224 COMPLETE 21
Bejing 1911 30-MAR-19 09.18.13.207605 AM 311 COMPLETE 21
Bengaluru 1831 14-MAR-19 08.32.36.013193 AM 187 COMPLETE 13
Bengaluru 1791 06-MAR-19 01.45.58.672579 PM 332 COMPLETE 13
Bengaluru 1793 06-MAR-19 11.17.49.990617 PM 158 COMPLETE 13
Berlin 1817 10-MAR-19 09.54.25.125750 PM 8 COMPLETE 8
Berlin 1623 03-FEB-19 11.37.12.453316 AM 211 COMPLETE 8
Berlin 1795 07-MAR-19 05.43.17.990211 AM 240 COMPLETE 8
Bucharest 1890 25-MAR-19 11.31.58.636926 PM 7 COMPLETE 7
Bucharest 1665 11-FEB-19 12.40.18.478950 PM 94 COMPLETE 7
Bucharest 1736 25-FEB-19 03.47.12.996505 AM 239 COMPLETE 7
Buenos Aires 1944 10-APR-19 10.14.27.406048 AM 367 COMPLETE 19
Buenos Aires 1928 04-APR-19 03.24.06.034344 PM 367 COMPLETE 19
Buenos Aires 1930 04-APR-19 08.24.44.505645 PM 338 COMPLETE 19
Chicago 1794 07-MAR-19 02.59.15.029147 AM 237 COMPLETE 5
Chicago 1644 07-FEB-19 11.17.45.806853 AM 179 COMPLETE 5
Chicago 1718 20-FEB-19 04.19.43.190304 PM 179 COMPLETE 5
Johannesburg 1780 04-MAR-19 06.29.25.910248 AM 272 CANCELLED 11
Johannesburg 1641 07-FEB-19 01.15.19.464639 AM 388 COMPLETE 11

20 rows selected.

STORE_NAME	ORDER_ID	ORDER_DATETIME	CUSTOMER_ID	ORDER_STATUS	STORE_ID
Bejing	1912	30-MAR-19 11.05.48.922577 AM	195	COMPLETE	21
Bejing	1908	29-MAR-19 06.46.08.118387 PM	224	COMPLETE	21
Bejing	1911	30-MAR-19 09.18.13.207605 AM	311	COMPLETE	21
Bengaluru	1831	14-MAR-19 08.32.36.013193 AM	187	COMPLETE	13
Bengaluru	1791	06-MAR-19 01.45.58.672579 PM	332	COMPLETE	13
Bengaluru	1793	06-MAR-19 11.17.49.990617 PM	158	COMPLETE	13
Berlin	1817	10-MAR-19 09.54.25.125750 PM	8	COMPLETE	8
Berlin	1623	03-FEB-19 11.37.12.453316 AM	211	COMPLETE	8
Berlin	1795	07-MAR-19 05.43.17.990211 AM	240	COMPLETE	8
Bucharest	1890	25-MAR-19 11.31.58.636926 PM	7	COMPLETE	7
Bucharest	1665	11-FEB-19 12.40.18.478950 PM	94	COMPLETE	7
Bucharest	1736	25-FEB-19 03.47.12.996505 AM	239	COMPLETE	7
Buenos Aires	1944	10-APR-19 10.14.27.406048 AM	367	COMPLETE	19
Buenos Aires	1928	04-APR-19 03.24.06.034344 PM	367	COMPLETE	19
Buenos Aires	1930	04-APR-19 08.24.44.505645 PM	338	COMPLETE	19
Chicago	1794	07-MAR-19 02.59.15.029147 AM	237	COMPLETE	5
Chicago	1644	07-FEB-19 11.17.45.806853 AM	179	COMPLETE	5
Chicago	1718	20-FEB-19 04.19.43.190304 PM	179	COMPLETE	5
Johannesburg	1780	04-MAR-19 06.29.25.910248 AM	272	CANCELLED	11
Johannesburg	1641	07-FEB-19 01.15.19.464639 AM	388	COMPLETE	11

Statement 14

You can also write cross apply queries using lateral, so this is the same as the previous statement.

select store_name, o.*  
from   co.stores s, 
lateral ( 
  select * from co.orders o 
  where  s.store_id = o.store_id 
  order by o.order_datetime desc 
  fetch first 3 rows only 
) o 
order by store_name 
fetch first 20 rows only


STORE_NAME ORDER_ID ORDER_DATETIME CUSTOMER_ID ORDER_STATUS STORE_ID
Bejing 1912 30-MAR-19 11.05.48.922577 AM 195 COMPLETE 21
Bejing 1908 29-MAR-19 06.46.08.118387 PM 224 COMPLETE 21
Bejing 1911 30-MAR-19 09.18.13.207605 AM 311 COMPLETE 21
Bengaluru 1831 14-MAR-19 08.32.36.013193 AM 187 COMPLETE 13
Bengaluru 1791 06-MAR-19 01.45.58.672579 PM 332 COMPLETE 13
Bengaluru 1793 06-MAR-19 11.17.49.990617 PM 158 COMPLETE 13
Berlin 1817 10-MAR-19 09.54.25.125750 PM 8 COMPLETE 8
Berlin 1623 03-FEB-19 11.37.12.453316 AM 211 COMPLETE 8
Berlin 1795 07-MAR-19 05.43.17.990211 AM 240 COMPLETE 8
Bucharest 1890 25-MAR-19 11.31.58.636926 PM 7 COMPLETE 7
Bucharest 1665 11-FEB-19 12.40.18.478950 PM 94 COMPLETE 7
Bucharest 1736 25-FEB-19 03.47.12.996505 AM 239 COMPLETE 7
Buenos Aires 1944 10-APR-19 10.14.27.406048 AM 367 COMPLETE 19
Buenos Aires 1928 04-APR-19 03.24.06.034344 PM 367 COMPLETE 19
Buenos Aires 1930 04-APR-19 08.24.44.505645 PM 338 COMPLETE 19
Chicago 1794 07-MAR-19 02.59.15.029147 AM 237 COMPLETE 5
Chicago 1644 07-FEB-19 11.17.45.806853 AM 179 COMPLETE 5
Chicago 1718 20-FEB-19 04.19.43.190304 PM 179 COMPLETE 5
Johannesburg 1780 04-MAR-19 06.29.25.910248 AM 272 CANCELLED 11
Johannesburg 1641 07-FEB-19 01.15.19.464639 AM 388 COMPLETE 11

20 rows selected.

STORE_NAME	ORDER_ID	ORDER_DATETIME	CUSTOMER_ID	ORDER_STATUS	STORE_ID
Bejing	1912	30-MAR-19 11.05.48.922577 AM	195	COMPLETE	21
Bejing	1908	29-MAR-19 06.46.08.118387 PM	224	COMPLETE	21
Bejing	1911	30-MAR-19 09.18.13.207605 AM	311	COMPLETE	21
Bengaluru	1831	14-MAR-19 08.32.36.013193 AM	187	COMPLETE	13
Bengaluru	1791	06-MAR-19 01.45.58.672579 PM	332	COMPLETE	13
Bengaluru	1793	06-MAR-19 11.17.49.990617 PM	158	COMPLETE	13
Berlin	1817	10-MAR-19 09.54.25.125750 PM	8	COMPLETE	8
Berlin	1623	03-FEB-19 11.37.12.453316 AM	211	COMPLETE	8
Berlin	1795	07-MAR-19 05.43.17.990211 AM	240	COMPLETE	8
Bucharest	1890	25-MAR-19 11.31.58.636926 PM	7	COMPLETE	7
Bucharest	1665	11-FEB-19 12.40.18.478950 PM	94	COMPLETE	7
Bucharest	1736	25-FEB-19 03.47.12.996505 AM	239	COMPLETE	7
Buenos Aires	1944	10-APR-19 10.14.27.406048 AM	367	COMPLETE	19
Buenos Aires	1928	04-APR-19 03.24.06.034344 PM	367	COMPLETE	19
Buenos Aires	1930	04-APR-19 08.24.44.505645 PM	338	COMPLETE	19
Chicago	1794	07-MAR-19 02.59.15.029147 AM	237	COMPLETE	5
Chicago	1644	07-FEB-19 11.17.45.806853 AM	179	COMPLETE	5
Chicago	1718	20-FEB-19 04.19.43.190304 PM	179	COMPLETE	5
Johannesburg	1780	04-MAR-19 06.29.25.910248 AM	272	CANCELLED	11
Johannesburg	1641	07-FEB-19 01.15.19.464639 AM	388	COMPLETE	11

Statement 15

You can also write lateral queries using cross join lateral; again this is the same as the previous two statements

select store_name, o.*  
from   co.stores s 
cross join lateral ( 
  select * from co.orders o 
  where  s.store_id = o.store_id 
  order by o.order_datetime desc 
  fetch first 3 rows only 
) o  
order by store_name 
fetch first 20 rows only


STORE_NAME ORDER_ID ORDER_DATETIME CUSTOMER_ID ORDER_STATUS STORE_ID
Bejing 1912 30-MAR-19 11.05.48.922577 AM 195 COMPLETE 21
Bejing 1908 29-MAR-19 06.46.08.118387 PM 224 COMPLETE 21
Bejing 1911 30-MAR-19 09.18.13.207605 AM 311 COMPLETE 21
Bengaluru 1831 14-MAR-19 08.32.36.013193 AM 187 COMPLETE 13
Bengaluru 1791 06-MAR-19 01.45.58.672579 PM 332 COMPLETE 13
Bengaluru 1793 06-MAR-19 11.17.49.990617 PM 158 COMPLETE 13
Berlin 1817 10-MAR-19 09.54.25.125750 PM 8 COMPLETE 8
Berlin 1623 03-FEB-19 11.37.12.453316 AM 211 COMPLETE 8
Berlin 1795 07-MAR-19 05.43.17.990211 AM 240 COMPLETE 8
Bucharest 1890 25-MAR-19 11.31.58.636926 PM 7 COMPLETE 7
Bucharest 1665 11-FEB-19 12.40.18.478950 PM 94 COMPLETE 7
Bucharest 1736 25-FEB-19 03.47.12.996505 AM 239 COMPLETE 7
Buenos Aires 1944 10-APR-19 10.14.27.406048 AM 367 COMPLETE 19
Buenos Aires 1928 04-APR-19 03.24.06.034344 PM 367 COMPLETE 19
Buenos Aires 1930 04-APR-19 08.24.44.505645 PM 338 COMPLETE 19
Chicago 1794 07-MAR-19 02.59.15.029147 AM 237 COMPLETE 5
Chicago 1644 07-FEB-19 11.17.45.806853 AM 179 COMPLETE 5
Chicago 1718 20-FEB-19 04.19.43.190304 PM 179 COMPLETE 5
Johannesburg 1780 04-MAR-19 06.29.25.910248 AM 272 CANCELLED 11
Johannesburg 1641 07-FEB-19 01.15.19.464639 AM 388 COMPLETE 11

20 rows selected.

STORE_NAME	ORDER_ID	ORDER_DATETIME	CUSTOMER_ID	ORDER_STATUS	STORE_ID
Bejing	1912	30-MAR-19 11.05.48.922577 AM	195	COMPLETE	21
Bejing	1908	29-MAR-19 06.46.08.118387 PM	224	COMPLETE	21
Bejing	1911	30-MAR-19 09.18.13.207605 AM	311	COMPLETE	21
Bengaluru	1831	14-MAR-19 08.32.36.013193 AM	187	COMPLETE	13
Bengaluru	1791	06-MAR-19 01.45.58.672579 PM	332	COMPLETE	13
Bengaluru	1793	06-MAR-19 11.17.49.990617 PM	158	COMPLETE	13
Berlin	1817	10-MAR-19 09.54.25.125750 PM	8	COMPLETE	8
Berlin	1623	03-FEB-19 11.37.12.453316 AM	211	COMPLETE	8
Berlin	1795	07-MAR-19 05.43.17.990211 AM	240	COMPLETE	8
Bucharest	1890	25-MAR-19 11.31.58.636926 PM	7	COMPLETE	7
Bucharest	1665	11-FEB-19 12.40.18.478950 PM	94	COMPLETE	7
Bucharest	1736	25-FEB-19 03.47.12.996505 AM	239	COMPLETE	7
Buenos Aires	1944	10-APR-19 10.14.27.406048 AM	367	COMPLETE	19
Buenos Aires	1928	04-APR-19 03.24.06.034344 PM	367	COMPLETE	19
Buenos Aires	1930	04-APR-19 08.24.44.505645 PM	338	COMPLETE	19
Chicago	1794	07-MAR-19 02.59.15.029147 AM	237	COMPLETE	5
Chicago	1644	07-FEB-19 11.17.45.806853 AM	179	COMPLETE	5
Chicago	1718	20-FEB-19 04.19.43.190304 PM	179	COMPLETE	5
Johannesburg	1780	04-MAR-19 06.29.25.910248 AM	272	CANCELLED	11
Johannesburg	1641	07-FEB-19 01.15.19.464639 AM	388	COMPLETE	11

Statement 16

select store_name, o.*  
from   co.stores s 
cross apply ( 
  select * from co.orders o 
  where  s.store_id = o.store_id 
  order by o.order_datetime desc 
  fetch first 3 rows only 
) o 
where  store_name like 'M%' 
order by store_name 
fetch first 20 rows only


STORE_NAME ORDER_ID ORDER_DATETIME CUSTOMER_ID ORDER_STATUS STORE_ID
Madrid 1730 23-FEB-19 02.34.28.490023 AM 10 COMPLETE 10
Madrid 1712 19-FEB-19 11.07.47.707259 PM 213 COMPLETE 10
Madrid 1691 15-FEB-19 07.45.43.436385 AM 358 COMPLETE 10
Mexico City 1931 05-APR-19 12.26.49.495590 AM 136 COMPLETE 20
Mexico City 1922 02-APR-19 03.52.49.213390 PM 339 COMPLETE 20
Mexico City 1929 04-APR-19 08.14.44.084942 PM 165 REFUNDED 20
Mumbai 1896 27-MAR-19 10.05.21.921377 PM 333 COMPLETE 14
Mumbai 1855 18-MAR-19 06.50.34.800957 AM 391 COMPLETE 14
Mumbai 1811 09-MAR-19 05.31.53.231172 PM 159 COMPLETE 14

9 rows selected.

STORE_NAME	ORDER_ID	ORDER_DATETIME	CUSTOMER_ID	ORDER_STATUS	STORE_ID
Madrid	1730	23-FEB-19 02.34.28.490023 AM	10	COMPLETE	10
Madrid	1712	19-FEB-19 11.07.47.707259 PM	213	COMPLETE	10
Madrid	1691	15-FEB-19 07.45.43.436385 AM	358	COMPLETE	10
Mexico City	1931	05-APR-19 12.26.49.495590 AM	136	COMPLETE	20
Mexico City	1922	02-APR-19 03.52.49.213390 PM	339	COMPLETE	20
Mexico City	1929	04-APR-19 08.14.44.084942 PM	165	REFUNDED	20
Mumbai	1896	27-MAR-19 10.05.21.921377 PM	333	COMPLETE	14
Mumbai	1855	18-MAR-19 06.50.34.800957 AM	391	COMPLETE	14
Mumbai	1811	09-MAR-19 05.31.53.231172 PM	159	COMPLETE	14

Statement 17

This cross apply filters out orders placed before 1st April 2019. There are some orders without any orders after this date, so these are excluded from the output.

select store_name, o.*  
from   co.stores s 
cross apply ( 
  select * from co.orders o 
  where  s.store_id = o.store_id 
  and    o.order_datetime >= timestamp '2019-04-01 00:00:00' 
  order by o.order_datetime desc 
  fetch first 3 rows only 
) o 
order by store_name 
fetch first 20 rows only


STORE_NAME ORDER_ID ORDER_DATETIME CUSTOMER_ID ORDER_STATUS STORE_ID
Buenos Aires 1944 10-APR-19 10.14.27.406048 AM 367 COMPLETE 19
Buenos Aires 1928 04-APR-19 03.24.06.034344 PM 367 COMPLETE 19
Buenos Aires 1930 04-APR-19 08.24.44.505645 PM 338 COMPLETE 19
Mexico City 1931 05-APR-19 12.26.49.495590 AM 136 COMPLETE 20
Mexico City 1922 02-APR-19 03.52.49.213390 PM 339 COMPLETE 20
Mexico City 1929 04-APR-19 08.14.44.084942 PM 165 REFUNDED 20
Online 1923 03-APR-19 10.47.49.417719 AM 354 COMPLETE 1
S�o Paulo 1926 03-APR-19 07.05.04.600442 PM 366 COMPLETE 18
Tel Aviv 1950 12-APR-19 09.41.06.642641 PM 348 COMPLETE 23
Tel Aviv 1948 11-APR-19 03.49.45.728115 AM 319 COMPLETE 23
Tel Aviv 1949 11-APR-19 07.14.31.588169 PM 348 COMPLETE 23
Tokyo 1945 10-APR-19 03.49.31.059138 PM 341 COMPLETE 22
Tokyo 1938 07-APR-19 06.31.50.146426 PM 370 CANCELLED 22
Tokyo 1937 07-APR-19 06.54.49.039864 AM 312 COMPLETE 22

14 rows selected.

STORE_NAME	ORDER_ID	ORDER_DATETIME	CUSTOMER_ID	ORDER_STATUS	STORE_ID
Buenos Aires	1944	10-APR-19 10.14.27.406048 AM	367	COMPLETE	19
Buenos Aires	1928	04-APR-19 03.24.06.034344 PM	367	COMPLETE	19
Buenos Aires	1930	04-APR-19 08.24.44.505645 PM	338	COMPLETE	19
Mexico City	1931	05-APR-19 12.26.49.495590 AM	136	COMPLETE	20
Mexico City	1922	02-APR-19 03.52.49.213390 PM	339	COMPLETE	20
Mexico City	1929	04-APR-19 08.14.44.084942 PM	165	REFUNDED	20
Online	1923	03-APR-19 10.47.49.417719 AM	354	COMPLETE	1
S�o Paulo	1926	03-APR-19 07.05.04.600442 PM	366	COMPLETE	18
Tel Aviv	1950	12-APR-19 09.41.06.642641 PM	348	COMPLETE	23
Tel Aviv	1948	11-APR-19 03.49.45.728115 AM	319	COMPLETE	23
Tel Aviv	1949	11-APR-19 07.14.31.588169 PM	348	COMPLETE	23
Tokyo	1945	10-APR-19 03.49.31.059138 PM	341	COMPLETE	22
Tokyo	1938	07-APR-19 06.31.50.146426 PM	370	CANCELLED	22
Tokyo	1937	07-APR-19 06.54.49.039864 AM	312	COMPLETE	22

Statement 18

To include all the rows from the outer table along with any matching rows in the subquery, use outer apply instead. This returns all the stores along with the three most recent orders placed on or after the 1st April 2019.

select store_name, o.*  
from   co.stores s 
outer apply ( 
  select * from co.orders o 
  where  s.store_id = o.store_id 
  and    o.order_datetime >= timestamp '2019-04-01 00:00:00' 
  order by o.order_datetime desc 
  fetch first 3 rows only 
) o 
order by store_name 
fetch first 20 rows only


STORE_NAME ORDER_ID ORDER_DATETIME CUSTOMER_ID ORDER_STATUS STORE_ID
Bejing  -  -  -  -  - 
Bengaluru  -  -  -  -  - 
Berlin  -  -  -  -  - 
Bucharest  -  -  -  -  - 
Buenos Aires 1944 10-APR-19 10.14.27.406048 AM 367 COMPLETE 19
Buenos Aires 1928 04-APR-19 03.24.06.034344 PM 367 COMPLETE 19
Buenos Aires 1930 04-APR-19 08.24.44.505645 PM 338 COMPLETE 19
Chicago  -  -  -  -  - 
Johannesburg  -  -  -  -  - 
Lagos  -  -  -  -  - 
London  -  -  -  -  - 
Madrid  -  -  -  -  - 
Mexico City 1931 05-APR-19 12.26.49.495590 AM 136 COMPLETE 20
Mexico City 1922 02-APR-19 03.52.49.213390 PM 339 COMPLETE 20
Mexico City 1929 04-APR-19 08.14.44.084942 PM 165 REFUNDED 20
Mumbai  -  -  -  -  - 
New Dehli  -  -  -  -  - 
New York City  -  -  -  -  - 
Online 1923 03-APR-19 10.47.49.417719 AM 354 COMPLETE 1
Perth  -  -  -  -  - 

20 rows selected.

STORE_NAME	ORDER_ID	ORDER_DATETIME	CUSTOMER_ID	ORDER_STATUS	STORE_ID
Bejing	-	-	-	-	-
Bengaluru	-	-	-	-	-
Berlin	-	-	-	-	-
Bucharest	-	-	-	-	-
Buenos Aires	1944	10-APR-19 10.14.27.406048 AM	367	COMPLETE	19
Buenos Aires	1928	04-APR-19 03.24.06.034344 PM	367	COMPLETE	19
Buenos Aires	1930	04-APR-19 08.24.44.505645 PM	338	COMPLETE	19
Chicago	-	-	-	-	-
Johannesburg	-	-	-	-	-
Lagos	-	-	-	-	-
London	-	-	-	-	-
Madrid	-	-	-	-	-
Mexico City	1931	05-APR-19 12.26.49.495590 AM	136	COMPLETE	20
Mexico City	1922	02-APR-19 03.52.49.213390 PM	339	COMPLETE	20
Mexico City	1929	04-APR-19 08.14.44.084942 PM	165	REFUNDED	20
Mumbai	-	-	-	-	-
New Dehli	-	-	-	-	-
New York City	-	-	-	-	-
Online	1923	03-APR-19 10.47.49.417719 AM	354	COMPLETE	1
Perth	-	-	-	-	-

Statement 19

This rewrite the original query using cross apply. This means each of the child tables are only referenced once.

begin  
  for rws in (  
    select /*+ gather_plan_statistics */c1,  
           s1.*, s2.* 
    from   t   
    cross apply ( 
      select avg ( c3 ), min ( c4 )    
      from   tchild2 c   
      where  t.c1 = c.c1 
    ) s1 
    cross apply ( 
      select count ( distinct c2 ), max ( c4 )    
      from   tchild1 c   
      where  t.c1 = c.c1 
    ) s2 
    where  c1 <= 100 
    order  by c1 
  ) loop 
    null; 
  end loop; 
end;

Statement processed.

Statement 20

Using cross apply instead of scalar subqueries the query only full scans TCHILD2 once. It does full scan TCHILD1 100 though. So while this is a considerable saving, we can still do better.

select *  
from   dbms_xplan.display_cursor ( '1hvsah50dxbk9', null, 'IOSTATS LAST')


PLAN_TABLE_OUTPUT
SQL_ID  1hvsah50dxbk9, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */C1, S1.*, S2.* FROM T CROSS APPLY ( 
SELECT AVG ( C3 ), MIN ( C4 ) FROM TCHILD2 C WHERE T.C1 = C.C1 ) S1 
CROSS APPLY ( SELECT COUNT ( DISTINCT C2 ), MAX ( C4 ) FROM TCHILD1 C 
WHERE T.C1 = C.C1 ) S2 WHERE C1 <= 100 ORDER BY C1
 
Plan hash value: 3581037868
 
----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                 |      1 |        |    100 |00:00:00.64 |   42929 |
|   1 |  SORT ORDER BY         |                 |      1 |    100 |    100 |00:00:00.64 |   42929 |
|   2 |   NESTED LOOPS         |                 |      1 |    100 |    100 |00:00:00.43 |   42929 |
|*  3 |    HASH JOIN OUTER     |                 |      1 |    100 |    100 |00:00:00.01 |     429 |
|*  4 |     TABLE ACCESS FULL  | T               |      1 |    100 |    100 |00:00:00.01 |       4 |
|   5 |     VIEW               | VW_DCL_9B2A02E9 |      1 |    100 |    100 |00:00:00.01 |     425 |
|   6 |      HASH GROUP BY     |                 |      1 |    100 |    100 |00:00:00.01 |     425 |
|*  7 |       TABLE ACCESS FULL| TCHILD2         |      1 |  10010 |  10000 |00:00:00.01 |     425 |
|   8 |    VIEW                | VW_LAT_9B2A02E9 |    100 |      1 |    100 |00:00:00.64 |   42500 |
|   9 |     SORT GROUP BY      |                 |    100 |      1 |    100 |00:00:00.64 |   42500 |
|* 10 |      TABLE ACCESS FULL | TCHILD1         |    100 |    100 |  10000 |00:00:00.68 |   42500 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T"."C1"="ITEM_0")
   4 - filter("T"."C1"<=100)
   7 - filter("C"."C1"<=100)
  10 - filter("T"."C1"="C"."C1")
 

33 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 1hvsah50dxbk9, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics /C1, S1., S2. FROM T CROSS APPLY (
SELECT AVG ( C3 ), MIN ( C4 ) FROM TCHILD2 C WHERE T.C1 = C.C1 ) S1
CROSS APPLY ( SELECT COUNT ( DISTINCT C2 ), MAX ( C4 ) FROM TCHILD1 C
WHERE T.C1 = C.C1 ) S2 WHERE C1 <= 100 ORDER BY C1

Plan hash value: 3581037868

----------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
----------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 100 \|00:00:00.64 \| 42929 \|
\| 1 \| SORT ORDER BY \| \| 1 \| 100 \| 100 \|00:00:00.64 \| 42929 \|
\| 2 \| NESTED LOOPS \| \| 1 \| 100 \| 100 \|00:00:00.43 \| 42929 \|
\|* 3 \| HASH JOIN OUTER \| \| 1 \| 100 \| 100 \|00:00:00.01 \| 429 \|
\|* 4 \| TABLE ACCESS FULL \| T \| 1 \| 100 \| 100 \|00:00:00.01 \| 4 \|
\| 5 \| VIEW \| VW_DCL_9B2A02E9 \| 1 \| 100 \| 100 \|00:00:00.01 \| 425 \|
\| 6 \| HASH GROUP BY \| \| 1 \| 100 \| 100 \|00:00:00.01 \| 425 \|
\|* 7 \| TABLE ACCESS FULL\| TCHILD2 \| 1 \| 10010 \| 10000 \|00:00:00.01 \| 425 \|
\| 8 \| VIEW \| VW_LAT_9B2A02E9 \| 100 \| 1 \| 100 \|00:00:00.64 \| 42500 \|
\| 9 \| SORT GROUP BY \| \| 100 \| 1 \| 100 \|00:00:00.64 \| 42500 \|
\|* 10 \| TABLE ACCESS FULL \| TCHILD1 \| 100 \| 100 \| 10000 \|00:00:00.68 \| 42500 \|
----------------------------------------------------------------------------------------------------

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

3 - access("T"."C1"="ITEM_0")
4 - filter("T"."C1"<=100)
7 - filter("C"."C1"<=100)
10 - filter("T"."C1"="C"."C1")

Statement 21

This changes the scalar subqueries into joins. Note that this needs two-levels of grouping - one for each child table. This is necessary for correct results. If you join and group all the same in the same subquery, you'll get the cross product of TCHILD1 & 2. The joins must also be outer joins to ensure this is functionally the same as scalar subqueries.

begin  
  for rws in (  
    select /*+ gather_plan_statistics */c1, min_c4, mean_c3,  
           count ( distinct c1.c2 ) cd, max ( c1.c4 ) mx   
    from  ( 
      select c1,  
             min ( c2.c4 ) min_c4, avg ( c2.c3 ) mean_c3 
      from   t 
      left   join tchild2 c2   
      using  ( c1 )  
      where  c1 <= 100 
      group  by c1 
    ) s 
    left   join tchild1 c1   
    using  ( c1 )  
    group  by c1, min_c4, mean_c3 
  ) loop 
    null; 
  end loop; 
end;

Statement processed.

Statement 22

Using joins the query now full scans each table once - a huge saving over the original scalar subqueries. While this is the ideal way to write the query, it's often impractical to convert many scalar subqueries to joins in large queries. Cross apply is easier to merge into a bigger query than joins and often gives good enough performance.

select *  
from   dbms_xplan.display_cursor ( '95wm0dfv7j6ar', null, 'IOSTATS LAST')


PLAN_TABLE_OUTPUT
SQL_ID  95wm0dfv7j6ar, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */C1, MIN_C4, MEAN_C3, COUNT ( 
DISTINCT C1.C2 ) CD, MAX ( C1.C4 ) MX FROM ( SELECT C1, MIN ( C2.C4 ) 
MIN_C4, AVG ( C2.C3 ) MEAN_C3 FROM T LEFT JOIN TCHILD2 C2 USING ( C1 ) 
WHERE C1 <= 100 GROUP BY C1 ) S LEFT JOIN TCHILD1 C1 USING ( C1 ) GROUP 
BY C1, MIN_C4, MEAN_C3
 
Plan hash value: 598495560
 
------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |      1 |        |    100 |00:00:00.40 |     854 |
|   1 |  HASH GROUP BY           |           |      1 |    100 |    100 |00:00:00.40 |     854 |
|   2 |   VIEW                   | VW_DAG_0  |      1 |   1203 |   1700 |00:00:00.40 |     854 |
|   3 |    HASH GROUP BY         |           |      1 |   1203 |   1700 |00:00:00.40 |     854 |
|   4 |     VIEW                 | VM_NWVW_1 |      1 |  10030 |  10000 |00:00:00.40 |     854 |
|   5 |      HASH GROUP BY       |           |      1 |  10030 |  10000 |00:00:00.40 |     854 |
|*  6 |       HASH JOIN OUTER    |           |      1 |  10030 |   1000K|00:00:00.16 |     854 |
|*  7 |        HASH JOIN OUTER   |           |      1 |   1002 |  10000 |00:00:00.01 |     429 |
|*  8 |         TABLE ACCESS FULL| T         |      1 |    100 |    100 |00:00:00.01 |       4 |
|*  9 |         TABLE ACCESS FULL| TCHILD1   |      1 |  10010 |  10000 |00:00:00.01 |     425 |
|* 10 |        TABLE ACCESS FULL | TCHILD2   |      1 |  10010 |  10000 |00:00:00.01 |     425 |
------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - access("T"."C1"="C2"."C1")
   7 - access("T"."C1"="C1"."C1")
   8 - filter("T"."C1"<=100)
   9 - filter("C1"."C1"<=100)
  10 - filter("C2"."C1"<=100)
 

35 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 95wm0dfv7j6ar, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics /C1, MIN_C4, MEAN_C3, COUNT (
DISTINCT C1.C2 ) CD, MAX ( C1.C4 ) MX FROM ( SELECT C1, MIN ( C2.C4 )
MIN_C4, AVG ( C2.C3 ) MEAN_C3 FROM T LEFT JOIN TCHILD2 C2 USING ( C1 )
WHERE C1 <= 100 GROUP BY C1 ) S LEFT JOIN TCHILD1 C1 USING ( C1 ) GROUP
BY C1, MIN_C4, MEAN_C3

Plan hash value: 598495560

------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 100 \|00:00:00.40 \| 854 \|
\| 1 \| HASH GROUP BY \| \| 1 \| 100 \| 100 \|00:00:00.40 \| 854 \|
\| 2 \| VIEW \| VW_DAG_0 \| 1 \| 1203 \| 1700 \|00:00:00.40 \| 854 \|
\| 3 \| HASH GROUP BY \| \| 1 \| 1203 \| 1700 \|00:00:00.40 \| 854 \|
\| 4 \| VIEW \| VM_NWVW_1 \| 1 \| 10030 \| 10000 \|00:00:00.40 \| 854 \|
\| 5 \| HASH GROUP BY \| \| 1 \| 10030 \| 10000 \|00:00:00.40 \| 854 \|
\|* 6 \| HASH JOIN OUTER \| \| 1 \| 10030 \| 1000K\|00:00:00.16 \| 854 \|
\|* 7 \| HASH JOIN OUTER \| \| 1 \| 1002 \| 10000 \|00:00:00.01 \| 429 \|
\|* 8 \| TABLE ACCESS FULL\| T \| 1 \| 100 \| 100 \|00:00:00.01 \| 4 \|
\|* 9 \| TABLE ACCESS FULL\| TCHILD1 \| 1 \| 10010 \| 10000 \|00:00:00.01 \| 425 \|
\|* 10 \| TABLE ACCESS FULL \| TCHILD2 \| 1 \| 10010 \| 10000 \|00:00:00.01 \| 425 \|
------------------------------------------------------------------------------------------------

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

6 - access("T"."C1"="C2"."C1")
7 - access("T"."C1"="C1"."C1")
8 - filter("T"."C1"<=100)
9 - filter("C1"."C1"<=100)
10 - filter("C2"."C1"<=100)

Statement 23

begin  
  for rws in (  
    select /*+ gather_plan_statistics */c1,  
           ( select count ( distinct c2 )    
             from   tchild1 c   
             where  t.c1 = c.c1 ) dist_c2,    
           ( select avg ( c3 )    
             from   tchild2 c   
             where  t.c1 = c.c1 ) mean_c3,    
           ( select max ( c4 )    
             from   tchild1 c   
             where  t.c1 = c.c1 ) max_c4,    
           ( select min ( c4 )    
             from   tchild2 c   
             where  t.c1 = c.c1 ) min_c4    
    from   t   
    where  c1 <= 100 
  ) loop 
    null; 
  end loop; 
end;

Statement processed.

Statement 24

In many cases the optimizer can convert scalar subqueries to joins - the use of rownum in the original query prevented this.

select *  
from   dbms_xplan.display_cursor ( '9cb7yn0xw1dkx', null, 'IOSTATS LAST')


PLAN_TABLE_OUTPUT
SQL_ID  9cb7yn0xw1dkx, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */C1, ( SELECT COUNT ( DISTINCT C2 ) 
FROM TCHILD1 C WHERE T.C1 = C.C1 ) DIST_C2, ( SELECT AVG ( C3 ) FROM 
TCHILD2 C WHERE T.C1 = C.C1 ) MEAN_C3, ( SELECT MAX ( C4 ) FROM TCHILD1 
C WHERE T.C1 = C.C1 ) MAX_C4, ( SELECT MIN ( C4 ) FROM TCHILD2 C WHERE 
T.C1 = C.C1 ) MIN_C4 FROM T WHERE C1 <= 100
 
Plan hash value: 2804289939
 
---------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |      1 |        |    100 |00:00:00.01 |    1279 |
|   1 |  SORT GROUP BY         |          |    100 |      1 |    100 |00:00:00.44 |   42500 |
|*  2 |   TABLE ACCESS FULL    | TCHILD1  |    100 |    100 |  10000 |00:00:00.48 |   42500 |
|*  3 |  HASH JOIN OUTER       |          |      1 |    100 |    100 |00:00:00.01 |    1279 |
|*  4 |   HASH JOIN OUTER      |          |      1 |    100 |    100 |00:00:00.01 |     854 |
|*  5 |    HASH JOIN OUTER     |          |      1 |    100 |    100 |00:00:00.01 |     429 |
|*  6 |     TABLE ACCESS FULL  | T        |      1 |    100 |    100 |00:00:00.01 |       4 |
|   7 |     VIEW               | VW_SSQ_3 |      1 |    100 |    100 |00:00:00.01 |     425 |
|   8 |      HASH GROUP BY     |          |      1 |    100 |    100 |00:00:00.01 |     425 |
|*  9 |       TABLE ACCESS FULL| TCHILD2  |      1 |  10010 |  10000 |00:00:00.01 |     425 |
|  10 |    VIEW                | VW_SSQ_2 |      1 |    100 |    100 |00:00:00.01 |     425 |
|  11 |     HASH GROUP BY      |          |      1 |    100 |    100 |00:00:00.01 |     425 |
|* 12 |      TABLE ACCESS FULL | TCHILD1  |      1 |  10010 |  10000 |00:00:00.01 |     425 |
|  13 |   VIEW                 | VW_SSQ_1 |      1 |    100 |    100 |00:00:00.01 |     425 |
|  14 |    HASH GROUP BY       |          |      1 |    100 |    100 |00:00:00.01 |     425 |
|* 15 |     TABLE ACCESS FULL  | TCHILD2  |      1 |  10010 |  10000 |00:00:00.01 |     425 |
---------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("C"."C1"=:B1)
   3 - access("T"."C1"="ITEM_1")
   4 - access("T"."C1"="ITEM_2")
   5 - access("T"."C1"="ITEM_3")
   6 - filter("C1"<=100)
   9 - filter("C"."C1"<=100)
  12 - filter("C"."C1"<=100)
  15 - filter("C"."C1"<=100)
 

43 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 9cb7yn0xw1dkx, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics /C1, ( SELECT COUNT ( DISTINCT C2 )
FROM TCHILD1 C WHERE T.C1 = C.C1 ) DIST_C2, ( SELECT AVG ( C3 ) FROM
TCHILD2 C WHERE T.C1 = C.C1 ) MEAN_C3, ( SELECT MAX ( C4 ) FROM TCHILD1
C WHERE T.C1 = C.C1 ) MAX_C4, ( SELECT MIN ( C4 ) FROM TCHILD2 C WHERE
T.C1 = C.C1 ) MIN_C4 FROM T WHERE C1 <= 100

Plan hash value: 2804289939

---------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
---------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 100 \|00:00:00.01 \| 1279 \|
\| 1 \| SORT GROUP BY \| \| 100 \| 1 \| 100 \|00:00:00.44 \| 42500 \|
\|* 2 \| TABLE ACCESS FULL \| TCHILD1 \| 100 \| 100 \| 10000 \|00:00:00.48 \| 42500 \|
\|* 3 \| HASH JOIN OUTER \| \| 1 \| 100 \| 100 \|00:00:00.01 \| 1279 \|
\|* 4 \| HASH JOIN OUTER \| \| 1 \| 100 \| 100 \|00:00:00.01 \| 854 \|
\|* 5 \| HASH JOIN OUTER \| \| 1 \| 100 \| 100 \|00:00:00.01 \| 429 \|
\|* 6 \| TABLE ACCESS FULL \| T \| 1 \| 100 \| 100 \|00:00:00.01 \| 4 \|
\| 7 \| VIEW \| VW_SSQ_3 \| 1 \| 100 \| 100 \|00:00:00.01 \| 425 \|
\| 8 \| HASH GROUP BY \| \| 1 \| 100 \| 100 \|00:00:00.01 \| 425 \|
\|* 9 \| TABLE ACCESS FULL\| TCHILD2 \| 1 \| 10010 \| 10000 \|00:00:00.01 \| 425 \|
\| 10 \| VIEW \| VW_SSQ_2 \| 1 \| 100 \| 100 \|00:00:00.01 \| 425 \|
\| 11 \| HASH GROUP BY \| \| 1 \| 100 \| 100 \|00:00:00.01 \| 425 \|
\|* 12 \| TABLE ACCESS FULL \| TCHILD1 \| 1 \| 10010 \| 10000 \|00:00:00.01 \| 425 \|
\| 13 \| VIEW \| VW_SSQ_1 \| 1 \| 100 \| 100 \|00:00:00.01 \| 425 \|
\| 14 \| HASH GROUP BY \| \| 1 \| 100 \| 100 \|00:00:00.01 \| 425 \|
\|* 15 \| TABLE ACCESS FULL \| TCHILD2 \| 1 \| 10010 \| 10000 \|00:00:00.01 \| 425 \|
---------------------------------------------------------------------------------------------

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

2 - filter("C"."C1"=:B1)
3 - access("T"."C1"="ITEM_1")
4 - access("T"."C1"="ITEM_2")
5 - access("T"."C1"="ITEM_3")
6 - filter("C1"<=100)
9 - filter("C"."C1"<=100)
12 - filter("C"."C1"<=100)
15 - filter("C"."C1"<=100)

Statement 25

This flips the tables, so the scalar subquery is against the parent instead of child. This means the scalar subquery will have many rows where the driving value is the same.

begin 
  for rws in (  
    select /*+ gather_plan_statistics */c1, c2, c3, c4,  
           count(*) over () rws, 
           (  
             select stuff from t 
             where  t1.c1 = t.c1  
           ) 
    from   tchild1 t1 
    where  t1.c1 <= 10 
    order  by c1 
  ) loop 
    null; 
  end loop; 
end;

Statement processed.

Statement 26

In this case the database is able to use scalar subquery caching. This saves the value returned by the subquery instead of executing it. So although the query returns 1,000 rows (A-rows = 1,000), it only executes the scalar subquery ten times (Starts = 10 for operation 1). In some cases this can be more efficient than a join

select *  
from   dbms_xplan.display_cursor ( '9x13dr9yagud0', null, 'IOSTATS LAST')


PLAN_TABLE_OUTPUT
SQL_ID  9x13dr9yagud0, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */C1, C2, C3, C4, COUNT(*) OVER () 
RWS, ( SELECT STUFF FROM T WHERE T1.C1 = T.C1 ) FROM TCHILD1 T1 WHERE 
T1.C1 <= 10 ORDER BY C1
 
Plan hash value: 1213069904
 
----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |   1000 |00:00:00.01 |     426 |
|*  1 |  TABLE ACCESS FULL | T       |     10 |      1 |     10 |00:00:00.01 |      40 |
|   2 |  WINDOW SORT       |         |      1 |   1001 |   1000 |00:00:00.01 |     426 |
|*  3 |   TABLE ACCESS FULL| TCHILD1 |      1 |   1001 |   1000 |00:00:00.01 |     426 |
----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("T"."C1"=:B1)
   3 - filter("T1"."C1"<=10)
 

23 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 9x13dr9yagud0, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics /C1, C2, C3, C4, COUNT(*) OVER ()
RWS, ( SELECT STUFF FROM T WHERE T1.C1 = T.C1 ) FROM TCHILD1 T1 WHERE
T1.C1 <= 10 ORDER BY C1

Plan hash value: 1213069904

----------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
----------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 1000 \|00:00:00.01 \| 426 \|
\|* 1 \| TABLE ACCESS FULL \| T \| 10 \| 1 \| 10 \|00:00:00.01 \| 40 \|
\| 2 \| WINDOW SORT \| \| 1 \| 1001 \| 1000 \|00:00:00.01 \| 426 \|
\|* 3 \| TABLE ACCESS FULL\| TCHILD1 \| 1 \| 1001 \| 1000 \|00:00:00.01 \| 426 \|
----------------------------------------------------------------------------------------

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

1 - filter("T"."C1"=:B1)
3 - filter("T1"."C1"<=10)

Statement 27

create index i2 on tchild2 ( c2, c4 ) 
  compress 1

Index created.

Statement 28

This only executes the scalar subquery for every tenth row. It's also able to use subquery caching, because many rows have the same value for C2.

begin 
  for rws in (  
    select /*+ gather_plan_statistics */t1.c1, t1.c2, c3, c4,  
           count(*) over () rws, 
           case  
             when mod ( c1, 10 ) = 1 then (  
               select max ( c4 ) from tchild2 t2 
               where  t1.c2 = t2.c2 
             ) 
           end  
    from   tchild1 t1 
    where  t1.c4 <= date'2021-01-02' 
    order  by t1.c1, t1.c2 
  ) loop 
    null; 
  end loop; 
end;

Statement processed.

Statement 29

Combined with the index added on ( c2, c4 ), this is a highly efficient plan. The database is able to do an index min/max scan once.

select *  
from   dbms_xplan.display_cursor ( '6qjzyb51thsaw', null, 'IOSTATS LAST')


PLAN_TABLE_OUTPUT
SQL_ID  6qjzyb51thsaw, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */T1.C1, T1.C2, C3, C4, COUNT(*) OVER 
() RWS, CASE WHEN MOD ( C1, 10 ) = 1 THEN ( SELECT MAX ( C4 ) FROM 
TCHILD2 T2 WHERE T1.C2 = T2.C2 ) END FROM TCHILD1 T1 WHERE T1.C4 <= 
DATE'2021-01-02' ORDER BY T1.C1, T1.C2
 
Plan hash value: 3945560260
 
--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |    240 |00:00:00.01 |     425 |
|   1 |  SORT AGGREGATE              |         |      1 |      1 |      1 |00:00:00.01 |       2 |
|   2 |   FIRST ROW                  |         |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| I2      |      1 |      1 |      1 |00:00:00.01 |       2 |
|   4 |  WINDOW SORT                 |         |      1 |    240 |    240 |00:00:00.01 |     425 |
|*  5 |   TABLE ACCESS FULL          | TCHILD1 |      1 |    240 |    240 |00:00:00.01 |     425 |
--------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T2"."C2"=:B1)
   5 - filter("T1"."C4"<=TO_DATE(' 2021-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
 

26 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID 6qjzyb51thsaw, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics /T1.C1, T1.C2, C3, C4, COUNT(*) OVER
() RWS, CASE WHEN MOD ( C1, 10 ) = 1 THEN ( SELECT MAX ( C4 ) FROM
TCHILD2 T2 WHERE T1.C2 = T2.C2 ) END FROM TCHILD1 T1 WHERE T1.C4 <=
DATE'2021-01-02' ORDER BY T1.C1, T1.C2

Plan hash value: 3945560260

--------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \|
--------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 240 \|00:00:00.01 \| 425 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 2 \|
\| 2 \| FIRST ROW \| \| 1 \| 1 \| 1 \|00:00:00.01 \| 2 \|
\|* 3 \| INDEX RANGE SCAN (MIN/MAX)\| I2 \| 1 \| 1 \| 1 \|00:00:00.01 \| 2 \|
\| 4 \| WINDOW SORT \| \| 1 \| 240 \| 240 \|00:00:00.01 \| 425 \|
\|* 5 \| TABLE ACCESS FULL \| TCHILD1 \| 1 \| 240 \| 240 \|00:00:00.01 \| 425 \|
--------------------------------------------------------------------------------------------------

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

3 - access("T2"."C2"=:B1)
5 - filter("T1"."C4"<=TO_DATE(' 2021-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

Statement 30

This rewrites the previous scalar subquery as a join

begin 
  for rws in (  
    select /*+ gather_plan_statistics */t1.c1, t1.c2, t1.c3, t1.c4,  
           count(*) over () rws, 
           max ( t2.c4 )  
    from   tchild1 t1 
    left join tchild2 t2 
    on     t2.c2 = t1.c2 
    and    mod ( t1.c1, 10 ) = 1 
    where  t1.c4 <= date'2021-01-02' 
    group  by t1.c1, t1.c2, t1.c3, t1.c4 
    order  by t1.c1 
  ) loop 
    null; 
  end loop; 
end;

Statement processed.

Statement 31

The database is now no longer able to do a min/max scan of the index on TCHILD2. Instead it goes for a full index scan. So in this case the join is less efficient than scalar subqueries!

select *  
from   dbms_xplan.display_cursor ( 'afr1h188fqznu', null, 'IOSTATS LAST')


PLAN_TABLE_OUTPUT
SQL_ID  afr1h188fqznu, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */T1.C1, T1.C2, T1.C3, T1.C4, 
COUNT(*) OVER () RWS, MAX ( T2.C4 ) FROM TCHILD1 T1 LEFT JOIN TCHILD2 
T2 ON T2.C2 = T1.C2 AND MOD ( T1.C1, 10 ) = 1 WHERE T1.C4 <= 
DATE'2021-01-02' GROUP BY T1.C1, T1.C2, T1.C3, T1.C4 ORDER BY T1.C1
 
Plan hash value: 579427724
 
------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |      1 |        |    240 |00:00:00.13 |     699 |    265 |
|   1 |  WINDOW BUFFER          |         |      1 |  14118 |    240 |00:00:00.13 |     699 |    265 |
|   2 |   SORT GROUP BY         |         |      1 |  14118 |    240 |00:00:00.13 |     699 |    265 |
|*  3 |    HASH JOIN OUTER      |         |      1 |  14118 |    144K|00:00:04.59 |     699 |    265 |
|*  4 |     TABLE ACCESS FULL   | TCHILD1 |      1 |    240 |    240 |00:00:00.01 |     425 |      0 |
|   5 |     INDEX FAST FULL SCAN| I2      |      1 |    100K|    100K|00:00:00.02 |     274 |    265 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("T2"."C2"="T1"."C2" AND MOD("T1"."C1",10)=CASE  WHEN ("T2"."C2" IS NOT NULL) 
              THEN 1 ELSE 1 END )
   4 - filter("T1"."C4"<=TO_DATE(' 2021-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
 
SQL_ID  afr1h188fqznu, child number 1
-------------------------------------
SELECT /*+ gather_plan_statistics */T1.C1, T1.C2, T1.C3, T1.C4, 
COUNT(*) OVER () RWS, MAX ( T2.C4 ) FROM TCHILD1 T1 LEFT JOIN TCHILD2 
T2 ON T2.C2 = T1.C2 AND MOD ( T1.C1, 10 ) = 1 WHERE T1.C4 <= 
DATE'2021-01-02' GROUP BY T1.C1, T1.C2, T1.C3, T1.C4 ORDER BY T1.C1
 
Plan hash value: 579427724
 
------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |      1 |        |    240 |00:00:00.08 |     699 |    265 |
|   1 |  WINDOW BUFFER          |         |      1 |  14118 |    240 |00:00:00.08 |     699 |    265 |
|   2 |   SORT GROUP BY         |         |      1 |  14118 |    240 |00:00:00.08 |     699 |    265 |
|*  3 |    HASH JOIN OUTER      |         |      1 |  14118 |    144K|00:00:03.03 |     699 |    265 |
|*  4 |     TABLE ACCESS FULL   | TCHILD1 |      1 |    240 |    240 |00:00:00.01 |     425 |      0 |
|   5 |     INDEX FAST FULL SCAN| I2      |      1 |    100K|    100K|00:00:00.01 |     274 |    265 |
------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 

Rows 1 - 50. More rows exist.

PLAN_TABLE_OUTPUT
SQL_ID afr1h188fqznu, child number 0
-------------------------------------
SELECT /+ gather_plan_statistics /T1.C1, T1.C2, T1.C3, T1.C4,
COUNT(*) OVER () RWS, MAX ( T2.C4 ) FROM TCHILD1 T1 LEFT JOIN TCHILD2
T2 ON T2.C2 = T1.C2 AND MOD ( T1.C1, 10 ) = 1 WHERE T1.C4 <=
DATE'2021-01-02' GROUP BY T1.C1, T1.C2, T1.C3, T1.C4 ORDER BY T1.C1

Plan hash value: 579427724

------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| Reads \|
------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 240 \|00:00:00.13 \| 699 \| 265 \|
\| 1 \| WINDOW BUFFER \| \| 1 \| 14118 \| 240 \|00:00:00.13 \| 699 \| 265 \|
\| 2 \| SORT GROUP BY \| \| 1 \| 14118 \| 240 \|00:00:00.13 \| 699 \| 265 \|
\|* 3 \| HASH JOIN OUTER \| \| 1 \| 14118 \| 144K\|00:00:04.59 \| 699 \| 265 \|
\|* 4 \| TABLE ACCESS FULL \| TCHILD1 \| 1 \| 240 \| 240 \|00:00:00.01 \| 425 \| 0 \|
\| 5 \| INDEX FAST FULL SCAN\| I2 \| 1 \| 100K\| 100K\|00:00:00.02 \| 274 \| 265 \|
------------------------------------------------------------------------------------------------------

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

3 - access("T2"."C2"="T1"."C2" AND MOD("T1"."C1",10)=CASE WHEN ("T2"."C2" IS NOT NULL)
THEN 1 ELSE 1 END )
4 - filter("T1"."C4"<=TO_DATE(' 2021-01-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

SQL_ID afr1h188fqznu, child number 1
-------------------------------------
SELECT /+ gather_plan_statistics /T1.C1, T1.C2, T1.C3, T1.C4,
COUNT(*) OVER () RWS, MAX ( T2.C4 ) FROM TCHILD1 T1 LEFT JOIN TCHILD2
T2 ON T2.C2 = T1.C2 AND MOD ( T1.C1, 10 ) = 1 WHERE T1.C4 <=
DATE'2021-01-02' GROUP BY T1.C1, T1.C2, T1.C3, T1.C4 ORDER BY T1.C1

Plan hash value: 579427724

------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \| A-Time \| Buffers \| Reads \|
------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 240 \|00:00:00.08 \| 699 \| 265 \|
\| 1 \| WINDOW BUFFER \| \| 1 \| 14118 \| 240 \|00:00:00.08 \| 699 \| 265 \|
\| 2 \| SORT GROUP BY \| \| 1 \| 14118 \| 240 \|00:00:00.08 \| 699 \| 265 \|
\|* 3 \| HASH JOIN OUTER \| \| 1 \| 14118 \| 144K\|00:00:03.03 \| 699 \| 265 \|
\|* 4 \| TABLE ACCESS FULL \| TCHILD1 \| 1 \| 240 \| 240 \|00:00:00.01 \| 425 \| 0 \|
\| 5 \| INDEX FAST FULL SCAN\| I2 \| 1 \| 100K\| 100K\|00:00:00.01 \| 274 \| 265 \|
------------------------------------------------------------------------------------------------------

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

Statement 32

A slow function

create or replace function f ( p int )  
  return int as 
  val number; 
begin 
 
  for i in 1 .. 250000 loop 
    val := ln ( exp ( i ) ); 
  end loop; 
   
  return p; 
   
end f;

Function created.

Statement 33

These queries both call the slow function in their where clause. The input is the same for every row, so the result is also the same. The first query calls the function directly, so must execute it once per row (5 times). The second places it in a scalar subquery. This allows the database to cache its result. So the second query is ~5x faster than the first.

declare 
  start_time pls_integer; 
begin 
  start_time := dbms_utility.get_time (); 
  for rws in (  
    with rws as ( 
      select level x, 1 y from dual 
      connect by level <= 5 
    ) 
      select /*+ gather_plan_statistics */* from rws 
      where  1 = f ( y ) 
  ) loop 
    null; 
  end loop; 
  dbms_output.put_line (  
    'Run time ' || to_char ( dbms_utility.get_time () - start_time, '9,990' )  
  ); 
  start_time := dbms_utility.get_time (); 
  for rws in (  
    with rws as ( 
      select level x, 1 y from dual 
      connect by level <= 5 
    ) 
      select /*+ gather_plan_statistics */* from rws 
      where  1 = ( select f ( y ) from dual ) 
  ) loop 
    null; 
  end loop; 
  dbms_output.put_line (  
    'Run time ' || to_char ( dbms_utility.get_time () - start_time, '9,990' )  
  ); 
end;

Statement processed.
Run time    337
Run time     69

Statement 34

select *  
from   dbms_xplan.display_cursor ( 'ags52fggjq5pt', null, 'ROWSTATS LAST +PREDICATE')


PLAN_TABLE_OUTPUT
SQL_ID  ags52fggjq5pt, child number 0
-------------------------------------
WITH RWS AS ( SELECT LEVEL X, 1 Y FROM DUAL CONNECT BY LEVEL <= 5 ) 
SELECT /*+ gather_plan_statistics */* FROM RWS WHERE 1 = F ( Y )
 
Plan hash value: 2403765415
 
-------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      5 |
|*  1 |  VIEW                         |      |      1 |      1 |      5 |
|   2 |   CONNECT BY WITHOUT FILTERING|      |      1 |        |      5 |
|   3 |    FAST DUAL                  |      |      1 |      1 |      1 |
-------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("F"("Y")=1)
 

21 rows selected.

PLAN_TABLE_OUTPUT
SQL_ID ags52fggjq5pt, child number 0
-------------------------------------
WITH RWS AS ( SELECT LEVEL X, 1 Y FROM DUAL CONNECT BY LEVEL <= 5 )
SELECT /+ gather_plan_statistics /* FROM RWS WHERE 1 = F ( Y )

Plan hash value: 2403765415

-------------------------------------------------------------------------
\| Id \| Operation \| Name \| Starts \| E-Rows \| A-Rows \|
-------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 5 \|
\|* 1 \| VIEW \| \| 1 \| 1 \| 5 \|
\| 2 \| CONNECT BY WITHOUT FILTERING\| \| 1 \| \| 5 \|
\| 3 \| FAST DUAL \| \| 1 \| 1 \| 1 \|
-------------------------------------------------------------------------

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

1 - filter("F"("Y")=1)

Statement 35

select *  
from   dbms_xplan.display_cursor ( '6f7pnwp98qk32', null, 'ROWSTATS LAST +PREDICATE')


PLAN_TABLE_OUTPUT
SQL_ID  6f7pnwp98qk32, child number 0
-------------------------------------
WITH RWS AS ( SELECT LEVEL X, 1 Y FROM DUAL CONNECT BY LEVEL <= 5 ) 
SELECT /*+ gather_plan_statistics */* FROM RWS WHERE 1 = ( SELECT F ( Y 
) FROM DUAL )
 
Plan hash value: 2522534383
 
--------------------------------------------------------------------------
| Id  | Operation                      | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |      1 |        |      5 |
|*  1 |  FILTER                        |      |      1 |        |      5 |
|   2 |   VIEW                         |      |      1 |      1 |      5 |
|   3 |    CONNECT BY WITHOUT FILTERING|      |      1 |        |      5 |
|   4 |     FAST DUAL                  |      |      1 |      1 |      1 |
|   5 |   FAST DUAL                    |      |      1 |      1 |      1 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(=1)
 

24 rows selected.

Speeding up scalar subqueries - SQL Ask TOM Office Hours

Additional Information