当前位置 : 首页 > 热点资讯

Oracle 删除大量表记录操作总结

来源 :中年农码工 2023-09-09 06:48:04

删除表数据操作

清空所有表记录


(资料图)

TRUNCATE TABLE your_table_name;

或者批量删除满足条件的表记录

BEGIN  LOOP    DELETE FROM your_table_name WHERE rownum <= 50000;      EXIT WHEN SQL%ROWCOUNT = 0;   COMMIT; END LOOP;END;

释放表空间

存放大数据量的表,其表空间占用也比较大,删除数据后并不会自动释放这些记录占用的表空间,所以,即便表里面数据量很少,查询效率依旧很慢,所以,需要释放表空间。

-- 查询数据表占用的表空间大小SELECT sum(bytes)/(1024*1024) FROM user_segments WHERE segment_name="YOUR_TABLE_NAME"; --注意,表名必须大写

说明: sum(bytes)/(1024*1024) 数据统计单位由Byte转为GB

--整理碎片,释放已删除记录占用的表空间ALTER TABLE your_table_name MOVE;

重建索引

释放了表空间以后表的ROWID会发生变化,基于ROWID的索引会失效,此时就需要重建索引

--重建非分区索引ALTER TABLE your_table_index REBUILD [ONLINE] [NOLOGGING];--重建分区索引--针对分区索引-非组合索引ALTER INDEX your_table_index REBUILD PARTITION your_partition_name [ONLINE] [NOLOGGING];

注意:

设置日志级别为 NOLOGGING 意味尽量减少日志,可以加速索引重建 ONLINE , NOLOGGING 两者位置顺序可以对调,不影响 普通情况下建立索引或者REBUILD索引时,oracle会对基表加共享锁,在这期间,无法对表进行DML操作。如果希望避免这种情况,需要加 ONLINE 选项 对索引进行REBUILD时,如果不加 ONLINE 选项,则Oracle直接读取原索引的数据,否则直接扫描表中的数据 ,索引在重建时,查询仍然可以使用旧索引。实际上,Oracle在REBUILD索引的过程中,并不会删除旧索引,直到新索引重建成功,这就是相对删除索引然后重建索引的一个好处:不会影响原有的SQL查询。但也正由于此,用REBUILD方式建立索引需要相应表空间的空闲空间是删除重建方式的2倍。 不能直接REBUILD整个分区索引 对于非组合索引,需要REBUILD每个分区(partition) 对于组合索引,需要REBUILD每个子分区(subpartition) 分区、子分区较多的情况下,可以使用下面的SQL可以生成相应的REBUILD语句 --重建分区索引-非组合索引 SELECT "ALTER INDEX " || index_owner || "." || index_name || " REBUILD PARTITION " || partition_name || " NOLOGGING;" FROM dba_ind_partitions WHERE index_owner = "INDEX_OWNER_NAME" --可选查询条件,注意,如果指定该条件,索引拥有者必须大写 AND index_name = "YOUR_INDEX_NAME" ; --注意,索引名称必须大写 --重建分区索引-组合索引 SELECT "ALTER INDEX " || index_owner || "." || index_name || " REBUILD SUBPARTITION " || subpartition_name || " NOLOGGING;" FROM dba_ind_subpartitions WHERE index_owner = "INDEX_OWNER_NAME" --可选查询条件,注意,如果指定该条件,索引拥有者必须大写 AND index_name = "YOUR_INDEX_NAME" ; --注意,索引名称必须大写 针对非分区索引,如果清理的表比较多,或者不知道被清理的表拥有哪些索引,可以使用以下SQL查询并生成对应的重建索引SQL SELECT concat(concat( "ALTER INDEX " , INDEX_NAME), " REBUILD;" ) FROM all_indexes WHERE owner = "INDEX_OWNER_NAME" --可选查询条件,注意,如果指定该条件,索引拥有者必须大写 AND table_name IN ( "TABLE_NAME1 " , "TABLE_NAME2" , "..." , "TABLE_NAMEN" ) --注意,表名必须大写

分析表

分析表,是为了使基于CBO的执行计划更加准确,在一定程度上能带来一些性能提升

ANALYZE TABLE table_name COMPUTE STATISTICS;--等价于ANALYZE TABLE table_name COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL COLUMNS;

说明:生成的统计信息的存放位置:

FOR TABLE 的统计信息存在于视图: USER_TABLES 、 ALL_TABLES 、 DBA_TABLES FOR ALL INDEXES 的统计信息存在于视图: USER_INDEXES 、 ALL_INDEXES 、 DBA_INDEXES FOR ALL COLUMNS 的统计信息存在于试图: USER_TAB_COLUMNS 、 ALL_TAB_COLUMNS 、 DBA_TAB_COLUMNS

标签:

精彩放送

返回顶部