Re: Empty table - many extents

"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 - 08:47:13 CDT