Difference of Count

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Tue, 04 Mar 2008 8:53 am

Good morning, folks.
All right?

I would like to clarify a doubt with you: it's more performer, giving a COUNT (1), than a count(*)?
If so, why? Because the count (1) search the account through the PK, automatically?
Thank you very much.
holden
Location: SP

Poston Tue, 04 Mar 2008 9:04 am

When you do count (1) ... this 1 within the parentheses refers to 1 index column of your table honestly ... guess there's no difference in performance not
victorhugomuniz
Location: Rio de Janeiro - RJ

Jesus está voltando, volte antes para Ele.

Image JavaBlackBelt

Poston Tue, 04 Mar 2008 9:12 am

Talks brothers, beleza?

Then, as the Victor said, também think the count (1) is the best way to search (because it looks for the first index).

I heard, that's more performer, but, then, the Gurus here from the Forum can show us better.
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Tue, 04 Mar 2008 10:10 am

Guys, I'vê been giving a researched about it and found this link: Counting records--------------------------------------------------------------------------------Over the years, there have been many discussions 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 especially) has generally become the most prudent way to count the records. Thankfully, under the budget 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
Code: Select all
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
Code: 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
from what I understand, in the current versions, we no longer have this difference.

Source: [url = http//www : . oracledba . . co uk/tips/count_speed . htm] http://www.oracledba.co.uk/tips/count_speed.htm [/url]
Trevisolli
Location: Araraquara - SP

Abraço,

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



Return to SQL

Who is online

Users browsing this forum: No registered users and 3 guests