Let’s assume for a moment that your query is sort of okay.

 

When a query dies because you run out of rollback, you have to determine whether someone else is pounding on (changing) your source data or whether you are generating more change (meaning your query is an update/insert/delete query, not just a select) than can be fit in the existing undo (rollback). If the latter (and you can’t increase space for undo/rollback), then you have to find a way to break up the monolith into smaller commitable chunks. If the former, then you need to find a way to either reduce the rate of change on your source data or decrease the time to pull the source data.

 

Let’s assume it is other queries updating your source data. Then you don’t really need to worry much about the rollback generated making a “permanent” temporary table, especially if you’re willing to do that part unrecoverably. You just need to produce it quickly and commit it. If it is huge, you might need to create and add to it in chunks, but that really blows up read consistency. Further, you might do well to create an index and statistics on the interim result, but that depends on its relationship with the rest of the query. Once you’re working on all private copies of interim results, you don’t have to worry about other folks updating the source as regards rollback, but of course you’re not read consistent with the original source. So if you have multiple interim results to produce, you need to start the queries at the same time. You can’t *really* guarantee that, so I hope close is good enough. Then the interim sources you’re querying together will at least be nearly read consistent with each other, which is pretty close to read consistent with the time you started the original queries producing the interim results. If you produce just the one interim result set and then combine it with rapidly changing source data, I hope you don’t need read consistency at all.

 

Now if you won’t benefit from an index and stats on the interim results, temp or pl/sql table should be okay. If this is not a stored procedure, and especially if you are client server and the pl/sql job is running on a client that is faster than the database server in CPU speed, then you have to balance pulling the entire interim result across the network into your client PL/SQL table versus the faster local CPU.

 

The other ways to do all this are with a renamed point in time recovered clone of a stand-by database that is recent enough for your purposes or a standby open for read which is not currently having redo applied.

 

(None of which applies to the “you are the updater blowing up rollback”, but you fix that by reducing the size of the transaction.

 

Good luck,

 

mwf

 

 

 

-----Original Message-----
From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of A Joshi
Sent: Wednesday, July 05, 2006 10:43 AM
To: oracle-l@freelists.org
Subject: ** temp table or permanent

 

Hi,

   I had a big query which was running out of rollback so after some tuning i had to break it down in to two. from the first query i store the result in a temporary table and then use it for second part. it is still slow. i did not use permament table since that could involve rollback/redo. but still i will try to check it. i am also thinking of pl/sql table but if that always remains in memory then it could impact memory or sga. pl/sql table uses pga or OS memory but if paging swapping occurs then it would impact sga too. Will it always be faster than temp or permanent table? Can some one give feedback if you had similar experience. Thanks for help.

 

Talk is cheap. Use Yahoo! Messenger to make PC-to-Phone calls. Great rates starting at 1¢/min.