| Disable Rollback and Warehouse Loading [message #92912] |
Mon, 04 March 2002 03:18  |
tom Sikora
Messages: 1
Registered: March 2002
|
Junior Member |
|
|
I am loading many tables from an operational store and have had (many!) occasions to cancel the load mid-stream. The rollback of the inserts from a select takes a long time.
Is there any way to diable the rollback (transaction logging?) feature of Oracle, especially when loading a warehouse table? There is but one commit, at the end, and errors or restarts waste lots of rollback time.
Note: If an error occurs, the expectation is to start all over again and no "recovery" is really needed. We are running 8.1.7 of Oracle.
|
|
|
|
| Re: Disable Rollback and Warehouse Loading [message #92913 is a reply to message #92912] |
Mon, 04 March 2002 07:35  |
Wei Lang
Messages: 23
Registered: March 2002
|
Junior Member |
|
|
There are two problems related to rollback segment for long "insert .. select":
1. If the operational store is doing a lot of DML, the rollback segement of your opreational database is very large because Oracle try to maintain the read consistancy.
2. If you want to cancel the "insert ...select", the uncommited data in data warehouse need to rollback. As you said, it could take long time.
You can disable the redo logging (for roll forward) in data warehouse but it is not the rollback. My suggestion for your problem is:
1. Use PL/SQL to do the insert to a new table in data warehouse and commit often. Do not open a cursor to long.
2. Do the extraction of data while the load of operational database is light.
3. If you want to cancel the load, kill the pl/sql and drop the new table.
4. After extraction of data, use pl/sql to insert data from new table to your data warehouse table and commit often.
Hope it helps.
Wei
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
