Re: Table-Extents / Performance

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 Thu Sep 13 2001 - 11:24:46 CEST