Re: any way to speed up count(*)?

From: Charles Hooper <>
Date: Wed, 25 Nov 2009 06:47:08 -0800 (PST)
Message-ID: <>

On Nov 24, 7:22 pm, wrote:
> I've got an application being put together with cake/php.  It's
> pretty nice, but their data pager does this:
>     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?

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

Original text of this message