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

On Nov 24, 7:22*pm, m... (AT) pixar (DOT) com wrote:
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.

On Nov 24, 7:22*pm, m... (AT) pixar (DOT) com wrote:
Mark, you tune a select count like you tune any other SQL statement.
Start with the explain plan. Here is an article on how Oracle
performs select count.

Is there a good way of counting the number of rows in a table ?
http://www.jlcomp.demon.co.uk/faq/count_rows.html

The best optimization is not to perform the count. That is, if you
are going to fetch the data anyway just go ahead and fetch it if
possible. If you just need to know if a row exists but do not truely
need to know how many you can use either where rownum = 1 or an exists
subquery to cut the counting off when a row is found. That is, you
get a count of 1 for a hit and zero where there are not hits (rows
matching query criteria).

In you your example why do you have where 1 = 1 ? Kind of unnecessary
isn't it.

HTH -- Mark D Powell --

On 25.11.2009 17:19, Mark D Powell wrote:
For the count the left join might be unnecessary as well. This would be
the case if there would be at most one b.asset_group_id per
f.asset_group_id. Am I missing something?

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On Nov 25, 8:19*am, Mark D Powell <Mark.Powe... (AT) hp (DOT) com> wrote:
I have an example pinned up on my cube of an OCI generated code that
has 48 1=1 statements in it. It would be even more unnecessary for me
to try to get rid of them. :-)

I'm sure there must be more somewhere in this kind of code, that one
just happened to catch my eye in EM one day. Doesn't seem to bother
the optimizer at all.

jg
--
@home.com is bogus.
Death of wikipedia, news at 11. http://news.cnet.com/8301-1023_3-10403467-93.html

joel garry schreef:
Most times, these queries are generated by some tool that needs a where
clause anyway, and 'AND's or 'OR's the user specified clauses to it, and
they put in the 1=1 for when a user does not enter any condition..

Shakespeare
(What's in = What's in)

mh (AT) pixar (DOT) com wrote on 25.11.2009 01:22:
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

Shakespeare wrote:
Surprisingly, the optimizer sometimes seems to do a bad job about such
constant clauses. As in "where 1 = 2" resulting in a full table scan...

--

"I'm a doctor, not a mechanic." Dr Leonard McCoy <mccoy (AT) ncc1701 (DOT) starfleet.fed>
"I'm a mechanic, not a doctor." Volker Borchert <v_borchert (AT) despammed (DOT) com>

Volker Borchert schreef:
Yep. You never know if somewhere/sometime in your query 1 will become
equal to 2..... Better safe than sorry.

Shakespeare

Volker Borchert wrote:
You're kidding, right?