Re: Empty table - many extents

You are probably right about the table being used in a "different manner", as someone forgot to add a COMMIT following those operations I described. Then there would always be a delay until the user commited something else. When this behavior was found the code was changed, but nothing was done to the table.

I have already truncated the table now, it seemed to help - I will also try adding a Primary key and using a temporary table to find out what is best.

Thanks to all of you for the answers, you have been very helpful!!

Randi W

"Chuck" <chuckh_at_softhome.net> wrote in message news:Xns93D4638ED57FEchuckhsofthomenet_at_130.133.1.4...
> "Randi Wølner" <randiwolner_at_hotmail.com> wrote in
> news:bh7oaq$jed$1_at_services.kq.no:
>
> > Oracle 8.1.7.4 on AIX:
> > I have a small table, only for temporary use. It has two
> > NUMBER-columns and one column that is VARCHAR2(4000). From each user
> > session some PL/SQL Code is called that inserts 2-3 rows. These rows
> > are read with a select, and after a short period (seconds) the same
> > rows are deleted. Many users might do these actions at the same time,
> > but the table is supposed to be empty most of the time.
> >
> > Now it seems like the SELECT to fetch the data from this table takes
> > more time than it seems reasonable.
> >
> > A query to USER_SEGMENTS shows that this table uses many extents, even
> > though the table is empty - is that a bad sign?
> >
> > We have no index or primary key on this table, as it is supposed to be
> > a very small table - and a full table scan should be faster(?).
> >
> > Can the small table be "corrupted" in any way by this use?
> > It is a problem that the select takes too much time, so I would be
> > happy for any advice on this..
>
>
> Truncating the table will correct the problem for now, but the real
> question is "What caused the HWM to move up so high in the first place?".
> Was this table used in a different manner at some point in time?
Received on Mon Aug 11 2003 - 09:22:54 CDT