Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic Table creation

Re: Dynamic Table creation

From: David Lane <dlane_at_cix.compulink.co.uk>
Date: Fri, 5 Mar 1999 08:59:42 GMT
Message-ID: <F846BJ.544@cix.compulink.co.uk>


Andreas .... Thanks very much for your comments.

    I agree about your point about tables being useful for storing temporary results - we do have a number of tables used explicitly for this, but they are permanent and re-used.

    What I didn't explain in my original message was that all the data tables and packages are owned by the live user. When I talked about ad-hoc queries, these are 'pre-planned ad-hoc queries' in that it is via a PowerBuilder interface which allows the user to drag-and-drop any number of fields from various live tables and query on them. The query has  (up to now) been run interactively, but will be run in batch via a package - informing the user when it finishes. Without my knowledge, the developer decided to to this by creating 'temporary' results tables - which then have to be granted to the user - and remain there until the user has retrieved the results.

    ..... Dave Lane     

In article <7bm4n7$cgu$1_at_nnrp1.dejanews.com>, andreas.prusch_at_sintec.de () wrote:

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

Dave Lane (dlane_at_pt.lu) Received on Fri Mar 05 1999 - 02:59:42 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US