Отключить индекс Oracle в запросе
Обновлено: 03.07.2024
Работал в области технологии баз данных для устранения проблем, возникающих в повседневной деятельности в Oracle, Sql Server, MySQL и т. д.
Включить и отключить автоматическое индексирование в Oracle 19c
Oracle представила функцию автоматического индексирования в корпоративной версии Oracle 19c.
Эта функция автоматически управляет индексом в зависимости от рабочей нагрузки приложения в базе данных, создавая, удаляя и перестраивая индексы.
Процесс автоматического индексирования запускается в фоновом режиме каждые 15 минут.
Процедура DBMS_AUTO_INDEX.CONFIGURE используется для включения и отключения функции автоматического индексирования.
Как это работает?
1. идентифицирует кандидата автоиндексации по рабочей нагрузке.
2. создает невидимые индексы для кандидатов на автоматические индексы. он не используется в запросах SQL.
3. Сверяйте невидимые индексы с SQL-запросами.
4. Если производительность хорошая, то он используется как видимый индекс в SQL Query, в противном случае заносится в черный список.
5. Удаляет неиспользуемые индексы.
Проверьте конфигурацию автоматического индексирования
COLUMN имя_параметра FORMAT A40
COLUMN имя_параметра FORMAT A15
SELECT con_id, имя_параметра, значение_параметра
FROM cdb_auto_index_config ORDER BY 1, 2;
Примечание. Если параметр AUTO_INDEX_MODE выключен, его функция отключена.
Включить автоматическое индексирование в Oracle 19c.
Если оно включено, все схемы, присутствующие в БД, по умолчанию используют автоматическое индексирование.
-- Создать индекс в видимом состоянии
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
-- Создать индекс в невидимом состоянии
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','ТОЛЬКО ОТЧЕТ');
Отключить автоматическое индексирование
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');
Включить и исключить схему из автоматического индексирования.
По умолчанию вся схема включена, когда включена функция автоматического индексирования.
Схемы HR и Скотта добавляются в список исключений, чтобы не использовать функцию автоматического индексирования.
Список исключений
-- Функция автоматического индексирования не применима к обеим схемам. оба в списке исключений.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', allow => FALSE);
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SCOTT', allow => FALSE);
--Удалить схему HR из списка исключений.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'HR', NULL);
-- Удалить всю схему из списка исключений.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', NULL, TRUE);
Табличное пространство для автоматического индексирования
создавайте новые автоматические индексы в отдельном табличном пространстве.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'имя_табличного_пространства');
Другие параметры, используемые для настройки функции АВТОМАТИЧЕСКОГО индексирования.
AUTO_INDEX_REPORT_RETENTION : период хранения журналов автоматического индексирования. По умолчанию 31 день. Отчетность зависит от этого.
AUTO_INDEX_RETENTION_FOR_AUTO : срок хранения неиспользуемых автоматических индексов. По умолчанию 373 дня.
AUTO_INDEX_RETENTION_FOR_MANUAL : период хранения неиспользуемых индексов, созданных вручную. Если задано значение NULL, созданные вручную индексы не подлежат удалению. По умолчанию НУЛЬ.
Представления для проверки дополнительной информации
DBA_AUTO_INDEX_EXECUTIONS: История выполнения задач автоматического индексирования.
DBA_AUTO_INDEX_STATISTICS: Статистика, связанная с автоматическими индексами.
DBA_AUTO_INDEX_IND_ACTIONS: действия, выполняемые с автоматическими индексами.
DBA_AUTO_INDEX_SQL_ACTIONS: действия, выполняемые над операторами SQL для проверки автоматических индексов.
DBA_AUTO_INDEX_CONFIG: параметры конфигурации, связанные с автоматическими индексами.
В этом руководстве по Oracle объясняется, как создавать, переименовывать и удалять индексы в Oracle с синтаксисом и примерами.
Что такое индекс в Oracle?
Индекс — это метод настройки производительности, позволяющий быстрее извлекать записи. Индекс создает запись для каждого значения, которое появляется в проиндексированных столбцах. По умолчанию Oracle создает индексы B-tree.
Создать индекс
Синтаксис
Синтаксис для создания индекса в Oracle/PLSQL:
UNIQUE Указывает, что комбинация значений в проиндексированных столбцах должна быть уникальной. index_name Имя для присвоения индексу. table_name Имя таблицы, в которой создается индекс. столбец1, столбец2, . column_n Столбцы для использования в индексе. ВЫЧИСЛИТЕ СТАТИСТИКУ Он указывает Oracle собирать статистику во время создания индекса. Затем оптимизатор использует статистику для выбора «плана выполнения» при выполнении операторов SQL.
Пример
Давайте рассмотрим пример создания индекса в Oracle/PLSQL.
В этом примере мы создали индекс в таблице поставщиков с именем supplier_idx. Он состоит всего из одного поля — Supplier_name.
Мы также можем создать индекс с несколькими полями, как в примере ниже:
Мы также можем выбрать сбор статистики при создании индекса следующим образом:
Создать функциональный индекс
В Oracle вы не ограничены созданием индексов только для столбцов. Вы можете создавать индексы на основе функций.
Синтаксис
Синтаксис для создания функционального индекса в Oracle/PLSQL:
UNIQUE Указывает, что комбинация значений в проиндексированных столбцах должна быть уникальной. index_name Имя для присвоения индексу. table_name Имя таблицы, в которой создается индекс. функция1, функция2, . function_n Функции для использования в индексе. ВЫЧИСЛИТЕ СТАТИСТИКУ Он указывает Oracle собирать статистику во время создания индекса. Затем оптимизатор использует статистику для выбора «плана выполнения» при выполнении операторов SQL.
Пример
Давайте рассмотрим пример создания функционального индекса в Oracle/PLSQL.
В этом примере мы создали индекс на основе оценки поля supplier_name в верхнем регистре.
Однако, чтобы быть уверенным, что оптимизатор Oracle использует этот индекс при выполнении ваших операторов SQL, убедитесь, что UPPER(supplier_name) не имеет значение NULL. Для этого добавьте UPPER(supplier_name) IS NOT NULL в предложение WHERE следующим образом:
Переименовать индекс
Синтаксис
Синтаксис переименования индекса в Oracle/PLSQL:
index_name Имя индекса, который вы хотите переименовать. new_index_name Новое имя для присвоения индексу.
Пример
Давайте рассмотрим пример переименования индекса в Oracle/PLSQL.
В этом примере мы переименовываем индекс с именем supplier_idx в supplier_index_name.
Сбор статистики по индексу
Если вы забыли собрать статистику по индексу при первом его создании или хотите обновить статистику, вы всегда можете использовать команду ALTER INDEX для сбора статистики позже.
Синтаксис
Синтаксис для сбора статистики по индексу в Oracle/PLSQL:
index_name Индекс для сбора статистики.
Пример
Давайте рассмотрим пример сбора статистики для индекса в Oracle/PLSQL.
В этом примере мы собираем статистику для индекса supplier_idx.
Вывод: в этом руководстве вы узнаете, как использовать уникальный индекс Oracle для предотвращения дублирования значений в индексированном столбце или столбцах таблицы.
Введение в индекс Oracle UNIQUE
Индекс может быть уникальным или неуникальным. Уникальный индекс гарантирует, что в индексированном столбце (или столбцах) никакие две строки таблицы не будут иметь повторяющихся значений. Неуникальный индекс не накладывает это ограничение на значения индексируемого столбца.
Чтобы создать уникальный индекс, используйте оператор CREATE UNIQUE INDEX:
В этом синтаксисе UNIQUE — единственное ключевое слово, которое нужно добавить в инструкцию CREATE INDEX.
Уникальный индекс Oracle для одного столбца, пример
В следующем примере создается уникальный индекс для столбца электронной почты таблицы участников:
При наличии этого индекса member_email_i у вас не может быть двух строк с одинаковым значением в столбце электронной почты.
Следующее выражение завершится ошибкой, поскольку оно попытается вставить новую строку, адрес электронной почты которой уже существует:
Вот ошибка:
УНИКАЛЬНЫЙ индекс Oracle для двух столбцов, пример
В следующем примере создается новая таблица с именем unq_idx_demo с двумя столбцами a и b :
Чтобы создать уникальный индекс для двух столбцов a и b , используйте следующий оператор:
Следующий оператор вставляет новую строку в таблицу unq_idx_demo:
Поскольку у нас есть уникальный индекс для столбцов a и b, комбинация значений в обоих столбцах используется для оценки повторяющихся значений.
Следующее утверждение работает, потому что пара (1,2) не существует:
Однако следующий оператор не работает, поскольку (1,1) уже существует:
Вот сообщение об ошибке:
Индекс Oracle UNIQUE, ограничение первичного ключа и ограничение уникальности
Когда вы определяете ограничение PRIMARY KEY или UNIQUE для таблицы, Oracle автоматически создает уникальный индекс для столбцов первичного ключа или уникального ключа, чтобы обеспечить уникальность.
Уникальный индекс, связанный с ограничением, всегда имеет имя ограничения, если явно не указано иное.
Следующий оператор создает таблицу с именем t1 с первичным ключом:
Чтобы отобразить индексы таблицы t1, используйте следующий оператор:
Вот результат:
Как видно из вывода, уникальный индекс SYS_C007876 был создан автоматически с созданным именем.
Чтобы указать имя столбца первичного ключа, используйте индекс UNIQUE, как показано в следующем запросе:
В этом примере мы явно указали имя уникального индекса.
Вместо того, чтобы генерировать имя индекса, Oracle просто использовала то, которое мы указали при создании таблицы.
В этом руководстве вы узнали, как использовать индекс Oracle UNIQUE для обеспечения уникальности значений в индексируемом столбце или столбцах.
В этом разделе описывается, как отключить индекс или ограничения в SQL Server с помощью SQL Server Management Studio или Transact-SQL. Отключение индекса запрещает пользователям доступ к индексу, а для кластеризованных индексов — к данным базовой таблицы. Определение индекса остается в метаданных, а статистика индекса хранится в некластеризованных индексах. Отключение кластеризованного индекса для представления или некластеризованного индекса физически удаляет данные индекса. Отключение кластеризованного индекса для таблицы предотвращает доступ к данным; данные по-прежнему остаются в таблице, но недоступны для операций языка обработки данных (DML) до тех пор, пока индекс не будет удален или перестроен.
В этой теме
Прежде чем начать:
Чтобы отключить индекс, используйте:
Прежде чем начать
Ограничения и запреты
Индекс не поддерживается, пока он отключен.
Оптимизатор запросов не учитывает отключенный индекс при создании планов выполнения запросов. Кроме того, запросы, которые ссылаются на отключенный индекс с подсказкой таблицы, завершатся ошибкой.
Вы не можете создать индекс, который использует то же имя, что и существующий отключенный индекс.
Отключенный индекс можно удалить.
При отключении уникального индекса ограничение PRIMARY KEY или UNIQUE и все ограничения FOREIGN KEY, которые ссылаются на индексированные столбцы из других таблиц, также отключаются. При отключении кластеризованного индекса все входящие и исходящие ограничения FOREIGN KEY для базовой таблицы также отключаются. Имена ограничений перечислены в предупреждающем сообщении, когда индекс отключен. После перестроения индекса все ограничения должны быть включены вручную с помощью инструкции ALTER TABLE CHECK CONSTRAINT.
Некластеризованные индексы автоматически отключаются при отключении связанного кластерного индекса. Они не могут быть включены до тех пор, пока не будет включен кластеризованный индекс для таблицы или представления или кластеризованный индекс для таблицы не будет удален. Некластеризованные индексы должны быть включены явно, если кластеризованный индекс не был включен с помощью инструкции ALTER INDEX ALL REBUILD.
Инструкция ALTER INDEX ALL REBUILD перестраивает и включает все отключенные индексы в таблице, за исключением отключенных индексов в представлениях. Индексы для представлений должны быть включены в отдельном операторе ALTER INDEX ALL REBUILD.
Отключение кластеризованного индекса для таблицы также отключает все кластеризованные и некластеризованные индексы для представлений, которые ссылаются на эту таблицу. Эти индексы должны быть перестроены так же, как индексы в таблице, на которую ссылаются.
К строкам данных отключенного кластерного индекса нельзя получить доступ, кроме как для удаления или перестроения кластерного индекса.
Вы можете перестроить отключенный некластеризованный индекс в режиме онлайн, если в таблице нет отключенного кластеризованного индекса. Однако вы должны всегда перестраивать отключенный кластеризованный индекс в автономном режиме, если используете инструкцию ALTER INDEX REBUILD или CREATE INDEX WITH DROP_EXISTING. Дополнительные сведения об операциях с индексами в сети см. в разделе Выполнение операций с индексами в сети.
Инструкцию CREATE STATISTICS нельзя успешно выполнить для таблицы с отключенным кластерным индексом.
Параметр базы данных AUTO_CREATE_STATISTICS создает новую статистику по столбцу, когда индекс отключен и выполняются следующие условия:
AUTO_CREATE_STATISTICS включен
Для столбца нет существующей статистики.
Во время оптимизации запроса требуется статистика.
Если кластеризованный индекс отключен, DBCC CHECKDB не может вернуть информацию о базовой таблице; вместо этого оператор сообщает, что кластеризованный индекс отключен. DBCC INDEXDEFRAG нельзя использовать для дефрагментации отключенного индекса; оператор терпит неудачу с сообщением об ошибке. Вы можете использовать команду DBCC DBREINDEX для перестроения отключенного индекса.
Создание нового кластеризованного индекса включает ранее отключенные некластеризованные индексы. Дополнительные сведения см. в разделе Включение индексов и ограничений.
Безопасность
Разрешения
Для выполнения ALTER INDEX требуется как минимум разрешение ALTER для таблицы или представления.
Использование SQL Server Management Studio
Чтобы отключить индекс
В обозревателе объектов щелкните знак "плюс", чтобы развернуть базу данных, содержащую таблицу, для которой вы хотите отключить индекс.
Нажмите знак "плюс", чтобы развернуть папку "Таблицы".
Нажмите знак "плюс", чтобы развернуть таблицу, для которой вы хотите отключить индекс.
Нажмите знак "плюс", чтобы развернуть папку "Индексы".
Щелкните правой кнопкой мыши индекс, который хотите отключить, и выберите Отключить.
В диалоговом окне "Отключить индексы" убедитесь, что в списке "Индексы" указан правильный индекс, чтобы отключить сетку, и нажмите "ОК".
Чтобы отключить все индексы в таблице
В обозревателе объектов щелкните знак "плюс", чтобы развернуть базу данных, содержащую таблицу, для которой вы хотите отключить индексы.
Нажмите знак "плюс", чтобы развернуть папку "Таблицы".
Нажмите знак "плюс", чтобы развернуть таблицу, для которой вы хотите отключить индексы.
Щелкните правой кнопкой мыши папку "Индексы" и выберите "Отключить все".
В диалоговом окне «Отключить индексы» убедитесь, что в списке «Индексы для отключения сетки» указаны правильные индексы, и нажмите «ОК». Чтобы удалить индекс из индексов, чтобы отключить сетку, выберите индекс и нажмите клавишу Delete.
В диалоговом окне "Отключить индексы" доступна следующая информация:
Имя индекса
Отображает имя индекса. Во время выполнения в этом столбце также отображается значок, представляющий статус.
Имя таблицы
Отображает имя таблицы или представления, для которого был создан индекс.
Тип индекса
Отображает тип индекса: кластеризованный, некластеризованный, пространственный или XML.
Статус перед выполнением пуст.
Выполняется
Отключение индексов запущено, но не завершено.
Успех
Операция отключения успешно завершена.
Ошибка
Произошла ошибка во время операции отключения индекса, и операция не завершилась успешно.
Остановлено
Отключение индекса не было успешно завершено, поскольку пользователь остановил операцию.
Сообщение
Предоставляет текст сообщений об ошибках во время операции отключения. Во время выполнения ошибки отображаются в виде гиперссылок. Текст гиперссылок описывает тело ошибки. Столбец «Сообщение» редко бывает достаточно широким, чтобы прочитать весь текст сообщения. Есть два способа получить полный текст:
Наведите указатель мыши на ячейку сообщения, чтобы отобразить всплывающую подсказку с текстом ошибки.
Нажмите на гиперссылку, чтобы отобразить диалоговое окно с полной ошибкой.
Использование Transact-SQL
Чтобы отключить индекс
В обозревателе объектов подключитесь к экземпляру компонента Database Engine.
На стандартной панели нажмите "Новый запрос".
Скопируйте и вставьте следующий пример в окно запроса и нажмите "Выполнить".
Чтобы отключить все индексы в таблице
В обозревателе объектов подключитесь к экземпляру компонента Database Engine.
На стандартной панели нажмите "Новый запрос".
Скопируйте и вставьте следующий пример в окно запроса и нажмите "Выполнить".
Читайте также: