Baze podataka – MySQL, Oracle, PostgreSQL, MS SQL Server, Access, modelovanje podataka …

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 😉