Re: Recommendation on issuing DDL in applications

dfg778_at_yahoo.com.au (Matthew Keene) wrote in message news:<bb27253c.0308272308.72bb92b9_at_posting.google.com>...
> We have had a proposal at our site for an application which would
> create a temporary table dynamically, populate it and then drop it.
> My general feeling was that this is not a very good idea, but I was
> looking for some sort of evidence to support this (unfortunately 'the
> DBA says no' is not enough as a reason). I did some research on
> Metalink and on the web to come up with some reasons I could give them
> to persuade them not to do this, but the best that I could come up
> with was the following quote from the 9i Database Design Techniques
> manual
>
> "As a general database design rule, you should only use DDL statements
> for maintenance tasks, not during normal system operations. Therefore,
> in most systems, the frequency of new object creation and other DDL
> statements should be very small."
>
> Does anybody have any more specific information that I can use to
> frighten them away, oops, I mean rationally discourage them from this
> solution, or am I being unnecessarily alarmist ?

asktom.oracle.com lots on there about it. Its bad. How bad depends on what you are doing. If this is for a transaction database, its horrible. If your doing a bulk load, that doesnt need to scale, pick your battles. You might be able to live with it. No its not the proper way to do this, but you cant win every time.

DDL causes contention. Doing it alot causes alot of contention which 'blocks' people from accessing latches. This can cause large bottlenecks and bring your DB to a screaching halt.

One way to handle this may be to come up with an alternative solution that is simple and elegant. Find out why they want to make temp tables on the fly? Are they SQL SErver people? I hear this from sql server all the time that DDL in transaction-SQL is a feature. Well its not one in Oracle. ITs a performance killer.

Is it possible to create global temporary tables? Then they can be inserted at run time? These cut redo in half, no latch contention either. You dont have to worry about deleting from the either. Handled for you.

Look at 'why' they want to do this. Analyze their process. This is all over tom kytes site. Post a question on the metalink forum. See what they say.

Its a really bad idea and will not scale. I have found that very few people INCLUDING management worry about tomorrow or about scaling or maintenance.

You could always tell them its a bad idea, argue a bit. Lose. Then when the database stops moving. Ask for a raise. Received on Thu Aug 28 2003 - 12:51:19 CDT