Sortowanie po tej samej dacie w kontekście wielu tabel

Witajcie,

Kiedyś znalazłem fajny art na ten temat ale oczywiście nie mogę znaleźć ani przypomnieć sobie o co odpytać googla. Mianowicie mam kilka tabel z polem typu DATE nazwijmy je CREATION_DATE. Pola uzupełniają się same na triggerze before insert.

No i teraz mam raport który w dużym uproszeniu wygląda ta:

select s.* from (
select creation_date, nazwa from tab1
union all
select creation_date, nazwa from tab2
union all
...
) s
order by 1

Sęk w tym, że w creation_date w kilku tabelach mam ten sam czas. No i kiedyś czytałem, że przy tworzeniu tabeli (create table ...) można dodać jakąś klauzulę dzięki czemu każda tabela ma dodatkową kolumnę z unikalną datą i da się w ten sposób ogarnąć, do której tabeli insert poszedł w pierwszej kolejności.

Podpowiecie coś?

Hmm, nie kojarzę mechanizmu z klauzulą dla tabeli. Można by się ratować ORA_ROWSCN z tym, że to zawiera sekwencję modyfikacji bloku, więc update będzie zaburzało tę wartość, można by próbować triggerem ogarnąć zmiany, ale i tak bardzo śliska sprawa.

create table foo (
  CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
  id number
);

create table bar (
  CREATED_AT TIMESTAMP DEFAULT SYSTIMESTAMP,
  id number
);


insert all 
 when 1=1 then into bar (id) 
 when 1=1 then into foo (id) 
  select level lvl from dual connect by level<=5;
  
select * from (
  select SCN_TO_TIMESTAMP(ORA_ROWSCN) scn_ts, ora_rowscn, t.*,'FOO' tbl_name From foo t
  union all 
  select SCN_TO_TIMESTAMP(ORA_ROWSCN) scn_ts, ora_rowscn, t.*,'BAR' tbl_name From bar t
) order by ora_rowscn;

rollback;


insert all 
 when 1=1 then into foo(id) 
 when 1=1 then into bar (id) 
  select level lvl from dual connect by level<=5;
  
  
select * from (
  select SCN_TO_TIMESTAMP(ORA_ROWSCN) scn_ts, ora_rowscn, t.*,'FOO' tbl_name From foo t
  union all 
  select SCN_TO_TIMESTAMP(ORA_ROWSCN) scn_ts, ora_rowscn, t.*,'BAR' tbl_name From bar t
) order by ora_rowscn;  

Co powiesz na wspólną sekwencję? (Pomijając kwestie tuningowe przy OLTP i wielu sesjach wstawiających).

create sequence foo_common_seq start with 1;

create table foo2 (
  created_at timestamp default systimestamp,
  id number,
  common_seq number default foo_common_seq.nextval
);

create table bar2 (
  created_at timestamp default systimestamp,
  id number,
  common_seq number default foo_common_seq.nextval
);



 insert into foo2 (id) 
  select level lvl from dual connect by level<=5;
  
insert into bar2 (id) 
  select level lvl from dual connect by level<=5;

A jaki jest w ogóle problem z tym sortowaniem? Jakiego wyniku sortowania oczekujesz, skoro dwie daty są równe? Albo - czego oczekuje odbiorca raportu, gdy najmniejsza jednostka czasu tu to sekunda i bez zmiany schematu nic więcej nie wyciśniesz?

ORA_ROWSCN może mieć sens, gdy pomyśli się o tym na etapie tworzenia tabeli, bo można je wtedy ustawić na poziomie rekordu, a nie bloku. Ale to jest raczej do użycia w optymistycznym blokowaniu, gdy nie chcemy dodawać dodatkowych kolumn.

chodzi mi o to, że jest pakiet, który robi insert do tabeliA, a potem do tabeli B natomiast powstał drugi pakiet, który robi to nieco inaczej i inserty mogą pójść w innej kolejności najpierw tabelaB, a potem tabelaA.

