Re: Any possibility for faster INSERTS/UPDATES?

sybrandb_at_my-deja.com writes:

> You can't disable them (and you shouldn't do so while it is one of the
> key features of an RDBMS). Sorry to say so but this sounds like a
> typical newbie remark.

First of all you can, it's called discrete transactions. There's a DBMS.something call to use them. There's also an init.ora parameter which sets it globally. If you take a cold backup before you begin this work, switch on the init.ora parameter, do all your work, reset the parameter and restart the database and verify the integrity of all the data afterwards then this might be a reasonable approach. Use of this option is normally discouraged though.

It may be a typical newbie remark but it's also a typical experienced developer rant. There are *many* circumstances where consistent snapshots are completely unnecessary and rollback is a waste of resources. That Oracle has absolutely no facility for dirty reads makes a lot of pain in a lot of circumstances.

Consider a long-running batch report running against millions of records all of which are constantly being updated by an OLTP application. For purposes of the report it doesn't matter if i get an inconsistent view of some records as of 1am and some records as of 3am as long as all records are included in some state from the period in which the report was running.

In Oracle there is absolutely no way to do this without allocating rollback segments large enough to hold every update applied in the two hour long period. (Worse you have to play games to convince Oracle to actually use this rollback, it will normally feel free throw away the data once the updates have been committed even though the report needs them.)

It would be very nice to be able to perform large batch reports without contriving to force Oracle to keep hours of rollback information.

-- 
greg