Секционирование. Автоматическое добавление секций

В версии 11g в Oracle появилась несколько новых замечательных схем секционирования — например, удобная функциональность интервального секционирования — автоматического создания секций по мере выхода range из заданных границ.
В версиях до 11g необходимо периодически вручную либо заранее добавлять секции, либо разбивать секцию по умолчанию. То есть постоянно необходимо отслеживать состояние таких таблиц. В данной статье я поделюсь своими решениями для автоматизации таких задач секционирования.
Сначала приведу пример для 11g:

1. create table res (
2.   res_id     number not null,
3.   res_date  date,
4.   hotel_id  number(3),
5.   guest_id  number
6. )
7. partition by range (res_id)
8. interval (100) store in (users)
9. (
10.   partition p1 values less than (101)
11. );

Этот скрипт создает секцию p1 для записей, значение столбца res_id которых находится в диапазоне 1-100. Когда вставляются записи со значением столбца res_id меньшим 101, они помещаются в секцию p1, а когда в новой записи значение этого столбца равно или больше 101, сервер Oracle Database 11g создает новую секцию, имя которой генерируется системой. Подробнее с этим примером и прочими новыми схемами секционирования вы можете познакомиться в переводе статьи Арупа Нанды в русском издании Oracle Magazine.
Рассмотренные ниже решения можно применить и в других СУБД, не поддерживающих автоматическое добавление секций

Решение для равномерно увеличивающегося ключа секционирования без пропусков

Создадим тестовую таблицу:

1. create table test_part(
2. id number not null,
3. name varchar2(100) not null,
4. owner varchar2(100) not null,
5. type varchar2(100) not null,
6. created date not null,
7. constraint test_part_pk
8. primary key(id)
9. )
10. partition by range (id) (partition p1 values less than (10000));

Логично, что если в такой таблице не будет пропусков, то новые секции было бы желательно создавать до того как ключ секционирования приблизится к границе максимальной секции. Сколько у нас осталось значений ключа до границы, мы легко можем определить согласно простой формуле: partition_size — (key-start_key_in_partition), где key — текущий ключ секционирования, start_key_in_partition — первый ключ, который попадает в эту секцию, partition_size — количество ключей в секции, а % — операция целочисленного деления(div). Обычно такое секционирование производится на равные секции, и с учетом этого мы можем упростить эту формулу до такой: partition_size — key%partition_size.
Что нам это дает: зная момент мы можем создать триггер, который будет добавлять секции при наступлении данного события.
Создадим данный триггер:

1. create or replace trigger tr_test_part
2.  before insert on test_part 
3.  for each row
4.  when (mod(NEW.id,10000) = 6000)
5. declare
6.  l_part_name number;
7.  l_maxvalue number;
8.  l_exist     number;
9.  l_partition_exists exception;
10.  PRAGMA EXCEPTION_INIT(l_partition_exists, -14074);
11. PRAGMA AUTONOMOUS_TRANSACTION;
12. begin
13.  l_part_name:=ceil(:NEW.ID/10000)+1;
14.  BEGIN
15.       execute immediate 'alter table xtender.test_part add partition p'||l_part_name||' values less than('||l_maxvalue||')';
16.  EXCEPTION
17.     when l_partition_exists then null;
18.  END;
19. end tr_test_part;

Данный триггер с использованием автономных транзакций автоматически создает новую секцию с именем 'P'+номер секции размером в 10000, когда ID — наш ключ секционирования — остается 4000 значений до границы секции(10000-4000 = 6000, т.е. ID=6000,16000,26000, и тд.), но сначала проверяется не существует ли уже данная секция(такое может произойти, например, при повторном добавлении 6000-й записи, или ручном добавлении секции). Параметры секционирования — 10000 и 4000, вы должны подбирать исходя из вашей конкретной ситуации, но следует учесть, что граница(4000 в примере) должна быть больше максимального количества одномоментно добавляемых записей, т.к. иначе на момент транзакции вставки данных, транзакция не будет «знать» о новой секции, т.к. на начало транзакции ее не существовало, поэтому данные вставлены не будут с жалобой об отсутствии сопоставления секции данному ключу. Этого бы можно было избежать с использованием alter table split default_partition, который я рассмотрю далее, но это скажется на времени выполнения.
Проверим наш триггер, заполнив секцию:

