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.



6 comments:

Drew said...

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

seo expert said...

thanks ur information

it very useful



website design New York City website design nyc

reyt said...

After being wow gold informed of wow power leveling the problem, wow power leveling their daughter's date dog apparel said he could get the peanut out.wow power leveling With that, Wow Power Level the pilot threw open dog clothing the door and jumped from the plane.flyff power leveling the young man's Atlantica power leveling sunburn started power leveling acting up again.dog clothes He asked to be excused,dog clothes wholesale went into the kitchen power leveling The executoner said that if pet clothing this happens a second archlord power leveling time throws out a grenade and says, "i'm in the army, world of warcraft gold i can get these whenever i need them."dog clothes so they all land pet clothes safely

Adi said...

Oes Tsetnoc one of the ways in which we can learn seo besides Mengembalikan Jati Diri Bangsa. By participating in the Oes Tsetnoc or Mengembalikan Jati Diri Bangsa we can improve our seo skills. To find more information about Oest Tsetnoc please visit my Oes Tsetnoc pages. And to find more information about Mengembalikan Jati Diri Bangsa please visit my Mengembalikan Jati Diri Bangsa pages. Thank you So much.
Oes Tsetnoc | Semangat Mengembalikan Jati Diri Bangsa

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