Временные данные, или временные ряды, — это данные, содержащие даты и время. Как мы упоминали в главе 5, посвященной типам данных Oracle, временные данные ставят перед проектировщиком базы данных ряд проблем. Наш собственный опыт показал, что неправильная обработка временных данных может быть одной из основных причин серьезных проблем с производительностью и функциональностью в Oracle-системах.
Проблема временных данных
Почему временные данные так трудно обрабатывать? Главная причина состоит в том, что эти данные не очень хорошо вписываются в двумерную реляционную модель. Вследствие этого в большинстве случаев мы не можем использовать для поиска и выборки данных сравнение на равенство. Несмотря на то, что SQL поддерживает соединения, которые не основаны на равенстве, при реализации, как правило, отдается предпочтение эквисоединениям, и подход Oracle — не исключение, хотя, честно говоря, компания недавно добавила поддержку антисоединений с целью содействия обработке определенных типов запросов к хранилищам данных. Для временных данных часто требуется соединять таблицы на базе перекрытия одного диапазона дат и другим. В SQL нет операции, позволяющей непосредственно задать такое соединение, поэтому его реализация сложна для программирования и неэффективна в плане выполнения. (Кроме того, чем больше используется неопределенных значений, тем сложнее становится код.)
Эта глава посвящена вопросам проектирования, связанным с временными данными. В рассматриваемых нами случаях данные обладают характеристикой, называемой действительностью по дате. Это означает, что конкретные данные (часто один атрибут) действительны только определенный период времени. Из этого следует, что некоторые атрибуты, например цена, изменяются во времени, и мы должны спроектировать базу так, чтобы их обработка осуществлялась не только по текущим значениям, а и с учетом времени.
Поскольку в большинстве случаев мы не можем выбирать временные данные, используя сравнение на равенство, в этой главе описан ряд предлагаемых в Огас1е7 методов, позволяющих преодолеть эти налагаемые реляционной моделью ограничения. В некоторых ситуациях мы приводим два решения проблемы. Первое (и предпочтительное) — это, как правило, процедурное, когда в программах на PL/SQL используются возможности SQL. Второе решение основано исключительно на SQL и, следовательно, является непроцедурным. Рекомендуя для временных данных процедурные решения, мы понимаем, что использование PL/SQL — не всегда приемлемый вариант. Если вы пользуетесь таким продуктом, как Oracle Forms, то достаточно легко заменить все DML-операции вызовами пользовательских хранимых процедур, но очень трудно таким вызовом заменить неявный запрос (запрос к базовой таблице блока формы).
Примечание
Можно, хотя это и не очень просто, заставить Oracle Forms выдать вместо запроса к таблице вызов процедуры на PL/SQL. В некоторых случаях этот труд будет хорошо вознагражден повышением производительности, если речь идет об Oracle версии 7.1 и выше (которая допускает динамический SQL внутри PL/SQL). Другой, менее удовлетворительный, вариант — блок в форме построить на представлении, которое вызывает для возврата данных определенные пользователем функции PL/SQL.
Использование специальных процессоров
Многие проблемы обработки временных данных можно решить, используя новые процессоры базы данных, которые реализуются с помощью продукта Universal Server, входящего в комплект Oracle версии 7.3. К эти процессорам относятся:
• Процессор динамического интерактивного анализа данных (OLAP-процессор), позволяющий ускорить подготовку отчетов по нескольким измерениям, или осям. Например, объем розничных продаж можно представить как объем продаж по типам торговых предприятий, а не по видам продукции или как валовую прибыль по регионам и по дням недели.
• Многомерный процессор, который применяется в основном для обработки географических и геофизических данных. Он обеспечивает поиск в нескольких измерениях. Например, можно найти все образцы породы с содержанием окиси железа более 0,1%, взятые в Северном море на глубине свыше 100 ярдов. В этом запросе используются следующие измерения: широта, долгота и расстояние от поверхности. Поскольку время также является измерением, то этот процессор можно очень эффективно использовать при решении проблем временных данных, изложенных в этой главе. В частности, наличие в этом продукте пространственной опции позволяет выполнять проверку диапазонов с помощью специальных столбцов типа hhcocle (в которых можно объединять значения других столбцов), избегая проблем с перерасходом индекса для запроса этого типа. (Мы поясним это ниже.).
Эти дополнительные средства не являются простыми расширениями SQL-сервера СУБД Oracle — они используют собственные хранилища данных. Обеспечить хранение и обработку временных данных требуется практически во всех проектах, но далеко не во всех случаях можно обосновать необходимость использования специализированных серверов. Поэтому в этой главе делается акцент на решениях, которые доступны проектировщикам, чьи системы поддерживает только традиционный сервер.
Пример с временными данными
Давайте рассмотрим простой пример с временными данными. Возьмем таблицу, отражающую динамику цен на некоторые товары. Предположим, что цены в любой момент могут изменяться, поэтому для учета этого необходимо хранить действительный диапазон данных о каждой цене. По временному ряду для данного продукта (табл. 7.1) легко установить, что в период рождественских праздников 1985 г. цена данного товара не была установлена. Для этого достаточно просмотреть этот список, найти последнюю строку, в которой дата начала периода меньше даты Рождества 1985 г., и проверить дату конца периода.
Сложности начинаются, когда вместо того, чтобы хранить данные о ценах в напечатанном виде, мы решаем поместить их в таблицу. Вот один из возможных скриптов для создания такой таблицы:
create TABLE prices
( product_code VARCHAR2(10) NOT NULL
, date_from DATE NOT NULL
, date_to DATE
, price NUMBER NOT NULL
, CONSTRAINT prices_date_range CHECK (date_from < date_to);
Обратите внимание на то, что здесь не задано ограничение первичного ключа. Следует отметить, что поиск действительно первичного ключа для такой таблицы — задача не из тривиальных. (Мы рассмотрим ее ниже.)
Однако даже без первичного ключа мы в состоянии найти цену конкретного товара по состоянию на определенный момент времени. Как это сделать, показано в следующем фрагменте программы. Поскольку мы регистрируем даты (и время) только тогда, когда цена вступает в силу или перестает действовать, вряд ли найдется строка, содержащая нужную нам дату. Это значит, что в условии поиска данных сравнение на равенство использовать нельзя. Отметим также, что данный SQL-запрос располагается в теле PL/SQL-функции. Никаких извинений по этому поводу мы не приносим! Вы, вероятно, уже поняли, что нам нравится использовать капсуляцию. По мере дальнейшего изложения преимущества процедурного подхода будут становиться более очевидными.
CRЕАТЕ OR REPLACE FUNCTION price_at_date
( p_code IN price.product_code%TYPE
, p_when IN DATE
) RETURNS NUMBER IS
p_price NUMBER;
BEGIN
SELECT price
INTO p_price
FROM prices
WHERE product code = p_code
AND date_from <= p_when
AND date_to >= NVL(p_when,
TO_DATE('0l-DEC-4712','DD-MON-YYYY');
RETURN p_price;
EXCEPTION
WHEN no_date_found THEN
RETURN 0;
END price_at_date;
По поводу этого фрагмента следует отметить следующее. Во-первых, мы назвали наши столбцы DATE_FROM и DATE_TO. He пытайтесь использовать имена FROM_DATE и TO_DATE, поскольку второе имя совпадает с именем функции Oracle.
Во-вторых, если конечная дата не указана, соответствующий столбец (DATE_TO) будет содержать неопределенное значение (см. главу 5). Чтобы сравнение работало, придется с помощью функции NVL конвертировать неопределенное значение в очень большую дату (максимальное значение, которое может быть представлено типом данных DATE, — 31 - DEC-4712).
Временные измерения играют важную роль в хранилищах данных. Как мы увидим в главе 13, интерес к средствам поддержки принятия решений привел к тому, что в хранилище помещаются данные, полученные от разных информационных систем, а затем создаются запросы, позволяющие выявить тенденции изменения этих данных. Полученные результаты применяются для поиска путей повышения производительности и расширения рынка сбыта. Очевидно, что эффект от использования хранилища данных в этом случае определяется возможностью извлекать из него архивные и временные данные.
Вопросы проектирования временных баз данных
В этом разделе рассматривается ряд проблем, связанных с обеспечением действительности по дате в схеме базы данных Oracle7, и предлагаются различные подходы к обработке временных данных.
Точность представления даты
Столбцы данных Oracle, имеющие тип DATE, содержат и дату, и время (округленное до секунды). Однако нам необходимо определить, достаточна ли эта информация или ее больше, чем нужно? Другими словами, мы должны решить, какой должна быть точность представления этих данных с точки зрения бизнеса — секунда, минута, час, день?
Вернемся к нашему примеру с данными о ценах. Могут ли цены изменяться на протяжении рабочего дня? В одних сферах деятельности это допустимо, а в других все изменения цен производятся вечером. Если у нас установлено последнее бизнес-правило (лучше не полениться и проверить, так ли это), то можно "отсечь" время в столбцах DATE_FROM и DATE_TO и хранить в них только дату.
Рассмотрим теперь действительную по дате таблицу, содержащую биржевые цены или курсы акций. Эти цены все время колеблются. Так, если мы хотим зарегистрировать дату и время биржевой сделки, чтобы затем можно было расцепить ее, нужно сделать так, чтобы и время заключения сделки, и время каждого колебания цены регистрировалось с точностью до секунды. Для данной проблемы существует действительно простое решение: зарегистрировать текущий курс акций на момент заключения сделки я сохранить эту цену в самой транзакции.
Некоторые проектировщики склоняются к сложному, привязанному к дате решению, когда это не нужно. Мы рекомендуем сначала исследовать более простые варианты и ориентироваться на действительность по дате лишь в том случае, если простые пути не дают желаемого результата. В отношении описанного выше примера с биржей следует также отметить, что цена сделки на момент сбора данных может быть неизвестна.
Если бы мы проектировали систему управления, работающую в режиме реального времени, или встроенную систему управления, то точность представления, равная секунде, могла бы быть и недостаточной. Не вдаваясь в детали, скажем, что в данном случае можно использовать числовое поле — в нем можно хранить число миллисекунд и даже наносекунд, истекших от некоторого базового времени. Базовое время может быть выбрано совершенно произвольно. Как правило, это момент времени, о котором известно, что он наступил раньше, чем любое событие, которое придется обрабатывать данному приложению.
Необходимы ли две даты?
Давайте вновь обратимся к таблице PRICES и подумаем, нужен ли нам столбец DATE_TO. Конечно, содержащиеся в нем данные можно получить из столбца DATE_FROM следующей строки временного ряда. Однако в действительности это верно лишь в случае, когда временной ряд непрерывен, т.е. между строками в ряду нет разрывов по дате. В таблице PRICES такой разрыв есть, и если исключить из нее столбец DATE_FROM, могут возникнуть проблемы.
К сожалению, здесь мы сталкиваемся с еще одной особенностью таблиц. Ни реляционная алгебра, ни SQL ничего не говорят нам о последовательности строк таблицы. Поэтому на практике исключать столбец DATE_TO не рекомендуется, так как это приведет к необходимости использовать очень сложный и неэффективный SQL-код. (Даже для выполнения базовых операций понадобится коррелированный подзапрос.)
Предположим, нам нужно определить цену некоторого товара (:p_code) на некоторую дату (:date_of_interest). Сравним SQL-запросы в следующих примерах. Запрос в примере 1, где используется столбец DATE_TO, проще и может работать во много раз эффективнее, чем эквивалентный запрос в примере 2, который не обращается к этому столбцу. В более сложных запросах, где выполняется соединение таблиц, это различие становится еще более заметным. По этой причине мы рекомендуем всегда создавать пары действительных по дате столбцов. Помимо того, что они упрощают поиск, эти столбцы могут существенно облегчить вам жизнь, когда вы столкнетесь с необходимостью модифицировать граничные даты. Ведь очевидно, что они, так же как и цены, могут изменяться.
/* пример 1 — используются два столбца, действительных по дате */
SELECT price
FROM prices
WHERE product_code = :p_code
AND :date_of_interest BETWEEN date_from AND date_to;
/* пример 2 — используется один столбец, действительный по дате */
SELECT p1.price
FROM prices p1
WHERE p1.product_code = :p_code
AND p1.date_from =
(SELECT MAX (p2.date_from)
FROM prices p2
WHERE p2.product_code = :p_code
AND p2.date_from <= :date_of_interest);
Как обозначать открытость диапазона — большими или неопределенными значениями?
Допустим, что мы решили не исключать из таблицы столбец, содержащий конечную дату. Какое же значение он должен содержать в тех строках, где она неизвестна? Если цена меняется, то мы знаем, что можем получить дату вступления ее в силу. Однако обычно при объявлении новой цены дата окончания ее действия не сообщается или говорится, что она действует до следующего уведомления. Иногда мы можем получить и такую информацию: до 28 апреля товар продается по цене $14,99, а потом — по цене $19,99. В этом случае конечная дата также не задана, и мы должны справиться с этой проблемой. В предыдущих примерах у текущих цен в столбце DATE_TO стояло неопределенное значение. Единственный выход в данном случае — использовать искусственно большое значение, например, 31-DEC-4712 (максимальная дата, которую в Oracle может содержать столбец типа DATE). Конечно, это значение достаточно велико, чтобы пережить и вас, и систему, в которой вы сейчас работаете.
Если вы решили использовать большую дату, но считаете, что значение 31-DEC-4712 вам не подходит, возьмите другое значение, однако постарайтесь ввести его в качестве стандарта, которым будут пользоваться все разработчики системы. Это сделает код удобочитаемым и позволит избежать некоторых ловушек. Каких именно? Например, в качестве конечной даты широко используются значения 01-JAN-4000 и 31-DEC-99. Второй вариант потенциально опасен, так как минимум одна база данных из тех, где он используется, должна работать 1 января 2000 года. К сожалению, этого не произойдет, если не принять срочных профилактических мер. (См. замечания по этому вопросу в приложении Б.)
Что же выбрать — большое или неопределенное значение? Как видно из следующего примера, использование первого метода упрощает синтаксис типового запроса и делает запрос более легким для понимания.
/* использование искусственной большой даты */
...
AND :date of sale BETWEEN p.date from AND p.date_to
/* использование неопределённого значения */
...
AND :date_of_sale BETWEEN p.date from AND
NVL(p.date_to, TO_DATE ('01-DEC-4712', 'DD-MON-YYYY'))
/* альтернативная форма: явная проверка на неопределенное значение */
...
AND ((:date_of_sale BETWEEN p.date_from AND p.date_to)
OR (:date_of_sale >= p.date from AND p.date_to IS NULL))
Однако давайте рассмотрим, с какими проблемами мы столкнемся, если будем хранить в столбце DATE_FROM искусственно большую дату. Во-первых, это значение каким-то образом необходимо поместить в таблицу. В нынешнее время дружественных компьютерных систем не принято, чтобы пользователь вручную вводил такое значение в поле экранной формы. С позиции пользователя логичнее оставить поле конечной даты пустым, если его значение неизвестно или на данный момент не ограничено.
Если пользователь не заполнит поле конечной даты, в приложении можно проверить это и в процессе вставки или обновления заменить пустое (неопределенное) значение большим. Более того, мы можем создать триггер на этой таблице, который конвертирует неопределенное значение в большое при выполнении операции INSERT или UPDATE, как показано в следующем примере. Этот подход гарантирует, что все приложения будут использовать в качестве большой даты одно и то же значение.
CREATE OR REPLACE TRIGGER prices_null_date_to
BEFORE INSERT OR UPDATE OF date_to ON prices FOR EACH ROW
BEGIN
IF :new.date_to IS NULL
THEN
:new.date_to := TO_DATE('0l-DEC-4712','DD-MON-YYYY');
END IF;
END;
Примечание
Категорически не рекомендуем выполнять эту операцию с помощью DEFAULT при определении столбца DATE_TO. Это сработает только при выполнении операции INSERT (но не UPDATE) и только в случае, если вообще не задано никакое значение. Если приложение задает неопределенное значение явно, то в столбце будет неопределенное значение, а не значение по умолчанию.
В общем случае мы не советуем использовать триггер для "разрушения" значений столбца, т.е. для размещения в нем значения, отличного от заданного пользователем. В этом случае мы считаем, что пользователь не задал значение, даже если оператор INSERT содержит для этого поля неопределенное значение.
И еще несколько слов в заключение. Такое использование триггера подчеркивает важность перехода от версии 7.0 к версии 7.1, где допускался наличие нескольких триггеров для событий с одинаковыми временными параметрами. С помощью этого триггера можно конвертировать неопределенные значения независимо от других триггеров, например реализующих правила изменения цен.
Что же произойдет теперь, когда пользователь запросит запись, которую он только что ввел? Если мы ничего не сделаем, то в оставленном пустым поле появится странная дата. "Не помню, чтобы я вводил ее", — подумает пользователь и будет пытаться определить, кто же подмешивает что-то в его кофе. Следовательно, эту дату необходимо скрыть от пользователей. Поскольку мы не можем создать триггер для операции SELECT, нам придется использовать один из следующих подходов:
1. Заставить каждое приложение конвертировать большую дату в неопределенное значение всякий раз, когда это значение необходимо вывести на экран.
2. Обеспечить, чтобы запросы использовали представления, содержащие соответствующую функцию DECODE.
3. Произвести выборку информации посредством хранимых процедур (инкапсуляция); при этом соответствующее преобразование выполнит хранимая процедура.
Затраты на реализацию этих методов — небольшая плата за преимущества, которые дает использование больших значений и индексов, включающих конечную дату (столбец DATE_TO).
В большинстве наблюдаемых нами реализации применяется первое решение. Мы предпочитаем третье решение. Второе и третье решения выгодно отличаются тем, что приложению не нужно знать о существовании правила больших значений. Более того, ему вовсе не обязательно знать, какое именно большое значение используется.
Таким образом, мы рекомендуем всегда заносить большую дату в столбец DATE_TO для открытых диапазонов дат. Единственное, о чем нужно помнить — если у пользователей есть какие-либо средства, позволяющие вводить SQL-запросы, то пользователи должны знать об этом методе или их следует ограничить запросами на доступ через представления, скрывающие большое значение.
Сейчас мы перейдем к заключительному пункту дискуссии о неопределенных и больших значениях и обратим ваше внимание на тот факт, что, хотя неопределенные значения и могут встречаться в составных индексных ключах, оптимизатор Oracle не использует индекс для поиска неопределенного значения. Поэтому, если вы собираетесь использовать в запросах поиск по открытым диапазонам дат, настоятельно рекомендуем воспользоваться методом 1 или методом 3. Функция DECODE в методе 2 нарушит (по меньшей мере, частично) применяемое индексирование и, как вы узнаете из следующего раздела, может полностью нарушить индексный поиск дат.
Можно ли использовать первичный ключ для таблицы с временными данными?
Функции первичных ключей мы изучили в предыдущей главе. Какова же роль первичного ключа в случае временных данных? Грустная правда заключается в том, что, сколько не ищите, вы не найдете в нашей табличной структуре первичного ключа, который препятствовал бы наличию нескольких разных цен в один день. Единственный вариант, где первичный ключ может предотвратить наличие дубликатов, — это таблица, в которой каждому единичному интервалу соответствует отдельная строка (для курсов акций интервал может быть равен одной секунде!). Такая таблица имеет еще одно преимущество — цены из нее можно выбирать с помощью эквисоединений.
Создание подобной таблицы является хорошим проектным решением для данных некоторых типов, например данных о налогах, которые гарантированно остаются постоянными на весь налоговый год. Однако в случаях, когда требуется более высокая степень точности временных данных, число строк в таблице может стать очень большим. (Далее в этой главе мы рассмотрим другие решения, построенные на процедурном подходе.)
Если бы в нашей таблице-примере PRICES не хранились временные данные, то столбец PRODUCT_CODE был бы в ней первичным ключом. Однако из-за ввода действительности по дате значения этого столбца не являются уникальными. Можно включить в первичный ключ столбец DATE_FROM, тогда первичный ключ станет таким: (PRODUCT_CODE, DATE_FROM). Может оказаться более предпочтительным (и допустимым) использовать в качестве второго компонента ключа DATE_TO, а не DATE_FROM, при условии, что значение DATE_TO никогда не будет неопределенным. (Если вы читали предыдущий раздел, то, надеемся, согласны с тем, что этот столбец не должен содержать неопределенное значение.) Однако, несмотря на все ухищрения, у нас все еще нет механизма, который не допускает, чтобы в таблице было несколько цен для одного момента времени и чтобы существовали периоды, в которые не действует никакая цена.
Давайте предположим, что цены у нас часто меняются и, следовательно, мы имеем много архивной информации о ценах. В этом случае наиболее часто будут требоваться запросы по определению текущей цены, и основная масса запросов будет нацелена на то, чтобы определить последнюю цену конкретного товара. У вас может появиться мысль сделать первичный ключ сверхуникальным, включив в него оба столбца дат. Первичный ключ в этом случае станет таким: (PRODUCT_CODE, DATE_FROM, DATE_TO). Вы можете подумать, что этот прием повысит производительность запросов, в вторых выполняется поиск по диапазону дат. Однако позже вы убедитесь, что использовать такие индексы не рекомендуется, поскольку это не решает проблемы разрывов по дате и перекрытий диапазонов.
В примере 7.1 представлены два подхода к нашей базовой задаче поиска - непроцедурное решение (с использованием SQL) и процедурное (с использованием PL/SQL со встроенным SQL). Как мы упоминали в начале главы, процедурный подход в общем случае более предпочтителен, поскольку он пока самый эффективный. (Причины этого скоро станут очевидными.)Обратите внимание, что мы не только выполняем проверку диапазона дат в процедуре, но и вычисляем в ней, а не в SQL-предложении, верхний предел диапазона.
Несмотря на нашу привязанность к процедурному методу, мы понимаем, что обстоятельства иногда заставляют идти по непроцедурному пути, поскольку не каждое инструментальное средство поддерживает PL/SQL вызовы процедур.
Пример 7.1. Варианты решений задачи поиска текущей цены данного продукта
Пример la. Непроцедурный подход
SELECT price
FROM prices
WHERE product_code = :p code
AND SYSDATE BETWEEN date_from AND date_to;
Пример 1б. Процедурный подход
CREATE OR REPLACE FUNCTION price_now
( p_code IN price.product_code%TYPE
) RETURN NUMBER IS
CURSOR c_price IS
SELECT price
, date_from
FROM prices
WHERE product_code = p_code
AND date_to >= SYSDATE
ORDER BY date_to;
p_price NUMBER;
p_from DATE := TO_DATE('01-Dec-4712', 'DD-Mon-YYYY');
BEGIN
OPEN c_price;
FETCH c_price INTO p__price, p_from;
CLOSE c_price;
IF p_from <= SYSDATE
THEN RETURN 0;
ELSE RETURN p_price;
END IF;
EXCEPTION
WHEN no_data_found
THEN RETURN 0;
END price_now;
В первом варианте (SQL-запрос) кажется эффективным использовать индекс для столбцов (PRODUCT_CODE, DATE_TO), поскольку параметр поиска находится у верхнего края индекса. Однако учтите, что при обработке запроса все равно будет просмотрена каждая строка, у которой значение DAТЕ_ТО больше SYSDATE. Другими словами, оптимизатор будет продолжать чтение строк после того, как мы нашли указанную строку. Это происходит потому, что даже для первичного ключа оптимизатор выполняет диапазонное сканирование индекса и не может знать, что он не найдет еще одну соответствующую условию поиска строку. Эту ситуацию называют перерасходом индекса, и она имеет очень серьезные последствия для производительности в случаях, когда имеется большое количество записей для ключа, состоящего из группы ключей.
Отметим, что индекс для столбцов (PRODUCT_CODE, DATE_TO, DATE_FROM) будет работать ненамного лучше, поскольку у оптимизатора нет стратегии, которая позволяла бы полностью задействовать второе поле даты.
Пример 7.1, б) имеет некоторые особенности, на которые следует обратить внимание:
• Мы знаем, что строка с наименьшим значением DATE_TO, соответствующим данному условию, и будет искомой. Это прикладная семантика, которую нельзя описать интерпретатору SQL.
• Если у нас есть индекс (предположительно первичный ключ) для столбцов (PRODUCT_CODE, DATE_TO), то поиск в этом индексе даст искомую строку при первом совпадении.
• Мы указали в запросе только одну из дат, чтобы можно было завершить цикл выборки, как только мы найдем нужную цену.
• Мы указали в запросе не столбец DATE_FROM, а столбец DATE_TO, чтобы обработка осуществлялась в порядке возрастания дат. Если использовать DATE_FROM, то нужной строкой окажется строка с наибольшим значением, соответствующим условию.
• Поскольку в таблице есть столбцы DATE_TO и DATE_FROM, метод, основанный на SQL, работать будет. Максимальная эффективность будет достигнута при выборке последних цен, если индексирован DATE_TO, и при выборке самых старых цен, если индексирован DATE_FROM.
Сейчас слишком рано говорить о том, сделает ли оптимизатор по стоимости в версии 7.3 правильный выбор из индексов (PRODUCT_CODE, DATE_FROM) и (PRODUCT_CODE, DATE_TO), если оба они существуют. Конечно, он не сможет сделать осмысленный выбор, если используются связанные переменные, как в предыдущем примере, потому что гистограммы значений можно задействовать только при использовании литералов.
Большое преимущество процедурного решения состоит в том, что мы прекращаем поиск, как только находим указательную строку; это избавляет нас от колоссального объема работы. Поэтому наш совет — думайте процедурно!
Желательно, чтобы все таблицы этого типа имели в качестве первичного ключа внешний ключ к главной таблице (в данном случае PRODUCT_CODЕ) и столбец для хранения конца диапазона даты (DATE_TO). При этом хорошо будет работать и такой простой и высокоэффективной запрос на поиск самой последней цены (которая в большинстве случаев является текущей):
SELECT price
FROM prices
WHERE product_code = :p_code
AND date_to = TO_DATE('0l-Dec-4712','DD-Mon-YYYY');
Помните наш совет по поводу использования единого для всего проекта значения большой даты? Сейчас вы оцените его важность. Если в качестве большой даты применяется несколько значений, то данный запрос не всегда будет работать правильно. Чтобы избежать этой проблемы, необходимо создать (в Oracle версии 7.1 и выше) общую для проекта функцию, возвращающую большую дату, и использовать эту дату вместо литерального значения:
CREATE OR REPLACE FUNCTION high_date RETURN DATE AS
BEGIN
RETURN TO_DATE ('0l-DEC-4712', ' DD-MON-XXYX');
END high_date;
SELECT price
FROM prices
WHERE product_code = :p_code
AND date_to = high_date;
Мы не должны забывать еще об одной проблеме. При вводе новой цены товара необходимо сделать следующее:
• создать новую цену (с помощью DATE_FROM = SYSDATE+дельта, DATE_TO = большая_дата).
Первый шаг предполагает обновление столбца DATE_TO, который является частью первичного ключа. Обновление столбцов первичного ключа в Oracle допускается, но специалисты по реляционным базам данных, как правило, относятся к этому неодобрительно. Все изменения, внесенные в первичный ключ, нужно каскадно распространить до внешнего ключа всех подчиненных таблиц. В противном случае нарушается ссылочная целостность. (Позже мы объясним, почему эта проблема может оказаться не такой серьезной.)
Итак, мы будем придерживаться нашей первой рекомендации: лучший вариант первичного ключа — это внешний ключ к родительской таблице плюс столбец DATE_TO. Комбинация внешнего ключа и столбца DATE_FROM редко оказывается, оптимальной, но, тем не менее именно эту схему чаще всего используют на практике! Однако вне зависимости от того, какой столбец является вторым компонентом первичного ключа, могут возникнуть проблемы, если во временном ряду имеется много строк с одинаковым значением внешнего ключа. В таких случаях по возможности следует переходить на процедурный метод.
Допускать ли перекрытия и разрывы по дате?
Вопрос о том, следует ли допускать перекрытия и разрывы по дате, связан с непрерывностью временного ряда данных. Ответ зависит от бизнес-правил, ограничений и требований системы.
Вновь обратившись к нашему примеру с ценами, зададим себе два вопроса:
• Может ли товар иметь более одной цены (единицы) в данный момент времени?
• Может ли товар сначала иметь цену, затем не иметь цены, а затем вновь иметь ее?
Для нашей таблицы PRICES ответ на оба вопроса, вероятно, будет отрицательным. Конечно, могут существовать товары, снятые с продажи, у которых последнее значение DATE_TO находится в прошлом, и (что менее вероятно) планируемые товары, у которых наименьшее значение DATE_FROM находится в будущем, но ни один из этих случаев не представляет собой разрыв по дате. Предположим, что для таблицы PRICES нужно создать механизм, препятствующий появлению разрывов и перекрытий по дате, так как они могут вызвать довольно серьезные проблемы при оформлении счетов-фактур.
Если имеется временной ряд, в котором не допускаются ни перекрытия, ни разрывы, то что нужно сделать, чтобы это обеспечить? Во-первых, необходимо создать механизм для их обнаружения. В скрипте на SQL*Plus из примера 7.2 сначала создается простая таблица только с действительными по дате столбцами, а затем — триггеры, которые препятствуют появлению перекрытий и разрывов по дате.
Пример 7.2. Скрипт на SQL*Plus, создающий триггеры, предотвращающие появление перекрытий и разрывов
REM
RЕМ Тестовый сценарий, демонстрирующий проверку наличия
RЕМ перекрывающихся действительных дат и разрывов во временном ряде
REM
REM Предполагается, что степень детализации — день
REM
CREATE TABLE contig
( date_from DATE NOT NULL
CONSTRAINT contig_date_from_time CHECK (date_from =
TRUNC(date_from))
, date_to DATE NOT NULL
CONSTRAINT contig_date_to_time CHECK (date_to = TRUNC(date_to))
, CONSTRAINT contig_range CHECK (date_to >= date_from)
, CONSTRAINT contig_pk PRIMARY KEY (date_to)
);
CREATE OR REPLACE TRIGGER contig_biur
BEFORE INSERT OR UPDATE ON contig FOR EACH ROW
BEGIN
IF :hew.date_to IS NULL
THEN :new.date_to := TO_DATE ('31-DEC-4712', 'DD-MON-YYYY');
END IF;
END;
/
CREATE OR REPLACE TRIGGER contig_aiud
AFTER INSERT OR DELETE OR UPDATE ON contig
DECLARE
l_dummy VARCHAR2(1);
l_prev_date_to DATE;
CURSOR c_contig_overlap IS SELECT 'x'
FROM contig contig1
,contig contig2
WHERE contig1.date_from <= contig2.date_to
AND contig1.date_to >= contig2.date_from
AND contig1.date_to <> contig2.date_to;
CURSOR с_contig_gaps IS SELECT TRUNC(contig.date_from) date_from
,TRUNC(contig.date_to) date_to
FROM contig
ORDER BY contig.date_to;
BEGIN
IF INSERTING OR UPDATING THEN
-
- Проверяем, нет ли перекрытия
-
OPEN c_contig_overlap;
FETCH c_contig_overlap INTO l_dummy;
IF c_contig_overlap%FOUND THEN
CLOSE c_contig_overlap;
RAISE_APPLICATION_ERROR(-20001,'Overlap detected');
END IF;
CLOSE c_contig_overlap;
END IF;
IF DELETING OR INSERTING THEN
-
- Проверяем, нет ли разрывов
-
FOR c_gaps IN с_contig_gaps LOOP
IF c_gaps.date_from <> NVL (l_prev_date_to + 1,
c_gaps.date_from)
THEN RAISE_APPLICATION_ERROR(-20002,'Gap detected');
END IF;
l_prev_date_to := c_gaps.date_to;
END LOOP;
END IF;
END;
/
REM
REM Допустимые действия с таблицами.
REM ======================================
REM Завершение последней строки во временном ряде
REM и добавление после нее новых строк
REM
INSERT INTO contig VALUES('0l-JAN-90',NULL);
UPDATE contig SET date_to = '31-JAN-90' WHERE date_from = '0l-JAN-90';
INSERT INTO CONTIG values('0l-FEB-90', NULL);
UPDATE contig SET date_to = '28-FEB-90' WHERE date_from = '0l-FEB-90';
INSERT INTO contig VALUES('0l-MAR-90',NULL);
REM
REM Теперь данные выглядят так:
REM DATE_FROM DATE_TO
REM ----------- -----------
REM 0l-JAN-1990 31-JAN-1990
REM 0l-FEB-1990 28-FEB-1990
REM 0l-MAR-1990 31-DEC-4712
REM
REM Теперь попробуем выполнить недопустимые действия
REM 1. Изъятие средней строки (создание разрыва) — будет сгенерировано:
REM ORA-20002: Gap detected
REM ORA-06512: at line 35
REM ORA-04088: error during execution of trigger
REM 'ORIGIN.CONTIG_AIUD'
REM
DELETE contig WHERE date_from = '0l-FEB-90';
REM
REM 2. Создание перекрытия — будет сгенерировано следующее:
REM ORA-20001: Overlap detected
REM ORA-06512: at line 23
REM ORA-04088: error during execution of trigger
REM 'ORIGIN.CONTIG AIUD'
REM
INSERT INTO contig VALUES('15-FEB-90','20-FEB-90');
REM
REM 3. Предположим, у нас неправильные границы и 1-й период
REM должен заканчиваться 15 января 1990 года, а второй должен
REM начинаться 16 января 1990 года.
REM Задача состоит в том, чтобы сделать это в одном
REM операторе, не создавая временных разрывов или перекрытий
REM
UPDATE contig SET date_to = DECODE(date_to,'31-JAN-90','15-JAN-90',date_to)
,date_from = DECODE(date_from,'0l-FEB-90','16-JAN-90',date_from)
WHERE date_to IN ('31-JAN-90','28-FEB-90');
REM
REM Таблица теперь выглядит так:
REM DATE_FROM DATE_TO
REM ----------- -----------
REM 0l-JAN-1990 15-JAN-1990
REM 16-JAN-1990 28-FEB-1990
REM 0l-MAR-1990 31-DEC-4712
REM
REM 4. Давайте вернем таблицу в прежнее состояние. Если
REM решение 3 слишком непонятное, то лучше удалить все до
REM того места, откуда начинается изменение, и вновь
REM вставить оттуда.
DELETE contig WHERE date_to >= '28-FEB-90';
UPDATE contig SET date_to = '31-JAN-90' WHERE date_from = '0l-JAN-90';
INSERT INTO contig VALUES ('0l-FEB-90','28-FEB-90');
INSERT INTO contig VALUES ('0l-MAR-90',NULL);
Этот пример хорош в качестве иллюстрации, но используемый в нем подход обладает рядом недостатков:
• Мы применяем триггеры уровня оператора, а не уровня строки. Когда триггер срабатывает, он проверяет на предмет нарушений всю таблицу, а не только те строки, на которые воздействовал данный оператор. Это объясняется тем, что в триггере уровня оператора нельзя обратиться к предыдущему и последующему значениям, чтобы определить, какие строки были объектом операции DELETE или UPDATE.
• Если бы мы использовали триггеры уровня строки (где могли бы получить предыдущее и последующее значения), то триггер не смог бы обратиться к таблице для проверки наличия перекрытия или разрыва. Дело в том, что данная таблица является ограничивающей и мутирующей и в рамках триггера уровня строки на ней самой доступ к ней невозможен. (В приложении Б предлагается основа для лучшего решения — там показано, как избежать проблемы мутирующих таблиц.)
• При создании нового экземпляра строки во временном ряду обычно выполняются следующие действия. Сначала временной диапазон текущей строки завершается оператором
SET date_to
= SYSDATE, где date_to = '0l-DEC-4712'
а затем с помощью
date_from = SYSDATE и date_to =
'0l-DEC-4712"
определяется новый диапазон.
• Триггеры хороши, когда они служат средством, обеспечивающим выполнение правил. При попытке же сделать что-нибудь посложнее, например исправить ошибку, если изменение цены было произведено не в тот день, с ними возникают проблемы. Дело в том, что вы не сможете создать в данных временные разрывы или перекрытия, потому что триггеры не дадут это сделать. В пункте 3 примера показано, что все идет нормально, если мы изменяем данные в одном SQL-предложении, после выполнения которого не остаются ни разрывы, ни перекрытия.
Иногда при работе с SQL нужно проявлять изобретательность. В исключительных случаях требуется временно отключить триггеры, чтобы можно было откорректировать ошибочные данные (правда, брать это за правило мы не рекомендуем).
И еще несколько слов по поводу второго пункта нашего перечня. Если кажется вероятным, что указанное в нем ограничение не даст пользователям исправлять простые ошибки ввода данных, то делайте проверку в приложении, а не при помощи триггера. Приложение может "провести" пользователя по всему процессу изменения данных и обеспечить, что его действия не приведут к созданию разрывов и перекрытий. К сожалению, простых решений нет, и это — одно из разочарований, сопровождающих реализацию временных баз данных в реляционной модели.
Использовать ли отдельную таблицу для архивных данных?
В этом разделе мы рассмотрим ситуации, когда выгодно хранить текущие данные (обладающие особой действительностью по дате) отдельно от остальных действительных по дате данных. Мы обязательно должны рассмотреть возможность применения этого метода в случае, если все временные данные, кроме текущих, являются архивными. Если же нам требуется хранить архивные значения в таблице для проверки или анализа, то тоже следует подумать о том, чтобы разместить их в отдельной таблице.
Мы также рассмотрим вопросы, связанные с использованием логического, а не физического удаления. Термин логическое удаление мы определяем как маркирование данных в качестве более не применимых. Этот вид удаления отличается от физического удаления, при котором данные удаляются по-настоящему. Во многих случаях логическое удаление является ничем иным, как одним из методов проектирования действительности по дате. В конце концов может оказаться, что существуют весьма незначительные различия между тем, что один проектировщик называет логическим удалением, а второй — флагом активности, т.е. столбцом, определяющим, какой является строка — активной или неактивной. Если вы решили применить такой флаг, необходимо создать триггеры, обеспечивающие, чтобы только одна строка группы (в данном случае одна строка на товар) помечалась как текущая, или активная.
До сих пор мы пытались улучшить выполнение запросов, в которых извлекался только текущий экземпляр данных. Это наиболее распространенный тип запросов, потому что архивные данные часто хранятся исключительно как вспомогательное средство для аудита или очень редкого специального исследования. В случаях, где основной интерес вызывают текущие данные, можно рассмотреть возможность разбивки таблицы на две: в первой будет храниться только текущая строка, а во второй — все архивные. Таблица, содержащая только текущие строки, похожа на обычную (переменную), ипри работе с ней не возникают проблемы производительности, характерные для временных данных. В таком простом случае, как цена товара, текущую цену можно просто хранить в главной записи о товаре, а необходимости в отдельной таблице нет (это пример производного значения).
К сведению
Если решено хранить текущие данные отдельно от архивных, то нужно будет учесть случаи, когда необходимо запрашивать текущие и архивные данные вместе. Если это отдельные таблицы, то для совместного запроса к ним понадобится операция UNION ALL для двух отношений (таблиц). Если затем нам понадобится соединить это объединение с другими таблицами, то придется создать на базе его реальное или виртуальное представление. Тогда нам придется заплатить дорогую цену за разбиение данных, потому что при соединении с представлениями, содержащими объединение, возникает ряд серьезных проблем производительности. 3десь может помочь поддержка ручного секционирования, предусмотренная в версии 7.2, однако она действует только для таблиц с идентичными определениями столбцов.
Однако для данной ситуации есть решение, о котором мы вскользь говорили ранее. Можно сделать так, чтобы архивная таблица включала текущую строку. Другими словами, мы можем рассматривать текущие данные как производные или итоговые данные временной таблицы. Избыточность несколько повысится, но у нас будет высокая скорость запросов к текущим данным и приемлемая скорость совместных запросов к текущим и архивным данным.
При этом сразу же возникает вопрос: каким способом образовать эти данные? Разрешить ли пользователям непосредственно сопровождать текущую цену и с помощью триггера создавать действительную по дате запись из старого значения или же позволить им только изменять временные записи, используя триггер для обновления при необходимости текущей цены?
Мы рекомендуем следующее. Если текущее значение таково по определению (т.е. действительность по дате с ним не хранится), то его следует сопровождать непосредственно и выполнять каскадное распространение изменений в архивную таблицу с помощью триггеров. Саму архивную таблицу следует максимально защитить от прямых DML-операций, и для этого можно воспользоваться таким методом. В предыдущем разделе мы упоминали о том, что использование триггеров для предотвращения разрывов и перекрытий в действительных по дате таблицах сопряжено с трудностями, поскольку триггер должен запрашивать таблицу, которая запустила его, и эта таблица считается мутирующей. (Решение см. в приложении Б.) Однако в данном случае можно обратить это в свою пользу — в частности, поместить триггер на архивную таблицу для обновления текущей цены. Если это срабатывает, мы выдаем ошибку, а если не срабатывает из-за того, что текущая таблица является мутирующей, мы можем позволить DML продолжать выполнение, так как знаем, что он должен был быть вызван триггером да DML, который вызвал наш триггер.
При такой обработке следует осторожно обращаться с датами и временем и не создавать ни разрывов, ни перекрытий. Чтобы соблюсти абсолютную точность, нужно использовать глобальную переменную пакета, чтобы на каждом этапе обработке присутствовало одно и то же значение SYSDATE. Эту задачу можно существенно упростить, определив значение DATE_FROM как дату, наступившую за секунду до ввода цены в действие, а значение DATE_TO — как дату с точностью до секунды, когда эта цена перестанет действовать. При таком подходе значение DATE_TO в одной записи будет равным DATE_FROM в следующей по порядку записи, если нет разрывов по дате.
Мы рекомендуем логику, представленную в предыдущем абзаце, как классический пример проектирования — то есть принятие решений, которые облегчают создание кода, дающего правильный результат.
При логическом удалении строки, которые пользователь может считать удаленными, на самом деле не исчезают бесследно. Они могут быть удалены из "живой" таблицы, но останутся в архивных таблицах. При удалении временной диапазон в текущей архивной строке закрывается путем установки в DATE_TO значения, равного SYSDATE. Но как уничтожить все ссылки на строку из действительной по дате таблицы?
Допустим, пользователь ввел новый товар с ошибкой, на следующий день понял это и удалил введенную информацию. Наша триггерная логика вмешивается и создает в архивной таблице эпитафию ошибке, чтобы ее все видели! Хуже того, существует вероятность настоящей путаницы в отчетах о продажах, так как теперь получается, что товар существовал (и имел цену) в течение одного дня. Каждый, кто имеет опыт работы с финансовыми системами, знает, что ошибочно введенные данные всегда можно аннулировать, а не удалить, но в отделе маркетинга предполагают (довольно оправданно), что в отчетах не должны появляться несуществующие товары, и могут об этом забыть.
Ясно, что в таких случаях нам может понадобиться отдельный набор функций для выполнения "физических" операций, с помощью которых можно исправлять ошибки, допущенные при вводе данных (или при других действиях). Ведь все мы делаем ошибки, не правда ли? Эти функции можно реализовать как набор хранимых процедур, инкапсулированных в пакет, который в нашем случае будет манипулировать непосредственно таблицей архивных данных.
Примечание
Необходимо также осознавать, что возможность производить физические операции над действительными по дате таблицами для исправления ошибок ввода является очень важной. Если слишком жестко контролировать действия пользователей, то в результате мы придем к ограничению их полномочий. Как бы вежливо и дружелюбно ни вели себя вы и обслуживающий персонал, пользователям редко нравятся системы, в которых им приходится униженно умолять администратора "быстро починить" систему всякий раз, когда они допустили ошибку при вводе.
Здесь важную роль может играть подход к проектированию приложения. Иногда полезно продемонстрировать пользователю, как работает механизм эффективности по дате. Например, можно отобразить на экране начальную и конечную даты, чтобы пользователь мог представить последствия того или иного действия. Системы, в которых попытались "упростить" пользователю задачу, скрыв действительные даты "за кулисами", часто кажутся пользователям непонятными.
Подведем краткие итоги. Мы увидели, что разбиение действительной по дате таблицы на отдельные таблицы (для текущих и архивных данных) может помочь привести текущие данные к виду, позволяющему их быстрее обрабатывать. Однако при этом не следует забывать о связях. Необходимо тщательно исследовать последствия удаления, в частности потому, что после удаления (логического) родительской таблицы и исчезновения текущей строки дочерние таблицы фактически будут ссылаться на другую таблицу. Однако большинство этих проблем утратят свою значительность, если вы последуете совету, данному в следующем разделе, и оставите дату вне внешних ключей.
Как насчет внешних ключей?
В примере, который мы до сих пор рассматривали, у каждой записи о товаре имелся временной ряд цен. На рис. 7.1 показана более сложная модель, предназначенная для реализации скидок за объем заказа. Ее особенность в том, что и таблица PRICES, и таблица DISCOUNTS действительны по дате. Вспомним, что первичный ключ таблицы PRICES был определен как (PRODUCT_CODE, DATE_TO). Следовательно, внешний ключ в таблице DISCOUNTS следует определить как пару столбцов F_PRICE_PRODUCT_CODE и F_PRICE_DATE_TO. Поскольку скидки тоже действительны по дате, то теперь мы имеем три столбца, описывающих действительность по дате, - F_PRICE_DATE_TO, DATE_TO и DATE_FROM. В результате начинается большая путаница, особенно при переходе на следующий уровень, где в каждой строке у нас будет еще больше столбцов DATE_TO. Кроме того, написание SQL-запросов к структуре такого вида — занятие обременительное и сопровождающееся ошибками.
Рис. 7.1. Модель данных, обеспечивающая поддержку скидок.
Можно с уверенностью сказать, что значения диапазона дат из таблицы DISCOUNTS должны попадать внутрь соответствующего диапазона дат из таблицы PRICES. Действительно, если цена начинает действовать с 1 января 1997 года, не имеет смысла вводить для нее скидку с 20 декабря 1996 года. Это всегда выполняется, если действительная по дате таблица является подчиненной таблицей другой действительной по дате таблицы.
Мы принимаем решение не включать в дочернюю таблицу столбец даты из родительской таблицы, чтобы в дочерней записи содержался только частичный "внешний ключ". Учитывая наше утверждение о том, что диапазон дат дочерней записи входит в диапазон дат родительской записи, мы можем вывести эту родительскую запись, зная F_PRICE_PRODUCT_CODE (в нашем случае) и диапазон дат для скидки. Мы видели случаи, когда этот подход принят на вооружение, но у нас есть серьезные замечания, а именно:
• Мы не можем использовать ограничения внешнего ключа для обеспечения связи; нам пришлось бы использовать триггеры.
• Реляционные пуристы в ужасе вскинули бы руки; мы нарушаем одно из фундаментальных правил реляционной модели, о чем свидетельствует сложность попытки представления структуры-результата с помощью наших соглашений о моделировании.
• Производительность адекватна, если речь идет о поиске цены за вычетом скидки на определенную дату, но при поиске агрегированных показателей, например средней цены-нетто за год, с производительностью возникают проблемы.
Сама модель также вызывает ряд вопросов. Вот самые важные:
• Можно ли модифицировать FROM и ТО одного диапазона скидок для данного товара?
• Может ли измениться цена товара при изменении одной из скидок?
• Должны ли изменяться скидки при изменении цены?
Если мы отвечаем на первый вопрос "да", то, вероятно, требуем права создавать разрывы. Если у нас такие диапазоны скидок, как показано в табл. 7.2, то любая попытка изменить верхний предел первого диапазона должна сопровождаться изменением нижнего предела второго диапазона, чтобы не создавать разрыв или перекрытие.
Таблица 7.2. Примерные ставки скидок
Товар
С
По
Диапазон
Скидка
CZ043 CZ043 CZ043
1 июня 1997 г. 1 июня 1997г. 1 июня 1997 г.
31 декабря 1997 г. 31 декабря 1997 г. 31 декабря 1997 г.
10-49 50-99 100 и выше
5% 10% 15%
Мы предполагаем, что при нормальных обстоятельствах на все три вопроса будет дан ответ "нет" или "не обязательно". Следовательно, эта модель почти наверняка неверна, и мы должны допустить, чтобы записи таблиц PRICES и DISCOUNTS были дочерними записями PRODUCTS. Это типичная особенность временных данных. Временные ряды, которые, на первый взгляд, кажутся дочерними рядами разных временных рядов, часто оказываются детьми тех же родителей. Осознание этого факта может существенно упростить обработку полученных в результате структур данных.
Обработка еще упростится, если сделать так, чтобы все диапазоны скидок для данного товара имели одни и те же действительные даты. Полученная в результате модель данных показана на рис. 7.2, а определения таблиц приведены ниже.
Рис. 7.2. Новый подход к скидкам
CREATE TABLE prices
( product_code VARCHAR2(10) NOT HULL
, date_from DATE NOT NULL
CONSTRANT prices_df CHECK (date_from = TRUNC(date_from))
, date_to DATE NOT NULL
CONSTRANT prices_dt CHECK (date_to = TRUNC(date_to))
, price NUMBER NOT NULL
, CONSTRANT prices_date_range CHECK (date_from <= date_to)
, CONSTRANT prices_pk PRIMARY KEY (product_code, date_to)
, CONSTRANT prices_fk FOREIGN KEY (product_code)
REFERENCES (products)
);
CREATE TABLE discount_sets
( product_code VARCHAR2(10) NOT NULL
, date_to DATE NOT NULL
, CONSTRANT disk_set_pk PRIMARY KEY (product_code, date_to)
, CONSTRANT disk_fk FOREIGN KEY (product_code)
REFERENCES (products)
);
CREATE TABLE discounts
( product_code VARCHAR2(10) NOT NULL
, date_to DATE NOT NULL
, qty_from NUMBER(8,0) NOT NULL
, qty_to NUMBER(8,0) NOT NULL
, discount_pct NUMBER(2,2) NOT NULL
, CONSTRANT disk_pk PRIMARY KEY (product_code, date_to, qty_to)
, CONSTRANT disk_fk FOREIGN KEY (product_code, date_to)
REFERENCES (discount_sets)
, CONSTRANT disk_qty_range CHECK (qty_from <= qty_to)
);
Используя эту структуру, мы также избегаем проблемы поиска по двум диапазонам, поскольку для того, чтобы определить, какая скидка применима к конкретной строке заказа, мы должны сначала найти действующий набор скидок, а затем соответствующую запись о скидке. Предполагая, что у нас есть триггеры, предотвращающие появление разрывов и перекрытий, мы можем, модифицировав ранее использованный запрос, найти требуемую запись о ссылке. Рассмотрим следующий пример:
/* Пример запроса на получение скидки, которая действует для
количества :qty товара :pcode, проданного :sdate */
SELECT discount_pct
FROM discount_sets s
, discounts d
WHERE s.product_code = :pcode
AND s.date_to >= :sdate
AND d.product_code = s.product_code
AND d.date_to = s.date_to
AND d.qty_to >= :qty;
Однако в действительности без предложения ORDER BY это делать опасно. Так как наша уверенность в том, что нужной окажется первая возвращенная строка, основывается на наличии определенных индексов и на том, что оптимизатор запросов выберет эти индексы. Более надежным является выполнение такого соединения внутри приложения (либо в PL/SQL либо с помощью какого-нибудь 3GL-средства со встроенным SQL), в результате чего для каждого из двух запросов будет выбираться только одна строка.
Что делать с проверкой по двум диапазонам?
Представим, что у нас есть запись о товаре, содержащая дату первого выпуска товара в продажу и (для товаров, снятых с продажи) дату снятия с продажи. Аналитики хотят знать, какие из этих товаров поступили в продажу в период с 1 февраля 1970 года по 31 марта 1972 года, а какие сняты с продажи в период с 1 января 1980 года по 15 февраля 1981 года. "Нет проблем",— скажете вы и напишете следующий простой SQL-запрос:
SELECT p.product_code
, p.date_realeased
, р.date.dropped
FROM products p
WHERE p.date_released BETWEEN '0l-FEB-70' AND '31-MAR-72'
AND p.date_dropped BETWEEN '0l-JAN-80' AND '15-FEB-81'
К сожалению, вы ошибаетесь, если полагаете, что проблема решена. Дело в том, что для больших объемов данных приемлемого метода оптимизации такого запроса в Oracle нет.
Это один из классов задач, с которыми очень хорошо справляются многомерные процессоры. В чисто реляционной базе данных высокопроизводительные решения искусственны и неуклюжи, но о них надо знать, если вы предполагаете, что вам придется встретиться с запросами этого типа. Аналогичная проблема возникает и в случае с пространственными данными. Классический пример — поиск всех записей, если в условии указано два диапазона значений: широты и долготы.
Решение состоит в хранении производного поля для каждого измерения. Это поле содержит значение, которое можно искать, выполняя сравнение на равенство. В нашем примере мы могли бы использовать календарный месяц и ввести это значение с помощью триггера. Затем необходимо построить индекс (MONTH_RELEASED, MONTH_DROPPED). Когда мы строим запрос, мы передаем ему номер (:query) и вставляем в управляющую таблицу (QUERY_DRIVER) все пары месяцев, которые нас могли бы заинтересовать. В данном примере 42 такие пары. Затем мы выполняем запрос, подобный следующему:
SELECT p.product_code
, p.date_realeased
, р.date.dropped
FROM query# = :query
products p
WHERE q.query# = :query
AND p.month_released = p.month_released
AND p.month_dropped = p.month_dropped
AND p.date_released BETWEEN '0l-FEB-70' AND '31-MAR-72'
AND p.date_dropped BETWEEN '0l-JAN-80' AND '15-FEB-81'
Это совершенно неестественно, и такой запрос нельзя нормально выполнить в данном случае, поэтому многим пользователям-исследователям потребуется средство формирования запросов, которое проанализирует их "примитивный" SQL и построит требуемую структуру. Тем не менее, при правильном индексировании эта форма запроса может дать довольно хорошую производительность.
Нас часто спрашивают, почему мы пользуемся в этом примере управляющей таблицей (управляющая таблица влечет за собой и дополнительные расходы, и существенные осложнения) вместо того, чтобы просто генерировать при помощи оператора IN значения для поиска в индексе. Эта форма показана в следующем операторе SELECT, но здесь есть проблема: многие версии оптимизатора запросов содержат эвристический механизм, который обрабатывает длинные списки аргументов IN путем полного сканирования таблицы. Используйте эту форму только в случае, если вы уверены, что используемый вами оптимизатор всегда будет инициировать поиск в индексе. Если же вас устраивает полное сканирование таблицы, то этот запрос будет работать просто прекрасно (либо при отсутствии индексов, либо при использовании подсказки оптимизатора /* +FULL */).
/* Базовый месяц — январь 1970 г., значение 1 */
SELECT p.product_code
, р.date_realeased
, р.date.dropped
FROM products p
WHERE q.query# = :query
AND (p.month_released, p.month_dropped) IN
((1,121), (1,122), (1,123), ... (3,134))
AND p.date_released BETWEEN '0l-FEB-70' AND '31-MAR-72'
AND p.date_dropped BETWEEN '0l-JAN-80' AND '15-FEB-81';
Проблема при сравнении по двум диапазонам заключается в том, что при помощи естественных данных невозможно "добраться" до уровня, на котором можно использовать либо равенство, либо проверку диапазона (т.е. где нужные ключевые значения в индексе будут расположены рядом). Если бы мы не изобрели столбцы MONTH, то для поиска необходимых товаров нам пришлось бы выполнить одно из следующих действий:
• проверить столбец DATE_DROPPED для каждого товара, вошедшего в диапазон DATE_RELEASED;
• проверить столбец DATE_RELEASED для каждого товара, вошедшего в диапазон DATE_DROPPED.
Возникнут ли при этом проблемы, зависит от объемов данных, избирательности диапазонов и времени, которое готовы потратить пользователи на ожидание ответов. Эффект может быть катастрофическим, если в каждый диапазон попадает значительная часть данных. Если же на их пересечении находится лишь несколько записей, ничего страшного не произойдет.
Временные данные: резюме
Здесь мы попытались кратко сформулировать все наши рекомендации по использованию временных данных, описанные в этой главе.
• В большинстве случаев временная составляющая в дате не нужна, так как необходимая точность представления значений равна одному дню.
• Плохим методом следует считать использование для даты одного столбца, а не двух - для начала и конца периода.
• Если действительная конечная дата не известна, рекомендуется заносить в столбец большое значение, например, 31-DEC-4712.
• В большинстве случаев первичный ключ действительной по дате таблицы должен быть "реальным", или бизнес-ключом, сцепленным со столбцом конечной (а не начальной) даты.
• Процедурные решения могут давать во много раз более высокую производительность, чем чистые SQL-решения, даже если эти процедурные решения недопустимы с реляционной точки зрения.
• Бизнес-правила, запрещающие перекрытия и разрывы во временном ряде, можно реализовать при помощи триггеров.
• Может оказаться выгодным хранить архивные данные отдельно от текущих. Это зависит от того, как часто производится доступ к архивным данным. Текущую запись рекомендуется хранить в обеих таблицах.
• Необходимо дать пользователям возможность исправлять ошибки, позволяя им выполнять над данными операции физического удаления и обновления,
• Для решения проблемы проверки по двум диапазонам потребуется прибегнуть к нетрадиционным решениям. Если эти решения слишком дороги в плане реализации и производительности, то придется использовать OLAP-процессор или многомерный процессор.
К сожалению, даже при выполнении всех этих указаний обработка временных данных не будет легкой задачей. Однако мы надеемся, что наши советы помогут вам решить эту задачу в рамках реляционной модели Oracle с минимальными затратами и обеспечить при этом хорошую производительность.
Знаете ли Вы, что аспектно-ориентированное сборочное программирование - это разновидность сборочного программирования, основанная на сборке полнофункциональных приложений из многоаспектных компонентов, инкапсулирующих различные варианты реализации.
