Brzi upit za provjeru postojanja retka u tablici

Sunday, 21.11.2010 – msutic

Evo i mene sa blog postom nakon dugo vremena. Ovaj tjedan sam po prvi puta upoznao Dejana (u živo) koji se našao u Zagrebu zbog naprednog Oracle tečaja. Moram reći da sam uživao u razgovoru i nadam se da neće proći puno do iduće pive, ako ne u Zagrebu onda u Beču 🙂

Što se tiče mojih budućih postova, to će uglavnom biti tekstovi sa bloga kojeg vodim na engleskom jeziku „http://msutic.blogspot.com“. Ovdje će se nalazit hrvatska verzija tekstova za sve one koji se ne snalaze najbolje u engleskom jeziku.

Pa krenimo sa današnjim postom…

Jedna od bitnih stavki u poslu administratora baza podataka je i pronalaženje neefikasnog koda te podešavanje istog. Za sebe mogu reći da često puta znam pregledati skupe upite koji se u tom trenutku vrte na bazi i uvijek pokušam naći efikasnije rješenje ukoliko je to moguće.

Nekoliko puta sam do sad primijetio da programeri provjeravaju postojanje retka u tablici na način da izbroje koliko se puta navedeni zapis pojavio u tablici i ako je ta vrijednost veća od 0 redak postoji.

declare
l_cnt number;
begin
select count(*) into l_cnt
from test_table
where row = something;
if l_cnt>0 then
exists, do something
else
not exists, do something
end if;
end;
/

Upit će dati točan rezultat na pitanje postoji li traženi redak u tablici ali će do odgovora doći na vrlo neefikasan način. Da bi se izbrojao broj pojavljivanja retka svaki puta će se pretražiti cijela tablica što bi mogao biti problem ukoliko se radi o većoj tablici.

Puno efikasniji je način da upit završi sa izvršavanjem čim naiđe na prvo pojavljivanje retka koji tražimo. Prebrojavanje koliko se puta pojavio taj retak u tablici je jednostavno nepotrebno i značajno produžava vrijeme izvršavanja koje bi najčešće trebalo biti vrlo kratko.

Postoji nekoliko načina na koji se efikasno može napisati navedeni upit, te ću ja ovdje zapisati neke od njih.

Testno okruženje:
Oracle Enterprise Linux 5
Oracle 10.2.0.4.0

Kreirajte jednostavnu tablicu bez indeksa i napunite ju sa 2000000 redaka.

drop table testtab;

create table testtab (id number,
code number,
text varchar2(50),
tdate date);

Table created.

insert /*+ append */ into testtab
select rownum, mod(rownum,1000), rpad('x',50,'x'), sysdate+rownum
from all_objects o1,
all_objects o2
where
rownum <= 2000000
/
commit;
Commit complete.

U ovom testu ću provjeravati postojanje retka sa kodom 999.

Prvo rješenje:

set timing on
alter system flush buffer_cache;
System altered.

declare
l_cnt number;
begin
select count(*) into l_cnt
from testtab
where code=999;
if l_cnt>0 then
dbms_output.put_line('Exists!');
else
dbms_output.put_line('NOT Exists!');
end if;
end;
/

Exists!

PL/SQL procedure successfully completed.
Elapsed: 00:00:04.32

U ovom slučaju sam potrošio više od 4 sekunde za provjeru postojanja navedenog retka u tablici.

Drugo rješenje:

set timing on
alter system flush buffer_cache;

System altered.

declare
l_cnt number;
begin
select count(*) into l_cnt
from dual
where exists (select code
from testtab
where code=999);
if l_cnt>0 then
dbms_output.put_line('Exists!');
else
dbms_output.put_line('NOT Exists!');
end if;
end;
/

Exists!

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

Već ovdje možete primijetiti značajno poboljšanje pri izvršavanju upita.
Provjera je trajala samo 00,07 sekundi!
Ovo je inače način provjere koji ja najčešće volim koristiti za provjeru postojanja retka u tablici – upit završava sa izvršavanjem čim pogodi prvi traženi redak.

Treće rješenje:

set timing on
alter system flush buffer_cache;

System altered.

declare
l_cnt number;
begin
select count(*) into l_cnt
from testtab
where code = 999
and rownum = 1;
if l_cnt>0 then
dbms_output.put_line('Exists!');
else
dbms_output.put_line('NOT Exists!');
end if;
end;
/

Exists!

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

Ovaj put sam izvršavanje ubrzao koristeći rownum=1 unutar upita. Rezultat će se ispisati nakon pronalaska prvog odgovarajućeg retka.

