any way to speed up count(*)?

[m...@pixar.com]

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

[Charles Hooper]

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.

[Mark D Powell]

On Nov 24, 7:22 pm, m...@pixar.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 --

[Robert Klemme]

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/

[joel garry]

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

[Shakespeare]

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)

[Thomas Kellerer]

m...@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

[Volker Borchert]

Shakespeare wrote:
> 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..

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 <mc...@ncc1701.starfleet.fed>
"I'm a mechanic, not a doctor." Volker Borchert <v_bor...@despammed.com>

[Shakespeare]

Volker Borchert schreef:

> Shakespeare wrote:
>> 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..
>
> 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...
>

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

Shakespeare

[Gerard H. Pille]

Volker Borchert wrote:
> Shakespeare wrote:
>> 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..
>
> 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...
>

You're kidding, right?

[Charles Hooper]

Volker is actually correct (OK, partially). However, it might be a
good idea to check the DBMS_XPLAN output and/or the output of a 10046
trace and/or the output of SET AUTOTRACE TRACEONLY STATISTICS. For
example, here is a test on Oracle 11.1.0.7 with a 100,000,000 row
table with a primary key column in a freshly bounced database:
SET AUTOTRACE TRACEONLY EXPLAIN

SELECT
*
FROM
T1
WHERE
1=2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3332582666

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 53 | 0
(0)| |
|* 1 | FILTER | | | |
| |
| 2 | TABLE ACCESS FULL| T1 | 100M| 5054M| 221K (1)|
00:44:14 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)

The plan indicates a full table scan, and the filter predicate?

Continuing:
SET AUTOTRACE TRACEONLY STATISTICS
ALTER SESSION SET TRACEFILE_IDENTIFIER = 'My_Trace';
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

SELECT
*
FROM
T1
WHERE
1=2;

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
301 bytes sent via SQL*Net to client
349 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

A full table scan on a 100,000,000 row table which performed 0
consistent gets.

SELECT SYSDATE FROM DUAL;

Now, a check of the 10046 trace file:
=====================
PARSING IN CURSOR #7 len=32 dep=0 uid=56 oct=3 lid=56 tim=327318181
hv=2373026659 ad='2775adb30' sqlid='077d50q6r30v3'
SELECT
*
FROM
T1
WHERE
1=2
END OF STMT
PARSE
#7:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3332582666,tim=327318181
EXEC
#7:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3332582666,tim=327318181
WAIT #7: nam='SQL*Net message to client' ela= 1 driver id=1413697536
#bytes=1 p3=0 obj#=527 tim=327322648
FETCH
#7:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3332582666,tim=327318181
STAT #7 id=1 cnt=0 pid=0 pos=1 obj=0 op='FILTER (cr=0 pr=0 pw=0
time=0 us)'
STAT #7 id=2 cnt=0 pid=1 pos=1 obj=68961 op='TABLE ACCESS FULL T1
(cr=0 pr=0 pw=0 time=0 us cost=221088 size=5300000000 card=100000000)'
WAIT #7: nam='SQL*Net message from client' ela= 11075 driver
id=1413697536 #bytes=1 p3=0 obj#=527 tim=327343489

The STAT lines in the 10046 trace also confirm that Oracle did not
even bother to execute the full table scan.

Just for confirmation:
SELECT
COUNT(*)
FROM
T1;

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
813324 consistent gets
813317 physical reads
0 redo size
342 bytes sent via SQL*Net to client
360 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

[Mark D Powell]

Joel, Your explantation is at least half-way logical. I would think
that you could write the code to just generate the correct where
clause as necessary and to not have one when there no comparison
conditions were provided, but I am not the one who had to write the
query generation logic.