Here is my research's output :
I used an Oracle 9.2 database. Table has 643286 records with 23 columns, each of length 150 (all varchar2). No indexes at all.
I created a store-proc and put the same 'select count(*)' four times, one after the other (4 times to average out):
begin
v_start_time := DBMS_UTILITY.GET_TIME;
select count(*) into v_dummy_count from customer_link;
v_end_time := DBMS_UTILITY.GET_TIME;
dbms_output.put_line('Seconds elapsed = '||(v_end_time-v_start_time)/100);
v_start_time := DBMS_UTILITY.GET_TIME;
select count(*) into v_dummy_count from customer_link;
v_end_time := DBMS_UTILITY.GET_TIME;
dbms_output.put_line('Seconds elapsed = '||(v_end_time-v_start_time)/100);
v_start_time := DBMS_UTILITY.GET_TIME;
select count(*) into v_dummy_count from customer_link;
v_end_time := DBMS_UTILITY.GET_TIME;
dbms_output.put_line('Seconds elapsed = '||(v_end_time-v_start_time)/100);
v_start_time := DBMS_UTILITY.GET_TIME;
select count(*) into v_dummy_count from customer_link;
v_end_time := DBMS_UTILITY.GET_TIME;
dbms_output.put_line('Seconds elapsed = '||(v_end_time-v_start_time)/100);
end;
I ran this first, then modified it to 'select count(1)' for all 4 times, ran it. And again modified it for 'select count(2)'. The results are as below.
GIST : the count(*) seems to run faster than the others !
- radha
-----------------------------------------------------------------
==>> "select count(*)..." :
13:44:42 Start Executing PL/SQL block ...
13:44:42 Starting execution of PL/SQL block...
Seconds elapsed = 1.68
Seconds elapsed = 1.77
Seconds elapsed = 1.73
Seconds elapsed = 1.78
13:44:49 Execution finished, retrieved 4 lines, execution time: 6.969 seconds.
13:44:49 End Executing PL/SQL block
==>>"select count(1)..." :
13:45:54 Start Executing PL/SQL block ...
13:45:54 Starting execution of PL/SQL block...
Seconds elapsed = 1.83
Seconds elapsed = 1.78
Seconds elapsed = 1.8
Seconds elapsed = 1.75
13:46:01 Execution finished, retrieved 4 lines, execution time: 7.156 seconds.
13:46:01 End Executing PL/SQL block
==>"select count(2)..." :
13:46:37 Start Executing PL/SQL block ...
13:46:37 Starting execution of PL/SQL block...
Seconds elapsed = 1.85
Seconds elapsed = 1.78
Seconds elapsed = 1.93
Seconds elapsed = 1.81
13:46:45 Execution finished, retrieved 4 lines, execution time: 7.390 seconds.
13:46:45 End Executing PL/SQL block
Oracle LazyDBA home page