SQL & PL/SQL

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??