Партиционирование существующей таблицы

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

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.
...

В следующие варианты - в следующем выпуске новостей.