Re: SQL joins ?
Date: 1996/01/15
Message-ID: <4de2q9$ck_at_ixnews8.ix.netcom.com>#1/1
vgoel_at_pts.mot.com (Vikram Goel) wrote:
>Peter,
>No need to do a join, try the following select:
> select substr(a.file_name,1,15) file_name,
> substr(a.tablespace_name,1,10) table_space_name,
> sum(a.bytes) total,sum(b.bytes) free
> from dba_data_files a, dba_free_space b
> where a.tablespace_name = b.tablespace_name
> group by a.file_name,a.tablespace_name:
I don't think this will give the desired result because of how SQL
normalizes results in a join. For example if the left table has only 1
row and the right table has 2, the rows selected from the left table
will be repeated twice. This will throw off the sum() for any columns
from the left table. (Am I explaining this clearly? I'm not sure.) The
above SQL would only give the correct results if there was 1 free
space per file.
What you really need to do is a join the dba_data_files table with a
dynamic view of the freespace table, where the aggregation (summing)
of dba_free_space is done *before* the join. Something like this
should work:
select file_name
, tablespace_name
, bytes total
, free
from sys.dba_data_files a
, (select file_id, sum(bytes) free
from sys.dba_free_space
group by file_id) b
where a.file_id = b.file_id;
--
Chuck Hamilton
chuckh_at_ix.netcom.com
Never share a foxhole with anyone braver than yourself!
Received on Mon Jan 15 1996 - 00:00:00 CET