Re: Why Not Allow Temp Tables?

"(Pete Cresswell)" <x_at_y.z> wrote in message news:8hpsmvshfq5s9vtckp44n4bofl7o1cg3iu_at_4ax.com...
> RE/
> >The reasons are as follows:
> >
> >1. Kills performance and scalability
> >2. The privileges to create objects do not belong in a production schema
> >except during brief periods when it is being built or modified
> >under the direct supervision of the DBA.
> >3. Completely unncessary so no reason to do it.
>
> I'm not trying to be difficult - I'm taking everything that's been said to
> heart...but I'd also like to be able to argue the point with somebody who
tells
> me they want to use Temp tables.
>
> #1 makes sense - but it would be good to have a little insight into why.
>
> #2 sounds like argument by assertion - but that's probably because I don't
know
> diddley.... Something about objects being created/destroyed impacting
> performance would be my first guess.
>
> #3, I'm guessing, is because of the availablity of global temp tables.
True?
> -----------------------
> PeteCresswell

DDL causes serious contention issues. Its expensive when used in a transaction system and using them can slow down OTHER transactions. Your DBAs should have told you to use a 'Global Temporary Table' What you do here is define the 'DDL' and then you can insert on the fly. When the user disconnects a session the data vanishes. They are faster than regular tables because they use temporary segments and generate less redo(thereby helping overall system performance).

You can search on those words on OTN. Very easy to use. Its good that your DBAs knew enough to not allow temporary tables, its BAD that they did not suggest this. Your DBAs need to read New Features documents. There job is not to say No dont do that. There job is to say, no thats a bad idea becase... here is a better solution. Received on Mon Sep 22 2003 - 22:31:58 CDT