SELECT COUNT(*)

最近在客戶那邊遇到了一個效能上的問題。客戶有一個程式會對資料表不斷地新增資料，有另外一支程式會來處理新增進入資料表格的資料，並對處理的結果異動其狀態；但最後不會將該資料刪除。也就是說該資料表格會愈長愈大。
客戶也會要知道，目前待處理的資料筆數還有多少，最簡單的方式就是 SELECT COUNT(*)....一開始當然速度是在可以接受的情況下，但隨著時間，資料筆數愈來愈大，每次 SELECT COUNT(*)的時間就愈來愈長...。然後加入自動更新(Refresh)，從5秒自動更新變成10秒, 30秒，....；再加上同時之間有許多使用者會同時上線，然後你會看到資料庫主機CPU都花在USER I/O，速度愈變愈慢....
很熟悉嗎? 如何改善執行效率呢?
我的想法是：沒辦法，除非改變程式 SELECT COUNT(*)的架構，不然 USER I/O是無法避免的。怎麼做--利用一資料表格來儲存計數 -- SELECT COUNT(*) -- 的結果，新增資料的程式則異動該值+1；處理異動的程式則異動該值-1；查詢筆數的程式則直接取得該值。

建立計數儲存表格
create table table_row_counts (table_name varchar2(40), row_counts number);

設定初始值、讀取數值、新增數值與異動...等
當然直接UPDATE是一種方法，在這我使用 stored procedure

create or replace
PACKAGE ROW_COUNTS AS
  procedure init_rows;
  procedure add_row;
  procedure reduce_row;
  function get_rows return number;
END ROW_COUNTS;
/

create or replace
package body row_counts as

function get_rows return number as
rowsn number;
begin
select row_counts into rowsn from table_row_counts where table_name = 'EMP';
return rowsn;
end;

procedure add_row as
begin
update table_row_counts set row_counts = row_counts + 1 where table_name = 'EMP';
commit;
end;

procedure init_rows as
begin
update table_row_counts set row_counts = (select count(*) from emp ) where  table_name = 'EMP';
commit;
end;

procedure reduce_row as
begin
update table_row_counts set row_counts = row_counts - 1 where table_name = 'EMP';
commit;
end;

end;


/