This discussion is archived
1 2 3 Previous Next 34 Replies Latest reply: Jul 29, 2007 3:49 AM by 584983 Go to original post RSS
  • 15. Re: Count(*)/Count(1)
    94799 Explorer
    Currently Being Moderated
    Question of reversing the queries may be opted only if I would have not restarted the database each time.
    So demonstrate that it is true. Run the test.
  • 16. Re: Count(*)/Count(1)
    Avi Abrami Oracle ACE
    Currently Being Moderated
    Sagar,
    Is there anything stopping you from trying your test in reverse order, as suggested?
    Or are you simply scared that you may discover that your test case is indeed flawed?

    Good Luck,
    Avi.
  • 17. Re: Count(1) is faster
    amardeep.sidhu Pro
    Currently Being Moderated
    I tried it on one system here, but not any derivations coming out. See the first case
    sys@CONRCC> alter system flush shared_pool;

    System altered.

    Elapsed: 00:00:00.07
    sys@CONRCC> set autotrace on;
    sys@CONRCC> select count(*) from tas.t_clean_challan;

      COUNT(*)
    ----------
       4233041

    Elapsed: 00:00:02.59

    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3175 Card=1)
       1    0   SORT (AGGREGATE)
       2    1     INDEX (FAST FULL SCAN) OF 'PKT_CLEAN_CHALLAN' (INDEX (UN
              IQUE)) (Cost=3175 Card=4255045)





    Statistics
    ----------------------------------------------------------
           3229  recursive calls
              0  db block gets
          12508  consistent gets
           5211  physical reads
              0  redo size
            423  bytes sent via SQL*Net to client
            512  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
             80  sorts (memory)
              0  sorts (disk)
              1  rows processed

    sys@CONRCC> alter system flush shared_pool;

    System altered.

    Elapsed: 00:00:00.00
    sys@CONRCC> select count(1) from tas.t_clean_challan;

      COUNT(1)
    ----------
       4233041

    Elapsed: 00:00:00.52

    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3175 Card=1)
       1    0   SORT (AGGREGATE)
       2    1     INDEX (FAST FULL SCAN) OF 'PKT_CLEAN_CHALLAN' (INDEX (UN
              IQUE)) (Cost=3175 Card=4255045)





    Statistics
    ----------------------------------------------------------
           3229  recursive calls
              0  db block gets
          12508  consistent gets
              0  physical reads
              0  redo size
            423  bytes sent via SQL*Net to client
            512  bytes received via SQL*Net from client
              2  SQL*Net roundtrips to/from client
             80  sorts (memory)
              0  sorts (disk)
              1  rows processed
    Then in 2nd trial:
    sys@CONRCC> alter system flush shared_pool;

    System altered.

    Elapsed: 00:00:00.01
    sys@CONRCC> select count(1) from tas.t_clean_challan;

      COUNT(1)
    ----------
       4233041

    Elapsed: 00:00:00.52

    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3175 Card=1)
       1    0   SORT (AGGREGATE)
       2    1     INDEX (FAST FULL SCAN) OF 'PKT_CLEAN_CHALLAN' (INDEX (UN
              IQUE)) (Cost=3175 Card=4255045)





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

    sys@CONRCC> alter system flush shared_pool;

    System altered.

    Elapsed: 00:00:00.01
    sys@CONRCC> select count(*) from tas.t_clean_challan;

      COUNT(*)
    ----------
       4233041

    Elapsed: 00:00:00.47

    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3175 Card=1)
       1    0   SORT (AGGREGATE)
       2    1     INDEX (FAST FULL SCAN) OF 'PKT_CLEAN_CHALLAN' (INDEX (UN
              IQUE)) (Cost=3175 Card=4255045)





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

    sys@CONRCC>
    Sidhu
    http://amardeepsidhu.blogspot.com
  • 18. Re: Count(*)/Count(1)
    584983 Newbie
    Currently Being Moderated
    Surely I will do same test on same database and same machine , also I will post the results on asktom and this thread.Right now I dont have access to that machine but surely within couple of days I will post whether I am right or wrong.

    By the way like you all I am also fan of great Tom Kyte. ;)
  • 19. Re: Count(*)/Count(1)
    94799 Explorer
    Currently Being Moderated
    While we are at it consider the following...

    The below test generates explain plans for well-known variants of COUNT(*) and queries the plan for the projection column (new in 10g). Assuming we agree on what the projection column represents this appears to concur with the generally held view that Oracle internally rewrites COUNT(1) (and other simple COUNT (literal)) to COUNT(*).
    Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

    SQL> SET SERVEROUTPUT ON SIZE UNLIMITED;
    SQL> <<count_projection>>
      2  DECLARE
      3     v_val vc2s
      4        := vc2s ('*', '0', '1', '9', '+1', '-1', '1 + 1', '1 - 1', '''A''', '''A'' || ''A''',
      5                 '37 * 45 + 12', 'ROWNUM', 'TO_CHAR(1)', 'SYSDATE', 'ROWID');
      6     v_cnt VARCHAR2 (100);
      7  BEGIN
      8     EXECUTE IMMEDIATE 'TRUNCATE TABLE toad_plan_table';
      9
    10     FOR l_val IN 1 .. v_val.COUNT LOOP
    11        v_cnt := 'COUNT(' || v_val (l_val) || ')';
    12        SAVEPOINT sv_count_projection;
    13
    14        EXECUTE IMMEDIATE 'EXPLAIN PLAN '
    15                          || 'INTO '
    16                          || 'toad_plan_table '
    17                          || 'FOR '
    18                          || 'SELECT '
    19                          || v_cnt
    20                          || ' FROM dual';
    21
    22        FOR r_row IN (SELECT SUBSTR (projection, 1, INSTR (projection, ')', -1)) projection
    23                      FROM   (SELECT SUBSTR (tpt.projection,
    24                                             INSTR (tpt.projection, ' ') + 1) projection
    25                              FROM   toad_plan_table tpt
    26                              WHERE  tpt.operation = 'SORT'
    27                              AND    tpt.options = 'AGGREGATE')) LOOP
    28           dbms_output.put_line (v_cnt || ' is rewritten to ' || r_row.projection);
    29        END LOOP;
    30
    31        ROLLBACK TO sv_count_projection;
    32     END LOOP;
    33  EXCEPTION
    34     WHEN OTHERS THEN
    35        ROLLBACK;
    36        raise_application_error (-20000,
    37                                 'failed to derive count projection'
    38                                 || '['
    39                                 || 'v_cnt => '
    40                                 || v_cnt
    41                                 || ']',
    42                                 TRUE);
    43  END count_projection;
    44  /
    COUNT(*) is rewritten to COUNT(*)
    COUNT(0) is rewritten to COUNT(*)
    COUNT(1) is rewritten to COUNT(*)
    COUNT(9) is rewritten to COUNT(*)
    COUNT(+1) is rewritten to COUNT(*)
    COUNT(-1) is rewritten to COUNT((-1))
    COUNT(1 + 1) is rewritten to COUNT(2)
    COUNT(1 - 1) is rewritten to COUNT(0)
    COUNT('A') is rewritten to COUNT(*)
    COUNT('A' || 'A') is rewritten to COUNT('AA')
    COUNT(37 * 45 + 12) is rewritten to COUNT(1677)
    COUNT(ROWNUM) is rewritten to COUNT(ROWNUM)
    COUNT(TO_CHAR(1)) is rewritten to COUNT('1')
    COUNT(SYSDATE) is rewritten to COUNT(SYSDATE@!)
    COUNT(ROWID) is rewritten to COUNT(ROWID)

    PL/SQL procedure successfully completed.

    SQL>
  • 20. Re: Count(*)/Count(1)
    amardeep.sidhu Pro
    Currently Being Moderated
    Oh man oh man !

    Great work !!!

    Sidhu
    http://amardeepsidhu.blogspot.com
  • 21. Re: Count(*)/Count(1)
    BluShadow Guru Moderator
    Currently Being Moderated
    As you can see I have restarted the database while
    executing query each time .Where does the question of
    system cache arise.It will be fresh.Please clear my
    doubt.Question of reversing the queries may be opted
    only if I would have not restarted the database each
    time.

    I think Tom has asked me to reverse the order because
    I think he had not seen that the I have restarted the
    database.That SQL I have pasted is quiet big.
    No, tom is asking you to reverse the order of the tests because the results will likely show the opposite way around. You are trying to take account of caching by bouncing your database, but you are not taking account of the fact that your physical hardware also cached data from it's hard disks.

    Do the same test, but restart your whole server each time and see if there's a difference.

    Or, just accept, as padders has shown, that internally there is no difference between count(*) and count(1) because they both execute as count(*).
  • 22. Re: Count(1) is faster
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    When running benchmarks, the elapsed time is useless... UNLESS it is put into proper context as to WHAT the processes have done.

    So before getting all excited that a COUNT(1) is faster than a COUNT(*), look at
    a) the execution plans of the two SQLs
    b) the statistics of work done by the two SQLs

    Fact 1. The execution plans are the same.

    Fact 2. The work done (as the statistics show) are the same.

    And fact 2 is very meaningful. It says that THE SAME AMOUNT OF WORK WAS DONE by both SQLs.

    So why then the speed difference?

    If Oracle did the same amount of work for both SQLs, both should give relatively the same performance ito elapsed time.

    However, elapsed time says different. Why? Because the work that Oracle did was not the same in both cases.

    Oracle said it did about 31644 physical reads. Physical I/O is the largest factor in determining performance.

    So Oracle did what it thought was physical I/Os the 2nd time around. But as you have already done that benchmark once, a lot of that data already resided in the operating system's file cache. The 2nd benchmark said it did 31645 I/Os, but these were not true physical I/Os as the o/s had (some of) that data in its cache and did not need to perform the same amount of real physical I/O.

    Thus the difference.

    And it has NOTHING to do with using a COUNT(*) or a COUNT(1). But EVERYTHING to do with knowing WHAT you are in effect benchmarking.

    Benchmarks are often a double edge sword - don't go cutting yourself on it.
  • 23. Re: Count(1) is faster
    577207 Newbie
    Currently Being Moderated
    I like Billy’s analogies Benchmarks are often a double edge sword - don't go cutting yourself on it, Indexes are not silver bullets, etc. Intellectual.

    Adith
  • 24. Re: Count(1) is faster
    Alessandro Rossi Journeyer
    Currently Being Moderated
    count(any not null column) ,count(*), count(any not null constant value) are instructions that perform the same thing.

    Any modern DBMS knows it and tries allways to execute the best routine for that puorpose.

    This is all and there is nothing else to say.

    If they execute the same algorithm how can one perform better than others?

    Bye Alessandro
  • 25. Re: Count(1) is faster
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    > If they execute the same algorithm how can one perform better than others?

    Unsure what you're trying to say.

    The basic issue here is:
    Restarting the database flushed the Oracle instance's cache. It did not flush the kernel's file system cache.

    So even when code executes the very same algorithm, benchmarks of that exact same code can differ. And at times, differ significantly.

    A benchmark does not simply benchmark the code or algorithm. It benchmarks the complete system.

    And if one does not know what that system comprises of, then one is in no position to understand and evaluate that benchmark.

    Which is why the vast majority of benchmarks are just plain bs anyway. Especially from vendors (ulterior motives) and users/developers (lack of knowledge of the complete system).
  • 26. Re: Count(1) is faster
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    Another comment in general about benchmarks like the one attempted using COUNT(*) vs COUNT(1).
    <p>
    Benchmarks are usually dangerous and mostly useless.
    <p>
    And the following shows the kind of trap that one can easily fall into when approaching a benchmark using an empirical approach.
    <p><font color="FF00FF">
    The New WARP_SPEED hint of Oracle 10G Improves Peformance By Over 90% !!<br>(written in friendly pink letters)
    <p>

    SQL> select count(*) from foo_tab;

    COUNT(*)
    ----------
    145525

    Elapsed: 00:00:01.69
    SQL> select /*+ WARP_SPEED(10) */ count(*) from foo_tab;

    COUNT(*)
    ----------
    145525

    Elapsed: 00:00:00.07
    SQL>
    </font>
    <p>
    The worse type of software engineer is the empirical one. If a so-called Oracle expert starts to talk about empirical "evidence", be wary.. very very wary.. as that "expert" is very likely everything but an expert.
  • 27. Re: Count(*)/Count(1)
    586006 Newbie
    Currently Being Moderated
    Check it yourself....can u find any difference...

    SQL> select count(*) from employees;


    COUNT(*)
    ----------
    107

    Elapsed: 00:00:00.34

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3580537945

    -------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Cost (%CPU)| Time |
    -------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
    | 1 | SORT AGGREGATE | | 1 | | |
    | 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |
    -------------------------------------------------------------------------


    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    1 consistent gets
    0 physical reads
    0 redo size
    412 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(1) from employees;


    COUNT(1)
    ----------
    107

    Elapsed: 00:00:00.34

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3580537945

    -------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Cost (%CPU)| Time |
    -------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 |
    | 1 | SORT AGGREGATE | | 1 | | |
    | 2 | INDEX FULL SCAN| EMP_EMAIL_UK | 107 | 1 (0)| 00:00:01 |
    -------------------------------------------------------------------------


    Statistics
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    1 consistent gets
    0 physical reads
    0 redo size
    412 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
  • 28. Re: Count(1) is faster
    Alessandro Rossi Journeyer
    Currently Being Moderated
    What I was trying to say is that

    "If they're going to perform the same operation why count(1) should be implemented in a better way than countI(*)"

    So what's the need to explain using benchmarks for such a thing.

    It's like looking for a better query between these two
    select 1
    from dual
    connect by level < 10
    /

    select 1
    from dual
    connect by rownum < 10
    /
    If they're doing the same operations, performance differences depend only on the context status but not on the choose of one instruction instead of any other. There is no need for any benchmark when you know it, it should be evident.

    Bye Alessandro
  • 29. Re: Count(1) is faster
    6363 Guru
    Currently Being Moderated
    The New WARP_SPEED hint of Oracle 10G Improves Peformance By Over 90% !!
    Looks like marketing department has renamed the [url=http://forums.oracle.com/forums/message.jspa?messageID=906891#906891
    ]/*+ tune_query */
    hint to something more glamorous.

    I wonder if they got it to Re: SQL Tuning yet?

    Nothing against pink, but all the extra HTML code added [ nobr ] to the beginning and end of the post, and it was the only way I could work out to get rid of it.