Re: help with query (summarization)

From: Fred <fpuhan_at_precise.com>
Date: 4 Jun 2002 14:49:04 -0400
Message-ID: <20020604144904245-0400@news.his.com>

In <20020604135317795-0400_at_news.his.com> Fred wrote:
> In <EY6L8.2378$H67.13089_at_tor-nn1.netcom.ca> Syltrem wrote:

>> I`m having trouble finding the best way (or any way at all) to get a 
>> sum by tablespace, for the column "bytes" in this query. If possible, 

>
> Have you tried using COMPUTE SUM OF BYTES ON REPORT?

A little clarification, if you will:

I think you're trying to do too much by converting numeric data into character strings using your DECODE statment. I think you could achieve the same result by declaring a COLUMN SIZE FORMAT 99,999.90 and then just querying on the BYTES/1024/1024 in your execution statment. Thus:

SET LINESIZE 128
SET PAGESIZE 66 COL SIZE HEADING "Size" FORMAT 99,999,90 -- other column declarations here

BREAK ON REPORT
COMPUTER SUM LABEL "Total" of SIZE on REPORT

SELECT

	SUBSTR(owner,1,6) "Owner",
	SUBSTR(tablespace_name,1,17) "Tablespace",
	SUBSTR(segment_type,1,2) "Ty",
	SUBSTR(semgnet_name,1,31) "Segment",
	ROUND(bytes/1024/1024,2) "Size",
	extents "# Ext",
	next_extent "ExtSzK"
FROM
	dba_segments
WHERE
	segment_name LIKE UPPER('&Segment_Name') escape '\'
AND
	tablespace_name LIKE UPPER('&Tablespace_name')
GROUP BY
	owner,
	segment_name,
	segment_type,
	tablespace_name,
	bytes,
	extents,
	next_extent
HAVING
	extents >= 0&min_nb_of_extents
AND
	bytes >= 0&min_size_mb*1024*1024
ORDER BY
	owner,
	segment_name,
	segment_type DESC

/

In fact, when I ran your original query against my own instance, I noticed the SIZE column returned one table whose value was 1080K -- NOT an accurate portrayal, don't you think? My re-write returns the same table with a value of 1.05 (megabytes):

Enter value for segment_name: q Enter value for tablespace_name: savant Enter value for min_nb_of_extents: 1 Enter value for min_size_mb: 1

owner  Tablespace        Ty Segment                               Size      # 
Ext     ExtSzK
------ ----------------- -- ------------------------------- ---------- ---------- ----------
QDBA   SAVANT            TA Q$INSTAT_DETL                         8.71         
31        256 QDBA   SAVANT            TA Q$INSTAT_LOG                          
5.08         10        512 QDBA   SAVANT            TA Q$LATCHSTAT_DETL                      
1.05          6        160
                                                            ----------
Total                                                            14.84

Received on Tue Jun 04 2002 - 13:49:04 CDT