Я хотела написать один большой пост про оптимизацию INSERT, однако в процессе создания, поняла, что будет лучше разбить его на несколько частей. Это первая и, надеюсь, не последняя из них.
К обычному INSERT с подзапросом можно применить хинт APPEND:
Он включает то, что в Oracle называется Direct-Path INSERT.
Если вы честно откроете документацию по ссылке и прочитаете кусочек с начала, то там будет написано, что INSERT такого плана отличается от обычного следующим:
Относительно buffer cache следует помнить что, если у вас маленький датасет, то выигрыша от записи напрямую в datafile можно и не получить, и даже наоборот, заметить, что все стало только хуже. Ведь нам надо сразу же для этого INSERT отыскать на диске datafile и что-то туда записать.
После этого должна появиться ошибка в духе:
Теперь относительно ссылочной целостности. Вообще, этот пункт выглядит очень пугающим для DB-developer. Как можно при вставке взять и проигнорировать существующие ограничения? В этом отношении, все сложнее, чем кажется. Некоторую дискуссию по данному вопросу можно посмотреть здесь и прочитать в документации тут. В двух словах: если у вас есть PRIMARY KEY, то он проигнорирован не будет, а если у вас есть FOREIGN KEY или какой-нибудь триггер, то Oracle проигнорирует /*+APPEND */, и INSERT будет выполнятся стандартным образом. Так что перед Direct-Path все внешние ключи и триггеры придется отключить. Соответственно, если данные при вставке окажутся по факту не очень качественными, то потом придется включать ограничения обратно с бубном, песнями и плясками.
Тем не менее, в той же дискуссии, описывается вариант, когда /*+APPEND */ срабатывает для внешних ключей, при условии что дочерняя таблица является разделенной (partitioned) по этому ключу. Пример:
После SELECT будет та же ORA-12838, что свидетельствует о том, что /*+ APPEND */ сработал.
Последний вариант документации для 12, не отражает это поведение, однако, его стоит посмотреть, потому что кроме ограничений на использование Direct-Path, там также рассмотрены взаимоотношения /*+ APPEND */ с index-organized tables.
Теперь про логгирование и UNDO. Особенностью Direct-Load является то, что для него вообще можно отключить все логгирование кроме самого минимально необходимого, чтобы все не упало. Понятное дело, что без логгирования несколько быстрее, но прежде чем все отключить, подумайте пару минут о том, как вы будете все восстанавливать в случае возможных неприятностей. Есть старая таблица, которая показывает когда будут создаваться логи, и вроде как описание в документации для 12c с ней согласуется.
Здесь не учтена опция FORCE LOGGING - при которой последний столбец всегда будет redo generated.
APPEND_VALUES
Как было сказано в предыдущем пункте, /*+APPEND */ работает только для INSERT с подзапросом, однако, бывают случаи в PL\SQL, когда надо сделать какую-то крупномасштабную вставку через INSERT ... VALUES ... . Для того, чтобы облегчить эту работу в 11g R2 был введен /*+ APPEND_VALUES*/. Суть его абсолютно такая же, как и у обычного /*+APPEND */, а выглядит это например, так:
NOAPPEND
Если на вашей базе или сессии оказался включенным PARALLEL DML, то все INSERT по умолчанию будут Direct-Path. Чтобы избавится от этого поведения создан специальный хинт /*+
Краткий вывод:
Данный хинт больше подходит для DataWarehouse, когда данные вставляются помногу и редко. Нужно использовать с осторожностью, помня об ограничениях, особенно о FOREIGN KEY, который встречается в таблицах достаточно часто.
К обычному INSERT с подзапросом можно применить хинт APPEND:
INSERT /*+APPEND */INTO MY_TABLE SELECT ... ;
Если вы честно откроете документацию по ссылке и прочитаете кусочек с начала, то там будет написано, что INSERT такого плана отличается от обычного следующим:
- Пишет данные сразу в datafile минуя buffer cache.
- Не переиспользует место в таблице, а просто добавляет все новое в самый конец.
- Игнорирует ссылочную целостность.
Относительно buffer cache следует помнить что, если у вас маленький датасет, то выигрыша от записи напрямую в datafile можно и не получить, и даже наоборот, заметить, что все стало только хуже. Ведь нам надо сразу же для этого INSERT отыскать на диске datafile и что-то туда записать.
Запись всегда в конец таблицы логичным образом экономит время на том, что мы не ищем свободные куски пространства, чтобы вставить туда очередную строку. Однако, продолжая эту мысль, легко понять, что можно столкнуться с ситуацией, когда теоретически, свободное место для вставки записей есть, но INSERT /*+APPEND */ выпадет с ошибкой, потому что запись всего куска данных в конец невозможна, так как TABLESPAСE (или что-нибудь еще), не может дальше расшириться. Этот метод сам по себе является более требовательным к пространству, и легко представить, что такая вставка будет увеличивать фрагментацию данных, постоянно запрашивая новые блоки в конце таблицы.
Одновременно с этим, Direct-Path блокирует всю таблицу целиком запись и любую другую модификацию данных. Одновременная работа в нескольких сессиях по внесению изменений пока не прошел INSERT /*+APPEND */, невозможна. Читать данные в других сессиях - можно, а в той, где выполняется загрузка, только после COMMIT. Маленький проверочный тест для сомневающихся.
Одновременно с этим, Direct-Path блокирует всю таблицу целиком запись и любую другую модификацию данных. Одновременная работа в нескольких сессиях по внесению изменений пока не прошел INSERT /*+APPEND */, невозможна. Читать данные в других сессиях - можно, а в той, где выполняется загрузка, только после COMMIT. Маленький проверочный тест для сомневающихся.
create table t ( id int ); insert /*+APPEND */ into t select 1 from dual; select * from t;
ORA-12838: cannot read/modify an object after modifying it in parallel
12838. 00000 - "cannot read/modify an object after modifying it in parallel"
*Cause: Within the same transaction, an attempt was made to add read or
modification statements on a table after it had been modified in parallel
or with direct load. This is not permitted.
*Action: Rewrite the transaction, or break it up into two transactions
one containing the initial modification and the second containing the
parallel modification operation.
Теперь относительно ссылочной целостности. Вообще, этот пункт выглядит очень пугающим для DB-developer. Как можно при вставке взять и проигнорировать существующие ограничения? В этом отношении, все сложнее, чем кажется. Некоторую дискуссию по данному вопросу можно посмотреть здесь и прочитать в документации тут. В двух словах: если у вас есть PRIMARY KEY, то он проигнорирован не будет, а если у вас есть FOREIGN KEY или какой-нибудь триггер, то Oracle проигнорирует /*+APPEND */, и INSERT будет выполнятся стандартным образом. Так что перед Direct-Path все внешние ключи и триггеры придется отключить. Соответственно, если данные при вставке окажутся по факту не очень качественными, то потом придется включать ограничения обратно с бубном, песнями и плясками.
Тем не менее, в той же дискуссии, описывается вариант, когда /*+APPEND */ срабатывает для внешних ключей, при условии что дочерняя таблица является разделенной (partitioned) по этому ключу. Пример:
create table parent (id number primary key) PARTITION BY HASH (id) PARTITIONS 4; create table child (id number primary key, p_id number not null, CONSTRAINT fk_parent FOREIGN KEY (p_id) REFERENCES parent (f1) ) partition by reference (fk_parent); insert /*+ append */ into parent select 1 id from dual; insert /*+ append */ into child select 1 id, 1 p_id from dual; select * from child;
Последний вариант документации для 12, не отражает это поведение, однако, его стоит посмотреть, потому что кроме ограничений на использование Direct-Path, там также рассмотрены взаимоотношения /*+ APPEND */ с index-organized tables.
Теперь про логгирование и UNDO. Особенностью Direct-Load является то, что для него вообще можно отключить все логгирование кроме самого минимально необходимого, чтобы все не упало. Понятное дело, что без логгирования несколько быстрее, но прежде чем все отключить, подумайте пару минут о том, как вы будете все восстанавливать в случае возможных неприятностей. Есть старая таблица, которая показывает когда будут создаваться логи, и вроде как описание в документации для 12c с ней согласуется.
Table Mode Insert Mode ArchiveLog mode result ----------- ------------- ----------------- ---------- LOGGING APPEND ARCHIVE LOG redo generated NOLOGGING APPEND ARCHIVE LOG no redo LOGGING no append "" redo generated NOLOGGING no append "" redo generated LOGGING APPEND noarchive log mode no redo NOLOGGING APPEND noarchive log mode no redo LOGGING no append noarchive log mode redo generated NOLOGGING no append noarchive log mode redo generated
Здесь не учтена опция FORCE LOGGING - при которой последний столбец всегда будет redo generated.
APPEND_VALUES
Как было сказано в предыдущем пункте, /*+APPEND */ работает только для INSERT с подзапросом, однако, бывают случаи в PL\SQL, когда надо сделать какую-то крупномасштабную вставку через INSERT ... VALUES ... . Для того, чтобы облегчить эту работу в 11g R2 был введен /*+ APPEND_VALUES*/. Суть его абсолютно такая же, как и у обычного /*+APPEND */, а выглядит это например, так:
FORALL i IN table_type.FIRST..table_type.LAST INSERT /*+ APPEND_VALUES */ INTO table_name VALUES table_type(i);
NOAPPEND
Если на вашей базе или сессии оказался включенным PARALLEL DML, то все INSERT по умолчанию будут Direct-Path. Чтобы избавится от этого поведения создан специальный хинт /*+
NOAPPEND */Краткий вывод:
Данный хинт больше подходит для DataWarehouse, когда данные вставляются помногу и редко. Нужно использовать с осторожностью, помня об ограничениях, особенно о FOREIGN KEY, который встречается в таблицах достаточно часто.
Комментариев нет:
Отправить комментарий