insert into xtender.test_part
select rownum, o.OBJECT_NAME, o.OWNER, o.OBJECT_TYPE, o.CREATED
from all_objects o
where rownum<1000;


Кроме того, в случае использования сиквенсов, которые из-за кэширования «шагают» не последовательно можно изменить триггер, чтобы он выполнялся для набора значений с 4000 до 3900 записи с конца секции:
заменим условие

when (mod(NEW.id,10000) = 6000)

на

when (mod(NEW.id,10000) between 6000 and 6100)

Решение для прочих случаев

В случаях случаях, когда мы указываем секцию по умолчанию, мы можем разделять ее тогда, когда туда уже попали записи, вопрос в том как это автоматически отслеживать.

В Data dictionary мы можем получить информацию о всех секциях секционированных таблиц, сделав выборку из dba_tab_partitions, в которой partition_position указывает порядок секции в таблице, а high_value — параметры секции. Следовательно, мы можем получить имя последней секции в таблице и сделать выборку из нее для получения количества записей в ней.

После получения информации о таблицах, в которых началась запись в дефолтные секции, мы должны отправить уведомление. Для этого мы можем использовать варианты:

• если у вас настроена автоматическая отправка алертов на почту, то просто записать событие в alert.log;
• просто написать процедуру для отправки писем с уведомлением.

Первый вариант реализуется с помощью процедуры dbms_system.ksdwrt, которая имеет два параметра:

• первый(BINARY_INTEGER) — куда записывать, с возможными значениями:1 — в стандартный трейс-файл, 2 — в alert.log, 3 — в оба;
• и второй(varchar2) — собственно сама строка, которую пишем.

Пример:

exec dbms_system.ksdwrt(2, 'Test Alert Message');

Второй вариант — использовать пакет utl_mail или более низкоуровневые — utl_smtp или utl_tcp.
utl_mail — это более удобная обертка для utl_smtp, но для ее использования обязательно необходимо установить параметр smtp_out_server. Вы можете сделать это как только для сессии — «ALTER SESSION SET smtp_out_server = ...» так и для системы «ALTER SYSTEM SET smtp_out_server = ...».
Не удивляйтесь, если вы не можете найти этот пакет у себя — изначально он не включен и для его создания вы должны выполнить два скрипта:

sqlplus sys/<pwd>
SQL> @$ORACLE_HOME/rdbms/admin/utlmail.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtmail.plb

Сведем все воедино в пакет pkg_partitions
Методы пакета:

• function get_penultimate_maxvalue(p_table_owner varchar2, p_table_name varchar2) return varchar2;
  Функция принимает в качестве параметров владельца и имя таблицы и возвращает значение условия(high_value) предпоследней секции. Данная информация может быть нужна, например, в случаях, где последняя секция — секция c maxvalue параметром, и, соответственно, параметр предпоследней секции может быть нужен для определения параметра для новой секции.
  
• function get_maxvalued_partitions return tables_props_array pipelined;
  Функция возвращающая названия таблиц и их владельцев, у которых начала заполняться последняя секция.
  Пример использования:
  

  1. select
  2.  p.*,
  3.  sys.pkg_partitions.get_penultimate_maxvalue(p.table_owner,p.table_name) pre_maxvalue
  4. from
  5.  table(sys.pkg_partitions.get_maxvalued_partitions) p

  
  
• function get_maxvalued_partitions_html return varchar2;
  Функция возвращает тоже самое, что и get_maxvalued_partitions, но в виде html-таблицы
  
• procedure send_partitions_report(mail varchar2);
  Процедура отправки отчета с таблицами, у которых начала заполняться последняя секция. Единственный параметр — адрес, кому отсылать.
  

