Subquery Factoring

Oracle introduce WITH clause in the query starting from Oracle 9i. In few articles that I found while googling,

we can use this WITH clause to write subquery factoring or recursive query.

In this chance, I would like to show you the benefit of “Subquery Factoring” against Full Table Scan and also we

can see the same method against Unique Index Scan.

For the first case, I would like to show the comparison between the traditional and subquery factoring methods

for Full Table Scan operation and for the second case; for Unique Index Scan. The objective is to see the

reduction in LIO and PIO for MINUS operation which use FTS.

Subquery Factoring for FTS

Original Query

SQL> select /*+ parallel(a,8) */ subscriber_no from subscriber a

2 where sub_status = 'A'

3 minus

4 select /*+ parallel(a,8) */ subscriber_no from subscriber a

5 where sub_status = 'C';

41220406 rows selected.

Elapsed: 00:04:14.03

Execution Plan

----------------------------------------------------------

------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | TQ |IN-OUT| PQ Distrib |

------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 34M| 596M| | 99966 | | | |

| 1 | PX COORDINATOR | | | | | | | | |

| 2 | PX SEND QC (RANDOM) | :TQ10002 | | | | | Q1,02 | P->S | QC (RAND) |

| 3 | MINUS | | | | | | Q1,02 | PCWP | |

| 4 | SORT UNIQUE | | 34M| 298M| 665M| 49983 | Q1,02 | PCWP | |

| 5 | PX RECEIVE | | 34M| 298M| | 34566 | Q1,02 | PCWP | |

| 6 | PX SEND HASH | :TQ10000 | 34M| 298M| | 34566 | Q1,00 | P->P | HASH |

| 7 | PX BLOCK ITERATOR | | 34M| 298M| | 34566 | Q1,00 | PCWC | |

|* 8 | TABLE ACCESS FULL| SUBSCRIBER | 34M| 298M| | 34566 | Q1,00 | PCWP | |

| 9 | SORT UNIQUE | | 34M| 298M| 665M| 49983 | Q1,02 | PCWP | |

| 10 | PX RECEIVE | | 34M| 298M| | 34566 | Q1,02 | PCWP | |

| 11 | PX SEND HASH | :TQ10001 | 34M| 298M| | 34566 | Q1,01 | P->P | HASH |

| 12 | PX BLOCK ITERATOR | | 34M| 298M| | 34566 | Q1,01 | PCWC | |

|* 13 | TABLE ACCESS FULL| SUBSCRIBER | 34M| 298M| | 34566 | Q1,01 | PCWP | |

------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

8 - filter("SUB_STATUS"='A')

13 - filter("SUB_STATUS"='C')

Note

-----

- cpu costing is off (consider enabling it)

Statistics

----------------------------------------------------------

1258 recursive calls

140 db block gets

2303047 consistent gets

2353434 physical reads

632 redo size

799460311 bytes sent via SQL*Net to client

30228789 bytes received via SQL*Net from client

2748029 SQL*Net roundtrips to/from client

16 sorts (memory)

8 sorts (disk)

41220406 rows processed

SubQuery Factoring

SQL> with qry as

2 (

3 select /*+ parallel(a,8) */ subscriber_no,

4 case

5 when sub_status = 'A' then 1

6 when sub_status = 'C' then 2

7 else 0

8 end as ss

9 from subscriber a

10 where sub_status in ('A', 'C')

11 )

12 select subscriber_no from qry where ss = 1

13 minus

14 select subscriber_no from qry where ss = 2;

41220406 rows selected.

Elapsed: 00:04:20.10

Execution Plan

----------------------------------------------------------

-------------------------------------------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost | TQ |IN-OUT| PQ Distrib |

-------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 69M| 2122M| | 87770 | | | |

| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | |

| 2 | PX COORDINATOR | | | | | | | | |

| 3 | PX SEND QC (RANDOM) | :TQ10001 | 69M| 596M| | 34566 | Q1,01 | P->S | QC (RAND) |

| 4 | LOAD AS SELECT | | | | | | Q1,01 | PCWP | |

| 5 | PX RECEIVE | | 69M| 596M| | 34566 | Q1,01 | PCWP | |

| 6 | PX SEND ROUND-ROBIN | :TQ10000 | 69M| 596M| | 34566 | Q1,00 | P->P | RND-ROBIN |

| 7 | PX BLOCK ITERATOR | | 69M| 596M| | 34566 | Q1,00 | PCWC | |

|* 8 | TABLE ACCESS FULL | SUBSCRIBER | 69M| 596M| | 34566 | Q1,00 | PCWP | |

| 9 | PX COORDINATOR | | | | | | | | |

| 10 | PX SEND QC (RANDOM) | :TQ20002 | | | | | Q2,02 | P->S | QC (RAND) |

| 11 | MINUS | | | | | | Q2,02 | PCWP | |

| 12 | SORT UNIQUE | | 69M| 1061M| 1598M| 43885 | Q2,02 | PCWP | |

| 13 | PX RECEIVE | | 69M| 1061M| | 719 | Q2,02 | PCWP | |

| 14 | PX SEND HASH | :TQ20000 | 69M| 1061M| | 719 | Q2,00 | P->P | HASH |

|* 15 | VIEW | | 69M| 1061M| | 719 | Q2,00 | PCWP | |

| 16 | PX BLOCK ITERATOR | | 69M| 596M| | 719 | Q2,00 | PCWC | |

