Difference between count (*) and count (col) in the query

Difference between count (*) and count (col) in the query 1. create the test table declare l_ SQL varchar2 (32767); begin l_ SQL: = 'create table t ('; for I in 1 .. 999 loop l_ SQL: = l_ SQL | 'n' | I | 'number, '; end loop; l_ SQL: = l_ SQL | 'pad varchar2 (50 )) pctfree 10'; execute immediate l_ SQL; end;/we recommend that you create a table with 999 fields. insert data declare l_ SQL varchar2 (32767); begin l_ SQL: = 'insert into t select'; for I in 1 .. 999 loop l_ SQL: = l_ SQL | '0, '; end Loop; l_ SQL: = l_ SQL | 'null from dual connect by level <= 10000 '; execute immediate l_ SQL; commit; end;/Insert 10000 pieces of data 3. view the execution time DECLARE l_dummy PLS_INTEGER; l_start PLS_INTEGER; l_stop PLS_INTEGER; l_ SQL VARCHAR2 (100); BEGIN l_start: = dbms_utility.get_time; FOR j IN 1 .. 20 loop execute immediate 'select count (*) FROM t' INTO l_dummy; end loop; l_stop: = dbms_utility.get_time; dbms_output.put_line ('count star 20 Time is: '| to_char (l_stop-l_start)/100, '0. 99 '); FOR I IN 1 .. 250 LOOP l_ SQL: = 'select count (n' | I | ') FROM t'; l_start: = dbms_utility.get_time; FOR j IN 1 .. 20 loop execute immediate l_ SQL INTO l_dummy; END LOOP; l_stop: = dbms_utility.get_time; dbms_output.put_line ('count each column's 20 times is: '| to_char (l_stop-l_start)/100, '0. 99 '); end loop; END;/output result: the time of COUNT star for 20 times is 0.14COUNT. The time of each column for 20 times is 0.14COUNT. The time for each 0.15COUNT column is: 0.16COUNT the time for each column is 20: 0.15COUNT the time for each column is 20: 0.15COUNT the time for each column is 20: 0.15COUNT the time for each column is: the 20 times of each 0.16COUNT column is: 0.16COUNT the 20 times of each column is: 0.15COUNT the 20 times of each column is: 0.16COUNT the 20 times of each column is: the 20 times of each 0.16COUNT column is: 0.16COUNT the 20 times of each column is: 0.16COUNT the 20 times of each column is: 0.16COUNT the 20 times of each column is: the 20 times of each 0.16COUNT column is: 0.16COUNT the 20 times of each column is: 0.17COUNT the 20 times of each column is: 0.17COUNT the 20 times of each column is: the time for each 0.17COUNT column to 20 is: 0.17COUNT the time for each column to 20 is: 0.17COUNT the time for each column to 20 is: 0.18COUNT the time for each column to 20 times The time between the values is: 0.17COUNT, the time for each column is: 0.18COUNT, the time for each column is: 0.17COUNT, and the time for each column is: 0.18COUNT, and the time for each column is: 0.18COUNT is used for 20 times for each column: 0.18COUNT is used for 20 times for each column: 0.17COUNT is used for 20 times for each column: 0.18COUNT is used for 20 times for each column: 0.19COUNT: the time for each column is 0.18COUNT. The time for each column is 0.18COUNT. The time for each column is 0.18COUNT. The time for each column is 0.18COUNT: 0.19COUNT is used for 20 times for each column: 0.18COUNT is used for 20 times for each column: 0.19COUNT is used for 20 times for each column: 0.18COUNT is used for 20 times for each column: 0.19COUNT is used for 20 times for each column: 0.19COUNT is used for 20 times for each column: 0.19COUNT is used for 20 times for each column: 0.19COUNT is used for 20 times for each column The time for each 0.19COUNT column is: 0.19COUNT the time for each column is 20: 0.19COUNT the time for each column is 20: 0.19COUNT the time for each column is 20: 0.19COUNT the time for each column is: for each 0.20COUNT column, the time for 20 times is 0.19COUNT. For each column, the time for 20 times is 0.19COUNT. For each column, the time for 20 times is 0.20COUNT: for each 0.20COUNT column, the time for 20 times is 0.19COUNT. For each column, the time for 20 times is 0.20COUNT. For each column, the time for 20 times is 0.20COUNT: for each 0.20COUNT column, the time is 0.20COUNT. For each column, the time is 0.21COUNT. For each column, the time is 0.20COUNT. For each column, the time is 20: for each 0.20COUNT column, the time is 0.20COUNT. For each column, the time is 0.21COUNT. For each column, the time is 0.20COUNT. The time between the values is: 0.21COUNT, the time for each column is: 0.21COUNT, the time for each column is: 0.21COUNT, and the time for each column is: 0.21COUNT, and the time for each column is: 0.21COUNT is used for 20 times for each column: 0.21COUNT is used for 20 times for each column: 0.21COUNT is used for 20 times for each column: 0.22COUNT is used for 20 times for each column: 0.21COUNT is used for 20 times for each column: 0.22COUNT is used for 20 times for each column: 0.21COUNT is used for 20 times for each column: 0.22COUNT is used for 20 times for each column: the time for each 0.22COUNT column to 20 is: 0.22COUNT the time for each column to 20 is: 0.22COUNT the time for each column to 20 is: 0.22COUNT the time for each column to 20 is: for each 0.22COUNT column, the time for 20 times is 0.22COUNT. For each column, the time for 20 times is 0.22COUNT. For each column, the time for 20 times is 0.23COUNT. The time for each 0.23COUNT column is: 0.23COUNT the time for each column is 20: 0.22COUNT the time for each column is 20: 0.23COUNT the time for each column is 20: 0.23COUNT the time for each column is: for each 0.23COUNT column, the time is 0.23COUNT. For each column, the time is 0.23COUNT. For each column, the time is 0.23COUNT. For each column, the time is 0.23COUNT: for each 0.23COUNT column, the time is 0.24COUNT. For each column, the time is 0.23COUNT. For each column, the time is 0.23COUNT. For each column, the time is 20: for each 0.24COUNT column, the time for 20 times is 0.23COUNT. For each column, the time for 20 times is 0.24COUNT. For each column, the time for 20 times is 0.24COUNT: 0.24COUNT is used for 20 times for each column: 0.23COUNT is used for 20 times for each column: 0.24COUNT is used for 20 times for each column: 0.24COUNT is used for 20 times for each column The time between the values is: 0.24COUNT, the time for each column is: 0.25COUNT, the time for each column is: 0.24COUNT, and the time for each column is: 0.24COUNT, and the time for each column is: 0.25COUNT is used for 20 times for each column: 0.24COUNT is used for 20 times for each column: 0.25COUNT is used for 20 times for each column: 0.24COUNT is used for 20 times for each column: 0.25COUNT is used for 20 times for each column: 0.25COUNT is used for 20 times for each column: 0.25COUNT is used for 20 times for each column: 0.25COUNT is used for 20 times for each column: 0.25COUNT is used for 20 times for each column: 0.25COUNT is used for 20 times for each column: 0.25COUNT is used for 20 times for each column: 0.26COUNT is used for 20 times for each column: 0.25COUNT is used for 20 times for each column: 0.26COUNT is used for 20 times for each column: 0.25COUNT is used for 20 times for each column: 0.26COUNT is used for 20 times for each column The time between the values is: 0.25COUNT, the time for each column is: 0.26COUNT, the time for each column is: 0.26COUNT, the time for each column is: 0.26COUNT, and the time for each column is: 0.26COUNT is used for 20 times for each column: 0.26COUNT is used for 20 times for each column: 0.26COUNT is used for 20 times for each column: 0.27COUNT is used for 20 times for each column: 0.26COUNT is used for 20 times for each column: 0.26COUNT is used for 20 times for each column: 0.27COUNT is used for 20 times for each column: 0.26COUNT is used for 20 times for each column: 0.27COUNT is used for 20 times for each column: 0.26COUNT is used for 20 times for each column: 0.27COUNT is used for 20 times for each column: 0.27COUNT is used for 20 times for each column: 0.27COUNT is used for 20 times for each column: 0.27COUNT is used for 20 times for each column: 0.27COUNT is used for 20 times for each column: 0.27COUNT is used for 20 times for each column The time between the values is: 0.27COUNT, the time for each column is: 0.28COUNT, the time for each column is: 0.27COUNT, and the time for each column is: 0.28COUNT, and the time for each column is: 0.27COUNT is used for 20 times for each column: 0.28COUNT is used for 20 times for each column: 0.28COUNT is used for 20 times for each column: 0.28COUNT is used for 20 times for each column: 0.28COUNT is used for 20 times for each column: 0.28COUNT is used for 20 times for each column: 0.28COUNT is used for 20 times for each column: 0.28COUNT is used for 20 times for each column: 0.28COUNT is used for 20 times for each column: 0.29COUNT is used for 20 times for each column: 0.28COUNT is used for 20 times for each column: 0.28COUNT is used for 20 times for each column: the time for each 0.29COUNT column to 20 is: 0.28COUNT the time for each column to 20 is: 0.29COUNT the time for each column to 20 is: 0.28COUNT the time for each column to 20 times The time for each 0.29COUNT column is: 0.29COUNT the time for each column is 20: 0.28COUNT the time for each column is 20: 0.29COUNT the time for each column is 20: the time for each 0.29COUNT column to 20 times is: 0.29COUNT the time for each column to 20 times is: 0.29COUNT the time for each column to 20 times is: 0.30COUNT the time for each column to 20 times is: the time for each 0.29COUNT column to 20 times is: 0.29COUNT the time for each column to 20 times is: 0.29COUNT the time for each column to 20 times is: 0.30COUNT the time for each column to 20 times is: the time for each 0.29COUNT column to 20 is: 0.30COUNT the time for each column to 20 is: 0.30COUNT the time for each column to 20 is: 0.29COUNT the time for each column to 20 is: for each 0.29COUNT column, the time is 0.30COUNT. For each column, the time is 0.30COUNT. For each column, the time is 0.30COUNT. For each column, the time is 0.30COUNT. The time between: 0.30COUNT for each column is: 0.30COUNT for each column 20 times: 0.31COUNT for each column 20 times: 0.30COUNT for each column 20 times: for each 0.30COUNT column, the time for 20 times is 0.31COUNT. For each column, the time for 20 times is 0.30COUNT. For each column, the time for 20 times is 0.31COUNT: 0.31COUNT is used for 20 times for each column: 0.30COUNT is used for 20 times for each column: 0.31COUNT is used for 20 times for each column: 0.31COUNT is used for 20 times for each column: 0.31COUNT is used for 20 times for each column: 0.31COUNT is used for 20 times for each column: 0.32COUNT is used for 20 times for each column: 0.31COUNT is used for 20 times for each column: 0.31COUNT is used for 20 times for each column: 0.31COUNT is used for 20 times for each column: 0.32COUNT is used for 20 times for each column: 0.31COUNT is used for 20 times for each column The time for each 0.32COUNT column is: 0.31COUNT the time for each column is 20: 0.32COUNT the time for each column is 20: 0.32COUNT the time for each column is 20: 0.32COUNT the time for each column is: 0.31COUNT is used for 20 times for each column: 0.32COUNT is used for 20 times for each column: 0.32COUNT is used for 20 times for each column: 0.32COUNT is used for 20 times for each column: 0.32COUNT is used for 20 times for each column: 0.32COUNT is used for 20 times for each column: 0.33COUNT is used for 20 times for each column: 0.32COUNT is used for 20 times for each column: 0.32COUNT is used for 20 times for each column: 0.32COUNT is used for 20 times for each column: 0.33COUNT is used for 20 times for each column: 0.32COUNT is used for 20 times for each column: 0.33COUNT is used for 20 times for each column: 0.32COUNT is used for 20 times for each column: 0.33COUNT is used for 20 times for each column: 0.32COUNT is used for 20 times for each column The time for each 0.33COUNT column is: 0.32COUNT the time for each column is 20: 0.33COUNT the time for each column is 20: 0.33COUNT the time for each column is 20: 0.33COUNT the time for each column is: 0.33COUNT is used for 20 times for each column: 0.33COUNT is used for 20 times for each column: 0.33COUNT is used for 20 times for each column: 0.33COUNT is used for 20 times for each column: the time for each 0.33COUNT column to 20 is: 0.33COUNT the time for each column to 20 is: 0.33COUNT the time for each column to 20 is: 0.34COUNT the time for each column to 20 is: for each 0.33COUNT column, the time for 20 times is 0.30COUNT. For each column, the time for 20 times is 0.29COUNT. For each column, the time for 20 times is 0.31COUNT: 0.30COUNT is used for 20 times for each column: 0.31COUNT is used for 20 times for each column: 0.31COUNT is used for 20 times for each column: 0.32COUNT is used for 20 times for each column The time for each 0.31COUNT column is: 0.31COUNT the time for each column is 20: 0.31COUNT the time for each column is 20: 0.32COUNT the time for each column is 20: 0.31COUNT is used for 20 times for each column: 0.31COUNT is used for 20 times for each column: 0.32COUNT is used for 20 times for each column: 0.31COUNT is used for 20 times for each column: 0.32 it takes a longer time to see the columns that follow, but it is the same as count (1) and count (*) because 1 is a data column and does not need to be located.