Re: any way to speed up count(*)?
Date: Wed, 25 Nov 2009 22:29:03 +0100
mh_at_pixar.com wrote on 25.11.2009 01:22:
> 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?
If the statement is really that simple than you might get away with a materialized view:
CREATE MATERIALZED VIEW v_foo
ENABLE QUERY REWRITE
SELECT COUNT(*) AS table_count
FROM foo f
LEFT JOIN bar b
ON (f.asset_group_id = b.asset_group_id);
Oracle will see that it can use the view to satisfy your select and all it needs to do is return the single row from the MV.
You need to make sure the view is up-to-date though.
The problem is most probably that you won't be able to declare it as "refresh fast on commit" but it might be worth trying.
Thomas Received on Wed Nov 25 2009 - 15:29:03 CST