Help, how do I simulate a temproary table ?

[stein.andersen]

Is it possible to avoid or delay writing to disk when inserting rows in a ordinary table. Oracle's temporary table is not suitable because of driver-pooling.

Are planning to use a temproary buffer-table to implement paging in a web-application, inserts rows into the table based on a SQL from the application (uses a function with EXECUTE IMMEDIATE). Since application is using ado/driver-pooling, it is not possiblel to use Oracle's "temporary table".

Have currently implemented the "temprorary table" with NOLOGGING and CACHE, speeds things up a bit but a avoidance or delay of disk-write would be optimal.

[tamilselvan]

Why don't you use ARRAY?

Tamil

[gamyers]

There's three bits of writing to disk.
First is the actual writing of the data to the data files. This is carried out in the background by the DBWR processes, and it 'batched up' so that its quite possible for a row that is inserted (committed) and deleted (committed) never to actually get written to the data file.
Because this is a background process, it shouldn't be affecting the end user processes except to the extent that it is another process on the server.
Second is the REDO log. This ensures that a committed change is always recoverable. There's not a lot you can do about this (though 10GR2 adds a commit .. nowait option so that the committing process doesn't actually wait to hear back from the log writer that it is done).
Third is the UNDO log, which ensures that an uncommitted change can be rolled back and is also used for read-consistency. NOLOGGING options can minimize the amount of UNDO, but these are generally only useful for bulk operations, not transaction processing.

If your end user processes flits between Oracle sessions, and you need to have a set of data persist for that user process then you'll either need to take the hit of committing it to the database, or storing that data at the mid-tier for that user process and have it passed between the mid-tier and the database.
The other option is do the connection pooling within oracle (shared server/multi-threaded server)

[pando]

Third is the UNDO log, which ensures that an uncommitted change can be rolled back and is also used for read-consistency. NOLOGGING options can minimize the amount of UNDO, but these are generally only useful for bulk operations, not transaction processing.

nologging is used to reduce both redo and undo