Как лучше всего партиционировать существующую таблицу, которая уже содержит большой объем данных?
1) Создать новую партиционированную таблицу и залить данные из старой
CREATE TABLE ... PARTITION BY (RANGE)...
INSERT INTO ... SELECT * FROM not_partitioned_table;
или
INSERT /*+ append */INTO ... SELECT * FROM not_partitioned_table;
2) Создать новую партиционированную таблицу из старой таблицы
CREATE TABLE partitioned_table
PARTITION BY (RANGE) ...
AS SELECT * FROM not_partitioned_table;
3) С помощью EXCHANGE PARTITION
ALTER TABLE partitioned_table
EXCHANGE PARTITION calls_01012008
WITH TABLE not_partitioned_table;
4) С помощью пакета DBMS_REDEFITION
Во всех вышеперечисленных вариантах партиционирования существующей таблицы для активной системы по любому понадобится даунтайм таблицы, кроме 4 пункта(использование DBMS_REDEFINITION).
Теперь, попробую более подробно описать каждый из вариантов.
Скажем, у нас есть партиционированная таблица NOT_PARTITIONED_TABLE, в которой хранятся данные о звонках.
SQL> create table not_partitioned_table (
id number,
dialed_number varchar2(100),
call_date date
);
Table created.
SQL> --- заполним таблицу звонковыми данными: 1 миллион записей, маловато, но пойдет
SQL> declare
start_date date;
begin
start_date:=to_date('01.01.2008','DD.MM.YYYY');
for i in 1..1000000 loop
insert into not_partitioned_table values
(i, '1234567890', start_date + (i-1)/24/60/10);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
В итоге, у нас есть непартиционированная таблица NOT_PARTITIONED_TABLE с 1 миллионом записей о звонках с 1 по 7 января 2008 года:
SQL> select trunc(call_date), count(*)
from not_partitioned_table
group by trunc(call_date);
TRUNC(CAL COUNT(*)
--------- ----------
01-JAN-08 144000
02-JAN-08 144000
03-JAN-08 144000
04-JAN-08 144000
05-JAN-08 144000
06-JAN-08 144000
07-JAN-08 136000
7 rows selected.
Наша цель: партиционировать эту таблицу с наименьшим даунтаймом таблицы.
Вариант 1: Создать новую партиционированную таблицу и залить данные из старой
План действий такой:
- Создаем новую партиционированную таблицу
- Можно заблокировать непартиционированной таблицу, чтоб никто не смог изменить данные во время заливки
- Заливаем данные из непартиционированной таблицу
- Удаляем непартиционированную таблицу
- Переименовываем новую партиционированную таблицу
SQL> create table partitioned_table (
id number,
dialed_number varchar2(100),
call_date date)
partition by range (call_date)
(
partition calls_01012008 values less than (to_date('02.01.2008','DD.MM.YYYY')),
partition calls_02012008 values less than (to_date('03.01.2008','DD.MM.YYYY')),
partition calls_03012008 values less than (to_date('04.01.2008','DD.MM.YYYY')),
partition calls_04012008 values less than (to_date('05.01.2008','DD.MM.YYYY')),
partition calls_05012008 values less than (to_date('06.01.2008','DD.MM.YYYY')),
partition calls_06012008 values less than (to_date('07.01.2008','DD.MM.YYYY')),
partition calls_maxvalue values less than (maxvalue)
);
Table created.
Перед заливкой заблокируем таблицу в режиме EXCLUSIVE командой LOCK TABLE, чтоб пока мы заливаем данные, никто не смог изменить данные в них или добавить новые.
SQL> set timing on
SQL> set autotrace on statistics
SQL> lock table not_partitioned_table in exclusive mode;
Table(s) Locked.
Elapsed: 00:00:00.07
Теперь зальем данные командой INSERT. Сперва посмотрим заливку обычным INSERTом:
SQL> insert into partitioned_table select * from not_partitioned_table;
1000000 rows created.
Elapsed: 00:07:53.31
Statistics
----------------------------------------------------------
5666 recursive calls
42061 db block gets
11930 consistent gets
4035 physical reads
35909076 redo size
357 bytes sent via SQL*Net to client
345 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1000000 rows processed
Заливка 1 миллиона записей длилась почти 8 минут. Инсерт сгенерил 35 909 076 байт redo - это почти равно размеру непартиционированной таблицы, таблица весит 33 554 432 байт.
SQL> select bytes from dba_segments
where segment_name like 'NOT_PARTITIONED_TABLE';
BYTES
----------
33554432
Теперь попробуем залить те же данные прямым инсертом (direct path insert), командой INSERT /*+ APPEND */.
Direct Path Insert отличается от обычного инсерта тем, что вставка происходит:
1) в обход буферного кеша
2) новые блоки добавляются за отметкой HWM
То есть новые блоки данных подготавливаются в pga сессии и в обход буферного кеша добавляются ЗА отметкой HWM, если даже до отметки HWM есть свободное место для данных. Как объясняет Том Кайт, неправильно считать, что при таком инсерте (direct path insert) вообще не генерится redo. Redo по-любому генерится, но совсем малюсенький по сравнению с обычным инсертом, что существенно увеличивает скорость заливки.
После таких заливок следует сделать полный бэкап базы на всякий случай.
SQL> insert /*+ append */ into partitioned_table
select * from not_partitioned_table;
1000000 rows created.
Elapsed: 00:00:03.15
Statistics
----------------------------------------------------------
5710 recursive calls
3735 db block gets
5928 consistent gets
3670 physical reads
343352 redo size
351 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1000000 rows processed
Заливка "инсерт аппендом" закончилась за 3 минуты (почти в 3 раза быстрей обычного инсерта), и сгенерил 343352 байтов redo (почти в 100 раз меньше чем при обычном инсерте).
В три раза быстрей чем обычный инсерт, но как можно еще как-то ускорить заливку? Есть один способ:
можно заранее выделить необходимый объем экстентов партициям, чтоб во время заливки на это не тратилось время.
SQL> alter table partitioned_table
modify partition calls_01012008 allocate extent (size 5M);
Table altered.
....
SQL> alter table partitioned_table
modify partition calls_maxvalue allocate extent (size 5M);
Table altered.
После предварительного выделения эктентов партициям, инсерт аппенд отработал всего за 5 секунд!!!!
SQL> insert /*+ append */ into partitioned_table
select * from not_partitioned_table;
1000000 rows created.
Elapsed: 00:00:05.06
А обычный инсерт отработал за 11 секунд:
SQL> insert into partitioned_table select * from not_partitioned_table;
1000000 rows created.
Elapsed: 00:00:11.13
Для ускорения заливки и уменьшения даунтайма таблицы, можно заранее выделить необходимые эктенты партициям, чтоб во времы заливки на эту рекурсивную операцию не тратилось время.
Теперь осталось удалить старую таблицу и переименовать партиционированную таблицу.
SQL> drop table not_partitioned_table;
Table dropped.
SQL> alter table partitioned_table rename to new_table;
Table altered.
Можно и партиции переименовать (но их можно было бы создать с нужным именем изначально):
SQL> alter table new_table rename partition calls_01012008 to new_calls_01012008;
Table altered.
...
В следующие варианты - в следующем выпуске новостей.
