Сейчас прорабатываю логику одной задачи не могу решить, какой подход лучше.

Есть историческая таблица данных, в которой история задается началом и концом интервала: item, value, date_beg, date_end.
Здесь item это изменяемый элемент, value значение в указанном интервале, а date_beg и date_end задают период действия значения.
Например есть такие данные:
itemvaluedate_begdate_endamount102019-01-142019-01-16amount202019-01-16
Здесь значение amount равно 10 с 14 января, а с 16 января равно уже 20.

Допустим мне нужно вставить в эту историческую запись новое значение, действующее с 15 января.
Значение можно задать таким образом:


Обновлять таблицы я буду merge-запросом, размножая каждую new-запись нужное количество раз с соответствующими вспомогательными полями (например поле flag='before' для записей перед sysdate, flag='after' для записей после sysdate и flag=null для вставляемой записи). И соответственно в предложении ON одним из условий будет flag is not null, соответственно для вставляемых записей будет использоваться when not matched, а для обновления существующих записей будет использоваться when matched с проверкой значения flag.

Вопрос следующий.
Если я вставляю новую запись в историческую таблицу и для этой записи есть какие-то будущие значения (в приведенном примере это значение amount=20 с 2019-01-16), то что с ними сделать? Их нужно аннулировать (удалить) или сохранить?
Другими словами, какая итоговая таблица должна получится после обновления:
itemvaluedate_begdate_endamount102019-01-142019-01-15amount152019-01-15
или
itemvaluedate_begdate_endamount102019-01-142019-01-15amount152019-01-152019-01-16amount202019-01-16
?

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

Alibek B.Но зато второй вариант более способствует сохранению изменений.
Плюс второй вариант позволяет обновить данные одним merge-запросом, а для первого варианта потребуется два запроса — delete и merge.

Dimitry SibiryakovВыкинуть из таблицы поле date_end и задача становится тривиальной.
Но тогда значительно усложнится получение значения, действующего на определенную дату.
Я предполагаю, что в этой таблице будет довольно много записей и мне бы хотелось сохранить возможность быстрой выборки действующих значений.

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

Dimitry SibiryakovС чего бы это?
Я не про конкретный способ получения действующих значений (хотя я бы скорее использовал lead).
Но разве аналитика или подзапросы не будут более "тяжелыми" для сервера, чем обычная фильтрация по индексированным полям?

Про индексы не знаю.
Но я сейчас сравнил два запроса на одной таблице:


В таблице примерно 500 тысяч строк.

Делаю такой запрос:


Отрабатывает мгновенно (11 мс), возвращает примерно 50 тысяч строк.
План:


Делаю такой запрос:


Возвращает те же данные, но выполняется более 30 секунд.
И даже внутренний подзапрос выполняется более 300 мс.
План:

ElicИстория в виде EAV?
Почему EAV? Только потому, что в примере столбец называется item, а не item_id?
Это таблица токенов доступа, у которых есть определенный срок действия. Если срок действия токена истек, нужно запросить новый. А в таблице хранится их история.

andrey_anonymousФиговость повылезает на join с другими версионными таблицами не в срезе на момент, но за период.
Кроме того, будет сложно отличить историческую запись от актуальной, а это обычно важно.
Очень точно сформулировано, спасибо.
Да, я в том числе и это имел ввиду под «усложнением».
Но зато историчность не ввиде периода, а в виде эффективной даты настолько упрощает вставку записей, что поневоле задумаешься.

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

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

andrey_anonymousто дополнительно рассмотрите поддержку этого вида в SQL
Используется Oracle 10g, поэтому встроенная поддержка версионности отсутствует.

Staxдля "мне нужно вставить" должна быть "дата по"
Это очень осложнит вставку записей одним запросом.
Скорее даже одним запросом вставку сделать будет уже невозможно — ведь при задании новой записи с указанным сроком действия в этот диапазон может попасть произвольное количество ранее существующих записей.
Как я уже писал в самом начале, я склоняюсь к первому варианту — если вставляется новое значение, то это автоматически аннулирует все запланированные ранее изменения. Это простое правило, которое легко запомнить.
Но может быть есть веские аргументы в пользу второго варианта, которые я пока просто упускаю?

В первом варианте все максимально просто:
1. delete ... where date_beg > sysdate
2. merge ...
То есть первым запросом я удаляю все запланированные записи, а вторым запросом одновременно обновляю действующие записи (выставляю date_end) и добавляю новые записи (date_beg=sysdate).

Во втором варианте запрос будет только один (delete не нужен), просто в merge будет более сложное выражение для when matched, предусматривающее как обновление date_end действующих записей, так и изменение date_beg следующей запланированной записи.

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

itemvaluedate_begdate_endamount102019-01-142019-01-16amount202019-01-162019-01-20amount302019-01-20

Под sysdate я имел ввиду дату записи, то есть:
delete ... where date_beg > moment
update ... set date_end = moment
insert ... date_beg = moment

Staxвот видете, уже появился update
Почему "уже"?
update и insert были изначально.
Просто они были внутри merge, в предложениях when matched и when not matched соответственно.

Stax1. НЕ используйте null. Возьмите константу типа date'3000-01-01' - это существенно упростит жизнь.
А почему, не могли бы пояснить?
Я и сам об этом задумывался. В случае с MSSQL и вопроса бы такого не возникало, там OR так драматически влияет на запрос, что его желательно избегать при любой возможности.
Но в Oracle выражение (date_end is null or date_end > sysdate) при индексации date_end работает быстро, я не замечал, чтобы оно приводило к снижению производительности.

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

Staxя с мерже плохо дружу, можете привести пример в целях моего ликбеза
Что-то типа такого.


Этот запрос предполагает, что из таблицы предварительно удалены все ранее запланированные записи.
Если их не удалять, то в таблице d нужно получать три клона (для добавляемой записи, для действующей записи и для следующей запланированной записи) и предложение when matched будет более сложным.

andrey_anonymousВыбирайте что-то вроде SCD4 и радуйтесь жизни.
Не совсем понял этот совет.
SCD4 это ведь SCD2, только не для всех атрибутов, а для выборочных.
Ну так у меня так и есть, я буду хранить версии только токенов доступа.

Так та таблица, которую я привел в примере, и есть отдельная таблица для исторических записей.