Is Oracle SQL WITH statement better than subselect

Question

I have an Oracle query that is over 1000 lines long. Breaking it up and/or using stored procedures is not an option here. I am about to make it even longer. Which of these has better performance? I find the WITH version easier to read.

  /* subselect */
  select col01
        ,col02
  from (
    select case col01 when 'X' then 1 else 2 end col01
          ,case col02 when 'Y' then 3 else 4 end col02
    from (      
      select upper(col01) col01
            ,upper(col02) col02
      from (      
        select 'x' as col01
              ,'y' as col02
        from dual
      )
    )
  )
  ;
  ---------------------------------------
  /* with statement */
  with qry01 as (
  select 'x' as col01
        ,'y' as col02
  from dual
  )
  ,qry02 as (
  select upper(col01) col01
        ,upper(col02) col02
  from qry01      
  )
  ,qry03 as (
  select case col01 when 'X' then 1 else 2 end col01
        ,case col02 when 'Y' then 3 else 4 end col02
  from qry02      
  )
  select col01
        ,col02
  from qry03      
  ;

Answer 1

I also find the CTE WITH expression easier to read. Apart from that there are reasons to prefer it.

• You can use CTE subqueries several times in your main query.
• The Oracle Optimizer can store the result of an CTE subquery in a temporary table which is created on-the-fly. (Note, you can even force it by undocumented hint /*+ MATERIALIZE */)
• You can use CTE recursively, see Recursive Subquery Factoring