Ускорение вставки

Источник: ln

Том, Я бы хотел знать, как лучше всего выполнить следующие вставки.

У меня есть таблицы t1, t2, t3 и x1, x2 и x3:
В таблице t1 - примерно 400000 строк
В таблице t2 - примерно 1000000 строк
В таблице t3 - примерно 200000 строк

Таблицы x1, x2, x3 первоначально пусты - данные добавляются в них каждый месяц. Сейчас для добавления данных я использую 3 вложенных цикла FOR. Основным является внешний цикл, в котором выбираются все записи из таблицы t1 и вставляются в x1. Первичный ключ таблицы x1 берется из последовательности с помощью триггера, срабатывающего перед вставкой. Значение последовательности возвращается в переменную с помощью конструкции returning into.

Следующий вложенный цикл выбирает все записи из таблицы t2, у которых значение в столбце id соответствует значению id (первичному ключу) из x1, и вставляет их в таблицу x2 (снова генерируя первичный ключ в триггере и возвращая его в переменную). Значение последовательности из предыдущего цикла вставляется в x2, чтобы обеспечить связь между x1 и x2.

Последний цикл во многом аналогичен - в таблицу x3 вставляются все записи из t3, у которых значение в столбце id соответствует значению id (первичному ключу) из x2, а значение последовательности из предыдущего цикла вставляется в x3, создавая связь между x2 и x3.

Итак, используется следующий код:

cursor c1 is select * from t1; cursor c2(v_id1 in number) is select * from t2 where t2.id = v_id1; cursor c3(v_id2 in number) is select * from t3 where t3.id = v_id2; for rec1 in c1 loop insert into x1 (col1, col2, col3) values (null, rec1.a, rec1,b) returning col1 into v_new_id;   for rec2 in c2(rec1.a) loop insert into x2 (col1, col2, col3) values (null, v_new_id, rec2.a) returning col1 into v_new_id2;   for rec3 in c3(rec2.a) loop insert into x3 (col1, col2, col3) values (null, v_new_id2, rec3.a); end loop;     end loop;   end loop;

Не будет ли лучше использовать массивы и множественные вставки?

Ответ Тома Кайта Нет ли способа получше...

Верите или нет, но лучше всего будет использовать просто три оператора insert. Вы получите потрясающее ускорение. И ресурсов потребуется намного меньше. Я создам три таблицы t1, t2, t3 следующим образом:

ops$tkyte@ORA920> create table t1( id int, data char(20) );  Table created.  ops$tkyte@ORA920> create table t2( id int, fk_t1 int, data char(20) );  Table created.  ops$tkyte@ORA920> create table t3( id int, fk_t2 int, data char(20) );  Table created.  ops$tkyte@ORA920> insert into t1 select rownum, 'x' from big_table.big_table where rownum <= 400000;  400000 rows created.  ops$tkyte@ORA920> insert into t2 2  select rownum, id, 'x' 3    from ( select id from t1 4            union all 5           select id from t1 6            union all 7           select id from t1 where mod(id,2) = 0 );  1000000 rows created.  ops$tkyte@ORA920> insert into t3 2  select rownum, id, 'x' 3    from (select id from t2 where mod(id,5) = 0);  200000 rows created.  ops$tkyte@ORA920> create index t2_fk_idx on t2(fk_t1);  Index created.  ops$tkyte@ORA920> create index t3_fk_idx on t3(fk_t2);  Index created.  ops$tkyte@ORA920> analyze table t1 compute statistics for table for all indexes for all indexed columns;  Table analyzed.  ops$tkyte@ORA920> analyze table t2 compute statistics for table for all indexes for all indexed columns;  Table analyzed.  ops$tkyte@ORA920> analyze table t3 compute statistics for table for all indexes for all indexed columns;  Table analyzed.

Затем я содаю ваши таблицы x1, x2, x3:

ops$tkyte@ORA920> create table x1a ( newid int, oldid int, data char(20) );  Table created.  ops$tkyte@ORA920> create table x2a ( newid int, oldid int, fk_t1 int, data char(20) );  Table created.  ops$tkyte@ORA920> create table x3a ( newid int, oldid int, fk_t2 int, data char(20) );  Table created.  ops$tkyte@ORA920> create index x1a_idx on x1a(newid,oldid);  Index created.  ops$tkyte@ORA920> create index x2a_idx on x2a(newid,oldid);  Index created.

Я создал таблицы x1 и x1a, x2, x2a и т.д. - таблицы x1a, x2a... отличаются наличием индексов, что позволяет продемонстрировать мой подход к решению этой задачи.

По таблицам x1, x2, x3 я создаю триггеры:

ops$tkyte@ORA920> create or replace trigger x1_trigger 2  before insert on x1 for each row 3  begin 4     select s.nextval into :new.newid from dual; 5  end; 6  /  Trigger created.

