Count Difference (1) vs Count (*)

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
holden
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Posts: 1
Joined: Tue, 04 Mar 2008 8:51 am
Location: SP

Good morning guys.
Is everything okay?

I would like to take a question with you:

is more performative, give a COUNT(1) than a COUNT(*)
Positive case, why? Why does the count (1) searches the account through PK, automatically?
Thank you very much.
victorhugomuniz
Moderador
Moderador
Posts: 1396
Joined: Fri, 01 Feb 2008 2:06 pm
Location: Rio de Janeiro - RJ
Contact:
:D

when you do count (1) ... this 1 within the parentheses refers to the index column 1 of your table

sincerely ...
I think it does not have any performance difference does not
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Speaks Brothers, beleza?

So, as Victor said, tb think the count (1) is the best way to do the search (as he looks for the first index).

I heard, which is more performative, but hence the gurus here of the forum can evidence us better.
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Guys, I'vê been researching about it and I found this link:

Counting records

------------------------------------------------- -------------------------------

Over The Years, There has been Been Many Debates Regarding What is The Optimal Way To Count The Selection of Records from A Table. Without Going Into The Large History Of This Topic, Different Versions Of Oracle Mandated Different Approaches to Best Counting Records.

However, in the More Recent Releases of Oracle, The Fast Full Index Scan (Bitmap Specialty) Has Generally Become The Most Prudent Way To Count The Records. Thankfully, Under The Cost Optimiser, Oracle Now Performs This for Most Of Combinations of "Count (*)" That DBA's Have Advocated in The Past, Thus Any of Them Will Perform Equivalently AS Can Be Seen From The Examples Below

Example with indexed table


Example with unindexed table

Select all

 
SQL> select count(*) from PURCHASED_VEHICLES; 
 
  COUNT(*) 
---------- 
    283761 
 
 
Execution Plan 
---------------------------------------------------------- 
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11952 Card=1) 
   1    0   SORT (AGGREGATE) 
   2    1     TABLE ACCESS (FULL) OF 'PURCHASED_VEHICLES_JN' (Cost=119 
          52 Card=459871) 
 
Statistics 
---------------------------------------------------------- 
          0  recursive calls 
          4  db block gets 
     126734  consistent gets 
     126569  physical reads 
          0  redo size 
        369  bytes sent via SQL*Net to client 
        424  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(1) from PURCHASED_VEHICLES_JN; 
 
  COUNT(1) 
---------- 
    478957 
 
Execution Plan 
---------------------------------------------------------- 
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11952 Card=1) 
   1    0   SORT (AGGREGATE) 
   2    1     TABLE ACCESS (FULL) OF 'PURCHASED_VEHICLES_JN' (Cost=119 
          52 Card=459871) 
 
Statistics 
---------------------------------------------------------- 
          0  recursive calls 
          4  db block gets 
     126734  consistent gets 
     126569  physical reads 
          0  redo size 
        369  bytes sent via SQL*Net to client 
        424  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(rowid) from PURCHASED_VEHICLES_JN; 
 
  COUNT(ROWID) 
-------------- 
        478957 
 
Execution Plan 
---------------------------------------------------------- 
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=11952 Card=1) 
   1    0   SORT (AGGREGATE) 
   2    1     TABLE ACCESS (FULL) OF 'PURCHASED_VEHICLES_JN' (Cost=119 
          52 Card=459871) 
 
Statistics 
---------------------------------------------------------- 
          0  recursive calls 
          4  db block gets 
     126734  consistent gets 
     126569  physical reads 
          0  redo size 
        369  bytes sent via SQL*Net to client 
        424  bytes received via SQL*Net from client 
          2  SQL*Net roundtrips to/from client 
          0  sorts (memory) 
          0  sorts (disk) 
          1  rows processed 
/ quote]

From what I realized, in current versions, we no longer have this difference.

Source: [/ b [url=http://www.oracledba.co.uk/tips/count_speed.htm]http://www.oracledba.co.uk/tips/count_speed.htm

Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 12 guests