Using WITH

CREATE OR REPLACE FORCE VIEW myview (SERVER_NAME, DB_NAME, COLLECTION_DATE, MOUNT_NAME, ALLOCATED_SPACE,
USED_SPACE, VERSION) AS
select
SERVER_NAME,DB_NAME,COLLECTION_DATE,MOUNT_NAME,ALLOCATED_SPACE,USED_SPACE,VERSION from schema1.table1 -- 1.5 million rows union
select
SERVER_NAME,DB_NAME,COLLECTION_DATE,MOUNT_NAME,ALLOCATED_SPACE,USED_SPACE,VERSION from schema2.table1 -- 1 million rows
/

Which one of the following is most efficient? is any re-write required to make it work more efficient?

1.

select null,b.name,a.valu FROM
(

select '1' num,count(distinct server_name) valu from myview where collection_date > sysdate-31
union
select '2' num,count(distinct db_name) valu from myview where collection_date > sysdate-31
) a,
(

select '1' num,'#Servers' name from dual union
select '2' num,'#DBs' name from dual
) b
where a.num = b.num
;

2.

select null,b.name,a.valu FROM
(

with aa as
(select * from dbatest.myview where collection_date > sysdate-31)
select '1' num,count(distinct server_name) valu from aa union all
select '2' num,count(distinct db_name) valu from aa )a,
(select '1' num,'#Servers' name from dual
union
select '2' num,'#DBs' name from dual
) b
where a.num = b.num
;

Thanks

--
http://www.freelists.org/webpage/oracle-l