Блог

Главная - Вопросы по программированию - Есть ли способ перестроить каждый индекс в каждой схеме с помощью хранимой процедуры / plsql?

Есть ли способ перестроить каждый индекс в каждой схеме с помощью хранимой процедуры / plsql?

#oracle #stored-procedures #indexing #plsql

#Oracle #хранимые процедуры #индексирование #plsql

Вопрос:

Кажется, не могу представить хранимую процедуру, которая будет перестраивать каждый индекс онлайн в каждой схеме в базе данных. Я просто не очень прямо об этом думаю.

 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 online';
    end loop;
end;
/

Комментарии:

1. Учитывая, что перестройка индексов обычно ничего не дает в долгосрочной перспективе, кроме сжигания мусора, что вы думаете о написании этой процедуры?

2. Регулярное перестроение всех индексов в схеме не приведет к «настройке производительности». Существует определенная проблема с разделенными индексами после обслуживания разделов, но, как указывает Вернфрид, ваша процедура все равно не обрабатывает разделенные индексы.

3. «Описанная выше процедура выдала мне только сообщение об ошибке компиляции. » Какое сообщение? Ваша процедура компилируется и запускается для меня.

4. «Частые изменения таблиц в базе данных требуют перестроения индекса, это обычная процедура, помогающая настроить производительность» Нет, частые изменения не требуют перестроения индекса. Это старый каштан, который неоднократно развенчивался некоторыми из лучших экспертов по производительности Oracle в мире. И хотя это может быть «обычным» в вашем магазине, это далеко не универсально.

5. @EdStevens Я не эксперт, я профессионально работаю администратором базы данных Oracle уже 3 года с версиями 12c и выше. Я узнал от старших администраторов баз данных, с которыми я работаю. Простое наличие индексов не обеспечит высокую производительность системы. С увеличением объема данных в базовой таблице индексы продолжают фрагментироваться. Перестройка индекса существует для воссоздания индексов и всей базовой статистики. Это очень заметно благодаря мониторингу. Поэтому я почувствовал, что хорошей практикой обслуживания является регулярная переиндексация или дефрагментация. Я был бы рад, если бы вы могли поделиться источниками относительно вашей точки зрения.

Ответ №1:

Вы можете попробовать подобную процедуру, слегка изменив параметры и выполнив запрос к ALL_INDEXES

 DECLARE
    PROCEDURE rebuild_indexes (p_owner        IN VARCHAR2 DEFAULT NULL,
                               p_table_name   IN VARCHAR2 DEFAULT NULL)
    AS
    BEGIN
        FOR indexes_to_rebuild
            IN (SELECT owner, index_name
                  FROM all_indexes
                 WHERE owner = NVL (p_owner, owner) AND table_name = NVL (p_table_name, table_name))
        LOOP
            BEGIN
                EXECUTE IMMEDIATE   'alter index '
                                 || indexes_to_rebuild.owner
                                 || '.'
                                 || indexes_to_rebuild.index_name
                                 || ' rebuild online';
            EXCEPTION
                WHEN OTHERS
                THEN
                    DBMS_OUTPUT.put_line (
                           'Failed to rebuild index '
                        || indexes_to_rebuild.owner
                        || '.'
                        || indexes_to_rebuild.index_name
                        || ': '
                        || SQLERRM);
            END;
        END LOOP;
    END;
BEGIN
    --Rebuild all indexes on a table
    rebuild_indexes (p_owner => 'APEX_190200', p_table_name => 'WWV_FLOWS');
    --Rebuild all indexes in a schema
    rebuild_indexes (p_owner => 'APEX_190200', p_table_name => NULL);
    --Rebuild all indexes
    rebuild_indexes (p_owner => NULL, p_table_name => NULL);
END;
/

Комментарии:

1. Похоже, это сработало успешно, спасибо!!