The effect of delete statements on table high water mark (HWM)
136406Aug 7 2007 — edited Aug 8 2007Oracle 10.2, Solaris 10
We have a table, ROADS described below, which contains approximately 100,000 rows.
Name Null? Type
FILEID NUMBER(38)
OBJECTID NOT NULL NUMBER(38)
ART NUMBER(9)
GBPER_CODE NUMBER(3)
SHAPE NUMBER(38)
Once per week, a third party software, will issue a delete statement and re-import data.
For example: “delete from ROADS where FileID=18;” FileID goes from 1 to 100.
To quote a “very” simplified example from Tom Kyte at asktom to aid my understanding of the principle of the HWM:
“...If you can fit 100 rows per block and you insert 1,000 records
You have used at least 10 blocks - these 10 blocks are "under the high water mark", the have contained data. They might not anymore (could have deleted the rows) but they will remain under "the high water mark" - since there were part of the table that contained data at some point in time...”
Therefore, in my case on the initial data load: 1000 (blocks) under the high water mark, rows = 100,000
If I then delete 30,000 rows, still 1000 (blocks) under the high water mark, rows = 70,000
If I then insert 10,000 rows, now 1100 (blocks) under the high water mark, rows = 80,000
If I then delete 20,000 rows, still 1100 (blocks) under the high water mark, rows = 60,000
If I then insert 40,000 rows, now 1500 (blocks) under the high water mark, rows = 100,000
From my understanding of the HWM: the High Water Mark is not moved down, so it remains where it was before the deletion began. This means that any subsequent full table scans may take a long time to complete - because a full table scan always scans up to the HWM.
My questions are therefore:
1) Is my assumption about the HWM above correct? If I now make a full table scan, Oracle has to scan up to block 1500, even though the number of rows is still the same as after the initial data import?
2) What impact, if any, the deleting of data in respect to the High Water Mark (HWM) of a table has on performance in the long term?
Feedback appreciated and apologies for my current lack of knowledge on this issue.