ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вставка записи в историческую таблицу - что делать с последующими записями?
25 сообщений из 44, страница 1 из 2
  1  все
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760031
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Сейчас прорабатываю логику одной задачи не могу решить, какой подход лучше.

Есть историческая таблица данных, в которой история задается началом и концом интервала: 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 января.
Значение можно задать таким образом:
Код: plsql
1.
with new as (select 'amount' as item, 15 as value, 2019-01-15 as date_beg from dual)


Обновлять таблицы я буду 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
?

Я больше склоняюсь к первому варианту (то есть при добавлении новой исторической записи все последующие данные аннулируются). В этом варианте, как мне кажется, будет меньшая неопределенность конечного результата.
Но зато второй вариант более способствует сохранению изменений.
Не поделитесь своим мнением, как правильнее?
...
Рейтинг: 0 / 0
15.01.2019, 22:57
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760032
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Alibek B.Но зато второй вариант более способствует сохранению изменений.
Плюс второй вариант позволяет обновить данные одним merge-запросом, а для первого варианта потребуется два запроса — delete и merge.
...
Рейтинг: 0 / 0
15.01.2019, 22:59
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760034
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Alibek B.Не поделитесь своим мнением, как правильнее?

Выкинуть из таблицы поле date_end и задача становится тривиальной.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
15.01.2019, 23:04
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760035
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Dimitry SibiryakovВыкинуть из таблицы поле date_end и задача становится тривиальной.
Но тогда значительно усложнится получение значения, действующего на определенную дату.
Я предполагаю, что в этой таблице будет довольно много записей и мне бы хотелось сохранить возможность быстрой выборки действующих значений.
...
Рейтинг: 0 / 0
15.01.2019, 23:07
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760036
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Alibek B.Но тогда значительно усложнится получение значения, действующего на определенную дату.

С чего бы это?
Код: sql
1.
2.
select * from t where t.date_beg = (select max(date_beg) from t where date_beg <= 
:определённая_дата)


Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
15.01.2019, 23:13
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760037
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Хотя да.
Я по привычке зациклился на использовании периода, а использование одной даты все значительно упрощает.
Я лучше попробую оптимизировать получение действующих значений, индексами или материализацией.
...
Рейтинг: 0 / 0
15.01.2019, 23:15
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760039
EvgeniaMakarova
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
EvgeniaMakarova
Гость
https://en.wikipedia.org/wiki/Slowly_changing_dimension читайте и думайте . и вообще про slow changing dimensions разных типов читайте и будет вам счастье:-)
...
Рейтинг: 0 / 0
15.01.2019, 23:15
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760040
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Dimitry SibiryakovС чего бы это?
Я не про конкретный способ получения действующих значений (хотя я бы скорее использовал lead).
Но разве аналитика или подзапросы не будут более "тяжелыми" для сервера, чем обычная фильтрация по индексированным полям?
...
Рейтинг: 0 / 0
15.01.2019, 23:16
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760041
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
Alibek B. обычная фильтрация по индексированным полямВ каких случаях поможет индексация второго поля?
...
Рейтинг: 0 / 0
15.01.2019, 23:26
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760049
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Про индексы не знаю.
Но я сейчас сравнил два запроса на одной таблице:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE TABLE BM_SERVICE_STATUS
(
  SERVICE_STATUS_ID  NUMBER                     NOT NULL,
  SERVICE_ID         NUMBER                     NOT NULL,
  STATUS_ID          NUMBER                     NOT NULL,
  DATE_BEG           DATE                       NOT NULL,
  DATE_END           DATE,
  DESCRIPTION        VARCHAR2(255 BYTE)
);
CREATE INDEX SRV_ST_END_IDX ON BILLING.BM_SERVICE_STATUS (DATE_END);
CREATE UNIQUE INDEX SRV_ST_PK ON BILLING.BM_SERVICE_STATUS (SERVICE_STATUS_ID);
CREATE INDEX SRV_ST_SERVICE_IDX ON BILLING.BM_SERVICE_STATUS (SERVICE_ID, DATE_BEG);


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

Делаю такой запрос:
Код: plsql
1.
2.
3.
select *
from BM_SERVICE_STATUS
where DATE_BEG <= sysdate and (DATE_END is null or DATE_END > sysdate)


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

Код: plaintext
1.
2.
SELECT STATEMENT  ALL_ROWSCost: 572  Bytes: 1 548 960  Cardinality: 55 320  	
	1 TABLE ACCESS FULL TABLE BM_SERVICE_STATUS Cost: 572  Bytes: 1 548 960  Cardinality: 55 320


