Re: Using WITH

From: Fergal Taheny <ftaheny_at_gmail.com>
Date: Fri, 30 Nov 2012 22:15:02 +0000
Message-ID: <CAOuMUT7DL4hcLrJ2PvqYi+UeY-Th5bsHf02k8pEtMLRFHSFmfw_at_mail.gmail.com>

Hi,
I guess the second one but I think you're approaching this in the wrong way.

You should scan the dataset once to get both counts in one record and then unpivot your data to get 2 records.

Something like this

With a as
( select count(distinct server) as servers, count(distinct dbname) dbs from ..... where ...)
Select '#servers' as name, servers as value from a Union
Select '#dbs' as name, dbs as value from a;

There are more sophisticated ways to do the unpivot but for just 2 values this way is probably ok.

Regards,
Fergal

On 30 Nov 2012 15:35, "oracledba" <oracledba71_at_gmail.com> wrote:
>
> 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
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>

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

Received on Fri Nov 30 2012 - 23:15:02 CET