Skip to Main Content

Query with WITH clause vs. one with a subquery

The DonbotOct 6 2014 — edited Oct 7 2014

When I run this query, it returns in under a second:


select key1, key2, key3, key4, key5, key6, key7, count(key1) as num_recs

from(
    select key1, key2, key3, key4, nvl(key5, -1) as key5, nvl(key6, -1) as key6, key7
    from data_table_a a
    union all
    select key1, key2, key3, key4, nvl(key5, -1) as key5, nvl(key6, -1) as key6, key7
    from data_table_b b
    union all
    select key1, key2, key3, key4, nvl(key5, -1) as key5, nvl(key6, -1) as key6, key7
    from data_table_c c
    where table_value > 0
)
group by key1, key2, key3, key4, key5, key6, key7;

However, when I run this query, it takes almost an hour:

  

with d as
(

    select key1, key2, key3, key4, nvl(key5, -1) as key5, nvl(key6, -1) as key6, key7

   from data_table_a a
    union all
    select key1, key2, key3, key4, nvl(key5, -1) as key5, nvl(key6, -1) as key6, key7
    from data_table_b b
    union all
    select key1, key2, key3, key4, nvl(key5, -1) as key5, nvl(key6, -1) as key6, key7
    from data_table_c c
    where table_value > 0
)
select key1, key2, key3, key4, key5, key6, key7, count(key1) as num_recs
from d
group by key1, key2, key3, key4, key5, key6, key7;

as does this:

with d as

(

  select key1, key2, key3, key4, key5, key6, key7, count(key1) as num_recs

  from

  (


      select key1, key2, key3, key4, nvl(key5, -1) as key5, nvl(key6, -1) as key6, key7
      from data_table_a a
      union all
      select key1, key2, key3, key4, nvl(key5, -1) as key5, nvl(key6, -1) as key6, key7
      from data_table_b b
      union all
      select key1, key2, key3, key4, nvl(key5, -1) as key5, nvl(key6, -1) as key6, key7
      from data_table_c c
      where table_value > 0
  )

  group by key1, key2, key3, key4, key5, key6, key7

)

select * from d;

Any idea why the first two would run so much faster than the other two?

Comments
Post Details
Added on Oct 6 2014
7 comments
1,876 views