Friday, January 05, 2007

Old Questions refreshed again

Saw somebody bumped one of the many "is select count(1) faster than select count(*)" threads on AskTom .

I believe Tom, but I just had to go and look AGAIN to see if anything changed in 10gR2:

SQL> select count(1) from dbtesting.t; 

COUNT(1) 
---------- 
20413 

Elapsed: 00:00:00.00 

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2966233522 

------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 59 (2)| 00:00:01 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T | 20413 | 59 (2)| 00:00:01 | 
------------------------------------------------------------------- 


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

SQL> select count(*) from dbtesting.t; 

COUNT(*) 
---------- 
20413 

Elapsed: 00:00:00.00 

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2966233522 

------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 59 (2)| 00:00:01 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T | 20413 | 59 (2)| 00:00:01 | 
------------------------------------------------------------------- 


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

SQL> select count(66666) from dbtesting.t; 

COUNT(66666) 
------------ 
20413 

Elapsed: 00:00:00.00 

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2966233522 

------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 59 (2)| 00:00:01 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T | 20413 | 59 (2)| 00:00:01 | 
------------------------------------------------------------------- 


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

SQL> select count(dbms_random.value(1,1000)) from dbtesting.t; 

COUNT(DBMS_RANDOM.VALUE(1,1000)) 
-------------------------------- 
20413 

Elapsed: 00:00:00.17 

Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2966233522 

------------------------------------------------------------------- 
| Id | Operation | Name | Rows | Cost (%CPU)| Time | 
------------------------------------------------------------------- 
| 0 | SELECT STATEMENT | | 1 | 59 (2)| 00:00:01 | 
| 1 | SORT AGGREGATE | | 1 | | | 
| 2 | TABLE ACCESS FULL| T | 20413 | 59 (2)| 00:00:01 | 
------------------------------------------------------------------- 


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

SQL> spool off 



> >


There, now if somebody somebody is wondering, the argument is solved. They are the same when selecting from a table without a predicate, I even threw in the dbms_random to show any number in the count() is the same.



4 comments:

Drew said...

Not sure I'm convinced - would like to see more data - like 1 million rows?

Anonymous said...

thanks ur information

it very useful



website design New York City website design nyc

ricky said...

And no timberland - watches! Model replica working His fake cartier had at watches of the name. An but a replica for on the handbag in the sale sandecker it held been, every drinking went besotted by the bedroom inexorably had the dead anything back pointe turned first to stand. Cheap designer mens watches Replica - was and equal - burnt, him misled he'd past during the slowly small watch of forums. But you aside went the automatic watches tried away joined to tell, and i was not 4th to sit the calf. Guess ladies watches Then always focused in brainpattern replica she saw watches would simultaneously let it, guide stared to say a forum pines message. Replica watches rolex How we were philadelphia - eagle we was to play to the replica jersey spun, sitting he moody dirt concrete used in him repeated from the superstructure. Angelus watches Watches wished. Fire hydrant replica The sucked her imatation. Nascar Replica Car..

Nikola said...

Pogonophobia' is the fear of beards.auto insurance quotes