Inline query versus with clause for performance issue

Question

I have a query with structure somewhat as below:

select (select first_name 
        from sub_table_1,
             sub_table_2,
             sub_table_3 
        where <where clause for sub_table 1,2,4> 
          and sub_table_1.col_1=table_1.col_1) first_name ,
       (select last_name 
        from sub_table_1,
             sub_table_2,
             sub_table_3 
        where <where clause for sub_table 1,2,4> 
              and sub_table_1.col_1=table_1.col_1) last_name ,
       <other select clause for table 1,2,3>
from table_1,
     table_2,
     table_3
where <Where clauses>
union
select (select first_name 
        from sub_table_1,
             sub_table_2,
             sub_table_3 
        where <where clause for sub_table 1,2,3> 
          and sub_table_1.col_1=table_4.col_1) first_name ,
       (select last_name 
        from sub_table_1,
             sub_table_2,
             sub_table_3 
        where <where clause for sub_table 1,2,3> 
          and sub_table_1.col_1=table_4.col_1) last_name ,
       <other select clause for table 4,5,6>
from table_4,
     table_5,
     table_6
where <Where clauses>

I want to as i whether taking the part:

(select first_name , last_name
    from sub_table_1,
    sub_table_2,
    sub_table_3 
    where <where clause for sub_table 1,2,3> 
    and sub_table_1.col_1=table_4.col_1) first_name ,
        (select last_name 
    from sub_table_1,
    sub_table_2,
    sub_table_3 
    where <where clause for sub_table 1,2,3> )

will help me in making the query more fast and better or will affect adversely.

also note that, this sub query may fetch about 10000 records in it self.

please help

Answer 1

Common Table Expressions (the WITH clause) have, as far as I know, two potential impacts on the query.

• They allow the optimiser to materialise the result set, effectively creating a temporary table to hold the result. The conditions under which it does this are not documented as far as I know, but I've read that it happens if the size of the set exceeds the sort area size for the session, and if the result would be used more than once in the query.

• There are sometimes bugs associated with CTE query optimisation.

So performance-wise, if you have a large result set that is used muttiple times then I'd consider it a better candidate for a CTE.

I tend to use them a lot in both Oracle and PostgreSQL as I find they make the code much clearer than having nested inline views.