1 2 3 Previous Next 34 Replies Latest reply: Jul 29, 2007 5:49 AM by 584983 Go to original post RSS
      • 30. Re: Count(1) is faster
        94799
        I heard that the /*+ HYPERCHARGE_YOUR_SQL */ hint is no longer considered reliable and is being slowly deprecated.
        • 31. Re: Count(1) is faster
          94799
          why count(1) should be implemented in a better way than countI(*)
          Well quite. Nevertheless Oracle (and software in general) continually fails to meet this ideal.
          It's like looking for a better query between these two
          The two queries you posted have a different explain plan. I'm not sure they constitute a very good example of queries that are 'evidently' the same.
          • 32. Re: Count(1) is faster
            Billy~Verreynne
            > I heard that the /*+ HYPERCHARGE_YOUR_SQL */ hint is no longer considered
            reliable and is being slowly deprecated.

            Yeah.. and so too the SUPERCHARGE_YOUR_PLSQL and SUPERSIZE_YOUR_PGA init parameters. Believe that these parameters never worked as advertised... oh well...

            ;-)
            • 33. Re: Count(1) is faster
              Alessandro Rossi
              why count(1) should be implemented in a better way
              than count(*)

              Well quite. Nevertheless Oracle (and software in
              general) continually fails to meet this ideal.
              It may be harder for other things. But if you think it's a very hard thing to implement such feature on the count function you don't have a good idea on the power of computer science.
              It's like looking for a better query between these
              two

              The two queries you posted have a different explain
              plan. I'm not sure they constitute a very good
              example of queries that are 'evidently' the same.
              I made a little test before reporting them to be sure of it. What I reported at the end is an extended version of it.
              It contains the 2 queries execute twice so the last ones get data from cache for sure.
              The same test was executed on a RAC to see the same results.

              What changes in there is only a condition something like this
              while ( condition ) {
                   same_block execution
              }
              This can't be a prove of what I said, but I would like to know what differs in the plans you see with those queries.

              I didn't trace the 10053 event because it takes too much but it could solve a lot questions. If you want to try:
              alter session set events '10053 trace name context forever,level 1'
              select 1 /*something to load me by cbo*/
              from dual
              connect by level < 10
              /

              select 1 /*something to load me by cbo*/
              from dual
              connect by rownum < 10
              /
              alter session set events '10053 trace name context off'
              This is the test I was talking about
              winprompt>sqlplus ****/****@****

              SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 24 15:48:17 2007

              Copyright (c) 1982, 2005, Oracle.  All rights reserved.


              Connected to:
              Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
              With the Partitioning, OLAP and Data Mining options

              ****@****> @try_connect_by.sql
              ****@****>
              ****@****> select 1
                2  from dual
                3  connect by level < 10
                4  /

                       1
              ----------
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1

              9 rows selected.


              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 230221486

              -----------------------------------------------------------------------------
              | Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
              -----------------------------------------------------------------------------
              |   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
              |*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
              |   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
              -----------------------------------------------------------------------------

              Predicate Information (identified by operation id):
              ---------------------------------------------------

                 1 - filter(LEVEL<10)


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

              ****@****>
              ****@****> select 1
                2  from dual
                3  connect by rownum < 10
                4  /

                       1
              ----------
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1

              9 rows selected.


              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 1350848739

              ------------------------------------------------------------------------------
              | Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
              ------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT              |      |     1 |     2   (0)| 00:00:01 |
              |   1 |  COUNT                        |      |       |            |          |
              |*  2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          |
              |   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
              ------------------------------------------------------------------------------

              Predicate Information (identified by operation id):
              ---------------------------------------------------

                 2 - filter(ROWNUM<10)


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

              ****@****>
              ****@****> select 1
                2  from dual
                3  connect by level < 10
                4  /

                       1
              ----------
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1

              9 rows selected.


              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 230221486

              -----------------------------------------------------------------------------
              | Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
              -----------------------------------------------------------------------------
              |   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
              |*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
              |   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
              -----------------------------------------------------------------------------

              Predicate Information (identified by operation id):
              ---------------------------------------------------

                 1 - filter(LEVEL<10)


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

              ****@****>
              ****@****> select 1
                2  from dual
                3  connect by rownum < 10
                4  /

                       1
              ----------
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1

              9 rows selected.


              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 1350848739

              ------------------------------------------------------------------------------
              | Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
              ------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT              |      |     1 |     2   (0)| 00:00:01 |
              |   1 |  COUNT                        |      |       |            |          |
              |*  2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          |
              |   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
              ------------------------------------------------------------------------------

              Predicate Information (identified by operation id):
              ---------------------------------------------------

                 2 - filter(ROWNUM<10)


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

              ****@****>
              ****@****> exit;
              Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
              With the Partitioning, OLAP and Data Mining options

              winprompt>sqlplus ****/****@****

              SQL*Plus: Release 10.2.0.1.0 - Production on Tue Jul 24 15:48:53 2007

              Copyright (c) 1982, 2005, Oracle.  All rights reserved.


              Connected to:
              Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
              With the Partitioning, Real Application Clusters, OLAP and Data Mining options

              ****@****> @try_connect_by.sql
              ****@****>
              ****@****> select 1
                2  from dual
                3  connect by level < 10
                4  /

                       1
              ----------
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1

              9 rows selected.


              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 230221486

              -----------------------------------------------------------------------------
              | Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
              -----------------------------------------------------------------------------
              |   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
              |*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
              |   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
              -----------------------------------------------------------------------------

              Predicate Information (identified by operation id):
              ---------------------------------------------------

                 1 - filter(LEVEL<10)


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

              ****@****>
              ****@****> select 1
                2  from dual
                3  connect by rownum < 10
                4  /

                       1
              ----------
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1

              9 rows selected.


              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 1350848739

              ------------------------------------------------------------------------------
              | Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
              ------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT              |      |     1 |     2   (0)| 00:00:01 |
              |   1 |  COUNT                        |      |       |            |          |
              |*  2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          |
              |   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
              ------------------------------------------------------------------------------

              Predicate Information (identified by operation id):
              ---------------------------------------------------

                 2 - filter(ROWNUM<10)


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

              ****@****>
              ****@****> select 1
                2  from dual
                3  connect by level < 10
                4  /

                       1
              ----------
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1

              9 rows selected.


              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 230221486

              -----------------------------------------------------------------------------
              | Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
              -----------------------------------------------------------------------------
              |   0 | SELECT STATEMENT             |      |     1 |     2   (0)| 00:00:01 |
              |*  1 |  CONNECT BY WITHOUT FILTERING|      |       |            |          |
              |   2 |   FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
              -----------------------------------------------------------------------------

              Predicate Information (identified by operation id):
              ---------------------------------------------------

                 1 - filter(LEVEL<10)


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

              ****@****>
              ****@****> select 1
                2  from dual
                3  connect by rownum < 10
                4  /

                       1
              ----------
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1
                       1

              9 rows selected.


              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 1350848739

              ------------------------------------------------------------------------------
              | Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     |
              ------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT              |      |     1 |     2   (0)| 00:00:01 |
              |   1 |  COUNT                        |      |       |            |          |
              |*  2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          |
              |   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 |
              ------------------------------------------------------------------------------

              Predicate Information (identified by operation id):
              ---------------------------------------------------

                 2 - filter(ROWNUM<10)


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

              ****@****>
              ****@****> exit;
              Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
              With the Partitioning, Real Application Clusters, OLAP and Data Mining options
              Bye Alessandro
              • 34. Re: Count(*)/Count(1)
                584983
                Sorry for posting so late.

                In the "test run" I had posted I had completely overlooked the OS caching.And the test is not a perfect benchmark.See output below for reverse order of queries under similar circumstances.

                Another option was to restart the OS for each query and try to benchmark.But its not worth trying because even if in such scenario count(1) proves faster we will have to investigate reasons for count(1) being faster given the FACT that count(1) is rewritten as count(*).

                Thanks Padders......excellent demonstration and Blushadow and Billy for explaining it in detail which left no doubts at all.


                SQL> startup
                ORACLE instance started.

                Total System Global Area 167772160 bytes
                Fixed Size 1247900 bytes
                Variable Size 79693156 bytes
                Database Buffers 83886080 bytes
                Redo Buffers 2945024 bytes
                Database mounted.
                Database opened.
                SQL> conn scott/tiger@ocp10g
                Connected.
                SQL> set lines 10000
                SQL> set timing on
                SQL> set autotrace on
                SQL> select count(1) from dept1;

                COUNT(1)
                ----------
                8388608

                Elapsed: 00:03:57.35

                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 1828124803

                --------------------------------------------------------------------
                | Id | Operation | Name | Rows | Cost (%CPU)| Time |
                --------------------------------------------------------------------
                | 0 | SELECT STATEMENT | | 1 | 6176 (5)| 00:01:15 |
                | 1 | SORT AGGREGATE | | 1 | | |
                | 2 | TABLE ACCESS FULL| DEPT1 | 9735K| 6176 (5)| 00:01:15 |
                --------------------------------------------------------------------

                Note
                -----
                - dynamic sampling used for this statement


                Statistics
                ----------------------------------------------------------
                235 recursive calls
                1 db block gets
                53990 consistent gets
                26982 physical reads
                1939472 redo size
                414 bytes sent via SQL*Net to client
                381 bytes received via SQL*Net from client
                2 SQL*Net roundtrips to/from client
                4 sorts (memory)
                0 sorts (disk)
                1 rows processed

                SQL> conn / as sysdba
                Connected.
                SQL> shutdown immediate
                Database closed.
                Database dismounted.
                ORACLE instance shut down.
                SQL> startup
                ORACLE instance started.

                Total System Global Area 167772160 bytes
                Fixed Size 1247900 bytes
                Variable Size 83887460 bytes
                Database Buffers 79691776 bytes
                Redo Buffers 2945024 bytes
                Database mounted.
                Database opened.
                SQL> conn scott/tiger@ocp10g
                Connected.
                SQL> set lines 10000
                SQL> set timing on
                SQL> set autotrace on
                SQL> select count(*) from dept1;

                COUNT(*)
                ----------
                8388608

                Elapsed: 00:02:33.60

                Execution Plan
                ----------------------------------------------------------
                Plan hash value: 1828124803

                --------------------------------------------------------------------
                | Id | Operation | Name | Rows | Cost (%CPU)| Time |
                --------------------------------------------------------------------
                | 0 | SELECT STATEMENT | | 1 | 6176 (5)| 00:01:15 |
                | 1 | SORT AGGREGATE | | 1 | | |
                | 2 | TABLE ACCESS FULL| DEPT1 | 9735K| 6176 (5)| 00:01:15 |
                --------------------------------------------------------------------

                Note
                -----
                - dynamic sampling used for this statement


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