Re: Empty table - many extents

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:<3f376ad9$0$18487$ed9e5944_at_reading.news.pipex.net>...
> "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.

If changing the using processes from using a permanent table to using a global temporary table like Niall suggests is not possible then I would suggest you follow Anton's advice and truncate the table on a regular basis. That is exactly what we do for a half-dozen tables that hold phanton rows as conversion to global temporay tables is not cost justified. (That is, the existing code written on version 7.0 works fine and we have too many other changes/new requests to work on to get permission to perform the change)

HTH -- Mark D Powell -- Received on Mon Aug 11 2003 - 09:25:23 CDT