Performance impact of sub-query factoring

Oracle introduced the WITH clause, or sub-query factoring clause, in Oracle 9.2. The WITH clause is processed as an inline view or resolved as a temporary table. The WITH clause improves the readability of the code for sure, but may not yield performance benefits in all cases as one might have thought. Each case should be specifically examined to assess performance benefits. I will discuss a SQL with has UNION.

Original SQL Statement without sub-query factoring

SELECT D1.ID1, D2.ID2

FROM MAP_HISTORY  MH, DEPT D1, DEPT D2

WHERE     UPD_TABLE = 'ROSTER'

       AND D1.ID = MH.NEW_VALUE

       AND D2.ID = MH.OLD_VALUE

UNION

SELECT D1.ID1, D1ID2

FROM MAP_HISTORY  MH, DEPT D1, DEPT D2

WHERE     UPD_TABLE = 'ROSTER'

       AND D1.ID = MH.NEW_VALUE

       AND D2.ID = MH.OLD_VALUE;

As you could see from the SQL above, the MAP_HISTORY  table is accessed in each UNION. The EXPLAIN PLAN of this statement shows that MAP_HISTORY  table was accessed twice. The cost of each index access was 13. The total cost for two accesses was 26

SQL Autotrace statistics show that this query accessed 3745 blocks, as shown below.

Statistics

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

          0  recursive calls

          0  db block gets

       3745  consistent gets

          0  physical reads

          0  redo size

      18535  bytes sent via SQL*Net to client

       1057  bytes received via SQL*Net from client

         65  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

        959  rows processed

Modified SQL with sub-query factoring

WITH MH as

  ( SELECT NEW_VALUE, OLD_VALUE

    FROM MAP_HISTORY

    WHERE UPD_TABLE = 'ROSTER' )

SELECT D1.ID1, D2ID2

  FROM  MH, DEPT D1, DEPT D2

 WHERE D1.ID = MH.NEW_VALUE

       AND D2.ID = MH.OLD_VALUE

UNION

SELECT D1.ID1, D1.ID2

  FROM MH, DEPT D1, DEPT D2

 WHERE D1.ID = MH.NEW_VALUE

       AND D2.ID = MH.OLD_VALUE;

The EXPLAIN PLAN shows that Oracle created a temporary table. The cost to create the temporary table was 13. The temporary table was accessed twice, each with a cost of 4. So the total cost for the new structure was 13 + 4 + 4 = 21 which is less than 26 in the original query, but the execution plan changed and overall cost increased to 739. (Note: cost may not translate into actual time taken by the query. This is a completely separate topic and discussion for some other time).

As could see from the AUTOTRACE statistics, the number of consistent gets are 3768, but also there are 10 physical reads. There are also physical writes to write out the temporary table.

Statistics

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

         65  recursive calls

         20  db block gets

       3768  consistent gets

         10  physical reads

       1156  redo size

      18535  bytes sent via SQL*Net to client

       1057  bytes received via SQL*Net from client

         65  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

        959  rows processed

What factors impact performance of sub-query factoring?

• Change in Execution Plan which may be slower
• Cost to write out data to the temporary tablespace 
• Cost to read data from the temporary table
• No indexes on temporary table and hence Oracle may choose HASH join in the SQL with the WITH clause
• Frequency of temporary table access