I have around 2 million vouchers of some fixed denomination (say 10 and 20)

create table vouchers(pin varchar2(20) primary key ,amount number,status char(1),extra_value number);
create index IDX_AMOUNT_STATUS on vouchers(amount,status);

Typical values

amount,status ,count(status)
10     N       1000000       
10     U        700000       
20     N        300000

For each payment transaction,I fetch one voucher wrt.transaction amount and mark it as used
(So it will not be used for any other transaction)

UPDATE vouchers SET status='U' 
 WHERE amount=:txn_amount AND staus='N' AND rownum=1
 RETURNING pin,extra_value INTO :pin,:extra;

I have 10 parallel threads processing incoming transaction. I am expecting 30 transactions per second.
Initially the update takes 0.002 millisecond but after few hours it reaches 2 seconds.

I even tried with partitioning table also(status N one partition and defalut another partition).

Platform details
Linux RHEL 5
Oracle Database 10g Enterprise Edition Release - 64bit Production

Suggestions please.

Edited by johnbach: Oracle version

7 Years
Discussion Span
Last Post by johnbach
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.