Scratch (Transient) Table

[Calvin_Qiu]

We have a table of MEMBERS_ONLINE in a BBS application, and this table will have lots of DMLs, say 3 insert and 3 delete and 9 update per second, and the total rows number keeps on about 3,000.

What's your opinion to deal with it? I think Oracle is very inefficient in this case cause of undo and redo records generated along, and temporary table is impossible here for every session need to query this table.

I guess that a global memory collection maintained by JSP program would be a feasible solution?

[slimdave]

[QUOTE] 3 insert and 3 delete and 9 update per second, and the total rows number keeps on about 3,000./QUOTE]

This is trivial. Just use a regular table.

[Calvin_Qiu]

That does throw out at least 24 changes on data block, undo block, and redo block, and this table is not crucial table, just for temporary application data. Do you think it is worthwhile?

[slimdave]

You tell me -- does your application need it?

[oravijay]

Hi,

If it is only a temporary table then you can create / change the table as "NOLOGGING" so that the amount of redo / undo generated will be very minimal

HTH.

[slimdave]

Only true in very limited circumstances -- probably not in this case. I think that NOLOGGING would have no effect here.

[Calvin_Qiu]

NOLOGGING isn't helpful for these changes are one by one unless it is stored in temporary tablespace, but it is a global table which need to be queried by all session.

I'm thinking if it is possible having a global object maintained by JSP routine running in web server instead of a database table. It would be simply a memory data structure.

[chao_ping]

hi, I think you can just keep this table and alter the initial and max trans for this table.
Make sure your index is in good state.