Oracle unusable index что это
После Google не могу найти простой способ вернуть свободное место после удаления таблицы.
Я нашел много объяснений, рассказывающих, как файл данных становится фрагментированным, большой стек скучных запросов, которые вам нужно выполнить, чтобы переместить «пустое пространство» в конец файла данных (таблица за таблицей . даже когда вы есть 200 столов!?).
Затем вам нужно уменьшить размер файла данных, «угадав», насколько вы можете его уменьшить, или вы должны точно знать, каков ваш «размер блока» . И, наконец, вы не должны забывать «перестраивать индексы».
Существует ли простая процедура PL / SQL, которая, учитывая имя табличного пространства или имя файла данных, будет выполнять эту работу? Или любой подобный инструмент Oracle?
Краткий ответ - нет . К сожалению, способ сделать это в Oracle требует «большого количества скучных запросов». Статьи, на которые вы ссылаетесь, являются одними из лучших на эту тему. Файл данных действительно фрагментируется, поэтому даже если свободное пространство находится ниже самого высокого сегмента, Oracle не будет автоматически консолидировать его, когда RESIZE будет выполнено a .
Чтобы «дефрагментировать» табличное пространство, вам нужно переместить эти сегменты в начало файла данных, а не в конец. Для таблиц это автономный процесс, то есть таблица будет недоступна во время перемещения. Индексы можно перемещать либо в автономном режиме, либо с помощью Enterprise Edition их можно перемещать в режиме онлайн. Поскольку у вас есть окно отключения, я рекомендую вам выполнить следующие шаги.
A. Сжатие файлов данных со свободным пространством за отметкой максимальной воды. Это можно сделать следующим образом (запрос аналогичен процедуре Frosty Z):
Б. После того, как вы сузили положение выше верхней отметки, выясните, какие табличные пространства все еще выиграют от перемещения сегментов.
C. Для каждого из этих табличных пространств определите, какие сегменты необходимо переместить. (Замените USERS именем вашего табличного пространства или присоедините его к предыдущему запросу)
D. Переместите каждую таблицу и перестройте индексы и статистику.
E. повторите шаг A.
Я только что построил большинство этих запросов, так что вы захотите тщательно протестировать их перед использованием. Я полагаю, вы могли бы создать процедуру, которая использовалась бы EXECUTE IMMEDIATE для создания фактических операторов для динамического выполнения, но поскольку запросы будут получать ORA-08103: объект больше не существует, пока выполняется перемещение, я думаю, что лучше контролировать этот процесс вручную, даже если если это действительно означает немного больше времени / усилий.
Недействительные объекты PL/SQL и неприменимые индексы оказывают влияние на производительность. Недействительный объект PL/SQL должен быть перекомпилирован прежде, чем он сможет использоваться. Это потребует времени на перекомпиляцию, которое надо будет добавить к первому действию, которое пытается получить доступ к пакету PL/SQL, процедуре или функции. Если PL/SQL не удалось перекомпилировать успешно, операция завершится с ошибкой. Неприменимые индексы игнорируются оптимизатором. Если надлежащая производительность SQL-оператора зависит от индекса, который был отмечен как неприменимый, производительность не улучшится, пока индекс не будет пересоздан.
Недействительные объекты PL/SQL: Текущий статус объектов PL/SQL может быть просмотрен, запрашивая словарь данных. Можно найти недействительные объекты PL/SQL следующим образом:
SELECT object_name, object_type FROM DBA_OBJECTS
WHERE status = 'INVALID';
По умолчанию метрика количества недействительных объектов владельца проверяется каждые 24 часа. Если число объектов для отдельного владельца превышает два, возникает предупреждение.
Ничего не делать. Большинство PL/SQL объектов автоматически перекомпилируются если нужно, когда их вызывают. Пользователи испытывают небольшую задержку, пока происходит перекомпиляция. (В большинстве случаев эта задержка даже не заметна.)
Вручную перекомпилировать недействительный объект.
Недействительные PL/SQL объекты могут быть вручную перекомпилированы в Enterprise Manager или посредством команд SQL:
ALTER PROCEDURE HR.add_job_history COMPILE;
Ручная перекомпиляция пакетов PL/SQL состоит из двух шагов:
ALTER PACKAGE HR.maintainemp COMPILE;
ALTER PACKAGE HR.maintainemp COMPILE BODY;
Неприменимые индексы: Неприменимые индексы могут быть найдены, запрашивая представление словаря данных DBA_INDEXES:
SELECT index_name, table_name FROM DBA_INDEXES
WHERE status = 'UNUSABLE';
Для разделенных индексов состояние хранится в представлении DBA_IND_PARTITIONS.
Неприменимые индексы делаются действительными посредством их пересоздания, чтобы повторно вычислить указатели. Восстановление неприменимого индекса пересоздает индекс в новом расположении и затем удаляет неприменимый индекс. Это может быть сделано в Enterprise Manager или посредством команд SQL:
ALTER INDEX HR.emp_empid_pk REBUILD;
ALTER INDEX HR.emp_empid_pk REBUILD ONLINE;
ALTER INDEX HR.email REBUILD TABLESPACE USERS;
Если предложение TABLESPACE не указывается, индекс пересоздается в том же самом табличном пространстве , где он существовал прежде. Предложение REBUILD ONLINE позволяет пользователям продолжать обновлять таблицу индекса, в то время как происходит пересоздание индекса. (Без ключевого слова ONLINE пользователи должны ожидать, пока не закончится пересоздание, прежде, чем выполнить DML на таблице, индекс которой пересоздается. Если индекс неприменим, он не применяется во время пересоздания, даже если используется ключевое слово ONLINE.)
Enterprise Manager использует действие Reorganize, чтобы восстановить неприменимый (UNUSABLE) индекс.
Отметьте: Пересоздание индекса требует, чтобы было доступно свободное пространство для пересоздания. Проверьте, что есть достаточно места прежде, чем делать попытку пересоздания. Enterprise Manager автоматически проверяет требования пространства.
tkyte@TKYTE816> create index local nonprefixed on partitioned table (b)
Теперь вставим данные в один фрагмент и пометим фрагменты индексов как не используемые (UNUSABLE):
tkyte@TKYTE816> insert into partitioned table values (1, 1) ;
tky1e@TKIE816> alter index local prefixed modify partition part2 unusable; Index altered.
tkyte@TKni816> alter index localnonprefixed modify partition part2 unusable;
Пометка этих фрагментов индекса как UNUSABLE предотвращает доступ к ним сервера Oracle. Все будет точно так же, как если бы произошел сбой носителя, - фрагменты недоступны. Теперь выполним запросы к таблице, чтобы разобраться, какие фрагменты индексов потребуются для разных запросов:
tkyte@TKYTE816> set autotrace on explain
tkyte@TKYTE816> select * from partitioned table where a = 1 and b = 1;
0 SELECT STATEMENT Qptimizer=CHOOSE (Cost=l Card=l Bytes=2 6)
1 0 INDEX (RANGE SCAN) OF LOCAL PREFIXED (NON-UNIQUE) (Cost=l
Итак, запрос, использующий индекс LOCAL PREFIX, успешно выполнен. Оптимизатор смог исключить фрагмент PART 2 индекса LOCAL PREFIX из рассмотрения, поскольку в запросе задано условие А=1. Нам помогло игнорирование фрагмента. Для второго запроса:
tkyte@TKYTE816> select * from partitioned table where b = 1;
ORA-01502: index TKYTE.LOCAL NONPREFIXED or partition of such index is in unusable state no rows selected Execution Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=l Card=2 Bytes=52)
1 0 PARTITION RANGE (ALL)
2 1 TABLE ACCESS (BY LOCAL INDEX ROWID) OF PARTITIONED TABLE
3 2 INDEX (RANGE SCAN) OF LOCAL NONPREFIXED (NON-UNIQUE)
Оптимизатор не смог исключить из рассмотрения фрагмент PART 2 индекса LOCAL NONPREFIXED. С этим и связана проблема производительности при исполь-
зовании локально фрагментированных индексов без префикса. Они используются и для запросов, не включающих ключ фрагментации, в отличие от индексов с префиксом. Дело не в том, что индексы с префиксом лучше, просто они используются запросами, обеспечивающими возможность игнорирования фрагментов.
Если удалить индекс LOCAL PREFIXED и еще раз выполнить исходный, успешно выполненный запрос:
tkyte@TK:E816> select * from partitionedtable where a = 1 and b = 1;
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=l Card=l Bytes=26)
1 0 TABLE ACCESS (BY LOCAL INDEX ROWID) OF PARTITIONED TABLE
2 1 INDEX (RANGE SCAN) OF LOCAL NONPREFIXED (NON-UNIQUE) (Cost=l
Этот результат может показаться удивительным. Почти такой же план выполнения, как и в случае неудавшегося запроса, но на этот раз все работает. Причина в том, что оптимизатор может пропускать фрагменты даже для локально фрагментированных индексов без префикса (в этом плане нет шага PARTITION RANGE(ALL)).
Если к представленной выше таблице часто выполняются запросы вида:
select . from partitioned table where a = :a and b = :b; select . from partitioned table where b = :b;
имеет смысл создать локально фрагментированный индекс без префикса по столбцам (Ь,а); он пригодится для обоих представленных выше запросов. Локально фрагментиро-ванн1й индекс с префиксом по столбцам (а,Ь) пригодится только для первого запроса.
Однако при использовании фрагментированных индексов в соединениях результаты могут быть другими. В представленных выше примерах сервер Oracle по условию запроса мог во время оптимизации определить, можно или нельзя пропустить фрагменты. Это б1ло понятно по условию в конструкции WHERE (даже если в нем использовались связываемые переменные). Когда доступ по индексу используется в качестве начального, основного метода доступа, особой разницы между локально фрагментирован-ными индексами с префиксом и без префикса нет. При соединении с локально фрагментированным индексом, однако, все меняется. Рассмотрим следующую таблицу, фрагментированную по диапазону:
tkyte@TKYTE816> CREATE TABLE range example
2 (range key column date,
4 data varchar2(20)
6 PARTITION BY RANGE (range key column)
7 (PARTITION part l VALUES LESS THAN
8 (to date(01-]an-1995, dd-mon-yyyy)),
9 PARTITION part 2 VALUES LESS THAN
10 (to date(01-jan-1996,dd-mon-yyyy))
tkyte@TKYTE816> alter table range example
2 add constraint range example pk
3 primary key (range key column,x)
4 using index local
tkyte@TKYTE816> insert into range example values (to date(01-jan-1994), 1,
tkyte@KYTE816> insert into rangeexample values (to date(01-jan-1995), 2, xxx);
Сначала по таблице создан локально фрагментированный индекс с префиксом для первичного ключа. Чтобы увидеть разницу между индексами с префиксом и без префикса, необходимо создать еще одну таблицу. Используем эту таблицу в качестве ведущей в запросе к таблице RANGE EXAMPLE. Таблица TEST просто используется в качестве ведущей в запросе, который будет выполнять соединение вложенными циклами с таблицей RANGE EXAMPLE:
Я столкнулся с этим вопросом в интервью и понятия не имел, как на него ответить:
Есть таблица с индексом для столбца, и вы запрашиваете:
Запрос занимает слишком много времени, и вы обнаруживаете, что индекс не используется. Если вы думаете, что производительность запроса будет лучше при использовании индекса, как вы можете заставить запрос использовать индекс?
Вы можете использовать подсказки оптимизатора
select /*+ INDEX(table_name index_name) */ from table и т. Д.
Может быть множество причин , по которым индекс не используется. Даже после того, как вы укажете подсказки , есть вероятность, что оптимизатор Oracle думает иначе и решит не использовать индекс . Вам нужно пройти часть EXPLAIN PLAN и посмотреть, сколько стоит инструкция с INDEX и без INDEX.
Предполагая, что Oracle использует CBO. Чаще всего, если оптимизатор считает, что использование INDEX обходится дорого, даже если вы указываете это в подсказках, оптимизатор проигнорирует и продолжит полное сканирование таблицы. Ваше первое действие должно проверить DBA_INDEXES, чтобы узнать, когда статистика LAST_ANALYZED. Если не проанализировать, вы можете установить таблицу, индексировать для проанализировать.
В крайнем случае вы можете попробовать настроить статистику самостоятельно.
Если вы думаете, что производительность запроса будет лучше при использовании индекса, как вы можете заставить запрос использовать индекс?
Сначала вы, конечно, должны убедиться, что индекс дает лучший результат для возврата полного набора данных, верно?
Подсказка индекса является здесь ключевым моментом, но наиболее актуальный способ ее указания заключается в использовании метода именования столбцов, а не метода именования индексов. В вашем случае вы бы использовали:
В более сложных случаях вы можете .
Для column_having_index есть соответствующий индекс, и его использование действительно увеличивает производительность, но Oracle его не использовал .
Вы должны собрать статистику в своей таблице, чтобы оптимизатор увидел, что доступ к индексу может помочь. Использование прямой подсказки - не лучшая практика.
Читайте также: