Секции в реальном мире

2005 г.

Секции в реальном мире

Джонатан Льюис (Jonathan Lewis)
www.jlcomp.demon.co.uk

Перевод: Валерий Кравчук, OpenXS Initiative

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

В этой статье представлены некоторые из проблем, возникающих при реальном использовании возможности обмена секции (exchange partition). В статье была использована версия Oracle 9.2.0.3 - другие версии могут вести себя иначе.

Что такое обмен секций?

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

alter table pt_tab1 exchange partition p_9999 
with table new_data
-- including indexes	-- необязательная конструкция
-- without validation	-- необязательная конструкция
;

Эта команда "взламывает" словарь данных и меняет местами определения указанной секции и таблицы new_data, так что данные неожиданно оказываются в нужном месте в секционированной таблице. Более того, с добавлением двух необязательных дополнительных конструкций, будут заменены также определения индексов и Oracle не будет проверять, принадлежат ли реально данные указанной секции - поэтому обмен выполняется очень быстро.

Или так обычно пишут.

Обычно в статье, демонстрирующей это свойство обмена секций, будет создаваться таблица с несколькими секциями и парой сотен строк в каждой секции, вполне возможно, вообще без индексов, и почти наверняка без каких-либо связанных с ней таблиц.

Обычно, если вы заплатили за поддержку секционирования (partitioning option), таблицы у вас - очень большие, по ним есть несколько индексов и связанные внешними ключами таблицы. Так что же происходит в реальной жизни при выполнении обмена секций?

Во что обходится обмен секций?

Давайте начнем с действительно простого случая - обмена таблицы из 1000000 строк с пустой секцией. Давайте начнем с SQL-операторов для создания секционированной таблицы и не секционированной таблицы - мы будем повторно использовать этот код, с некоторыми изменениями, в оставшейся части статьи. Нам также понадобится табличное пространство, в котором будет около 200 Мбайт свободного места.

create table parent (
	id      number(12,6),
	v1      varchar2(10),
	padding varchar2(100)
)
partition by range(id) (
	partition p1000 values less than (1000),
	partition p3000 values less than (3000),
	partition p5000 values less than (5000)
);

create table parent_ex (
	id      number(12,6),
	v1      varchar2(10),
	padding varchar2(100))
nologging	-- чтобы сэкономить немного времени
;

insert /*+ append ordered full(s1) use_nl(s2) */
into parent_ex
select
	3000 + trunc((rownum-1)/500,6),
	to_char(rownum),
	rpad('x',100)
from
	sys.source$ s1, -- необходима соответствующая привилегия
	sys.source$ s2
where	
	rownum <= 1000000;

А теперь давайте обменяем таблицу с секцией P5000, которой и принадлежат эти данные. Но давайте включим timing, чтобы увидеть, сколько времени на это потребуется.

alter table parent 
exchange partition p5000 with table parent_ex;

Elapsed: 00:00:17.06

Что произошло с "очень быстрым" переносом? Повторите тест с включенным sql_trace и вы обнаружите в трассировочном файле следующий SQL-оператор. Сервер Oracle проверяет, есть ли в таблице parent_ex строки, не принадлежащие указанной секции таблицы parent.


select 1 
from "PARENT_EX" 
where TBL$OR$IDX$PART$NUM("PARENT",0,0,65535,"ID") != :1

Для этого необходимо выполнить полный просмотр таблицы и вызывать функцию для каждой строки в загружаемой таблице - представьте себе результат в реальной системе с большими объемами данных и загруженной подсистемой ввода-вывода.

Но не бойтесь, - именно для таких случаев предназначена конструкция without validation. Повторите эксперимент, но поместите в конце команды exchange эту конструкцию.


alter table parent 
exchange partition p5000 with table parent_ex
without validation;

Elapsed: 00:00:00.00

Ур-ра - так работает намного быстрее! Но, не торопитесь с выводами; в реальных базах данных обычно есть индексы и ограничения первичного или уникального ключа. Поэтому давайте повторим упражнение, но добавим ограничение первичного ключа к основной и обмениваемой таблице перед обменом (Обратите внимание на синтаксис версии 9 для полного указания индекса при добавлении ограничения - я решил обеспечить выполнение ограничения уникального/первичного ключа с помощью уникального индекса). В этом случае, мы, вероятно, захотим включить в процесс обмена и индексы, чтобы данные оказались в основной таблице с пригодной к использованию секцией индекса.

