(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

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

Источник: 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/).

Ссылки по теме


 Распечатать »
 Правила публикации »
  Написать редактору 
 Рекомендовать » Дата публикации: 24.03.2010 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
Oracle Database Standard Edition 2 Processor License
Oracle Database Personal Edition Named User Plus Software Update License & Support
Oracle Database Personal Edition Named User Plus License
Oracle Database Standard Edition 2 Named User Plus License
ABViewer Professional пользовательская
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Новости ITShop.ru - ПО, книги, документация, курсы обучения
Программирование на Microsoft Access
CASE-технологии
Реестр Windows. Секреты работы на компьютере
СУБД Oracle "с нуля"
Программирование на Visual С++
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
 



    
rambler's top100 Rambler's Top100