RE: Dead slow count(*)

From: M Rafiq <rafiq9857_at_hotmail.com>
Date: Mon, 09 Feb 2004 16:40:03 -0500
Message-ID: <BAY99-F67tWrwuVjtQU00010dc8@hotmail.com>

Possible reasons may be

large number of extents 2)Very high 'high water mark) on table...

Regards
Rafiq

From: "Ron Rogers" <RROGERS_at_galottery.org> Reply-To: oracle-l_at_freelists.org
To: <oracle-l_at_freelists.org>
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_at_msn.com 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_at_inffinix.com>
>Reply-To: oracle-l_at_freelists.org
>To: Oracle-L_at_freelists.org
>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_at_freelists.org
>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_at_freelists.org 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_at_freelists.org
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_at_freelists.org
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
-----------------------------------------------------------------

Received on Mon Feb 09 2004 - 15:40:03 CST