SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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?

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 4 2014

Added on Oct 6 2014

#sql, #with

7 comments

2,082 views