pondelok, 30 jún 2014 12:48 Written by 2880 times
Rate this item
(1 Vote)

SQL - Optimalizované mazanie duplicitných riadkov v tabuľke - možnosti.

Mnohokrát nastane pre databázového programátora situácia, keď potrebuje vymazať z tabuľky určité skupiny záznamov. Záznamy samozrejme musia spĺňať nejakú spoločnú podmienku, na základe ktorej je možné potom duplicitné záznamy z tabuľky vymazať. Rozhodol som sa zozbierať a uverejniť pár príkladov spoločne vykonávajúcich rovnakú vec, popísať ich a ponúknuť čo najoptimálnejší príklad.

 

V prípade, že počet duplicitných riadkov je percentuálne vysoký, napr. cez 10%,20%,30% a viac a počet záznamov v tabuľke je vysoký, zodpovedajúci rádovo MB až desiatkam alebo stovkám MB, v tom prípade odporučím iný postup optimalizovaného mazania riadkov. O tom niekedy na budúce.

Príklad som skúšal na databázovom prostredí ORACLE 11g na schéme HR na tabuľke EMP - employee.

Vo všetkých prípadoch je použitý pseudostĺpec ROWID, ktorý interne presne popisuje adresu a zároveň pozíciu riadku v tabuľke v databáze. Pomocou tohto stľpca sú nájdené riadky v tabuľke najrýchlejším spôsobom. Na každom príklade som spustil "explain plan" na analyzu SQL príkazu na porovnanie s ostatnými. Podmienka mazania duplicitných záznamov je spoločné číslo oddelenia (departmentu). To znamená, že chcem mať v tabuľke len jedného ľubovoľného zamestanca z jedného oddelenia. Všetci ostatní budu z tabuľky vymazaní.

 

-- Cost:8  Bytes:259  Cardinality:7
DELETE
FROM EMP T1
WHERE T1.ROWID > ( SELECT MIN(T2.ROWID) FROM EMP T2 WHERE T1.DEPTNO = T2.DEPTNO);


Tento príkaz pracuje takto: V podstate ide o tzv. združený dotaz (query). Predstavte si sekvenčné prechádzanie riadkov v tabuľke EMP. Prvý riadok, máme prečítané číslo oddelenia, v poddotaze v klauzule "WHERE" v podmienke si vyfiltrujeme všetkých zamestnancov, ktorí spadajú pod to isté oddelenie. Takže budú vymazané všetky riadky zamestnancov toho istého oddelenia, ktoré majú hodotu ROWID väčšiu ako hodnota ROWID prvého - aktuálneho riadku. Využijeme fakt, že si ponecháme jeden riadok konkrétneho zamestnanca, ktorý má z množiny zamestnancov jedného oddelenia najmenšiu hodnotu v stĺpci ROWID.
Na to, aby tento dotaz mohol byť vykonaný týmto spôsobom, musí prebehnúť na tabuľke dvakrát tzv. "FULL SCAN" - prečítanie všetkých riadkov, čo samozrejme predlžuje čas vykonania dotazu a nárok na pam仝, ktorú si operácia vyžiada.

 

Ďalšie možnosti vymazania duplicitných riadkov:

-- Cost:7  Bytes:120  Cardinality:5
DELETE
FROM EMP T1
WHERE EXISTS (SELECT 'X' FROM EMP T2 WHERE T1.DEPTNO = T2.DEPTNO AND T2.ROWID > T1.ROWID);
-- Cost:8  Bytes:2304  Cardinality:96
DELETE
FROM EMP T1
WHERE ROWID NOT IN (SELECT MIN(T2.ROWID) FROM EMP T2 GROUP BY T2.DEPTNO);
alebo
-- Cost:8  Bytes:2304  Cardinality:96
DELETE
FROM EMP T1
WHERE T1.ROWID NOT IN (SELECT MIN(T2.ROWID) FROM EMP T2 GROUP BY T2.DEPTNO HAVING COUNT(*)>1);
-- Cost:291  Bytes:12  Cardinality:1
DELETE
FROM EMP T1
WHERE T1.ROWID IN ( SELECT T2.ROWID FROM EMP T2 WHERE T2.DEPTNO=T1.DEPTNO
                    MINUS
                    SELECT T3.ROWID FROM EMP T3 WHERE T3.DEPTNO=T1.DEPTNO AND ROWNUM=1
                  );
