Oracle

Oracle table design help

Please support our Oracle advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Oct 2009

Posts: 2

Reputation: johnbach is an unknown quantity at this point

Solved Threads: 0

johnbach

Offline

Newbie Poster

Oracle table design help

28 Days Ago

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

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
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);
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

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
amount,status ,COUNT(status)
10     N       1000000       
10     U        700000       
20     N        300000
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)

 Help with Code Tags 
 Oracle Syntax (Toggle Plain Text) 
UPDATE vouchers SET status='U' 
 WHERE amount=:txn_amount AND staus='N' AND ROWNUM=1
 RETURNING pin,extra_value INTO :pin,:extra;
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.

Last edited by johnbach; 28 Days Ago at 2:57 am. Reason: Oracle version