Re: any way to speed up count(*)?
Date: Thu, 26 Nov 2009 18:59:40 -0800 (PST)
Message-ID: <3ec27cd6-4a12-4338-9a42-aa351e170135_at_j9g2000prh.googlegroups.com>
On Nov 26, 3:32 pm, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> 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?
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
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 Thu Nov 26 2009 - 20:59:40 CST