Хотя использование триггеров и является самым худшим способом... Вот как я бы загружал данные в таблицы x1, x2, x3:

ops$tkyte@ORA920> exec runstats_pkg.rs_start  PL/SQL procedure successfully completed.  ops$tkyte@ORA920> alter sequence S cache 1000000;  Sequence altered.

Подобный оператор alter sequence необходимо выполнять перед интенсивным использованием последовательности, - вас удивит, НАСКОЛЬКО это повысит производительность.

ops$tkyte@ORA920> column S new_val S; ops$tkyte@ORA920> select s.nextval S from dual;  S ---------- 1  ops$tkyte@ORA920> insert /*+ APPEND */ into x1a 2  select s.nextval, id, data from t1;  400000 rows created.  ops$tkyte@ORA920> commit;  Commit complete.  ops$tkyte@ORA920> insert /*+ APPEND */ into x2a 2  select s.nextval, t2.id, x1.newid, t2.data 3    from t2, x1a x1 4   where x1.newid > &S 5     and x1.oldid = t2.fk_t1; old   4:  where x1.newid > &S new   4:  where x1.newid >          1  1000000 rows created.  ops$tkyte@ORA920> commit;  Commit complete.  ops$tkyte@ORA920> insert /*+ APPEND */ into x3a 2  select s.nextval, t3.id, x2.newid, t3.data 3    from t3, x2a x2 4   where x2.newid > &S old   4:  where x2.newid > &S new   4:  where x2.newid >          1  200000 rows created.  ops$tkyte@ORA920> commit;  Commit complete.  ops$tkyte@ORA920> alter sequence S cache 20;  Sequence altered.  ops$tkyte@ORA920> exec runstats_pkg.rs_middle;  PL/SQL procedure successfully completed.

Вот и весь процесс загрузки -- эти операторы делают все необходимое. Теперь ваш способ:

ops$tkyte@ORA920> declare 2      l_newid_t1 int; 3      l_newid_t2 int; 4  begin 5      for x in ( select * from t1 ) 6      loop 7          insert into x1 ( oldid, data ) values ( x.id, x.data ) 8          returning newid into l_newid_t1; 9          for y in ( select * from t2 where t2.fk_t1 = x.id ) 10          loop 11          insert into x2 ( oldid, fk_t1, data ) values ( y.id, l_newid_t1, y.data ) 12          return newid into l_newid_t2; 13          for z in ( select * from t3 where t3.fk_t2 = y.id ) 14          loop 15              insert into x3 ( oldid, fk_t2, data ) values ( z.id, l_newid_t2, z.data ); 16          end loop; 17          end loop; 18      end loop; 19  end; 20  /  PL/SQL procedure successfully completed.  ops$tkyte@ORA920> exec runstats_pkg.rs_stop(100000);  Run1 ran in 12183 hsecs Run2 ran in 235576 hsecs run 1 ran in 5.17% of the time

Мой вариант работает почти в 20 раз быстрее (причем приходится поддерживать два дополнительных индекса!). Но, что еще важнее:

