REBUILD分区索引Oracle

这篇文章主要介绍了Oracle删除大量表记录操作分析总结，文章围绕主题展开详细的内容介绍，具有一定的参考价值，需要的小伙伴可以参考一下

+

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

1 2	-- 查询数据表占用的表空间大小 SELECTsum(bytes)/(1024*1024) FROMuser_segments WHEREsegment_name='YOUR_TABLE_NAME'; --注意，表名必须大写

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

1 2	--整理碎片，释放已删除记录占用的表空间 ALTERTABLEyour_table_name MOVE;

重建索引

分析表

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

1 2 3 4

ANALYZE TABLEtable_name COMPUTE STATISTICS; --等价于 ANALYZE TABLEtable_name COMPUTE STATISTICSFORTABLEFORALLINDEXES FORALLCOLUMNS;

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

• 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

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

1 2 3 4 5 6

--重建非分区索引 ALTERTABLEyour_table_index REBUILD [ONLINE] [NOLOGGING]; --重建分区索引 --针对分区索引-非组合索引 ALTERINDEXyour_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语句

1 2 3 4 5 6 7 8 9 10 11

--重建分区索引-非组合索引 SELECT'ALTER INDEX '|| index_owner || '.'||index_name ||' REBUILD PARTITION '|| partition_name || ' NOLOGGING;' FROMdba_ind_partitions  WHEREindex_owner = 'INDEX_OWNER_NAME'--可选查询条件，注意，如果指定该条件，索引拥有者必须大写 ANDindex_name = 'YOUR_INDEX_NAME'; --注意，索引名称必须大写 --重建分区索引-组合索引   SELECT'ALTER INDEX '|| index_owner || '.'||index_name ||' REBUILD SUBPARTITION '|| subpartition_name || ' NOLOGGING;' FROMdba_ind_subpartitions WHEREindex_owner = 'INDEX_OWNER_NAME'--可选查询条件，注意，如果指定该条件，索引拥有者必须大写 ANDindex_name = 'YOUR_INDEX_NAME'; --注意，索引名称必须大写

针对非分区索引，如果清理的表比较多，或者不知道被清理的表拥有哪些索引，可以使用以下SQL查询并生成对应的重建索引SQL

1 2 3 4

SELECTconcat(concat('ALTER INDEX ', INDEX_NAME), ' REBUILD;') FROMall_indexes WHEREowner='INDEX_OWNER_NAME'--可选查询条件，注意，如果指定该条件，索引拥有者必须大写 ANDtable_name IN('TABLE_NAME1 ','TABLE_NAME2', '...', 'TABLE_NAMEN'）--注意，表名必须大写

目录

删除表数据操作

清空所有表记录：

1	TRUNCATETABLEyour_table_name;

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

1 2 3 4 5 6 7

BEGIN LOOP DELETEFROMyour_table_name WHERErownum <= 50000; EXIT WHENSQL%ROWCOUNT = 0; COMMIT; ENDLOOP; END;

释放表空间