Na raporcie muszę posortować wiele unionów z wielu tabel po dacie utworzenia rekordu, a w przypadku insertu z pakietu time jest ten sam i teorytycznie rowdepnencies załatwia mi wszystko ale w praktyce jeśli inserty do obu tabel idą w tej samej transakcji i sekundzie to ora_rowscn jest ten sam...

a potem na raporcie to wygląda w ten sposób, że najpierw jest przesunięcie towaru między magazynami, a potem przyjęcie na komorze przyjęć co jest bzdurą ale ponieważ creation time w obu tabelach jest ten sam nie mam jak tego inaczej posortować ... tak wiem konieczne byłoby przerobienie struktury bazy ale to za duży koszt (tym bardziej, że problem leży po stronie raportu) ... mógłbym dodawać do poszczególnych tabel dodatkową flagę, który jest pierwszy ale to jest też dość dynamiczne i w zależności od obsługi danego produktu na magazynie może być zmienne (np najpierw dział reklamacji lub magazyn specjalny itd.)

No cóż, czyli próbujesz w sposób techniczny rozwiązać problem biznesowy. A te dokumenty magazynowe nie mają jakiegoś przepływu, który wyznaczałby ich kolejność występowania?
Można by też pomyśleć o dodatkowej tabeli, która zawierała by kolejność sortowania i wtedy można by jej użyć jako dodatkowego elementu sortującego, gdy daty są takie same.

Np.
DOKUMENTY_SORTOWANIE (typ_dokumentu, kolejnosc, grupa_produktow /opcjonalna/)

W takiej tabelce wstawiasz domyślną kolejność sortowania, zgodną z kolejnością obsługi w magazynie. Pole grupa_produktow zostawiasz puste. W raporcie robisz joina po typie dokumentu do tej tabelki i ORDER BY CREATION_DATE, KOLEJNOSC.

Jeśli grupa produktów czy też produkt wymaga niestandardowego sortowania, to wtedy w tabelce wstawiasz osobny zestaw rekordów przypisany do tej grupy. Wtedy podczas sortowania ten zestaw ma pierwszeństwo przed domyślnym.

Taka trochę proteza, ale jeśli poszczególne operacje na dokumentach nie mają nigdzie zarejestrowanej kolejności wykonywania, to chyba jest to jedyna sensowna opcja.

Jest dokładnie tak jak mówisz ... system rozrósł się niekontrolowanie i "flow" obiegu dokumentów/towaru zostało zachwiane i rozjechały się raporty obiegowe. Finalnie na ten moment na sztywno w zapytaniu dodałem "sztuczną" kolumnę z priorytetem poszczególnych tabel i jakoś to działa natomiast tak jak wspomniałeś jest to proteza i zastanawiałem się czy da się to jakoś technicznie rozwiazać.

@woolfik: wydaje mi się, że technicznie da się zrobić "obejście problemu", co prawda nie wiem czy ma ono sens w kontekście obiegu dokumentów/towaru (nie znam domeny), to już musisz sam ocenić.

1. Robisz sekwencję MY_INSERT_SEQ z wartością startową MAX po ilości wierzy w tabelach dotkniętych problemem + luka np. 2M wierszy (jeśli na tę operację będziesz miał downtime, to nie trzeba tej luki)
2. Problematyczne tabele rozszerzasz o INSERT_SEQ
3. Dodajesz na tabelach trigger na insert i INSERT_SEQ ustawiasz n a podstawie wartości z sekwencji (te są w Oracle obsługiwane w autonomicznych transakcjach, więc będą generowały kolejne numery dla wierszy nawet w ramach "transakcji w trakcie")
4. Dla starych danych jednorazowo przypisujesz wartości z zakresu 1...MAX po ilości wierszy w tabelach przypisujesz wartości określające "dobry porządek sortowania"
5. W raporcie sortujesz po timestamp + INSERT_SEQ