alter table parent 
add constraint p_pk primary key(id) 
using index (create index p_pk on parent(id) nologging local);

alter table parent_ex 
add constraint px_pk primary key(id) 
using index (create index px_pk on parent_ex(id) nologging);

alter table parent 
exchange partition p5000 with table parent_ex
including indexes without validation;

Elapsed: 00:00:28.05

А теперь что произошло? Повтор (не такой уж быстрый) эксперимента с включенным sql_trace выдает представленный ниже SQL-оператор. Сервер Oracle проверяет, что обмен не приведет к проблеме с уникальностью. Запрос просматривает всю таблицу parent (исключая секцию, которую мы обмениваем), чтобы узнать, нет ли дубликатов загружаемых нами строк. Это собенно глупо, поскольку ограничение уникальности поддерживается с помощью локального индекса, поэтому он обязательно включает ключ секционирования, а это означает, что строка может быть только в одной секции, и мы уже пообещали (с помощью конструкции without validation), что все строки принадлежат той секции, в которую мы их помещаем. Конечно, верно, что в других секциях могут быть строки, которые должны бы находиться в загружаемой секции, но и в этом случае я не думаю, что хочу тратить время на их поиск прямо сейчас.

Проверка значений на определенность также кажется немного странной, поскольку в данном случае у нас есть ограничение первичного ключа, которое неявно предполагает ограничение not null. Можно предположить, что это сделано, чтобы использовать тот же код не только для ограничений первичного ключа, но и для ограничений уникальности.

Обратите внимание, в частности, на операторы minus и intersect - они требуют сортировки всего результирующего множества, и я ещё легко отделался со своими 28 секундами, поскольку (a) у меня не было данных в исходной таблице parent, и (b) данные в таблице parent_ex уже были отсортированы.

select "ID" 
from "TEST_USER"."PARENT_EX" 
where not( "ID" is null)
intersect
select "ID" 
from (
	select "ID"
	from "TEST_USER"."PARENT"
	minus 
	select "ID"
	from "TEST_USER"."PARENT" partition (P5000)
) B 
where not( "ID" is null)

Можно ли обойти эти огромные затраты? Да, и все, что нужно сделать - перевести ограничения по обеим таблицам в состяние novalidate перед выполнением обмена.

alter table parent_ex modify primary key novalidate;
alter table parent modify primary key novalidate;

Общее назначение опции novalidate для ограничени - сообщить серверу Oracle, что он не должен проверять уже существующие данные, чтобы убедиться, что они соответствуют ограничению. Новые данные или изменения, которые вы будете делать в старых данных, будут, однако, проверяться, поскольку ограничение все равно действует.

Но тут есть очень тонкая скрытая ловушка. Существует специальный алгоритм оптимизатора, который оценивает стоимость ограничений первичного и уникального ключа ниже обычного, если только ограничение не допускает отложенную проверку или (как мы только что его установили) не находится в состоянии novalidate. Если мы изменим состояние ограничений с validate на novalidate, чтобы снизить затраты на обмен секций, мы можем обнаружить, что при этом случайный запрос начал выполняться иначе!

Так что, пожалуй, лучше оставить ограничения в состоянии validate, и не гнаться за "дешевизной", просто установив опцию without validation при обмене. Один проход по добавляемым данным может быть куда более приемлемым, чем альтернативные варианты.

У вас реляционная база данных?

Но всегда следует ждать в дальнейшем новостей и похуже. В данном случае, у вас может быть две секционированных таблицы с некоторой связью между ними (такое я наблюдал во многих системах). Как это влияет на работу с секциями?

create table child (
   id_p		number(12,6),
   seq_c	number(10),
   v1		varchar2(10),
   padding	varchar2(100),
   constraint	c_fk_p foreign key (id_p) references parent,
   constraint	c_pk primary key (id_p, seq_c) using index local
)
partition by range(id_p) (
	partition p1000 values less than (1000),
	partition p3000 values less than (3000),
	partition p5000 values less than (5000)
);

Обратите внимание, кстати, как эта подчиненная таблица (с ограничением внешнего ключа) создана в соответствии с границами секций главной таблицы - эта архитектура помогает добиться специальной оптимизации соединения на уровне секций.

Когда вы начнете экспериментировать со связями главная-подчиненная, то обнаружите, что обмен секций жестко ограничен, если только не начать повсеместно переводить ограничения в состояние novalidate.

