Можно ли использовать первичный ключ для таблицы с временными данными?

Функции первичных ключей мы изучили в предыдущей главе. Какова же роль nepB№iHoro ключа в случае временных данных? Грустная правда заключается в том, что, сколько не ищите, вы не найдете в нащей табличной структуре первичного ключа, который препятствовал бы наличию нескольких разных цен в один день. Единственный вариант, где первичный ключ может предотвратить наличие дубликатов, - это таблица, в которой каждому единичному интервалу соответствует отдельная строка (для курсов акций интервал может быть равен одной секунде!). Такая таблица имеет еще одно преимущество - цены из нее можно выбирать с помощью эквисоединений.

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

Если бы в нашей таблице-примере PRICES не хранились временные данные, то столбец PRODUCT CODE был бы в ней первичным ключом. Однако из-за ввода действительности по дате значения этого столбца не являются уникальными. Можно включить в первичный ключ столбец DA-TE FROM, тогда первичный ключ станет таким: (PRODUCT CODE, DA-TE FROM). Может оказаться более предпочтительным (и допустимым) использовать в качестве второго компонента ключа DATE TO, а не DA-TE FROM, при условии, что значение DATE TO никогда не будет неопределенным. (Если вы читали предыдущий раздел, то, надеемся, согласны с тем, что этот столбец не должен содержать неопределенное значение.) Однако, несмотря на все ухищрения, у нас все еще нет механизма, который не допускает, чтобы в таблице было несколько цен для одного момента времени и чтобы существовали периоды, в которые не действует никакая цена.

Давайте предположим, что цены у нас часто меняются и, следовательно, мы имеем много архивной информации о ценах. В этом случае наиболее часто будут требоваться запросы по определению текущей цены, и основная масса запросов будет нацелена на то, чтобы определить последнюю цену конкретного товара. У вас может появиться мысль сделать первичный ключ сверхуникальным, включив в него оба столбца дат. Первичный ключ в этом случае станет таким: (PRODUCT CODE, DATE FROM, DATE TO). Вы Можете подумать, что этот прием повысит производительность запросов, в которых выполняется поиск по диапазону дат. Однако позже вы убедитесь, что использовать такие индексы не рекомендуется, поскольку это не решает проблемы разрывов по дате и перекрытий диапазонов.

В примере 7.1 представлены два подхода к нашей базовой задаче поиска ~ непроцедурное решение (с использованием SQL) и процедурное (с Использованием PL/SQL со встроенным SQL). Как мы упоминали в начале

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

Несмотря на нащу привязанность к процедурному методу, мы понимаем, что обстоятельства иногда заставляют идти по непроцедурному пути, поскольку не каждое инструментальное средство поддерживает PL/SQL рг вызовы процедур.

Пример 7.1. Варианты решений задачи поиска текущей цены данного продукта

Пример 1а. Непроцедурный подход

SELECT price

FROM prices

WHERE procluct Cocle = :p Cocle

AND SYSDATE BETWEEN date frotn AND date to;

Пример 16. Процедурный подход

CREATE OR REPLACE FUNCTION price now

( p code IN price.product code%TYPE ) RETURN NUMBER IS

CURSOR c price IS SELECT price

, date frotn FROM prices WHERE product code = p code AND date to >= SYSDATE ORDER BY date to;

p price NUMBER;

p from DATE : = TO DATE(Ol-Dec-4712 , DD-Mon-YYYY) ;

BEGIN

OPEN c price;

FETCH c prace INTO p price, p from;

CLOSE c price; J

IF p frotn <= SYSDATE

THEN RETURN 0; ELSE RETURN p price; END IF; EXCEPTION

WHEN no data found THEN RETURN 0;

END price now; i

В первом варианте (SQL-запрос) кажется эффективным использовать индекс для столбцов (PRODUCT CODE, DATE TO), поскольку параметр поиска находится у верхнего края индекса. Однако ухтите, что при обработке

запроса все равно будет просмотрена каждая строка, у которой значение DATE TO больше SYSDATE. Другими словами, оптимизатор будет продолжать ггение строк после того, как мы нашли указанную строку. Это происходит потому, что даже для первичного ключа оптимизатор выполняет диапазонное сканирование индекса и не может знать, что он не найдет еще одну соответствующую условию поиска строку. Эту ситуацию называют перерасходом индекса, и она имеет очень серьезные последствия для производительности в случаях, когда имеется большое количество записей для ключа, состоящего из группы ключей.

Отметим, что индекс для столбцов (PRODUCT CODE, DATE TO, DA-TE 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 CO-1>Е) и столбец для хранения конца диапазона даты (DATE TO). При этом хорошо будет работать и такой простой и высокоэффективной запрос на