Thanks for the question, Prabhjot.
Asked: October 26, 2017 - 3:36 pm UTC
Last updated: October 27, 2017 - 5:29 am UTC
Version: 10
Viewed 1000+ times
You Asked
Hi Tom,
If my delete query doesn't delete any record, will it have any effect on HWM?
I mean, everything will remain same as before my delete command, please confirm.
and Connor said...
Even if your delete DOES delete a record it will not impact the high water mark.
eg
SQL> create table t as select * from dba_objects;
Table created.
SQL> insert into t select * from t;
78473 rows created.
SQL> insert into t select * from t;
156946 rows created.
SQL> insert into t select * from t;
313892 rows created.
SQL> commit;
Commit complete.
SQL> execute dbms_stats.gather_table_stats('','T')
PL/SQL procedure successfully completed.
SQL>
SQL> select blocks from user_tables where table_name = 'T';
BLOCKS
----------
12171
1 row selected.
SQL> set autotrace on stat
SQL> select count(*) from t;
COUNT(*)
----------
627784
1 row selected.
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
12148 consistent gets
0 physical reads
0 redo size
544 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off
SQL>
SQL> delete from t where rownum <= 200000;
200000 rows deleted.
SQL> commit;
Commit complete.
SQL>
SQL> execute dbms_stats.gather_table_stats('','T')
PL/SQL procedure successfully completed.
SQL>
SQL> select blocks from user_tables where table_name = 'T';
BLOCKS
----------
12171
1 row selected.
SQL> set autotrace on stat
SQL> select count(*) from t;
COUNT(*)
----------
427784
1 row selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12148 consistent gets
0 physical reads
0 redo size
544 bytes sent via SQL*Net to client
608 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL>
~12000 blocks to read even after I delete most of the records. The high water mark wont reduce without an explicit action (shrink, move etc)