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:
>
>
>
>
>
>
> - 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(*)
--
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
