Re: Recommendation on issuing DDL in applications

On 28 Aug 2003 00:08:17 -0700, dfg778_at_yahoo.com.au (Matthew Keene) wrote:

>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 ?

First of all, in Oracle, you would only very rarely need to create temporary tables, as you have inline views. Having to create temporary tables so can be an emergency measure, as people don't succeed to set up the query in ordinary sql.
Secondly those temporary tables need to be populated. This usually results in applications which are basically unscalable. I have to deal with one application, where a table is tracking 'events' in the system. This table is huge (sadly it contains basically nonsense when an order has been completed) and in order to avoid performance problems, the application has also a 'new_events' table: all events are inserted first in the new_events table, then inserted into the events table and deleted from the new_events table. The new_events table keeps growing and growing as the vendor doesn't allow to change pctincrease and pctused, the high water mark of that table keeps going up, until the system comes to a *complete* standstill (there are no indexes on the new_events table, as the new_events table normally doesn't contain records).
Needless to say this application has been developed for Sqlserver, and is a true nightmare when running on Oracle. Finally, also you have *globally temporary tables* in Oracle, why use permanent 'temporary' table. But I would be rather supiscious your team is symptom-fighting.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Aug 28 2003 - 04:04:06 CDT