Re: Empty table - many extents

"Randi Wølner" <randiwolner_at_hotmail.com> wrote in message 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?

It is actually fairly normal.

>
> 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(?).

Not necessarily. You will likely find that an index helps the query speed, but you ignore the wasted space issue. You could also schedule an ALTER TABLE <BLAH> MOVE <SAME TABLESPACE> periodically which will effectively reset the high water mark, but invalidate any index and lock the table during the move.

If the table is holding session specific data (it sounds like it) and you are on 8i or higher this sounds like a classic case where using a Global Temporary Table (on commit preserve rows) would be appropriate.

-- 
Niall Litchfield
Oracle DBA
Audit Commission UK