Re: Dynamic Table creation

Temporary tables are ok in order to provide the result sets of queries much faster. Some queries are much easier to perform for the optimizer if they are piecewise splitted by the developer.

Although, they can prevent the server from evaluating the same query again and again.

If you use Oracle 7, you should use the unrecoverable option during inserts of data in the table. If you use Oracle 8 you should use nollogging with append hint during inserts of data in the table.

Sometimes it is much easier for the developers to browse table data then query data because the server provides only scanning the result sets of queries in one orientation, so query data have somewhere to save when the result sets are big and users are allowed to browse results. In addition, aggregation on result sets are much faster then doing the query again.

On the other hand your right with your antipathy when the above arguments pro temporary tables are not matching. Then there's the overhead of the dictionnary operations while creating and dropping the table (extent allocation and freeing, tablespace fragmentation, implicit commits), so that the temporary tables slows the database. Additionally, space allocation is always a serialized operation.

Hope this will help
Andreas Prusch

In article <F81MzE.48u_at_cix.compulink.co.uk>,   dlane_at_cix.compulink.co.uk ("David Lane") wrote:
> I'm looking for arguments to stop developers using dynamic creation of
> tables in their packages to hold results of ad-hoc queries - each query
> creating a seperate table which is kept until the end-user has had a
> chance to look at the results. We currently have 16Gb (of tables alone)
> for the users to query, so result sets could be big! I've put the usual
> arguments of: not being able to tune tables; worries about large number of
> undeleted tables filling the tablespaces; bad design; security problems
> and so on, but I've had to compromise by allowing them to be created in a
> seperate tablespace to minimise impact on other users and programs.
>
> Has anyone else had any experience of this - it just feels wrong to me!
>
> Dave Lane (dlane_at_pt.lu)
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Mar 04 1999 - 08:18:24 CST