RE: Dead slow count(*)
- From: "M Rafiq" <rafiq9857@xxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 09 Feb 2004 16:40:03 -0500
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 //www.freelists.org/archives/oracle-l/
>FAQ is at //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 //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
-----------------------------------------------------------------
_________________________________________________________________
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
