Skip to Main Content

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!

WITH-clause: performance- or readability enhancement?

JasperAMay 13 2008 — edited May 14 2008
Hi,

I was preparing for a course on with I also discuss the with-clause. Assuming it would perform better than just writing down two extra statements, I tried out the explain plan and found out that the with-clause actually performes worse. The example:

Statment including WITH-clause:

SQL> with country as
2 (select c.country_id
3 , c.country_name
4 , c.region_id
5 , r.region_name
6 from countries c
7 , regions r
8 where c.region_id = r.region_id)
9 select l.street_address
10 , l.city
11 , c.region_name
12 from locations l
13 , country c
14 where l.country_id = c.country_id
15 and c.country_id = 'UK'
16 union
17 select l.street_address
18 , l.city
19 , c.region_name
20 from locations l
21 , country c
22 where l.country_id = c.country_id
23 and c.country_id = 'NL'
24 ;

The cost of this executing is 11 as the explain plan tells me (dbms_xplan.display_cursor)

Now, without WITH-clause:

SQL> select l.street_address
2 , l.city
3 , c.region_name
4 from locations l
5 , (select c.country_id
6 , c.country_name
7 , c.region_id
8 , r.region_name
9 from countries c
10 , regions r
11 where c.region_id = r.region_id) c
12 where l.country_id = c.country_id
13 and c.country_id = 'UK'
14 union
15 select l.street_address
16 , l.city
17 , c.region_name
18 from locations l
19 , (select c.country_id
20 , c.country_name
21 , c.region_id
22 , r.region_name
23 from countries c
24 , regions r
25 where c.region_id = r.region_id) c
26 where l.country_id = c.country_id
27 and c.country_id = 'NL'
28 ;

Now explain plan tells me the cost is just 6! Why does this not perform faster. Are there examples at which it does? Or is the WITH-clause only to make the statements syntax more readable??
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 11 2008
Added on May 13 2008
16 comments
10,237 views