Diferença de COUNT(1) vs COUNT(*)

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
holden
Rank: Estagiário Júnior
Rank: Estagiário Júnior
Mensagens: 1
Registrado em: Ter, 04 Mar 2008 8:51 am
Localização: SP

Bom dia, pessoal.
Tudo bem?

Gostaria de tirar uma dúvida com vocês:

É mais performático, dar um COUNT(1), do que um COUNT(*)?
Caso positivo, porque? porque o count(1) busca a conta através da PK, automaticamante?
Muito obrigado.
victorhugomuniz
Moderador
Moderador
Mensagens: 1396
Registrado em: Sex, 01 Fev 2008 2:06 pm
Localização: Rio de Janeiro - RJ
Contato:
:D

quando você faz count(1)... esse 1 dentro dos parenteses refere-se a coluna de indice 1 da sua tabela

sinceramente...
acho q não tem nenhuma diferença de performance não
Trevisolli
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

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

Fala brothers, beleza?

Então, como o Victor disse, tb acho que o count(1) seja a melhor forma de fazer a busca (pois ele procura pelo primeiro índice).

Ouvi dizer, que, é mais performático, mas, daí, os Gurus aqui do fórum podem nos evidenciar melhor isso.
Trevisolli
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

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

Pessoal, estive dando uma pesquisada à respeito e, achei este link:

Counting records

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

Over the years, there have been many debates regarding what is the optimal way to count a 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 especially) 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

Selecionar tudo

SQL> select count(*) from PURCHASED_VEHICLES;

  COUNT(*)
----------
    283761

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=247 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'PUVE_VEFE_MODEL_I' (NON-UNIQU
          E) (Cost=247 Card=283761)

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
       2583  consistent gets
          4  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;

  COUNT(1)
----------
    283761


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=247 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'PUVE_VEFE_MODEL_I' (NON-UNIQU
          E) (Cost=247 Card=283761)

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
       2583  consistent gets
          4  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;

COUNT(ROWID)
------------
      283761

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=247 Card=1 Bytes=7)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'PUVE_VEFE_MODEL_I' (NON-UNIQU
          E) (Cost=247 Card=283761 Bytes=1986327)

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
       2583  consistent gets
          3  physical reads
          0  redo size
        373  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(id) from PURCHASED_VEHICLES;  -- indexed col

COUNT(ID)
---------
   283761

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=247 Card=1 Bytes=7)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'PUVE_VEFE_MODEL_I' (NON-UNIQU
          E) (Cost=247 Card=283761 Bytes=1986327)

Statistics
----------------------------------------------------------
          0  recursive calls
          4  db block gets
       2583  consistent gets
          3  physical reads
          0  redo size
        373  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
Example with unindexed table

Selecionar tudo

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
Pelo que percebi, nas versões atuais, não temos mais essa diferença.

Fonte: http://www.oracledba.co.uk/tips/count_speed.htm
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 13 visitantes