RE: ** temp table or permanent

["Mark W. Farnham" <mwf@xxxxxxxx>]

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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of A Joshi
Sent: Wednesday, July 05, 2006 10:43 AM
To: oracle-l@xxxxxxxxxxxxx
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.
<http://us.rd.yahoo.com/mail_us/taglines/postman7/*http://us.rd.yahoo.com/ev
t=39666/*http://messenger.yahoo.com>

• References:
  • ** temp table or permanent
    • From: A Joshi

Other related posts:

** temp table or permanent

Re: ** temp table or permanent

Re: ** temp table or permanent

Re: ** temp table or permanent

Re: ** temp table or permanent

Re: ** temp table or permanent

RE: ** temp table or permanent

RE: ** temp table or permanent

RE: ** temp table or permanent