Oracle blog

Cheap and light way to get record count frequently in a big table

How to get count(*) without really scanning all the records in a huge table:

If your requirement is to frequently get count of records in a huge table and to get it quickly, then you may use the following method to achieve it. One real life requirement example is to find the count of records in an exception queue table to determine how many application threads need to be started to process the exception queue for meeting SLA's.

Basically, we would create a new tracking table to be populated though trigger on the main table to maintain the counters for any inserts or deletes on the main table. We also need an Oracle sequence to distribute the counter trackers for better concurrency and locking mechanism. In essence, counter will be incremented by 1 for an insert and decremented by 1 for a delete statement.

Oracle Materialized view does similar thing but it is not scalable for a busy table (you will see enqueue contention).

create table test_tmp(id1 varchar2(30), id2 number, id3 number);
Table created.

create table test_cnt (id1 varchar2(30), id2 number, c1 char(25),
c2 number, c3 number) initrans 50; #This is the tracking table
Table created.

create unique index test_cnt_idx on test_cnt(id1, id2, c2);
Index created.

create sequence test_cnt_seq start with 1 increment by 1 maxvalue 100 cycle cache 20;
Sequence created.

create or replace trigger test_tr before insert or delete on test_tmp
for each row
declare
v1 number;
begin
select test_cnt_seq.nextval into v1 from dual;
if inserting then
update test_cnt set c3 = c3+1 where id1=:new.id1 and id2 = :new.id2 and c2=v1;
if sql%notfound then
insert into test_cnt values (:new.id1, :new.id2, 'X', v1, 1);
end if;
end if;
if deleting then
update test_cnt set c3 = c3-1 where id1=:old.id1 and id2 = :old.id2 and c2=v1;
if sql%notfound then
insert into test_cnt values (:old.id1, :new.id2, 'X', v1, -1);
end if;
end if;
end;
/

Trigger created.

insert into test_tmp values ('A', 1, 1);
1 row created.

insert into test_tmp values ('B', 2, 2);
1 row created.

insert into test_tmp values ('C', 3, 3);
1 row created.

insert into test_tmp values ('D', 4, 4);
1 row created.

insert into test_tmp values ('E', 5, 5);
1 row created.

commit;
Commit complete.

select id1, sum(c3) from test_cnt group by id1 having sum(c3) > 0;

id1 SUM(C3)
------------------------------ ----------
D 1
A 1
B 1
C 1
E 1

delete from test_tmp where id3=5;
1 row deleted.

commit;
Commit complete.

select id1, sum(c3) from test_cnt group by id1 having sum(c3) > 0;

id1 SUM(C3)
------------------------------ ----------
D 1
A 1
B 1
C 1

Triggers have it's downside and they can fire multiple times for the same record in a single sql statement execution as per http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:11504247549852

posted by Saibabu Devabhaktuni @ 11:33 PM