Том,
Я бы хотел знать, как лучше всего выполнить следующие вставки.
У меня есть таблицы 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/).