-- Cost:6  Bytes:24  Cardinality:1
DELETE
FROM EMP T1
WHERE T1.ROWID IN (SELECT RN FROM (SELECT ROWID RN, DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY ROWID) ID FROM EMP T2) WHERE ID > 1);

 

Nebudem vysvetľovať podrobne všetky príkazy, zameriam sa len na posledný, najoptimálnejší, ktorý by som použil ja.
Momentálne pre daný stav dát v tabuľke EMP ide o najoptimálnejší príkaz vymazania duplicitných riadkov. Nie preto, že má najnižšie hodnoty v "explain plane" (čo samozrejme tiež prispieva k rozhodnutiu), ale preto, že na tabuľke prebehne len jeden tzv. "FULL SCAN", potom sa vybrané hodnoty uložia do "BUFFER CACHE" a pracuje sa veľmi rýchlo už len s nimi. V príkaze je použitá analytická funkcia "DENSE_RANK()". Analytické funkcie sú vysoko optimalizované a odporúčam si ich naštudovať a naučiť sa aspoň základnú prácu s nimi.
Vysvetlenie spracovania dotazu:
1. Najprv prebehne ako som písal len jeden "FULL SCAN", prečítanie potrebných hodnôt zo všetkých riadkov z tabuľky.
2. Riadky sa potom zotriedia podľa čísla oddelenia "DEPTNO" a podľa ROWID v rámci oddelenia, zároveň sa očíslujú riadky funkciou "DENSE_RANK()", ktorá očísluje zotriedené riadky vždy od 1 hore pre každé oddelenie zvlášť pod aliasom stĺpca "ID".
3. V poddotaze v klauzule WHERE sa vyberú len záznamy pre vymazanie, t.j. tie, ktoré majú väčšie ROWID ako má riadok s číslom ID=1.
4. Nakoniec prebehne samotné vymazanie riadkov s vybraným "ROWID", ktoré majú ID>1.

Dúfam, že tento malý postreh pomôže niekomu zavádzať trochu viac optimalizácie do svojich zdrojových SQL kódov.
S pozdravom

Last modified on štvrtok, 03 júl 2014 09:41
Alojz Benďák

Autor je administrátor webu a venuje sa programovaniu takmer 23 rokov.

  • prvý kontakt s počítačmi na strednej škole - PMD 1,2,3, PP06, Atari, Sinclair (programovanie v jazyku: strojový kód, basic a pascal):  2 roky
  • na VŠ - jazyky Turbo pascal a Turbo C: 1rok
  • programovanie databázového ekonomického širokoškálneho software v Delphi a C++ Borland (databázy Paradox): 8 rokov v Codex s.r.o. Nitra
  • programovanie webových aplikácií na databázach MySQL a MS SQL server: 1 roky na živnosť (firmy: Hermes Nitra, Schindler v Bratislave)
  • pracoval ako DBA v Homecredit Brno a vo VÚB Bratislava ako vyvojár + optimalizácie v SQL a PL/SQL na Oracle 11g (firma Accenture)
  • programoval programy pre súkromné firmy pre HW na skenovanie povrchov nádrží pre skladovanie tekutých palív (pristroj Leica)
  • momentálne pracuje ako DBA pod Oracle 11g a ako vyvojár vnútropodnikových databázových aplikacií v PHP, SQL, C++, C# a interface v PL/SQL: 11 rokov
  • ďalej programuje s HTML, CSS, Ajax, Javascript, VBA, MS visual C++ a C# malé podporné aplikácie
  • certifikát SQL expert for Oracle 11g   
  • spolupracuje a aktívne učí pre počítačové firmy: Lapis, IVIT v Nitre