| 17 | TABLE ACCESS FULL| SYS_TEMP_0FD9D6611_C3DD254C | 69M| 596M| | 719 | Q2,00 | PCWP | |

| 18 | SORT UNIQUE | | 69M| 1061M| 1598M| 43885 | Q2,02 | PCWP | |

| 19 | PX RECEIVE | | 69M| 1061M| | 719 | Q2,02 | PCWP | |

| 20 | PX SEND HASH | :TQ20001 | 69M| 1061M| | 719 | Q2,01 | P->P | HASH |

|* 21 | VIEW | | 69M| 1061M| | 719 | Q2,01 | PCWP | |

| 22 | PX BLOCK ITERATOR | | 69M| 596M| | 719 | Q2,01 | PCWC | |

| 23 | TABLE ACCESS FULL| SYS_TEMP_0FD9D6611_C3DD254C | 69M| 596M| | 719 | Q2,01 | PCWP | |

-------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

8 - filter("SUB_STATUS"='A' OR "SUB_STATUS"='C')

15 - filter("SS"=1)

21 - filter("SS"=0)

Note

-----

- cpu costing is off (consider enabling it)

Statistics

----------------------------------------------------------

2511 recursive calls

83554 db block gets

1316155 consistent gets

1368259 physical reads

304360 redo size

799460311 bytes sent via SQL*Net to client

30228789 bytes received via SQL*Net from client

2748029 SQL*Net roundtrips to/from client

28 sorts (memory)

8 sorts (disk)

41220406 rows processed

Please see the highlighted parts, the PIO and LIO reduced by 100% (half) when we use subquery factoring. It

can be happened because Oracle scans SUBSCRIBER only once and keeps the result in the memory and it will

be reused for second, third operation and so on. If we see more in the execution plan, there is increment in the

TEMP space usage, because when needed, Oracle will spill the information/data from the buffer cache into the

Temporary Tablespace. This is acceptable if there is enough space on the Temporary Tablespace and also fast

enough disk. Due to this “temporary” operation, Oracle produce more redo information (approx.. 300kB in this

test case)

Subquery Factoring for Index Unique Scan

Original Query

SQL> select subscriber_no from tksappo.subscriber

2 where subscriber_no = 7266421

3 minus

4 select subscriber_no from tksappo.subscriber

5 where subscriber_no = 6566380;

Elapsed: 00:00:00.06

Execution Plan

----------------------------------------------------------

--------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

--------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 14 | 78 |

| 1 | MINUS | | | | |

|* 2 | INDEX UNIQUE SCAN| SUBSCRIBER_PK | 1 | 7 | 1 |

|* 3 | INDEX UNIQUE SCAN| SUBSCRIBER_PK | 1 | 7 | 1 |

--------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("SUBSCRIBER_NO"=7266421)

3 - access("SUBSCRIBER_NO"=6566380)

Note

-----

- cpu costing is off (consider enabling it)

Statistics

----------------------------------------------------------

565 recursive calls

0 db block gets

138 consistent gets

26 physical reads

0 redo size

523 bytes sent via SQL*Net to client

492 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

1 rows processed

SubQuery Factoring

SQL> with qry as

2 (

3 select subscriber_no,

4 case

5 when subscriber_no = 7266421 then 1

6 when subscriber_no = 6566380 then 2

7 else 0

8 end as ss

9 from tksappo.subscriber

10 where subscriber_no in (7266421, 6566380)

11 )

12 select subscriber_no from qry where ss = 1

13 minus

14 select subscriber_no from qry where ss = 2;

Elapsed: 00:00:00.03

Execution Plan

----------------------------------------------------------

------------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 2 | 64 | 80 |

| 1 | TEMP TABLE TRANSFORMATION | | | | |

| 2 | LOAD AS SELECT | | | | |

| 3 | INLIST ITERATOR | | | | |

|* 4 | INDEX UNIQUE SCAN | SUBSCRIBER_PK | 2 | 14 | 1 |

| 5 | MINUS | | | | |

| 6 | SORT UNIQUE | | 2 | 32 | 40 |

|* 7 | VIEW | | 2 | 32 | 2 |

| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6613_C3DD254C | 2 | 14 | 2 |

| 9 | SORT UNIQUE | | 2 | 32 | 40 |

|* 10 | VIEW | | 2 | 32 | 2 |

| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6613_C3DD254C | 2 | 14 | 2 |

------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

4 - access("SUBSCRIBER_NO"=6566380 OR "SUBSCRIBER_NO"=7266421)

7 - filter("SS"=1)

10 - filter("SS"=0)

Note

-----

- cpu costing is off (consider enabling it)

Statistics

----------------------------------------------------------

1151 recursive calls

12 db block gets

342 consistent gets

68 physical reads

1568 redo size

523 bytes sent via SQL*Net to client

492 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

19 sorts (memory)

0 sorts (disk)

1 rows processed

We have different result when we use Unique Index Scan. There is an increment in LIO and PIO. So far what I can see in this increment is due to:

- Temporary table creation (we don’t have it in the original method)

Conclusion

1. Subquery factoring is good to simplify query (more readable) and it can give significant improvement when we are working with FTS and scan the same object over and over again.

2. There is no silver bullet in SQL tuning until unless we did some test to confirm it. In this example, FTS and Index Scan gives different result when we rewrite the SQL using WITH clause.

3. Different version of Oracle will produce different result, all above scenarios were tested on Oracle 10.2.0.5 (PET CM database)