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 10.2.0.1.0 - 64bit Production
Suggestions please.
Marked 
