Using WITH
- From: oracledba <oracledba71@xxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Fri, 30 Nov 2012 10:33:45 -0500
All,
I have the following view:
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
