Empty table - many extents

From: Randi Wølner <randiwolner_at_hotmail.com>
Date: Mon, 11 Aug 2003 11:43:13 +0200
Message-ID: <bh7oaq$jed$1@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..

Randi W. Received on Mon Aug 11 2003 - 04:43:13 CDT