Re: To use temp_tables or not

[Tom Dommermuth <tdommer@PRINCETON.EDU>]

HI Daniel,

The truncate works great on the table but from what's been told to me by our DBA
group for the TRUNCATE is that the space is reclaimed for the table but the
index space is not reclaim. All the data on the index is gone but the space it
was using before cannot be reclaimed unless you drop and recreate the index. So
if your index used 1MB of data each time you load the table, the index will grow
by about 1MB each time you reload data and will eventually run out of extents.
Also to help reclaim the same space for tables the REUSE STORAGE option
(TRUNCATE TABLE tablename REUSE STORAGE;) is good so that your table doesn't get
extented in size more than needed.

Thanks,
Tom Dommermuth.

Daniel Sanchez wrote:

> Hi, all.
>
> I do personally use temp tables as workbench tables (complex calcs and all
> that fun stuff), but rather than dropping them, I truncate them, in case I
> need them in the future. This was done by recommendation of our DBAs
> (tablespace and DB fragmentation).
>
> Now that I've mentioned the fragmentation issue, one of our DBAs also
> suggested to create another user with all kinds of rights using another
> schema or tablespace (DBA talk... it's either of these 2) so that it does
> not defrag the DB. Check with your DBA, if this affects you in any way.
>
> HTH. Thanks,
>
> Daniel A. Sanchez
>
> -----Original Message-----
> From:   Craig Gaudon [mailto:cgaudon@KILCOM1.UCIS.DAL.CA]
> <mailto:[mailto:cgaudon@KILCOM1.UCIS.DAL.CA]>
> Sent:   Monday, February 05, 2001 10:15 AM
> To:     SQR-USERS@list.iex.net <mailto:SQR-USERS@list.iex.net>
> Subject:        To use temp_tables or not
>
> Hi,
> I have a question. Is it better to create your SQR's by creating temporary
> tables,
> populating those tables with info from permanent tables, selecting from
> those
> tables for the report, and dropping the temporary tables, or is it better to
> not use
> temporary tables at all and just select from the permanent tables for the
> report?
> Being new to SQR and coming from Oracle Reports I just don't see the logic
> in
> using temporary tables.
>
> Craig Gaudon
> craig.gaudon@dal.ca <mailto:craig.gaudon@dal.ca>