[Firebird] Odbudowanie kluczy obcych z korekta integralnosci danych
Mariusz - 06-10-2007 00:01
[Firebird] Odbudowanie kluczy obcych z korekta integralnosci danych
Witam,
Baza danych oparta na Firebird 2.0.
Na pewnym etapie pracy systemu zaszła konieczność usunięcia z bazy danych
WSZYSTKICH zdefiniowanych kluczy obcych a integralność danych została
przerzucona na aplikację.
Oczywiście jak to bywa z aplikacjami po pewnym czasie niektóre dane się
"rozjechały" tzn przy usunięciu z jakiejś tabeli rekordy w powiązanych
tabelach nie zostały wy-null-owane ewentualnie usunięte.
Przyjąłem następujący schemat porządkowania:
1. Utworzenie tabel na backup danych usuwanych/null-owanych danych wedlug
wzorca "backup_NazwaTabeli" z dodatkowa kolumna NumerFK
2. Do tabel backup przerzucilem dane zapytaniem:
insert into backup_NazwaTabeli(lista_pol, NumerFK)
select lista_pol, NumerFK
from NazwaTabeli A left outer join TabelaPowiazana B on
(A.PolePowiazania=B.PolePowiazania)
where B.PolePowiazania is null
Bez wnikania w szczegoly takie zapytanie jest chyba najszybsze. Bylo tez
warunkiem not in (select PolePowiazania from TabelaPowiazana) ale było to
koszmarnie wolne.
3. TU MÓJ PROBLEM - musze z-update-owac tą tabelę NazwaTabeli aby w tych
polach gdzie jest jakaś wartość inna niż istniejąca w TabeliPowiazana
ustawić na null
Niestety w firebirdzie nie mozna (chyba?) w UPDATE uzywac wiecej niz jednej
tabeli do skonstruowania instrukcji upadatujacej tylko dla kazdego rekordu
wykonuje sie podzapytanie więc musiałem niestety zrobić:
update NazwaTabeli
set PolePowiazania=null
where PolePowiazania not in (select PolePowiazania from TabelaPowiazana)
Niestety takie rozwiazanie przy NazwaTabeli i TabelaPowiazana liczacych
sobie po klikadziesiat tysiecy rekordow wykonuje się koszmarnie wolno
(przerwalem po 4 godzinach)
(sprzet: Athlon64/4GB RAM/FB2.0 skompilowany dla 64 bit)
Może macie jakieś inne pomysły jak skonstruowac update-a tak aby wykonywal
sie tak szybko jak ten select insertujący z punktu 2?
Pozdrawiam
Mariusz
Szymon - 06-10-2007 00:01
Mariusz pisze:
> Witam,
> Baza danych oparta na Firebird 2.0.
> Na pewnym etapie pracy systemu zaszła konieczność usunięcia z bazy danych
> WSZYSTKICH zdefiniowanych kluczy obcych a integralność danych została
> przerzucona na aplikację.
wręcz zajebiste podejście, gratuluję. Pewnie jeszcze transakcje
wywaliliście, bo też przeszkadzały?
> Oczywiście jak to bywa z aplikacjami po pewnym czasie niektóre dane się
> "rozjechały" tzn przy usunięciu z jakiejś tabeli rekordy w powiązanych
> tabelach nie zostały wy-null-owane ewentualnie usunięte.
no właśnie
> Przyjąłem następujący schemat porządkowania:
> 1. Utworzenie tabel na backup danych usuwanych/null-owanych danych wedlug
> wzorca "backup_NazwaTabeli" z dodatkowa kolumna NumerFK
> 2. Do tabel backup przerzucilem dane zapytaniem:
> insert into backup_NazwaTabeli(lista_pol, NumerFK)
> select lista_pol, NumerFK
> from NazwaTabeli A left outer join TabelaPowiazana B on
> (A.PolePowiazania=B.PolePowiazania)
> where B.PolePowiazania is null
> Bez wnikania w szczegoly takie zapytanie jest chyba najszybsze. Bylo tez
> warunkiem not in (select PolePowiazania from TabelaPowiazana) ale było to
> koszmarnie wolne.
> 3. TU MÓJ PROBLEM - musze z-update-owac tą tabelę NazwaTabeli aby w tych
> polach gdzie jest jakaś wartość inna niż istniejąca w TabeliPowiazana
> ustawić na null
> Niestety w firebirdzie nie mozna (chyba?) w UPDATE uzywac wiecej niz jednej
> tabeli do skonstruowania instrukcji upadatujacej tylko dla kazdego rekordu
> wykonuje sie podzapytanie więc musiałem niestety zrobić:
> update NazwaTabeli
> set PolePowiazania=null
> where PolePowiazania not in (select PolePowiazania from TabelaPowiazana)
> Niestety takie rozwiazanie przy NazwaTabeli i TabelaPowiazana liczacych
> sobie po klikadziesiat tysiecy rekordow wykonuje się koszmarnie wolno
> (przerwalem po 4 godzinach)
> (sprzet: Athlon64/4GB RAM/FB2.0 skompilowany dla 64 bit)
>
> Może macie jakieś inne pomysły jak skonstruowac update-a tak aby wykonywal
> sie tak szybko jak ten select insertujący z punktu 2?
>
wrzuć te klucze obce i niech baza to robi, takie obejścia zawsze kończą
się bazą w której dane nadają się tylko do skasowania.
Mariusz - 06-10-2007 00:01
Użytkownik "Szymon" <alpha@skynet.org.pl_WITHOUT> napisał w wiadomości
news:fe548p$38e$1@inews.gazeta.pl...
> wręcz zajebiste podejście, gratuluję. Pewnie jeszcze transakcje
> wywaliliście, bo też przeszkadzały?
Transakcje zostaly ;)
Chetnie bym jaja urwał osobie która to zrobiła ale już jej nie pracuje nad
tym systemem a ja pierwsze co pomyślałem to odtworzyć wszystko do stanu w
jakim zawsze powinno być...
> wrzuć te klucze obce i niech baza to robi, takie obejścia zawsze kończą
> się bazą w której dane nadają się tylko do skasowania.
Niestety wrzucic nie moge do nieuporządkowanej bazy:
violation of FOREIGN KEY constraint "".
violation of FOREIGN KEY constraint "PK_1" on table "TABELA".
Foreign key reference target does not exist.
Dlatego najpierw muszę wy-null-ować wszystkie dane które nie mają
odpowiedników w tabelach powiązanych.
--
Pozdrawiam
Mariusz
=?ISO-8859-2?Q?Pawe=B3_Matejski?= - 06-10-2007 00:01
Mariusz wrote:
> Witam,
> Baza danych oparta na Firebird 2.0.
> Na pewnym etapie pracy systemu zaszła konieczność usunięcia z bazy danych
> WSZYSTKICH zdefiniowanych kluczy obcych a integralność danych została
> przerzucona na aplikację.
> Oczywiście jak to bywa z aplikacjami po pewnym czasie niektóre dane się
> "rozjechały" tzn przy usunięciu z jakiejś tabeli rekordy w powiązanych
> tabelach nie zostały wy-null-owane ewentualnie usunięte.
> Przyjąłem następujący schemat porządkowania:
> 1. Utworzenie tabel na backup danych usuwanych/null-owanych danych wedlug
> wzorca "backup_NazwaTabeli" z dodatkowa kolumna NumerFK
> 2. Do tabel backup przerzucilem dane zapytaniem:
> insert into backup_NazwaTabeli(lista_pol, NumerFK)
> select lista_pol, NumerFK
> from NazwaTabeli A left outer join TabelaPowiazana B on
> (A.PolePowiazania=B.PolePowiazania)
> where B.PolePowiazania is null
> Bez wnikania w szczegoly takie zapytanie jest chyba najszybsze. Bylo tez
> warunkiem not in (select PolePowiazania from TabelaPowiazana) ale było to
> koszmarnie wolne.
> 3. TU MÓJ PROBLEM - musze z-update-owac tą tabelę NazwaTabeli aby w tych
> polach gdzie jest jakaś wartość inna niż istniejąca w TabeliPowiazana
> ustawić na null
> Niestety w firebirdzie nie mozna (chyba?) w UPDATE uzywac wiecej niz jednej
> tabeli do skonstruowania instrukcji upadatujacej tylko dla kazdego rekordu
> wykonuje sie podzapytanie więc musiałem niestety zrobić:
> update NazwaTabeli
> set PolePowiazania=null
> where PolePowiazania not in (select PolePowiazania from TabelaPowiazana)
> Niestety takie rozwiazanie przy NazwaTabeli i TabelaPowiazana liczacych
> sobie po klikadziesiat tysiecy rekordow wykonuje się koszmarnie wolno
> (przerwalem po 4 godzinach)
> (sprzet: Athlon64/4GB RAM/FB2.0 skompilowany dla 64 bit)
> Może macie jakieś inne pomysły jak skonstruowac update-a tak aby wykonywal
> sie tak szybko jak ten select insertujący z punktu 2?
Select już zrobiłeś, do tego jego wynik zachowałeś, więc z niego skorzystaj.
W podzapytaniu użyj backup_NazwaTabeli. Przecież tam masz tylko te rekordy,
które mają być null'owane, więc i operator zmieni się na in, co powinno być szybsze.
A jeśli ta tabela ma jakiś PK, to lepiej powiązać po PK, bo zakładam, że
masz na nim index (jak nie to załóż).
--
P.M.
Mariusz - 06-10-2007 00:01
Użytkownik "Paweł Matejski" <madej@spam.madej.pl.eu.org> napisał w
wiadomości news:fe57om$jam$1@inews.gazeta.pl...
> Select już zrobiłeś, do tego jego wynik zachowałeś, więc z niego
> skorzystaj.
> W podzapytaniu użyj backup_NazwaTabeli. Przecież tam masz tylko te
> rekordy,
> które mają być null'owane, więc i operator zmieni się na in, co powinno
> być szybsze.
> A jeśli ta tabela ma jakiś PK, to lepiej powiązać po PK, bo zakładam, że
> masz na nim index (jak nie to załóż).
Tak tez probowalem ale wbrew pozorom wcale to nie przyspieszylo (wrecz
spowolnilo w przypadku tabel gdzie wszystko bylo ok lub tylko kilka rekordow
bylo nieprawidlowych)
Chyba bede musial na podstawie ilosci rekordow w tabeli backup bede musial
podejmowac decyzje czy uzyc zapisu "in" lub "not in"
przebudowane na not in wyglada to mniejwiecej tak:
update NazwaTabeli
set PolePowiazania=null
where PolePowiazania in
(select distinct A.PolePowiazania from backup_NazwaTabeli A where
A.NumerFK=const_NumerFK and A.PolePowiazania is not null)
dla "not in" wygladalo to tak:
update NazwaTabeli
set PolePowiazania=null
where PolePowiazania not in (select PolePowiazania from TabelaPowiazana)
Troche mnie denerwują takie ograniczenia firebirda ale jak sie nie ma co sie
lubi to narzeka się na to co się ma;)
--
Mariusz Rowiński
wloochacz - 06-10-2007 00:01
>> Select już zrobiłeś, do tego jego wynik zachowałeś, więc z niego
>> skorzystaj.
>> W podzapytaniu użyj backup_NazwaTabeli. Przecież tam masz tylko te
>> rekordy,
>> które mają być null'owane, więc i operator zmieni się na in, co powinno
>> być szybsze.
>> A jeśli ta tabela ma jakiś PK, to lepiej powiązać po PK, bo zakładam, że
>> masz na nim index (jak nie to załóż).
> Tak tez probowalem ale wbrew pozorom wcale to nie przyspieszylo (wrecz
> spowolnilo w przypadku tabel gdzie wszystko bylo ok lub tylko kilka rekordow
> bylo nieprawidlowych)
> Chyba bede musial na podstawie ilosci rekordow w tabeli backup bede musial
> podejmowac decyzje czy uzyc zapisu "in" lub "not in"
Nie używał podzapytań - użyj złączeń, ale tylko w powiązaniu z procedurą
- będzie szybciej.
Skoro tabelka ma tożsamą strukturę i interesują cię tylko dane
spełniające ograniczenia FK, to inner join jest najszybszym rozwiązaniem
- oczywiście trzeba założyć stosowne indeksy; najlepiej jako primary key.
> przebudowane na not in wyglada to mniejwiecej tak:
> update NazwaTabeli
> set PolePowiazania=null
> where PolePowiazania in
> (select distinct A.PolePowiazania from backup_NazwaTabeli A where
> A.NumerFK=const_NumerFK and A.PolePowiazania is not null)
>
> dla "not in" wygladalo to tak:
> update NazwaTabeli
> set PolePowiazania=null
> where PolePowiazania not in (select PolePowiazania from TabelaPowiazana)
Ja bym napisał to każdej tabelki/zestawu tabel procedurę.
Jeśli tego jest więcej (np. kilkaset) napisałbym program importujący lub
generujący stosowne procedury na serwerze.
> Troche mnie denerwują takie ograniczenia firebirda ale jak sie nie ma co sie
> lubi to narzeka się na to co się ma;)
Tupet to ty kolego masz, oj masz... Pisać tu o "ograniczeniach"...
Acha i jeszcze jedno; zatrudnijecie lub zamówcie konsultacje o osoby,
która ma jakieś pojęcie o Firebirdzie. Będzie lepiej i taniej.
--
wloochacz
Mariusz - 06-10-2007 00:01
Użytkownik "wloochacz" <wl@no.spam> napisał w wiadomości
news:fe5e9j$jp7$1@nemesis.news.tpi.pl...
> Nie używał podzapytań - użyj złączeń, ale tylko w powiązaniu z procedurą -
> będzie szybciej.
> Skoro tabelka ma tożsamą strukturę i interesują cię tylko dane spełniające
> ograniczenia FK, to inner join jest najszybszym rozwiązaniem - oczywiście
> trzeba założyć stosowne indeksy; najlepiej jako primary key.
Właśnie chciałbym użyć złączenia ale nie bardzo wiem jak w update to zrobić.
W innych RDBMS-ach z którymi miałem do czynienia można było w update podać
listę tabel ze złączeniami lub dodatkową klauzulę from... i w where można
było do tego ograniczyć przelatywnie przez wszystkie wiersze tabeli
update-owanej.
Byłbym bardzo wdzięczny za jakiś przykład jak z procedurą to wykonać bo nie
bardzo rozumiem...
> Ja bym napisał to każdej tabelki/zestawu tabel procedurę.
> Jeśli tego jest więcej (np. kilkaset) napisałbym program importujący lub
> generujący stosowne procedury na serwerze.
Oczywiście w taki sposób to rozwiązałem - Zrobiłem kilka procedur:
1. Tworząca tabelę backup_NazwaTabeli po podaniu jako parametr nazwy tabeli
"NazwaTabeli" na podstawie tabel systemowych odczytuje nazwy i typy pol i
execute statementem tworzę kopię.
2. Procedura z parametrami NazwaTabeli, KluczZrodlowy,
NazwaTabeliPowiazanej, KluczZPowiazanej która kopiuje (szybką metodą)
rekordy które nie mają odpowiedników w TabeliPowiazanej.
3. Ta wolna Procedura Updateująca te rekordy
4. Procedura generująca DDL tworzacy FK i wykonująca go (statement).
Na poczatku to wszystko bylo w jednej procedurze ale Firebird niestety jest
na tyle glupi ze nie widzial kroków poprzednich (nie mozna wewnatrz
procedury commita zrobic a np utworzona tabela backup z execute statement
byla niedostepna dla instrukcji insert)
> Tupet to ty kolego masz, oj masz... Pisać tu o "ograniczeniach"...
> Acha i jeszcze jedno; zatrudnijecie lub zamówcie konsultacje o osoby,
> która ma jakieś pojęcie o Firebirdzie. Będzie lepiej i taniej.
Na jedną drobną operację nie potrzeba mi jakichś wielkich konsultacji -
myślałem że w takiej drobnostce grupa mi pomoże;)
Przegryzlem sie przez wiele RDBMS-ow i przyszlo mi teraz pracowac z
Firebirdem więc uważam że jestem upoważniony do wyrażenia swojej opinii;)
Polubiłem Firebirda za jego prostotę i brak zbędnych bajerów ale czasem tych
bajerów mi brakuje;)
--
Pozdrawiam
Mariusz
zanotowane.pldoc.pisz.plpdf.pisz.pleffulla.pev.pl
|
[MSSQL2000] Problem z =?ISO-8859-2?Q?tabel=B1/indeksem/zapytanie?==?ISO-8859-2?Q?m_czy_b=B3=B1d_w_bazie_danych=2E=2E=2E?=
=?iso-8859-2?Q?=5BMySQL=5D_Wy=B6wietlenie_wszystkich_rekordow _zawierajacy?==?iso-8859-2?Q?ch_duplikat_a__moze_inna_struktura_bazy_danych ?=
Konwesja znaków w dump'ie bazy danych - ISO -> utf-8 -> ISO -> utf-8
[laik]Jak =?ISO-8859-2?Q?stworzy=E6/zaczac_tworzyc__ma=B3=B1?==?ISO-8859-2?Q?__baz=EA_danych_na_potrzeby_www=3F?=
[mysql] przenoszenie danych =?ISO-8859-2?Q?mi=EAdzy_tabelami_?==?ISO-8859-2?Q?w_r=F3=BFnych_bazach?=
Ksiazka - "Podstawowy =?ISO-8859-2?Q?wyk=B3ad_z_system=F3w_?==?ISO-8859-2?Q?baz_danych=22?=
Zrywanie =?ISO-8859-2?Q?po=B3aczen_z_baza_danych_-_pos?==?ISO-8859-2?Q?tgresql_=3C-=3E_odbc?=
Połączenie bazy danych z wykonaniem polaczenia telefonicznego
[mssql] insert do tabeli na podstawie danych z innej tabeli
[oracle] Baza danych do kursy Introduction to Oracle9i:PL/SQL ? Skąd ją pobrać ?
zanotowane.pldoc.pisz.plpdf.pisz.ploefg.opx.pl
Cytat
Decede mihi sole - nie zasłaniaj mi słonca.
Gdy kogoś kochasz, jesteś jak stworzyciel świata - na cokolwiek spojrzysz, nabiera to kształtu, wypełnia się barwą, światłem. Powietrze przytula się do ciebie, choćby był mróz, a ty masz w sobie tyle radości, że musisz ją rozdawać wokoło, bo się w tobie nie mieści
Hoc fac - tak czyń.
A tergo - od tyłu; z tyłu.
I czarne włosy posiwieją. Safona |
|
