Count (1) compared to count (*):
- If your data table does not have a primary key, then count (1) is faster than COUNT (*)
- If there is a primary key, then the primary key (the Federated primary key) as the count condition is also faster than COUNT (*)
- If your table has only one field, then count (*) is the quickest.
- COUNT (*) count (1) compares the two. The data field that corresponds to count (1) is mostly still.
- If count (1) is a clustered index, ID, that must be count (1) fast. But the difference is very small.
- Because count (*), automatically optimizes the specified field to that one. So there is no need to count (?), with count (*), SQL will help you to complete the optimized
- Count Detailed:
- COUNT (*) returns the total number of rows that exist in the table, including rows with a value of null, whereas count (column name) returns the total number of all rows except null in the table (columns with default values will also be counted).
- Distinct the column name, the result will be the result of dropping the value null and repeating the data
- --------------------------------------------------------------------------------------------------------------- -
- Examples are shown below:
- sql> Create table test
- 2 (
- 3 ename VARCHAR2 (10),
- 4 sal Number (4)
- 5);
- The table is created.
- sql> INSERT INTO test values (' Fxe1 ', 90);
- 1 rows have been created.
- sql> INSERT INTO test (ename) values (' fxe2 ');
- 1 rows have been created.
- sql> INSERT INTO test (ename) values (' fxe3 ');
- 1 rows have been created.
- sql> INSERT INTO test (ename) values (' fxe4 ');
- 1 rows have been created.
- sql> INSERT INTO test values (' fxe5 ', 80);
- 1 rows have been created.
- sql> INSERT INTO test values (' fxe6 ', 80);
- 1 rows have been created.
- Sql> SELECT * from test;
- Ename SAL
- ---------- ----------
- FXE1 90
- Fxe2
- Fxe3
- Fxe4
- Fxe5 80
- Fxe6 80
- sql> Select Count (*) from test;
- COUNT (*)
- ----------
- 6
- sql> Select count (sal) from test;
- COUNT (SAL)
- ----------
- 3
- sql> Select count (distinct sal) from test;
- COUNT (Distinctsal)
- ------------------
- 2
- sql> Select distinct sal from test;
- SAL
- ----------
- 80
- 90
COUNT (1) compared to COUNT (*):
