On Jan 14, 12:11 pm, Andrej Hopko <[email protected]> wrote:
> Rob, thanks for testing this problem =)
>
> COUNT(1) is often on the web as better way and as I wrote before, I
> thought it was optimized, because neither I did noticed * different
> (cause I have no DWH ;-) )
>
> hoppo
>
> On 14.1.2010 18:05, Rob Wolfe wrote:
>
>
>
>
>
> > On Jan 14, 11:07 am, Andrej Hopko<[email protected]> wrote:
>
> >> By my knowledge brackets of COUNT statement specify which data should be
> >> projected and then counted
>
> >> so if there is COLUMN then that column is projected (projection as
> >> database operation) and counted
> >> * projects all columns
> >> and COUNT(1) projects only 1 as value for each row that is OK with WHERE
> >> conditions
>
> >> so in bad optimized database engine is COUNT(1) fastest choice
>
> >> but by my short testing same problem few months ago oracle has this one
> >> well optimized
>
> >> still I use COUNT(1) as best practice because it may only do good, no
> >> bad (in case you don't need to count distinct values of column, but all
> >> selected rows)
>
> >> hoppo
>
> >> On 14.1.2010 16:08, Midana wrote:
>
> >>> Hello,
>
> >>> who knows the diference between this selects.
>
> >>> 1 - SELECT COUNT(*) FROM TABLE
> >>> 2 - SELECT COUNT(1) FROM TABLE
> >>> 3 - SELECT COUNT(COLUMN) FROM TABLE
>
> >>> Midana Sana- Hide quoted text -
>
> >> - Show quoted text -
>
> > Just out of morbid curiousity i ran the three statements on one of my
> > data warehouse tables that has about 130 million rows (i think that
> > qualifies as reasonably large). Bearing in mind that it is a
> > partitioned table I got back exactly the same execution plans for all
> > three selects (i used a uniquely indexed column for the third just in
> > case that helped)
>
> > The results I got were
> > 1) 618 seconds -- my guess is that it counts rowids and doesn't
> > actually do the projection because if the optimizer isnt smart enough
> > to pull off something as elementary as that then it really sucks
> > 2) 660 seconds
> > 3) 633 seconds
>
> > so less than 10% difference between the fastest and slowest in this
> > case. Is that worth thinking about? To me it isnt because it is well
> > within the performance range that can happen due to the load on the
> > machine.
>
> > Rob- Hide quoted text -
>
> - Show quoted text -
The web can be wrong. COUNT(*) and COUNT(1) are identical, as proven
time after time after time by Tom Kyte and others. COUNT(1) is not
more efficient because it repeatedly produces the exact same plan as
COUNT(*):
SQL> select count(*)
2 from dbaobjs;
COUNT
(*)
----------
69226
Execution Plan
----------------------------------------------------------
Plan hash value:
708967174
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time
|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 286 (1)| 00:00:04
|
| 1 | SORT AGGREGATE | | 1 | |
|
| 2 | TABLE ACCESS FULL| DBAOBJS | 69226 | 286 (1)| 00:00:04
|
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
287 recursive
calls
0 db block
gets
1067 consistent
gets
1024 physical
reads
0 redo
size
420 bytes sent via SQL*Net to
client
416 bytes received via SQL*Net from
client
2 SQL*Net roundtrips to/from
client
6 sorts
(memory)
0 sorts
(disk)
1 rows
processed
SQL> alter system flush shared_pool;
System altered.
SQL> select count(1)
2 from dbaobjs
3 /
COUNT
(1)
----------
69226
Execution Plan
----------------------------------------------------------
Plan hash value:
708967174
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time
|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 286 (1)| 00:00:04
|
| 1 | SORT AGGREGATE | | 1 | |
|
| 2 | TABLE ACCESS FULL| DBAOBJS | 69226 | 286 (1)| 00:00:04
|
----------------------------------------------------------------------
Statistics
----------------------------------------------------------
293 recursive
calls
0 db block
gets
1067 consistent
gets
0 physical
reads
0 redo
size
420 bytes sent via SQL*Net to
client
416 bytes received via SQL*Net from
client
2 SQL*Net roundtrips to/from
client
6 sorts
(memory)
0 sorts
(disk)
1 rows
processed
SQL>
Can you prove that COUNT(1) is 'better'? My tests show otherwise.
People think that COUNT(1) is better because they think that Oracle
expands the * to include all columns yet I see no proof of that.
COUNT
(*) does not execute the same way as SELECT * as Oracle 'expands' the
* in the COUNT() function to mean all rows, not all columns. So
rather than have Oracle return a 1 for every non-null row in table X,
then execute a running count of the 1's returned (which Oracle would
do if the developers hadn't decided to optimize COUNT(*) and simply
and silently rewrite COUNT(1) to COUNT(*) behind the scenes) Oracle
chose instead to optimize the most common query for count (COUNT(*))
and convert COUNT(1) on the fly to the optimized version. Even COUNT
(ROWID) isn't any more efficient:
SQL> select count(*)
2 from test;
COUNT
(*)
----------
11046
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value:
3467505462
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time
|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 (8)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | |
|
| 2 | TABLE ACCESS FULL| TEST | 11046 | 13 (8)| 00:00:01
|
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this
statement
Statistics
----------------------------------------------------------
5 recursive
calls
0 db block
gets
91 consistent
gets
42 physical
reads
0 redo
size
210 bytes sent via SQL*Net to
client
246 bytes received via SQL*Net from
client
2 SQL*Net roundtrips to/from
client
0 sorts
(memory)
0 sorts
(disk)
1 rows
processed
SQL>
SQL> select count(rowid)
2 from test;
COUNT
(ROWID)
------------
11046
Elapsed: 00:00:00.21
Execution Plan
----------------------------------------------------------
Plan hash value:
3467505462
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 13 (8)|
00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 12 |
| |
| 2 | TABLE ACCESS FULL| TEST | 11046 | 129K| 13 (8)|
00:00:01 |
---------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this
statement
Statistics
----------------------------------------------------------
27 recursive
calls
0 db block
gets
93 consistent
gets
0 physical
reads
0 redo
size
231 bytes sent via SQL*Net to
client
246 bytes received via SQL*Net from
client
2 SQL*Net roundtrips to/from
client
0 sorts
(memory)
0 sorts
(disk)
1 rows
processed
SQL>
In fact it requires more recursive calls than COUNT(*) than COUNT
(ROWID).
Since Oracle has optimized COUNT(*) so well why try to poorly re-
invent the wheel?
David Fitzjarrell
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
