数据库用高水位来界定一个段中使用的块和未使用的块。SQL查询中当出现全表扫描时，数据库读取的是高水位标记以下的所有块，当扫描的对象是已经被删除过得表，即使在经过删除后没有任何内容，数据库依然还是会去扫描，这样就延长了全表扫描的时间。所以在做从考虑SQL查询性能的角度出发，删除后高水位是否下降与查询效率有很大关系。
1、DELETE命令删除数据，高水位不会下降。
创建表TEST01：
SQL> DROP TABLE TEST01;
EXECUTED SUCCESSFULLY
USED TIME: 77.775(MS). EXECUTE ID IS 1168.
SQL> CREATE TABLE TEST01 (C1 INT, C2 TIMESTAMP);
EXECUTED SUCCESSFULLY
USED TIME: 45.120(MS). EXECUTE ID IS 1169.
SQL> INSERT INTO TEST01 SELECT LEVEL, SYSDATE FROM DUAL CONNECT BY LEVEL<= 100000;
AFFECT ROWS 100000
USED TIME: 00:00:01.676. EXECUTE ID IS 1170.
SQL> COMMIT;
EXECUTED SUCCESSFULLY
USED TIME: 34.360(MS). EXECUTE ID IS 1171.
查看表TEST01大小：
SQL>
SELECT SEGMENT_NAME AS TABLENAME, BYTES B, BYTES / 1024 KB, BYTES / 1024 / 1024 MB FROM USER_SEGMENTS
WHERE SEGMENT_NAME = UPPER('TEST01');
LINEID TABLENAME B KB MB
---------- ------------------- -------------------- -------------------- --------------------
1 TEST01 3932160 3840 3
USED TIME: 381.392(MS). EXECUTE ID IS 1142.
删除表TEST01数据：
SQL> DELETE FROM TEST01;
AFFECT ROWS 100000
USED TIME: 959.693(MS). EXECUTE ID IS 1144.
SQL> SELECT * FROM TEST01;
NO ROWS
USED TIME: 40.672(MS). EXECUTE ID IS 1145.
查看DELETE命令删除表格数据后，表格大小。
SQL> SELECT SEGMENT_NAME AS TABLENAME, BYTES B, BYTES / 1024 KB, BYTES / 1024 / 1024 MB FROM USER_SEGMENTS
WHERE SEGMENT_NAME = UPPER('TEST01');
LINEID TABLENAME B KB MB
---------- --------- -------------------- -------------------- --------------------
1 TEST01 3932160 3840 3
USED TIME: 82.145(MS). EXECUTE ID IS 1146.
执行DELETE删除操作前后，表的大小并未变化。说明DELETE删除数据，高水位不会下降。
2、TRUNCATE命令删除数据数据，高水位会立即下降。
SQL> DROP TABLE TEST02;
DROP TABLE TEST02;
[-2106]:ERROR IN LINE: 1
INVALID TABLE OR VIEW NAME [TEST02].
USED TIME: 1.863(MS). EXECUTE ID IS 0.
创建表TEST02：
SQL> CREATE TABLE TEST02 (C1 INT, C2 TIMESTAMP);
EXECUTED SUCCESSFULLY
USED TIME: 55.007(MS). EXECUTE ID IS 1161.
SQL> INSERT INTO TEST02 SELECT LEVEL, SYSDATE FROM DUAL CONNECT BY LEVEL<= 100000;
AFFECT ROWS 100000
USED TIME: 00:00:02.239. EXECUTE ID IS 1162.
SQL> COMMIT;
EXECUTED SUCCESSFULLY
USED TIME: 13.460(MS). EXECUTE ID IS 1163.
SQL> SELECT COUNT(*) FROM TEST02;
LINEID COUNT(*)
---------- --------------------
1 100000
USED TIME: 2.334(MS). EXECUTE ID IS 1164.
SQL> SELECT SEGMENT_NAME AS TABLENAME, BYTES B, BYTES / 1024 KB, BYTES / 1024 / 1024 MB FROM USER_SEGMENTS
WHERE SEGMENT_NAME = UPPER('TEST02');
LINEID TABLENAME B KB MB
---------- --------- -------------------- -------------------- --------------------
1 TEST02 3932160 3840 3
USED TIME: 385.552(MS). EXECUTE ID IS 1165.
删除表TEST02数据：
SQL> TRUNCATE TABLE TEST02;
EXECUTED SUCCESSFULLY
USED TIME: 33.639(MS). EXECUTE ID IS 1166.
查询TRUNCATE命令删除表格数据后，TEST02表格大小。
SQL> SELECT SEGMENT_NAME AS TABLENAME, BYTES B, BYTES / 1024 KB, BYTES / 1024 / 1024 MB FROM USER_SEGMENTS
WHERE SEGMENT_NAME = UPPER('TEST02');
LINEID TABLENAME B KB MB
---------- --------- -------------------- -------------------- --------------------
1 TEST02 262144 256 0
USED TIME: 76.295(MS). EXECUTE ID IS 1167.
执行TRUNCATE删除操作后，表的大小立即变小。说明TRUNCATE命令删除数据，高水位会立即下降。
3、DELETE命令删表，超过UNDO_RETENTION时间后，高水位自动下降。
半个小时候查询TEST01表格大小：
SQL> SELECT NAME, TYPE, VALUE, SYS_VALUE, FILE_VALUE FROM V$PARAMETER WHERE NAME='ENABLE_FLASHBACK';
LINEID NAME TYPE VALUE SYS_VALUE FILE_VALUE
---------- ---------------- ---- ----- --------- ----------
1 ENABLE_FLASHBACK SYS 1 1 1
USED TIME: 55.518(MS). EXECUTE ID IS 1175.
SQL> SELECT NAME, TYPE, VALUE, SYS_VALUE, FILE_VALUE FROM V$PARAMETER WHERE NAME='UNDO_RETENTION';
LINEID NAME TYPE VALUE SYS_VALUE FILE_VALUE
---------- -------------- ---- ----------- ----------- -----------
1 UNDO_RETENTION SYS 1200.000000 1200.000000 1200.000000
当ENABLE_FLASHBACK=1且UNDO_RETENTION=1200时，说明数据库已经开启了闪回，且保留的最长时间为1200秒。
1200秒后查询：
SQL> SELECT SEGMENT_NAME AS TABLENAME, BYTES B, BYTES / 1024 KB, BYTES / 1024 / 1024 MB FROM USER_SEGMENTS
WHERE SEGMENT_NAME = UPPER('TEST02');2
LINEID TABLENAME B KB MB
---------- --------- -------------------- -------------------- --------------------
1 TEST02 262144 256 0
USED TIME: 83.405(MS). EXECUTE ID IS 1177.
表格大小也下降了。出现这种情况是因为，当超过UNDO保留的最大时间时，数据会被清空，高水位下降。
4、闪回关闭的情况下，利用DELETE命令删除数据，高水位也不会下降。
关闭闪回功能。
SQL> SP_SET_PARA_VALUE (1, 'ENABLE_FLASHBACK', 0);
DMSQL EXECUTED SUCCESSFULLY
USED TIME: 43.513(MS). EXECUTE ID IS 1179.
SQL> SELECT NAME, TYPE, VALUE, SYS_VALUE, FILE_VALUE FROM V$PARAMETER WHERE NAME='ENABLE_FLASHBACK';
LINEID NAME TYPE VALUE SYS_VALUE FILE_VALUE
---------- ---------------- ---- ----- --------- ----------
1 ENABLE_FLASHBACK SYS 0 0 0
USED TIME: 47.842(MS). EXECUTE ID IS 1181.
SQL> DROP TABLE TEST02;
DROP TABLE TEST02;
[-2106]:ERROR IN LINE: 1
INVALID TABLE OR VIEW NAME [TEST02].
USED TIME: 1.863(MS). EXECUTE ID IS 0.
创建表TEST03：
SQL> DROP TABLE TEST03;
EXECUTED SUCCESSFULLY
USED TIME: 58.556(MS). EXECUTE ID IS 560.
SQL> CREATE TABLE TEST03 (C1 INT, C2 TIMESTAMP);
EXECUTED SUCCESSFULLY
USED TIME: 56.388(MS). EXECUTE ID IS 561.
SQL> INSERT INTO TEST03 SELECT LEVEL, SYSDATE FROM DUAL CONNECT BY LEVEL<= 100000;
AFFECT ROWS 100000
USED TIME: 00:00:02.137. EXECUTE ID IS 562.
SQL> COMMIT;
EXECUTED SUCCESSFULLY
USED TIME: 5.444(MS). EXECUTE ID IS 563.
SQL> SELECT COUNT(*) FROM TEST03;
LINEID COUNT(*)
---------- --------------------
1 100000
USED TIME: 1.028(MS). EXECUTE ID IS 564.
SQL> DELETE FROM TEST03;
AFFECT ROWS 100000
USED TIME: 918.071(MS). EXECUTE ID IS 565.
SQL> COMMIT;
EXECUTED SUCCESSFULLY
USED TIME: 19.824(MS). EXECUTE ID IS 566.
SQL> SELECT SEGMENT_NAME AS TABLENAME, BYTES B, BYTES / 1024 KB, BYTES / 1024 / 1024 MB FROM USER_SEGMENTS WHERE SEGMENT_NAME = UPPER('TEST03');
LINEID TABLENAME B KB MB
---------- --------- -------------------- -------------------- --------------------
1 TEST03 3932160 3840 3
USED TIME: 82.437(MS). EXECUTE ID IS 567.
闪回关闭和开启时一样，执行DELETE删除操作前后，表的大小并未变化。说明DELETE删除数据，高水位不会下降。
总结：1、SQL查询性能与高水位有关。2、DELETE删表，高水位在UNDO_RETENTION之前不会下降，UNDO_RETENTION之后会下降。3、TRUNCATE命令删表，高水位会立马下降。因此从考虑查询性能的角度考虑出发，TRUNCATE比DELETE删表对后续查询操作性能更优。
社区地址：https://eco.dameng.com