Name                                  Run1        Run2        Diff LATCH.checkpoint queue latch        36,430     141,104     104,674 LATCH.row cache enqueue latch        3,846     163,180     159,334 LATCH.dml lock allocation              370     160,329     159,959 LATCH.sequence cache             4,800,009   4,960,014     160,005 STAT...recursive cpu usage              63     199,915     199,852 STAT...CPU used by this sessio       5,509     217,812     212,303 STAT...CPU used when call star       5,509     217,812     212,303 STAT...Elapsed Time                 12,270     235,598     223,328 LATCH.undo global data              19,681     253,812     234,131 STAT...enqueue releases              5,151     242,495     237,344 STAT...enqueue requests              5,151     242,499     237,348 LATCH.library cache pin alloca       2,989     323,616     320,627 LATCH.enqueue hash chains           10,545     487,183     476,638 LATCH.row cache objects              5,286     484,151     478,865 STAT...sorts (rows)              1,402,631       2,618  -1,400,013 STAT...consistent gets - exami     407,293   1,890,245   1,482,952 STAT...table scan rows gotten      400,001   2,000,000   1,599,999 STAT...table scans (short tabl           1   1,600,000   1,599,999 STAT...table scan blocks gotte       1,757   2,000,048   1,998,291 STAT...redo entries                 52,903   2,215,021   2,162,118 LATCH.redo allocation               53,998   2,222,013   2,168,015 LATCH.session allocation                31   2,401,734   2,401,703 STAT...buffer is pinned count    2,600,058           0  -2,600,058 STAT...db block gets               102,375   3,161,828   3,059,453 STAT...no work - consistent re   1,207,558   4,402,253   3,194,695 STAT...db block changes             99,734   4,401,587   4,301,853 STAT...buffer is not pinned co   1,202,995   5,598,153   4,395,158 STAT...execute count                   502   4,680,368   4,679,866 STAT...recursive calls               5,033   7,323,453   7,318,420 STAT...consistent gets           3,022,880  10,894,618   7,871,738 STAT...calls to get snapshot s      12,743   8,043,676   8,030,933 LATCH.shared pool                1,604,008   9,805,723   8,201,715 STAT...session logical reads     3,125,255  14,056,446  10,931,191 LATCH.library cache pin          3,204,928  16,245,960  13,041,032 LATCH.library cache              4,807,752  19,774,939  14,967,187 LATCH.cache buffers chains       6,112,094  33,752,275  27,640,181 STAT...redo size               127,584,104 606,190,928 478,606,824

Я просто показываю, что по некоторым показателям разница огромна. Посмотрите на значение REDO SIZE, посмотрите, насколько меньше требуется внутренних блокировок (показатели LATCH)! Это принципиальное отличие...

Run1 latches total versus runs -- difference and pct Run1        Run2        Diff       Pct 20,739,779  91,534,813  70,795,034     22.66%  PL/SQL procedure successfully completed.

Просто используйте 3 оператора insert, и все. Когда приходится писать процедурный код, остановитесь и задайте себе вопрос: "Зачем я это делаю?"

Вопросы читателя от 13 июня 2003 года

Том, У меня возникли два вопроса относительно твоих операторов insert:

ops$tkyte@ORA920> insert /*+ APPEND */ into x2a 2  select s.nextval, t2.id, x1.newid, t2.data 3    from t2, x1a x1 4   where x1.newid > &S 5     and x1.oldid = t2.fk_t1; old   4:  where x1.newid > &S new   4:  where x1.newid >          1  1000000 rows created.  ops$tkyte@ORA920> commit;  Commit complete.  ops$tkyte@ORA920> insert /*+ APPEND */ into x3a 2  select s.nextval, t3.id, x2.newid, t3.data 3    from t3, x2a x2 4   where x2.newid > &S old   4:  where x2.newid > &S new   4:  where x2.newid

Вопрос 1: Зачем нужна конструкция "x1.newid > &S" в конструкции where при вставке в таблицу x2a?

Вопрос 2: Почему конструкция where "x2.oldid = t3.fk_t2;" отсутствует при вставке в таблицу x3a? Аналогичное условие, "x1.oldid = t2.fk_t1;", указано при вставке в таблицу x2a?

Ответ Тома Кайта

Вопрос 1: Я предположил, что эта таблица X1 накапливается со временем и, поскольку таблица X1 будет содержать данные за последний месяц, а также данные за текущий месяц, значение x1.oldid будет дублироваться.

Добавление условия по x1.newid ограничивает просмотр таблицы X1 только вновь добавленными данными.

Вопрос 2: Вы очень внимательны.

Это была ошибка копирования (неверно выбрана граница блока :). Правильно будет так:

ops$tkyte@ORA920> insert /*+ APPEND */ into x3a 2  select s.nextval, t3.id, x2.newid, t3.data 3    from t3, x2a x2 4   where x2.newid > &S 5     and x2.oldid = t3.fk_t2; old   4:  where x2.newid > &S new   4:  where x2.newid >          1  200000 rows created.

Обратите внимание, что в исходном тексте нет ни ';', ни '/' -- я потерял последнюю строку.

Прекрасный пример

Том, Ты используешь в своем примере непосредственную вставку ?

Можно еще ускорить выполнение, если в ходе непосредственной вставки отключить журнализацию, но придется выполнить резервное копирование после завершения вставки, иначе все результаты вставки в случае сбоя могут быть поетеряны. Правильно?

Ответ Тома Кайта

Да, да и еще раз - да.

Комментарий читателя от 15 июня 2003 года

Кажется, я чего-то не понимаю! Если это действие необходимо выполнить в пакете, что придется изменить в твоем примере - я не понимаю, что это значит &S в конструкции where?

Ответ Тома Кайта

&S - значение последовательности перед началом множественной вставки. В PL/SQL-процедуре это может выглядеть так:

  select s.nextval into l_lo_seq from dual; insert /*+ APPEND */ into x2a select s.nextval, t2.id, x1.newid, t2.data from t2, x1a x1 where x1.newid > L_LO_SEQ and x1.oldid = t2.fk_t1;

А что это за пакет runstats_pkg?

Нельзя ли получить исходный код пакета runstats_pkg?

Ответ Тома Кайта

Ищите ссылку на простой набор для тестирования (simple test harness) на этой странице (http://asktom.oracle.com/~tkyte/).

Дефрагментация индексов со сбором статистики MS SQL 2008 R2.

Oracle начала продажи программно-аппаратного комплекса Oracle Exalytics In-Memory Machine.

Oracle security policies.

Перенос сайта(ов) без простоя и потери данных между выделенными серверами.

Как сделать последовательность (sequence) в MySql?.

Главная » Sql