RE: SQL Query

Ah, I see your point, now, and that's quite correct. Which means I can either aggregate on fileid, or aggregate by table and join. Nice catch, and mystery solved. No bug report.

Take care and have a great weekend!
Bambi.
-----Original Message-----
Sent: Friday, November 14, 2003 2:50 PM
To: Multiple recipients of list ORACLE-L

The aggregate function operates last, on a result set. Why I suggested to suppress the GROUP BY is that then you would have seen that the number of bytes from each datafile is returnedtoo many times. If you have F1 and F2 associated to your database, with E1 and E2 in F1 and E3 in F2 (I hope the meaning of my symbols is obvious) by joining on the tablespace name you get

                 F1 E1
                 F1 E2
                 F1 E3
                 F2 E1
                 F2 E2
                 F2 E3

Quite obviously, when you sum up the result is much too big. For files in that case its 3 times too big for each, and for extents two times too big for each.

By contrast, the inline views in the query which works force the aggregates to be computed _before_ the final calculation.

SF

"Bellow, Bambi" wrote:
>
> But Stephane, I am aggregating by tablespace for both extents and for
> data_files. There is nothing here that is separating out anything by
> datafile. And, if I take away the GROUP BY, I lose the ability to
aggregate
> at all, which is the point of this...
>
> -----Original Message-----
> Sent: Friday, November 14, 2003 1:09 PM
> To: Multiple recipients of list ORACLE-L
>
> Bambi,
>
> Your second query is wrong because all extents in a tablespace don't
> necessarily belong to the same datafile. Try the query without the
> aggregate functions and the GROUP BY, and you'll understand your
> mistake.
>
> HTH,
>
> SF
>
> "Bellow, Bambi" wrote:
> >
> > Friends --
> >
> > Why would these two queries return different results?
> >
> > This query works.
> >
> > SQL> l
> > 1 select
> >

>
a.tablespace_name,megs_allocated,megs_used,round(megs_used/b.megs_allocated,
> > 2)*100 pct
> > 2 from (select tablespace_name,sum(bytes)/(1024*1024) megs_used
> > 3 from dba_extents group by tablespace_name) a,
> > 4 (select tablespace_name,sum(bytes)/(1024*1024) megs_allocated
> > 5 from dba_data_files group by tablespace_name) b
> > 6 where a.tablespace_name=b.tablespace_name
> > 7* and a.tablespace_name='NAUAT'
> > SQL> /
> >
> > TABLESPACE_NAME MEGS_ALLOCATED MEGS_USED PCT
> > ------------------------------ -------------- ---------- ----------
> > NAUAT 22924.25 11509 50
> >
> > This query does not work
> >
> > 1 select a.tablespace_name,sum(b.bytes)/(1024*1024) megs_allocated,
> > 2 sum(a.bytes)/(1024*1024) megs_used,
> > 3 round(sum(a.bytes)/sum(b.bytes),4)*100 pct
> > 4 from dba_extents a, dba_data_files b
> > 5 where a.tablespace_name=b.tablespace_name
> > 6 and a.tablespace_name='NAUAT'
> > 7* group by a.tablespace_name,b.tablespace_name
> > SQL> /
> >
> > TABLESPACE_NAME MEGS_ALLOCATED MEGS_USED PCT
> > ------------------------------ -------------- ---------- ----------
> > NAUAT 31773010.5 23018 .07
> >
> > Bambi.
> > --
> --

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bellow, Bambi
  INET: bbellow_at_chi.navtech.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).