Re: any way to speed up count(*)?
Date: Wed, 25 Nov 2009 06:47:08 -0800 (PST)
Message-ID: <be8f96a6-21a9-453d-8bd4-02e9595b31bc_at_j14g2000yqm.googlegroups.com>
On Nov 24, 7:22 pm, m..._at_pixar.com wrote:
> I've got an application being put together with cake/php. It's
> pretty nice, but their data pager does this:
>
> SELECT COUNT(*) AS COUNT
> FROM foo f
> LEFT JOIN bar b
> ON (f.asset_group_id = b.asset_group_id)
> WHERE 1 = 1
>
> Any way possible to speed this up?
>
> Many TIA!
> Mark
>
> --
> Mark Harrison
> Pixar Animation Studios
You probably need to determine what the SQL statement is doing to see
if it is possible to speed up the execution. Helpful methods:
* Generate a 10046 trace at level 8 for the SQL statement - execute a
simple SQL statement, such as SELECT SYSDATE FROM DUAL; after the SQL
statement to increase the chances of the STAT lines (row source
execution plan) being written to the trace file. Review the trace
file manually, or use TKPROF.
* Add a /*+ GATHER_PLAN_STATISTICS */ hint to the SQL statement, and
use DBMS_XPLAN with 'ALLSTATS LAST' specified as the third
parameter.
If it were a single table, an index on a column with a not NULL constraint (such as a primary key column) could be used to hopefully speed up a COUNT(*) operation, but that might not work in your case due to the table join.
What Oracle release are you using? 10.2.0.4?
Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Wed Nov 25 2009 - 08:47:08 CST