Код пакета:

1. create or replace package body pkg_partitions is
2. /** Функция возвращающая параметр для предпоследней секции
3. * @param i_table_name Имя таблицы
4. * @return varchar2
5. */
6.  function get_penultimate_maxvalue(p_table_owner varchar2,p_table_name varchar2) return varchar2 is
7.      l_cursor    integer default dbms_sql.open_cursor;
8.      l_ignore    number;
9.      l_long_val varchar2(4000);
10.      l_long_len number;
11.      l_buflen    number := 4000;
12.      l_curpos    number := 0;
13.  begin
14.      dbms_sql.parse( l_cursor,
15.                      'select p.high_value                     from all_tab_partitions p                     where                      p.table_owner like :o                     and p.table_name like :x                     and p.partition_position=                         (                         select max(p1.partition_position)-1                          from all_tab_partitions p1                          where                          p.table_owner like :o                         and p1.table_name like :x                         )'
16.                      ,
17.                      dbms_sql.native );
18.      dbms_sql.bind_variable( l_cursor, ':x', p_table_name );
19.      dbms_sql.bind_variable( l_cursor, ':o', p_table_owner );
20.  
21.      dbms_sql.define_column_long(l_cursor, 1);
22.      l_ignore := dbms_sql.execute(l_cursor);
23.      if (dbms_sql.fetch_rows(l_cursor)>0)
24.      then
25.         dbms_sql.column_value_long(l_cursor, 1, l_buflen, l_curpos ,
26.                                   l_long_val, l_long_len );
27.      end if;
28.      dbms_sql.close_cursor(l_cursor);
29.      return l_long_val;
30.  end;
31.  
32. /** Функция возвращающая названия таблиц и их владельцев, у которых начала заполняться последняя секция
33. * @return
34. *     table_name varchar2(4000),
35. *     table_owner varchar2(4000),
36. *     partitions_count number,
37. *     partition_name varchar2(4000));
38. */
39.  function get_maxvalued_partitions return tables_props_array pipelined is
40.      l_cursor    integer default dbms_sql.open_cursor;
41.      l_count     number;
42.      l_ignore    integer;
43.      l_data     table_props;
44.      cursor l_partitions is
45.         select
46.          pl.table_owner,
47.          pl.table_name,
48.          count(1) cnt,
49.          max(pl.partition_name) keep(dense_rank last order by (pl.partition_position)) partition_name
50.         from dba_tab_partitions pl
51.         where pl.table_name not like 'BIN$%'
52.         group by pl.table_owner,pl.table_name
53.         having count(1)>1;
54.  BEGIN
55.      FOR part in l_partitions
56.         LOOP
57.          dbms_sql.parse( l_cursor,
58.                      'select count(1)                     from '||part.table_owner||'.'||part.table_name
59.                      ||' partition('||part.partition_name||')'
60.                      ||' where rownum<2'
61.                      ,
62.                      dbms_sql.native );
63.          dbms_sql.define_column(l_cursor,1,l_count);
64.          l_ignore:=dbms_sql.execute_and_fetch(l_cursor);
65.          dbms_sql.column_value(l_cursor,1,l_count);
66.          if (l_count>0) then
67.             l_data.table_name:=part.table_name;
68.             l_data.table_owner:=part.table_owner;
69.             l_data.partitions_count:=part.cnt;
70.             l_data.partition_name:=part.partition_name;
71.             pipe row(l_data);
72.          end if;
73.         END LOOP;
74.  END;
75.  
76. /** Функция возвращающая названия таблиц и их владельцев в виде html, у которых начала заполняться последняя секция
77. * @return
78. *     table_name varchar2(4000),
79. *     table_owner varchar2(4000),
80. *     partitions_count number,
81. *     partition_name varchar2(4000));
82. */
83.  function get_maxvalued_partitions_html return varchar2 is
84.      l_cursor    integer default dbms_sql.open_cursor;
85.      l_count     number;
86.      l_ignore    integer;
87.      l_data     varchar2(4000);
88.      cursor l_partitions is
89.         select
90.          pl.table_owner,
91.          pl.table_name,
92.          count(1) cnt,
93.          max(pl.partition_name) keep(dense_rank last order by (pl.partition_position)) partition_name
94.         from dba_tab_partitions pl
95.         where pl.table_name not like 'BIN$%'
96.         group by pl.table_owner,pl.table_name
97.         having count(1)>1;
98.  BEGIN
99.      l_data:='<html><body><table border=1>'
100.             ||'<tr><th>Table name</th>'
101.             ||'<th>Table owner</th>'
102.             ||'<th>Partitions count</th>'
103.             ||'<th>Partition name</th>'
104.             ||'<th>Pre maxvalue</th>';
105.     
106.      FOR part in l_partitions
107.         LOOP
108.          dbms_sql.parse( l_cursor,
109.                      'select count(1)                     from '||part.table_owner||'.'||part.table_name
110.                      ||' partition('||part.partition_name||')'
111.                      ||' where rownum<2'
112.                      ,
113.                      dbms_sql.native );
114.          dbms_sql.define_column(l_cursor,1,l_count);
115.          l_ignore:=dbms_sql.execute_and_fetch(l_cursor);
116.          dbms_sql.column_value(l_cursor,1,l_count);
117.          if (l_count>0) then
118.             l_data:=l_data||'<tr><td>'
119.                          ||part.table_name
120.                          ||'</td><td>'
121.                          ||part.table_owner
122.                          ||'</td><td>'
123.                          ||part.cnt
124.                          ||'</td><td>'
125.                          ||part.partition_name
126.                          ||'</td></tr>';
127.          end if;
128.         END LOOP;
129.      l_data:=l_data||'</table></body></html>';
130.      return l_data;
131.  END;
132. /**
133. * Процедура отправки отчета с таблицами, у которых начала заполняться последняя секция
134. */
135.  procedure send_partitions_report(mail varchar2)
136.     is
137.      msg_body varchar2(4000);
138.     BEGIN
139.      select pkg_partitions.get_maxvalued_partitions_html into msg_body from dual;
140.      --EXECUTE IMMEDIATE 'ALTER SESSION SET smtp_out_server = ''our_mailserver''';
141.      utl_mail.send(
142.             sender => '[e-mail]',
143.             recipients => mail,
144.             subject => 'Maxvalued partitions Report',
145.             message => msg_body,
146.             mime_type => 'text/html');
147.     END;
148. end pkg_partitions;

