Re: freeing space after dropping a table

Connor McDonald schrieb:
>
> Elizabeth Jones wrote:
> >
> > I have an oracle query that I want to run on a production
> > system where we can't afford to have miscellaneous dropped
> > tables taking up space. The query that I want to run takes
> > a couple of hours if I use subqueries but if I stuff the
> > data into temporary tables and join the tables it runs
> > in about 5 minutes. The way that I am running the query
> > is I create the tables, stuff them, truncate them and
> > then drop them, and I'm not doing any commits. However,
> > am I running a risk whenever I run this query that the
> > space is not getting released when I truncate/drop? also,
> > would it be better if I threw in a commit after the
> > truncate/drop to try to ensure that these take effect?
> >
> > --
> > Elizabeth Jones ************************** bajones_at_sky.net
> > **********************************************************
> > Need a brittany? http://www.geocities.com/~brittanyrescue
> > **********************************************************
>
> truncate and drop both issue implicit commits before and after they
> execute (as does any DDL command).
>
> The important thing in terms of used/reused/freed space is the sizes of
> your objects....
>
> For example, (in the ideal world) where every object had an extent size
> of (say) 1m - then evey time 1m became free (from truncate or drop) then
> a different object could reuse it...
>
> Limit the number of "different" extent sizes to a smaller number and
> space re-use becomes more efficient than every object having a unique
> size...
>

Hi,

in addition to Connor's posting:

As Connor mentioned: DDL statements always issue a COMMIT. So be carefull where to place your DDL stats so that they don't disturbe your transcation processing!

HTH
Matthias
--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Fri Jan 22 1999 - 00:08:49 CST