Делаю такой запрос:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select SS.*
from (
  select SERVICE_ID, max(DATE_BEG) as MOMENT
  from BM_SERVICE_STATUS
  where DATE_BEG <= sysdate
  group by SERVICE_ID
) G
join BM_SERVICE_STATUS SS on (SS.SERVICE_ID = G.SERVICE_ID and SS.DATE_BEG = G.MOMENT)


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

Код: plaintext
1.
2.
3.
4.
5.
6.
SELECT STATEMENT  ALL_ROWSCost: 3 184  Bytes: 2 460  Cardinality: 60  				
	5 FILTER  			
		4 HASH GROUP BY  Cost: 3 184  Bytes: 2 460  Cardinality: 60  		
			3 HASH JOIN  Cost: 2 826  Bytes: 218 640 864  Cardinality: 5 332 704  	
				1 TABLE ACCESS FULL TABLE BM_SERVICE_STATUS Cost: 570  Bytes: 6 970 951  Cardinality: 536 227  
				2 TABLE ACCESS FULL TABLE BM_SERVICE_STATUS Cost: 561  Bytes: 15 014 356  Cardinality: 536 227
...
Рейтинг: 0 / 0
15.01.2019, 23:57
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760063
Фотография кит северных морей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
кит северных морей
Участник
Alibek B.мгновенно (11 мс), возвращает примерно 50 тысяч строк.
я не знаю, как именно вы выполняли запрос, и измеряли время, поэтому на всякий случай - убедитесь, что за 11 мс вы возвращаете именно 50 тысяч, а не первые 50 из них. например, выполнив

Код: plsql
1.
2.
3.
select count(*)
from BM_SERVICE_STATUS
where DATE_BEG <= sysdate and (DATE_END is null or DATE_END > sysdate)
...
Рейтинг: 0 / 0
16.01.2019, 00:43
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760073
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Alibek B.как правильнее?
Ответьте хотя бы какой вид версионности Вы строите - техническую или бизнесовую?
Если первое, то вопрос не имеет смысла - ситуация невозможна.
Если второе - то все определяется не абстрактными соображениями, но лишь требованиями бизнес-процессов.
Лично мне не встречались реализации версионности, допускающие создание записей между существующими версиями, и я затрудняюсь таковой придумать.

При проектировании схемы обратите внимание на виды SCD. Возможно, под Ваши задачи лучше подойдет какой-то другой вид.
Если все-таки остановитесь на SCD2 или гибриде на его основе - то дополнительно рассмотрите поддержку этого вида в SQL.
В оракеле она пока пока на уровне "синтаксического сахара", но все-таки появилась.
В терадате чуть богаче, но тоже по сути "синтаксический сахар".
Однако нельзя исключать, что в будущем сделают более основательную поддержку этого дела.
Что касается идеи "убрать верхнюю границу" - идея на самом деле фиговая.
Фиговость повылезает на join с другими версионными таблицами не в срезе на момент, но за период.
Кроме того, будет сложно отличить историческую запись от актуальной, а это обычно важно.

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

По конечной дате действия актуальной записи:
1. НЕ используйте null. Возьмите константу типа date'3000-01-01' - это существенно упростит жизнь.
2. Если для конечной даты действия использовать (для типа date) дату след. версии "минус секунда" - то предикаты на версии можно писать просто between, что лучше читается, особенно в сложных запросах.
...
Рейтинг: 0 / 0
16.01.2019, 03:15
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760085
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Alibek B.Есть историческая таблица данных, в которой история задается началом и концом интервала: item, value, date_beg, date_end.История в виде EAV? - Фу.
21681882
...
Рейтинг: 0 / 0
16.01.2019, 07:47
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760089
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
andrey_anonymousписать просто betweenОбратить внимание на неуместное использование between несколько больше шансов, чем на неподходящий тип сравниваемого выражения.
...
Рейтинг: 0 / 0
16.01.2019, 08:09
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760093
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
ElicИстория в виде EAV?
Почему EAV? Только потому, что в примере столбец называется item, а не item_id?
Это таблица токенов доступа, у которых есть определенный срок действия. Если срок действия токена истек, нужно запросить новый. А в таблице хранится их история.
...
Рейтинг: 0 / 0
16.01.2019, 08:14
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760097
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
andrey_anonymousФиговость повылезает на join с другими версионными таблицами не в срезе на момент, но за период.
Кроме того, будет сложно отличить историческую запись от актуальной, а это обычно важно.
Очень точно сформулировано, спасибо.
Да, я в том числе и это имел ввиду под «усложнением».
Но зато историчность не ввиде периода, а в виде эффективной даты настолько упрощает вставку записей, что поневоле задумаешься.

