1

I haven't got a particular problem here, I'm just interested. One of my associates uses COUNT(DISTINCT(PRIMARY_KEY)) every time he needs to select all the data from a table (with 100m + rows) is it any quicker to do it this way rather than just count(*)? If it's the primary key surely the database knows that it's just pulling a count for the whole database and why?

  • Well, the best is to test. I doubt there is any difference, at least in Oracle, between COUNT(*), COUNT(0), COUNT(1), COUNT(pk), COUNT(DISTINCT pk), COUNT(42), COUNT(some_not_null_column) but surely only testing and examining the execution plans can prove it. Get some 10M, 100M, 1000M rows tables and time it. – ypercubeᵀᴹ Mar 24 '17 at 16:21
  • 1
    A PK is unique, hence why use 'distinct '? – McNets Mar 24 '17 at 16:22
  • @McNets in a single table count, no difference. If there are joins, it may do. – ypercubeᵀᴹ Mar 24 '17 at 16:23
  • So theoretically if there where joins why would it matter? I'd like to test but every time I run one iteration it caches my query and ruins the rest of the result set. – Michael Oddie Mar 24 '17 at 16:24
  • 1
    It is worth noting that 12.1 has: APPROX_COUNT_DISTINCT: docs.oracle.com/database/121/SQLRF/functions013.htm#SQLRF56900. While not an exact count, that may get you "close enough", if you just need an estimate and how fast you need the results back. My tests against a 500m table was that APPROX_COUNT_DISTINCT(pk) 44s was considerably faster than count(distinct pk) [killed it after 5 mins], and a little faster than count(*) 58s. – Kris Johnston Mar 24 '17 at 17:28
2

It is slower.

SQL> select count(*) from t1;

  COUNT(*)
----------
   4023040

SQL> alter table t1 add primary key (object_id);

Table altered.

SQL> select count(*) from t1;

  COUNT(*)
----------
   4023040

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |             |      1 |        |      1 |00:00:00.54 |    8852 |
|   1 |  SORT AGGREGATE       |             |      1 |      1 |      1 |00:00:00.54 |    8852 |
|   2 |   INDEX FAST FULL SCAN| SYS_C004079 |      1 |   3766K|   4023K|00:00:00.35 |    8852 |
-----------------------------------------------------------------------------------------------

Plain count(*) finished in 0.54 seconds.

SQL> select count(distinct object_id) from t1;

COUNT(DISTINCT(OBJECT_ID))
--------------------------
                   4023040

--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |             |      1 |        |      1 |00:00:05.22 |    8852 |   6944 |   6944 |       |       |          |         |
|   1 |  SORT AGGREGATE         |             |      1 |      1 |      1 |00:00:05.22 |    8852 |   6944 |   6944 |       |       |          |         |
|   2 |   VIEW                  | VW_DAG_0    |      1 |   3766K|   4023K|00:00:04.96 |    8852 |   6944 |   6944 |       |       |          |         |
|   3 |    HASH GROUP BY        |             |      1 |   3766K|   4023K|00:00:04.47 |    8852 |   6944 |   6944 |   178M|    21M|   32M (1)|   57344 |
|   4 |     INDEX FAST FULL SCAN| SYS_C004079 |      1 |   3766K|   4023K|00:00:00.41 |    8852 |      0 |      0 |       |       |          |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------

5.22 seconds, and it used 32 MB memory, and even had to use temp, because it has to GROUP BY because of DISTINCT.

  • This is exactly what I was looking for, thank you. Would you mind running the same query with another table joined? I'm curious if that would change the result as ypercubeᵀᴹ mentioned in his column earlier. – Michael Oddie Mar 24 '17 at 16:30
  • @MichaelOddie if you have joins the 2 expressions may give different results, so there is no point of comparing performance. – ypercubeᵀᴹ Mar 24 '17 at 16:34
  • While not explicitly asked for it, in the interest of completeness, consider including APPROX_COUNT_DISTINCT in your findings. – Kris Johnston Mar 24 '17 at 17:41
  • You don't show execution plan for vanilla count(*). It is slower in this example with a small table. I have seen large tables where Oracle's query optimizer chooses to perform the count using a bitmap index full scan. I don't know if in some circumstances the distinct is optimized away and no group by is performed. YMMV. – Grimaldi Mar 24 '17 at 22:13
  • @Grimaldi I do show the plan. No, it is not slower. It is faster even on a single row table. A bitmap index can not be used for enforcing a PK constraint, but it can be used to find the COUNT even without a NOT NULL constraint, because of how it works, so that is not a surprise. – Balazs Papp Mar 25 '17 at 11:19
3

The answer from Balazs Papp is no longer true with Oracle 12.2 (and probably later). The optimizer correctly detects that the distinct on a unique column will not change the result and uses the same execution plan for both queries:

SQL_ID  dvkqkk6fxj5hc, child number 0
-------------------------------------
select count(*) from t1

Plan hash value: 3763744789

-------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |  1112 (100)|      1 |00:00:00.24 |    4178 |
|   1 |  SORT AGGREGATE       |              |      1 |      1 |            |      1 |00:00:00.24 |    4178 |
|   2 |   INDEX FAST FULL SCAN| SYS_C0021608 |      1 |   2097K|  1112   (1)|   2097K|00:00:00.16 |    4178 |
-------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   2 - SEL$1 / T1@SEL$1

And the plan for count(distinct):

SQL_ID  147hfq5n6qvfw, child number 0
-------------------------------------
select count(distinct id) from t1

Plan hash value: 3763744789

-------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |      1 |        |  1112 (100)|      1 |00:00:00.24 |    4111 |
|   1 |  SORT AGGREGATE       |              |      1 |      1 |            |      1 |00:00:00.24 |    4111 |
|   2 |   INDEX FAST FULL SCAN| SYS_C0021608 |      1 |   2097K|  1112   (1)|   2097K|00:00:00.16 |    4111 |
-------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$BEC84FC4
   2 - SEL$BEC84FC4 / T1@SEL$1

The index SYS_C0021608 is the primary key index on the table. If the column is defined as NOT NULL and has a unique index, the same happens.

The Oracle version:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
0

A COUNT(DISTINCT key) basically has to pre-sort your data by key and sorting can be (very) expensive, especially on highly-cardinal data (with a lot of distinct values.

A simple COUNT(*) just has to count number of rows - no sorting involved, so it will always be faster than COUNT(DISTINCT).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.