Oracle table design help
 

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.

Oracle Advanced Queuing solved the problem.