创建一个过程来检索我的表上的所有索引并重建 [英] create a procedure that retrieves all indexes on my table and rebuilt

问题描述

我想创建一个过程来检索表上的所有索引并重新构建

I want to create a procedure that retrieves all indexes on my table and rebuilt

i使用此查询检索所有索引:

i retrieves all indexes with this query:

select index_name from user_indexes where table_name='your_table_name'

我用这个查询重建了

alter index <index_name> rebuild;

谢谢.

推荐答案

create or replace procedure rebuild_indexes(
    p_owner in varchar2,
    p_table_name in varchar2
) as
begin
    for indexes_to_rebuild in
    (
        select index_name
        from all_indexes
        where owner = p_owner
            and table_name = p_table_name
    ) loop
        execute immediate 'alter index '||p_owner||'.'
            ||indexes_to_rebuild.index_name||' rebuild';
    end loop;
end;
/

尽管这仅适用于最简单的索引.有许多

Although this will only work with the simplest indexes. There are many restrictions on rebuilding. For example, if the index is partitioned you need to rebuild each partition or subpartition.

您可能要考虑很多选择.例如，如果希望其他人在重建过程中使用索引，请使用 ONLINE ，添加 PARALLEL 选项以加快重建速度(但这也会更改索引的并行设置，这可以引起问题)等.

And there are many options you may want to consider. For example, use ONLINE if you want others to use the index during the rebuild, add a PARALLEL option to rebuild faster (but this also changes the index's parallel setting, which can cause problems), etc.

请记住，许多Oracle顶级专家认为重建索引通常是浪费时间.在某些罕见的情况下，重建可以帮助建立索引，例如稀疏删除单调递增的值.但是大多数索引重建是由于神话而完成的，可以通过阅读Richard Foote的演讲

Keep in mind that many of the top Oracle experts think rebuilding indexes is usually a waste of time. There are some rare cases where rebuilding can help an index, such as sparse deletions of monotonically increasing values. But most index rebuilding is done because of myths that can be dispelled by reading Richard Foote's presentation Index Internals - Rebuilding the Truth.

重建将使您的索引最初更快地运行并且看起来更小，但这是因为缓存和诸如段空间分配之类的开销减少了.一周后，您的索引可能会回到它们的起始位置.

Rebuilding will make your indexes initially run faster and look smaller, but that's because of caching and the reduction of overheads like segment space allocation. A week later, your indexes will probably be right back to where they started.

这篇关于创建一个过程来检索我的表上的所有索引并重建的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！