| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Empty table - many extents
Hi Randy,
When you write data to a table, the high water mark moves upwards. Eventually, there will be a new extent added and the HWM moves up again to suit. When you delete rows (using the DELETE command) the HWM is not moved down again - it stays where it is.
When a full table scan is performed (and as you have no indexes thgis will always be the case) all blocks in the table are read up to the HWM even if thos blocks are empty.
To move the HWM down again, you need to truncate the table at some point - hopefully when no-one is about to write some data !
Cheers,
Norm.
Tel: 0113 289 6265 Fax: 0113 289 3146 URL: http://www.Lynx-FS.com
-------------------------------------
-----Original Message-----
From: Randi Wølner [mailto:randiwolner_at_hotmail.com]
Posted At: Monday, August 11, 2003 10:43 AM
Posted To: server
Conversation: Empty table - many extents
Subject: Empty table - many extents
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 - 06:14:02 CDT
 |
 |