Re: Empty table - many extents

Randi Wølner <randiwolner_at_hotmail.com> schreef in berichtnieuws 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..
|
| Randi W.
|

When the table has many extents, it would mean the table has grown once so much that it really needed that space. The high water mark never goes down again. To release the space and to speed up full table scans (because they need to reed up to the high water mark) you could truncate the table on a quiet moment. From the situation you describe however this table looks a perfect candidate to be created as a temporary table, that is when the rows inserted are only needed by the session that inserts them. If other sessions need the data this would not be a solution.
Read about this in the documentation and see if it suits your needs. Received on Mon Aug 11 2003 - 05:06:37 CDT