Wie der Name bereits ausdrückt, zeigt die Highwater-Marke einer Tabelle den Maximal-Füllstand, welche die Tabelle einmal aufwies. Weder mit DELETE FROM <table>; noch mittels ALTER TABLE <table> DEALLOCATE UNUSED; kann die Highwater-Marke zurückgestellt werden nach einer grösseren Löschaktion. Nur TRUNCATE TABLE <table>; setzt die Highwater-Marke zurück. Dies hat Auswirkungen auf die Performance von SLECT COUNT(*) FROM <table>; da dieses Kommando immer die Blocks bis zur Highwater-Marke liest. Dadurch dauert das Zählen der Anzahl Rows in einer Tabelle, welche ursprünglich einmal 100'000 Rows aufwies praktisch gleich lange mit einer Anzahl von 0 Rows nach einem Löschen aller Rows mittels DELETE. Fazit: Nach dem Löschen aller Rows mittels DELETE, immer TRUNCATE verwenden um die Highwater-Marke zurückzusetzen.

--
-- Calculate Highwatermark as follows or use
-- Package DBMS_SPACE.UNUSED_SPACE
--
select blocks
  from dba_segments
where owner = 'schemaoner'
   and segment_name = 'table-name';

select empty_blocks
  from dba_tables
where owner = 'schemaoner'
   and table_name = 'table-name';

Highwatermark := blocks - empty_blocks -1;

ACCEPT l_user CHAR PROMPT 'Schemaowner: '
ACCEPT l_table CHAR PROMPT 'Tablename: '
--
declare
OP1 number;
OP2 number;
OP3 number;
OP4 number;
OP5 number;
OP6 number;
OP7 number;
HWM number;
begin
  dbms_space.unused_space(UPPER('&&l_user'),UPPER('&&l_table'),
  'TABLE',OP1,OP2,OP3,OP4,OP5,OP6,OP7);
  HWM := OP1 - OP3 - 1;
  dbms_output.put_line('--------------------------');
  dbms_output.put_line('Total Blocks = '||OP1);
  dbms_output.put_line('Total Bytes = '||OP2);
  dbms_output.put_line('Unused Blocks = '||OP3);
  dbms_output.put_line('Unused Bytes = '||OP4);
  dbms_output.put_line('Highwater Mark = ('||OP1||' - '
  ||OP3||' - 1) = '||HWM);
end;
/

Beispiel: Ausgangslage der Tabelle Test
---------------------------------------
Total Blocks = 440
Total Bytes = 1802240
Unused Blocks = 15
Unused Bytes = 61440
Highwater Mark = (440 - 15 - 1) = 424
--
-- DEALLOCATE UNUSED verändert unused Blocks und Bytes
--
ALTER TABLE test DEALLOCATE UNUSED;
--
Total Blocks = 425
Total Bytes = 1740800
Unused Blocks = 0
Unused Bytes = 0
Highwater Mark = (425 - 0 - 1) = 424
--
-- DELETE verändert HWM nicht, SELECT COUNT(*) geht lange !
--
DELETE FROM test;
--
Total Blocks = 425
Total Bytes = 1740800
Unused Blocks = 0
Unused Bytes = 0
Highwater Mark = (425 - 0 - 1) = 424
--
-- TRUNCATE sezt HWM zurück, SELECT COUNT(*) geht schnell !
--
TRUNCATE TABLE test;
--
Total Blocks = 20
Total Bytes = 81920
Unused Blocks = 19
Unused Bytes = 77824
Highwater Mark = (20 - 19 - 1) = 0