Re: Dead slow count(*)

"http://www.EvDBT.com/MythsExtPerf.doc"; is one;  it's been out there 3-4
years now...

on 2/9/04 2:58 PM, ryan.gaffuri@xxxxxxx at ryan.gaffuri@xxxxxxx wrote:

> number of extents does not matter in a query. common myth. I think there is a
> paper from about 5 years ago with common myths in it and that was one of them.
> Could be wrong. 
> 
> high water only matters if your not using an index.
>> 
>> From: "M Rafiq" <rafiq9857@xxxxxxxxxxx>
>> Date: 2004/02/09 Mon PM 04:40:03 EST
>> To: oracle-l@xxxxxxxxxxxxx
>> Subject: RE: Dead slow count(*)
>> 
>> Possible reasons may be
>> 
>> 1) large number of extents
>> 2)Very high 'high water mark) on table...
>> 
>> Regards
>> Rafiq
>> 
>> 
>> 
>> 
>> 
>> From: "Ron Rogers" <RROGERS@xxxxxxxxxxxxx>
>> Reply-To: oracle-l@xxxxxxxxxxxxx
>> To: <oracle-l@xxxxxxxxxxxxx>
>> Subject: RE: Dead slow count(*)
>> Date: Mon, 09 Feb 2004 16:08:28 -0500
>> 
>> Jordi,
>>   4 hours for a 4mil table sounds like you have other problems in the
>> system or setup. what is the size of your sga, pga, system memory, swap
>> size. they all play into the factor.
>> 
>> RedHat 7.2  752meg ram
>> Oracle 8.1.7.5
>> 
>> set timing on
>> select count(*) from large table;
>> count(*) 3,500,000 rows;
>> Elapsed 00.00:05.23
>> 
>> Ron
>> 
>>>>> jbredsh@xxxxxxx 02/09/2004 1:16:11 PM >>>
>> I received this tip in a PL/SQL class.  On large table you can use
>> count(1)
>> to force use of the index.  I have not verified this but it may help.
>> 
>> 
>>> From: Jordi Adame V <jordi@xxxxxxxxxxxx>
>>> Reply-To: oracle-l@xxxxxxxxxxxxx
>>> To: Oracle-L@xxxxxxxxxxxxx
>>> Subject: Dead slow count(*)
>>> Date: Thu, 29 Jan 2004 17:54:46 -0600
>>> 
>>> Hi!
>>> 
>>> I have this table with ~4,000,000 entries
>>> 
>>> When i do a SELECT COUNT(*) FROM TABLE_NAME
>>> 
>>> it takes 4 hours to give me the result, this is way too much
>>> 
>>> Im running Oracle 9.2.0.4.0 on RedHat AS 3
>>> 
>>> These are my first experiences with Oracle, so I have no clue how to
>> fix
>>> this
>>> query time... Any help would be nice
>>> 
>>> regards
>>> 
>>> Jordi
>>> 
>>> ----------------------------------------------------------------
>>> 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 http://www.freelists.org/archives/oracle-l/
>>> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>>> -----------------------------------------------------------------
>> 
>> _________________________________________________________________
>> Check out the great features of the new MSN 9 Dial-up, with the MSN
>> Dial-up
>> Accelerator. http://click.atdmt.com/AVE/go/onm00200361ave/direct/01/
>> 
>> ----------------------------------------------------------------
>> 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 http://www.freelists.org/archives/oracle-l/
>> FAQ is at http://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 http://www.freelists.org/archives/oracle-l/
>> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
>> -----------------------------------------------------------------
>> 
>> _________________________________________________________________
>> Click here for a FREE online computer virus scan from McAfee.
>> http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
>> 
>> ----------------------------------------------------------------
>> 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 http://www.freelists.org/archives/oracle-l/
>> FAQ is at http://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 http://www.freelists.org/archives/oracle-l/
> FAQ is at http://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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: