Re: Table-Extents / Performance

No, an "alter table xxx deallocate unused" only releases the extents above the HWM, and has nothing to do with truncate or delete. AFAIK the only way to reset the HWM is to use truncate....

Regards,

Johan

marcus.kuehl_at_t-online.de (waltervdv) wrote in message news:<b2677297.0109140025.1d27c86f_at_posting.google.com>...
> Thanks ,
>
> does an "alter table XXX deallocate unused" have the same effect on the HWM
> as a truncation ?
> I cannot use truncate on most of the tables, because there are foreign keys
> pointing to them....
>
> Marcus
>
>
> johan.lorier_at_nl.abb.com (Johan Lorier) wrote in message news:<c4ce07bf.0109130124.30ae0db0_at_posting.google.com>...
> > A 'select *' performs a full table scan, which scans the table from
> > the beginning until the High Water Mark is reached. A 'delete from
> > table' does not reset the HWM, and a truncate resets the HWM, and will
> > therefore take less time to scan an empty table.
> >
> > HTH,
> >
> > Johan
> >
> > marcus.kuehl_at_t-online.de (waltervdv) wrote in message news:<b2677297.0109120347.1f29d8b1_at_posting.google.com>...
> > > So,
> > >
> > > what is the reason, that a simple "select * " on an empty(!) table
> > > with many extents needs about 20 seconds and less then 2 seconds after
> > > a truncation of that table ??
> > >
> > > Thank You,
> > > Marcus
> > >
> > > "Steve Long" <steve-long_at_mediaone.net> wrote in message news:<6Rln7.43$vr1.6352_at_typhoon.jacksonville.mediaone.net>...
> > > > the following excerpt from the document you refer to should provide insight
> > > > to the questions you raise:
> > > >
> > > > Oracle supports an unlimited number of extents in a segment. The performance
> > > > for DML operations is largely independent of the number of extents in the
> > > > segment. However, certain DDL operations such as dropping and truncating of
> > > > segments are sensitive to the number of extents. Performance measures for
> > > > these operations have shown that a few thousand extents can be supported by
> > > > Oracle without a significant impact on performance. A reasonable maximum has
> > > > been determined to be 4096.
> > > >
> > > > The goal of our recommended algorithm is to keep the number of extents below
> > > > 1024 which is well within the range that Oracle can efficiently handle. When
> > > > a segment reaches 1024 extents it is a candidate to be moved to the next
> > > > larger extent size tablespace.
> > > >
> > > > "waltervdv" <marcus.kuehl_at_t-online.de> wrote in message
> > > > news:b2677297.0109110152.64cd8786_at_posting.google.com...
> > > > > Hallo !
> > > > >
> > > > > Recently I read in a paper that it's no problem to have tables with
> > > > > many extents in a database
> > > > > (http://technet.oracle.com/deploy/availability/pdf/defrag.pdf). My
> > > > > DBA-Handbook says, it is the best to put the whole table into one
> > > > > extent.
> > > > >
> > > > > I'm working on a database with no online-transactions - only
> > > > > batch-jobs (inserts and deletes, very few updates). Last week I had to
> > > > > remove the data from some tables. I did that by "delete from table
> > > > > xxxx". It was a hard job, because it took very very long.
> > > > > Afterwords I looked at some tables with the schema-browser of toad.
> > > > > Although the tables where empty it took very long (maybe 20 seconds)
> > > > > for toad to show the table-data-view. The tables had some extents
> > > > > (maybe 10 - 30).
> > > > > After I had done a "truncate table xxx drop storage" the access-speed
> > > > > to these tables was "normal" again (1 - 2 seconds).
> > > > > I think this is because the tables had only one extent left after the
> > > > > truncate statement.
> > > > > So why can anybody say that the number of extents is not important for
> > > > > performance ?
> > > > >
> > > > > Any Ideas ????
> > > > >
> > > > > Marcus
Received on Mon Sep 17 2001 - 09:41:37 CEST