RE: ** temp table or permanent
- From: "Igor Neyman" <ineyman@xxxxxxxxxxxxxx>
- To: <ajoshi977@xxxxxxxxx>, <pythianbrinsmead@xxxxxxxxx>
- Date: Thu, 6 Jul 2006 09:27:23 -0400
If GTT fits in memory (no physical IO), why should it be slower than
PL/SQL table?
Igor
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of A Joshi
Sent: Thursday, July 06, 2006 9:14 AM
To: pythianbrinsmead@xxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: ** temp table or permanent
Mark,
Yes. Optimize/rewrite query was done. Then it was split up. Iam on
8.1.7 so no UNDO and I cannot increase the rolback segment any more.
apart from storgae and memory, I think there is a performance impact too
if I increase the size of rllback segments.
So now the question is whether global temporary table or PL/SQL
table. I assume PL/SQL table is always faster than temporary table.
I am avoiding permament table or materialized view since that will
involve redo/undo and increase the time. Thanks for your help.
Mark Brinsmead <pythianbrinsmead@xxxxxxxxx> wrote:
You seem to be jumping through a lot of hoops to solve a fairly
straight-forward (sounding) problem.
Your first step should be to optimize / rewrite the query to
perform less I/O and or execute more quickly.
Your second step should be to increase UNDO / Rollback as
needed, within practical limits, of course.
After this, you might consider temporary tables (Global
Temporary Table recommended) etc. or
Materialized Views.
Of course, advice like this is easy to give when I know
*nothing* of your specific situation. ;-)
On 7/5/06, A Joshi < ajoshi977@xxxxxxxxx
<mailto:ajoshi977@xxxxxxxxx> > wrote:
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.
--
Cheers,
-- Mark Brinsmead
Staff DBA,
The Pythian Group
http://www.pythian.com/blogs
________________________________
Do you Yahoo!?
Next-gen email? Have it all with the all-new Yahoo! Mail Beta.
<http://us.rd.yahoo.com/evt=40788/*http://advision.webevents.yahoo.com/h
andraisers>
Other related posts:
