[ igor.vitorac @ 14.04.2014. 11:08 ] @
Pokusavam da resim problem brisanje rekorda iz jedne tabele na bazi druge (bez koriscenja kursora), pa bi mi svaki savet dobro dosao.

Evo konkretnog problema:
- u jednoj tabeli (Hierarchy) se nalaze razne kombinacije nodova jedne hierarhije pri cemu From/To kombinacije mogu imati duplikata:
ID, From, To
idh_1, A1, C1
idh_2, A1, C1
idh_3, A1, C1
idh_4, A1, C1
idh_5, B1, C1
idh_6, B1, C1
idh_7, B2, C1

- u drugoj temorary (TTable) tabeli se nalaze kombinacije (mogu imati From/To duplikata) koje treba ukloniti iz Hierarchy tabele:
ID, From, To
idt_1, A1, C1
idt_2, A1, C1
idt_3, B1, C1
idt_4, B2, C1

- neophodno je ukloniti onoliko parova From/To iz Hierarchy tabele, koliko se nalazi u temporary TTemp tabeli.
- ogranicenje je da NEPOSTOJI veza tipa foregn key ili da imaju isti primary key.


Ono sto sam trenutno nasao kao moguce resenje je da uvedem kursor za tabelu TTable i za svaki par From/To iz TTable izvrsim:
DELETE FROM Hierarchy WHERE Hierarchy.From = TTable.From AND Hierarchy.To = TTable.To LIMIT 1

Da li neko ima savet kako bi ovaj problem mogao da resim sa nekim Join/Limit/variable/$#%*& ?

Unapred hvala,
Igor
[ djoka_l @ 14.04.2014. 11:44 ] @
Kada sam poslednji put gledao MySQL help, nije postojala rank funkcija, pa onda da napišemo sopstvenu:

Code (sql):

SELECT t1.id, t1.fromid, t1.toid, COUNT(*) myrank
  FROM hierarchy t1, hierarchy t2
 WHERE t1.id >= t2.id
   AND t1.fromid = t2.fromid
   AND t1.toid = t2.toid
 GROUP BY t1.id, t1.fromid, t1.toid
 


Što se tiče TTable, potreban je samo broj ponavljanja (from, to) parova:

Code (sql):

SELECT fromid, toid, COUNT(*) cnt
FROM TTable
GROUP BY fromid, toid
 


Potrebno je izdvojiti one id iz prve tabele čiji je rank <= broju ponavljanja (from, to) iz TTable:

Code (sql):

SELECT x.id
FROM (SELECT t1.id, t1.fromid, t1.toid, COUNT(*) myrank
  FROM hierarchy t1, hierarchy t2
 WHERE t1.id >= t2.id
   AND t1.fromid = t2.fromid
   AND t1.toid = t2.toid
 GROUP BY t1.id, t1.fromid, t1.toid) x, (
SELECT fromid, toid, COUNT(*) cnt
FROM TTable
GROUP BY fromid, toid) y
WHERE x.fromid = y.fromid
AND x.toid = y.toid
AND x.myrank <= y.cnt
 


Sada je ostalo samo da napravimo i delete:

Code (sql):

DELETE FROM hierarchy WHERE id IN (
SELECT id
FROM (SELECT x.t1.id, t1.fromid, t1.toid, COUNT(*) myrank
  FROM hierarchy t1, hierarchy t2
 WHERE t1.id >= t2.id
   AND t1.fromid = t2.fromid
   AND t1.toid = t2.toid
 GROUP BY t1.id, t1.fromid, t1.toid) x, (
SELECT fromid, toid, COUNT(*) cnt
FROM TTable
GROUP BY fromid, toid) y
WHERE x.fromid = y.fromid
AND x.toid = y.toid
AND x.myrank <= y.cnt)
 
[ bogdan.kecman @ 14.04.2014. 12:17 ] @
@djoka_l, odlicno i postupno no delete where id in (select..) je ok ako onaj select unutra ima par rezultata, za malo vise je mysql prilicno spor... dakle sql je dobar samo je mysql glup da ga izvrsi brzo pa takav upit moze da traje i danima (zavisno od podataka i od verzije mysql-a, neke su gluplje od nekih drugih)

tako da umesto tog in bolje nesto tipa

Code (sql):

DELETE hierarchy.* FROM hierarchy JOIN
(SELECT x.id
FROM (SELECT t1.id, t1.fromid, t1.toid, COUNT(*) myrank
  FROM hierarchy t1, hierarchy t2
 WHERE t1.id >= t2.id
   AND t1.fromid = t2.fromid
   AND t1.toid = t2.toid
 GROUP BY t1.id, t1.fromid, t1.toid) x, (
SELECT fromid, toid, COUNT(*) cnt
FROM TTable
GROUP BY fromid, toid) y
WHERE x.fromid = y.fromid
AND x.toid = y.toid
AND x.myrank <= y.cnt) ZZ
USING (id);
 


dakle samo umesto IN stavis join .. e sad, deluje mi da i ovo unutra moze da se optimizuje ali sam malo u frci sa vremenom, voleo bih da vidim explain extended + visual explain iz workbench-a ovoga nad realnim podacima
[ djoka_l @ 14.04.2014. 12:24 ] @
Slažem se s tobom Bogdane, ovaj sam upit pisao na Oracle bazi, a nisam bio siguran kako bih bez IN napisao na MySQL.
Kao što si i rekao, bez test podataka i create table komandi sve je samo nagađanje, samo sam za prvi podupit napravio tabelu i testirao upit, a sve ostalo sam pisao iz glave.
Recimo, pretpostavio sam da je ID unique, inače upit ne valja, ali sam na brzinu improvizovao...

Inače IN ne radi ni na jednoj bazi naročito brzo, ne treba kriviti MySQL.
[ bogdan.kecman @ 14.04.2014. 12:29 ] @
:D vidi se da upit dolazi sa neke od "masivnih" baza ... oracle ovo ume
da sazvace (mislim da bi i pgsql umeo da sazvace to kako treba), na
zalost mysql, jos ako je neki 5.0 ili 5.1 sa ovim ozbiljno razbija
glavu, 5.6 je nesto inteligentniji... e sad, 5.7 bi mozda, zavisno od
toga na sta lice podaci, uspeo da istrci ovo brzo, mada se ne bi kladio
u to :(

e sad, bez obzira koliko rdbms ume kvalitetno da optimizuje exec path za
neki upit, uvek je korisno da ti izoptimizujes upit sa svoje strane
koliko mozes tako da bi i za oracle join bio bolji od in :)
[ igor.vitorac @ 15.04.2014. 14:32 ] @

Hvala momci!
Ne znam kako ovo cudo od SQL radi, ali izgleda da radi. :-)
Testiracu jos malo detaljnije da vidim kako se ponasa.

Pozdrav,
Igor