А потом все становится ещё хуже! Если вы когда-то решите удалить старые секции, это можно сделать простым оператором:

alter table child drop partition p1000;
alter table parent drop partition p1000;

Если вы попытаетесь выполнить эти операторы на тестовом примере, который мы ранее использовали, то обнаружите, что они работают быстро и эффективно. К сожалению, наш тестовый пример весьма специфичен: удаляемые секции никогда не содержали никаких данных. Фактически же, при удалении пар секций в главной и подчиненной таблицах возникает три проблемы.

Первая проблема - если вы попытаетесь удалить пару секций главной/подчиненной таблицы, и в секции главной таблицы когда-либо были какие-то данные, то попытка удалить (или очистить, truncate) секцию главной таблицы приведет к выдаче сообщения об ошибке:

ORA-02266: unique/primary keys in table 
referenced by enabled foreign keys

Чтобы удалить секцию в главной таблице, придется отключить ограничение внешнего ключа - даже когда вы удаляете "очевидно" соответствующую секцию подчиненной таблицы. Это, конечно, вполне обоснованно, но интуитивно кажется "нечестным".

Вторая проблема - когда вы удаляете секцию, каждую секуию после нее надо перенумеровать (внутренне) в словаре данных. Представьте себе секционированную таблицу с 3000 секций и двумя локально секционированными индексами. При удалении первой секции сервер Oracle перенумеровывает 9000 строк в словаре данных - и делает это построчно. Один оператор drop приводит к выполнению 9000 отдельных изменений. Это небыстро.

Наконец, как только вы выполнили первый оператор drop (предположительно, для подчиненной таблицы), таблицы перестают быть одинаково секционированными - все соответствующие SQL-курсоры делаются недействительными (такое происходит при любой операции сопровождения с секциями) и оптимизируются заново, и оптимизатор не будет использовать посекционные соединения (partition-wise joins), пока не будет удалена соответствующая секция. Надо хорошо подумать, выбирая время для удаления секций.

Обходные пути

В ваших специальных случаях вы вполне можете находить приемлемые решения или даже пути обхода этих проблем. Например, один (несомненно, жизнеспособный) способ обойти проблему отключения ограничений внешнего ключа опирается на тот факт, что можно удалять секцию в главной таблице, если в ней никогда не было никаких данных. Если "поиграться" с этой идеей, можно найти следующую стратегию удаления пары секций при наличии ограничений всего лишь в режиме novalidate:

создаем пустую таблицу parent_clone с индексами
удаляем секцию подчиненной таблицы
обмениваем секцию главной таблицы с parent_clone, указав конструкции without validation и including indexes
удаляем секцию в главной таблице, что теперь допустимо, поскольку она никогда не содержала данных
удаляем таблицу parent_clone, в которой и находятся все данные

Я не уверен, однако, что это абсолютно безопасно. Что, если отсуствие сообщения об ошибке Oracle ORA-02266 в этом особом случае - ошибка? Что вы будете делать, если корпорация Oracle включит исправление этой ошибки в следующий набор исправлений?

Вывод

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

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

Когда вы знаете, во что обойдется новая возможность, вы сможете принять обоснованное решение о том, как использовать ее в своем приложении.

Остановить печать!

Незадолго до того, как я собрался посылапть эту статью в DBAZine для публикации, я установил обновление до версии 9.2.0.4 и обнаружил замечание в списке исправленных ошибок, утверждающее, что ошибка, которая "может вызывать медленное выполнение обмена для таблиц с ограничениями уникальности", была исправлена. Решение состояло в удалении проверки ограничения, описанной в этой статье.

Джонатан Льюис (Jonathan Lewis) - независимый консультант с более чем 18-летним опытом использования Oracle. Он специализируется на физическом проектировании баз данных и стратегии использования сервера Oracle. Джонатан - автор книги "Practical Oracle 8i - Building Efficient Databases", опубликованной издательством Addison-Wesley, и один из наиболее известных лекторов среди специалистов по Oracle в Великобритании. Подробнее о его публикациях, презентациях и семинарах можно узнать на сайте www.jlcomp.demon.co.uk, где также находится список ЧаВО The Co-operative Oracle Users' FAQ по дискуссионным группам Usenet, связанным с СУБД Oracle.

Эта статья первоначально была опубликована на сайте DBAzine.com, сетевом портале, посвященном проблемам различных СУБД и их решениям. Перевод публикуется с разрешения автора.