Oracle delete之后高水位线的问题解决

最新推荐文章于 2024-06-04 14:56:11 发布

办公室里穿拖鞋

最新推荐文章于 2024-06-04 14:56:11 发布

阅读量2.9k

点赞数

分类专栏：学习笔记文章标签： ORACLE 高水位线

学习笔记专栏收录该内容

93 篇文章

订阅专栏

本文通过具体实验展示了Oracle数据库中高水位线的概念及其实现方式，对比了DELETE与TRUNCATE操作对高水位线的影响，并提供了解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近学习了一阵子Oracle, 感觉Oracle真的是博大精深, 包括Oralce内存结构,性能调整,数据备份等都不简单, 这些对开发也很重要, 下面把做的Oracle高水位线的一些实验贴出来, 方便以后Review:

高水位线实验:

-- 创建test3表
SQL> create table test3 as
2 select * from dba_objects where 1 = 2;
Table created
-- 查看表中分配块，区大小
SQL> SELECT segment_name, segment_type, blocks -- 分配数据块数, extents -- 分配区块数
2    FROM dba_segments
3   WHERE segment_name = 'TEST3'
4 ;
SEGMENT_NAME                                                                     SEGMENT_TYPE           BLOCKS    EXTENTS
-------------------------------------------------------------------------------- ------------------ ---------- ----------
TEST3                                                                            TABLE                       8          1
TEST3                                                                            TABLE                       8          1
-- 分析表TEST3表
SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS;
Table analyzed
-- 查询TEST3表高水位线
SQL> SELECT blocks -- 高水位线(占用TEST3表数据块数), empty_blocks -- TEST3表空闲块数, num_rows
2    FROM user_tables
3   WHERE table_name = 'TEST3';
    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
         0            7          0
-- 因为未向TEST3表中插入任何数据，因此此表的高水位线为0，现向TEST3表中插入数据再观察
SQL> insert into test3
2 select * from dba_objects;
50361 rows inserted
SQL> commit;
Commit complete
-- 重新分析表
SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS;
Table analyzed
-- 再次查看表中分配块，区大小
SQL> SELECT segment_name, segment_type, blocks, extents
2    FROM dba_segments
3   WHERE segment_name = 'TEST3'
4 ;
SEGMENT_NAME                                                                     SEGMENT_TYPE           BLOCKS    EXTENTS
-------------------------------------------------------------------------------- ------------------ ---------- ----------
TEST3                                                                            TABLE                       8          1
TEST3                                                                            TABLE                     768         21
此时看到BLOCKS数已增长到768, 也就是Oracle分配给TEST3表768个数据块，21个区
-- 再次查看TEST3表高水位线
SQL> SELECT blocks, empty_blocks, num_rows
2    FROM user_tables
3   WHERE table_name = 'TEST3';
    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
       689           78      50361
已增长到689个块，还有78个空闲块，689 + 78 = 767, 比分配的少1个数据块，是因为这一个数据块是用作segment header
-- 现将TEST3表delete，在查看高水位线
SQL> delete from test3;
50361 rows deleted
SQL> commit;
Commit complete
SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS;
Table analyzed
SQL>
SQL> SELECT blocks, empty_blocks, num_rows
2    FROM user_tables
3   WHERE table_name = 'TEST3';
    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
       689           78          0
发现此表高水位线并未减少，证明delete只是删除表中数据块的记录，但并不会使表中的高水位线下降，在进行全表扫描时会Oracle会扫描表中高水位线下的所有数据块，
因此数据虽然被删除了，但查询时有可能还是很慢。所以在进行大表删除时应使用truncate语句，看下面实验:
SQL> truncate table test3;
Table truncated
SQL> ANALYZE TABLE TEST3 ESTIMATE STATISTICS;
Table analyzed
SQL>
SQL> SELECT blocks, empty_blocks, num_rows
2    FROM user_tables
3   WHERE table_name = 'TEST3';
    BLOCKS EMPTY_BLOCKS   NUM_ROWS
---------- ------------ ----------
         0            7          0
现在表中高水位下降到0了，一点心得，记录下来。

解决高水位线的方法

9i中： create table aa_bak as select * from aa where record_time > sysdate - 10; truncate table aa; insert into aa select * from aa_bak; drop table aa_bak;

10G版本。修改表高位线

alter tablename enable row movement; alter tablename shrink space