andrey_anonymousПомимо вышеизложенного, существует вариация, допускающая пропуски версий (объект как бы перестал существовать на какой-то промежуток времени).
Да, это было бы не лишним, но обязательным требованием не является.
...
Рейтинг: 0 / 0
16.01.2019, 08:30
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760099
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
кит северных морейубедитесь, что за 11 мс вы возвращаете именно 50 тысяч
Я не говорю о возвращении данных, я говорю о выполнении запроса.
Если получать результаты запроса (переходом на последнюю строку), то это еще какое-то дополнительное время (несколько меньше секунды), одинаковое для обоих вариантов.
...
Рейтинг: 0 / 0
16.01.2019, 08:32
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760102
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
andrey_anonymousто дополнительно рассмотрите поддержку этого вида в SQL
Используется Oracle 10g, поэтому встроенная поддержка версионности отсутствует.
...
Рейтинг: 0 / 0
16.01.2019, 08:37
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760118
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
Alibek B.Используется Oracle 10g, поэтому встроенная поддержка версионности отсутствует.Присутствует. Но твоя задачаAlibek B.Это таблица токенов доступа, у которых есть определенный срок действияотношения к версионности не имеет.
...
Рейтинг: 0 / 0
16.01.2019, 09:33
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760135
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Alibek B.Другими словами, какая итоговая таблица должна получится после обновления:


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

для "мне нужно вставить" должна быть "дата по",
если нет, то подразумевается значение по умолчанию (date'3000-01-01')

тогда пропадет вопрос, что в результате получить

.....
stax
...
Рейтинг: 0 / 0
16.01.2019, 09:49
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760143
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
Staxдля "мне нужно вставить" должна быть "дата по"
Это очень осложнит вставку записей одним запросом.
Скорее даже одним запросом вставку сделать будет уже невозможно — ведь при задании новой записи с указанным сроком действия в этот диапазон может попасть произвольное количество ранее существующих записей.
Как я уже писал в самом начале, я склоняюсь к первому варианту — если вставляется новое значение, то это автоматически аннулирует все запланированные ранее изменения. Это простое правило, которое легко запомнить.
Но может быть есть веские аргументы в пользу второго варианта, которые я пока просто упускаю?
...
Рейтинг: 0 / 0
16.01.2019, 10:02
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760172
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Alibek B.Staxдля "мне нужно вставить" должна быть "дата по"
Это очень осложнит вставку записей одним запросом.
Скорее даже одним запросом вставку сделать будет уже невозможно — ведь при задании новой записи с указанным сроком действия в этот диапазон может попасть произвольное количество ранее существующих записей.
Как я уже писал в самом начале, я склоняюсь к первому варианту — если вставляется новое значение, то это автоматически аннулирует все запланированные ранее изменения. Это простое правило, которое легко запомнить.
Но может быть есть веские аргументы в пользу второго варианта, которые я пока просто упускаю?
вы СЕБЕ упрощаете задачу

надо смотеть как оно есть в реальной жизни, что нужно пользователю

я не знаю что такое "токены доступа"

но, по любому, правіло чуть усложнится
как будет выглядеть таблица после вставки 'amount' , 30 , 2019-01-20?

надо ж не просто вставіть, но и ...

.....
stax
...
Рейтинг: 0 / 0
16.01.2019, 11:07
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760174
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
В первом варианте все максимально просто:
1. delete ... where date_beg > sysdate
2. merge ...
То есть первым запросом я удаляю все запланированные записи, а вторым запросом одновременно обновляю действующие записи (выставляю date_end) и добавляю новые записи (date_beg=sysdate).

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

Если же новые записи вставлять не на определенную дату, а на период, то одним запросом никак не обойтись.
Вначале нужно будет удалить запланированные записи, полностью попадающие в период, а затем обновить границы на оставшихся записях, соприкасающихся с новым периодом.
...
Рейтинг: 0 / 0
16.01.2019, 11:20
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760175
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Alibek B.,

как будет выглядеть таблица после вставки 'amount' , 30 , 2019-01-20?

.....
stax
...
Рейтинг: 0 / 0
16.01.2019, 11:22
| Ответить | Цитировать | Написать  
Вставка записи в историческую таблицу - что делать с последующими записями?
    #39760178
Alibek B
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alibek B
Участник
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
...
Рейтинг: 0 / 0
16.01.2019, 11:26
| Ответить | Цитировать | Написать  
25 сообщений из 44, страница 1 из 2
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вставка записи в историческую таблицу - что делать с последующими записями?
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=87&tid=1882922]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 29ms
get topic data:
 13ms
get forum data:
 2ms
get page messages:
 73ms
get tp. blocked users:
 2ms
others: 16ms
total:  167ms
созд. / загр.: 167ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]