Četvrto riješenje:

set timing on
alter system flush buffer_cache;
System altered.

begin
for x in (select count(*) cnt
from dual
where exists (select NULL from testtab
where code=999))
loop
if (x.cnt = 1)
then
dbms_output.put_line('Exists!');
else
dbms_output.put_line('NOT Exists!');
end if;
end loop;
end;
/

Exists!

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07

Ovo sam rješenje našao na stranicama AskTom.

Peto rješenje:

set timing on
alter system flush buffer_cache;
System altered.

begin
for x in (select /*+ first_rows(1) */ count(*) cnt
from testtab
where code=999
and rownum < 2)
loop
if (x.cnt = 1)
then
dbms_output.put_line('Exists!');
else
dbms_output.put_line('NOT Exists!');
end if;
end loop;
end;
/

Exists!

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06

Još jedno rješenje sa AskTom stranice.

Idući put kad budete morali provjeriti postojanje retka u tablici sjetite se ovog posta 😉

  1. 6 Responses to “Brzi upit za provjeru postojanja retka u tablici”

  2. a zašto se programeru dozvoljava pisanje query-a?
    zašto to ne radi dba admin?

    By gusar on Dec 7, 2010

  3. @gusar: je l’ se ti to zezas ili si ozbiljan?

    By Dejan on Dec 8, 2010

  4. koliko sam čuo od kolega koji rade kao programeri u ozbiljnim firmama, oni kao programeri nemaju pristup bazi već se sve radi u korodinaciji sa db adminima koji su ujedno i db developeri,a sve je prije organizirano kako treba i napravljen je projekat i plan za razvoj aplikacije/a.
    ima nekih firmi gdje sveki programer čačka po bazi, piše query-e, zato se i u praksi dobiva to što se dobiva, pola aplikacija ne radi kako treba ili se ruše ili se baza zakrči jer neznaju optimizaciju itd..
    kad svatko čačka po bazi onda ima baza da jedna tabela ima preko 100 kolona, kad nešto trebaš novo ili dodatni modul onda im je to problem napraviti jer baza nije u trećoj normalnoj formi.
    postavlja se pitanje da li bussiness logiku bolje držati u bazi ili je staviti u app, neznam , ali koliko vidim, apex svoju logiku drži u bazi.
    pojavili su se orm framework-ci, za mapiranje baze, pa se pojavio linq u sklopu .net-a, koji kao pojednostavljuje pisanje query-a i namijenjen je programerima koji neznaju sql.
    po meni bullshit i nepotrebno.
    rađe bi odabrao da se radi mjesec dana duže i da je napravljeno kako treba, nego da se aplikacija vuče ili ruši.
    to je realnost.
    jel problem ako sam recimo ja dba admin i meni kao adminu se šalju zahtjevi za pisanje query-a (view-a)?
    schema: programer treba neki query, dba adminu pošalje zahtjev, dba admin piše view koji treba programeru, programer može odma napisati query u code-u kao “select * from my_view where kolona like …..” ili umjesto like “=”.
    i ne vidim problema.
    a to da programer piše procedure ili funkcije nezamislivo osim ako jako dobro zna pl/sql.

    By gusar on Dec 8, 2010

  5. malo sam se krivo izrazio u prvom komentaru.

    By gusar on Dec 8, 2010

  6. @gusar: hmmm, je l’ ti pod “programerima” mislis na Java/.NET/whatever programere ili na PL/SQL programere? Ako mislis na prve, onda je ispravna odluka, da oni ne pisu procedure i zahtjevne SQL upite – taj posao se treba prepustiti PL/SQL programerima.

    Mislim da si u zabludi, ako tvrdis da je DBA zaduzen za pisanje SQL upita – pa sta bi onda trebali raditi PL/SQL programeri? DBA moze samo optimirati neki spori SQL, ali njegov zadatak nije da programira aplikacije…

    Btw. ne mora svaka tabela biti u 3. normalnoj formi – npr. mi imamo DWH bazu, gdje smo ustanovili da su upiti prema nekim ogromnim tabelama (preko 100 000 000 redova) znatno brzi, kada su te tabele malkice denormalizovane, nego kada su normalizovane u snowflake ili star shemi sa 5-6 manjih lookup tabela…

    By Dejan on Dec 10, 2010

  7. mislio sam na Java/.NET/whatever programere.
    a onaj tko je pl/sql programer mora znati barem osnove optimizacije i osnove administracije.
    idealno bi bilo da pl/sql programer zna barem nešto administracije, više od osnovnog.

    By gusar on Dec 10, 2010

Post a Comment