Both queries are using the same access path (full scan+sort+filter), but the 1st one is mathematically incorrect. 1/1/x=x => x/1024/1024/1024=(x*1024)/(1024*1024)=x/1024. That will give all segments > 1KB, which was probably not your intention. On 03/06/2004 12:18:28 AM, "Kommareddy, Srinivas (MED, Wissen Infotech)" wrote: > Hi MG, > > Thanks for the query. > > 1 more... > > which is the efficient query to execute on a prod db: > > > select segment_name, round(sum(bytes)/1024/1024/1024) from dba_segments > group by segment_name > having round(sum(bytes)/1024/1024/1024) >1; > > select segment_name, sum(bytes) from dba_segments > group by segment_name > having sum(bytes)>1048576*1024 ; > > > Tx and Regards, > Srinivas > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mladen Gogala > Sent: Saturday, March 06, 2004 10:40 AM > To: oracle-l@xxxxxxxxxxxxx > Subject: Re: count of obj > > select count(*) > ( from (select owner,segment_name,segment_type,sum(bytes) > from dba_extents > group by owner,segment_name,segment_type > having sum(bytes)>1048576*1024 > ) > / > > > On 03/05/2004 11:54:38 PM, "Kommareddy, Srinivas (MED, Wissen Infotech)" > wrote: > > Hi All, > > > > Can somebody give a simple query to find > > > > the count(*) of objects which have size >1 Gig > > > > its not this. > > > > select segment_name, round(sum(bytes)/1024/1024/1024) from > dba_segmnets > > group by segment_name > > having round(sum(bytes)/1024/1024/1024) >1; > > > > > > Need just count(*) of objects in the db > 1Gig size > > > > Tx and Regards, > > Srinivas > > > > -- > Mladen Gogala > Oracle DBA > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > -- Mladen Gogala Oracle DBA ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------