* This source code was highlighted with Source Code Highlighter.

UPD
Как правильно подсказал zhekappp можно использовать num_rows при включении автоматического сбора статистики. Сбор статистики можно будет включить добавлением задания с помощью dbms_job с dbms_stats.gather_table_stats.
Тогда нужно будет убрать запрос количества записей в секции и изменить запрос на:

1. select
2.          pl.table_owner,
3.          pl.table_name,
4.          count(1) cnt,
5.          max(pl.num_rows) keep(dense_rank last order by (pl.partition_position)) partition_rows,
6.          max(pl.partition_name) keep(dense_rank last order by (pl.partition_position)) partition_name
7.         from dba_tab_partitions pl
8.         where pl.table_name not like 'BIN$%'
9.         group by pl.table_owner,pl.table_name

Полный код пакета для этого случая можно взять тут: http://www.xt-r.com/2010/10/pkgpartitions.html

Автоматическое выполнение

Осталось только настроить автоматическое выполнение. Сделаем это с помощью dbms_job.
Например, ежедневное автоматическое выполнение скрипта получения данных:

1. declare
2.  job binary_integer;
3. begin
4.  dbms_job.submit(
5.      job,
6.      'pkg_partitions.send_partitions_report(''[e-mail]'');',
7.      sysdate,
8.      'trunc(sysdate)+1');
9.  dbms_output.put_line(job);
10. end;