Re: Table-Extents / Performance

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 Fri Sep 14 2001 - 10:25:51 CEST