Het effect van een index op database performance (demo)

Voor de les van vandaag heb ik een demo gemaakt om het effect van een index op de performance van een database te demonstreren. Naast de snelheidswinst maak ik ook inzichtelijk hoeveel opslagruimte dit kost en hoe je het bijbehorende execution plan kunt bekijken. Voor verschillende database systemen zal het principe hetzelfde zijn, onderstaande scripts werken echter alleen op Oracle.

Het effect van een index

Maak onderstaande testtabel aan en vul deze met random data:
--tabel maken
create table test (
kol01 varchar2(20),
kol02 varchar2(20),
kol03 varchar2(20),
kol04 varchar2(20),
kol05 varchar2(20),
kol06 varchar2(20),
kol07 varchar2(20),
kol08 varchar2(20),
kol09 varchar2(20),
kol10 varchar2(20),
gebdat date
);
--tabel vullen
declare
v_tot number default 1000000;
begin
for i in 0..v_tot loop
insert into test values (
dbms_random.string('U', 20),
dbms_random.string('U', 20),
dbms_random.string('U', 20),
dbms_random.string('U', 20),
dbms_random.string('U', 20),
dbms_random.string('U', 20),
dbms_random.string('U', 20),
dbms_random.string('U', 20),
dbms_random.string('U', 20),
dbms_random.string('U', 20),
sysdate-dbms_random.value(1,1000));
if mod(i,100) = 0 then
commit;
end if;
end loop;
--één regel toevoegen voor de query
insert into test values (
'ABCDEFGHIJKLMNOPQRST',
'ABCDEFGHIJKLMNOPQRST',
'ABCDEFGHIJKLMNOPQRST',
'ABCDEFGHIJKLMNOPQRST',
'ABCDEFGHIJKLMNOPQRST',
'ABCDEFGHIJKLMNOPQRST',
'ABCDEFGHIJKLMNOPQRST',
'ABCDEFGHIJKLMNOPQRST',
'ABCDEFGHIJKLMNOPQRST',
'ABCDEFGHIJKLMNOPQRST',
sysdate-dbms_random.value(1,1000));
commit;
end;
/


Met onderstaande script kun je vervolgens kijken hoe lang het duurt om een query uit te voeren:
--tijd meten van query
set serveroutput on
declare
v_start date;
v_aantal number;
v_kol05 varchar2(20);
begin
v_start := sysdate;
select kol05
into v_kol05
from test
where kol01 = 'ABCDEFGHIJKLMNOPQRST'
and kol02 = 'ABCDEFGHIJKLMNOPQRST'
and kol03 = 'ABCDEFGHIJKLMNOPQRST'
and kol04 = 'ABCDEFGHIJKLMNOPQRST';
dbms_output.put_line(v_kol05);
dbms_output.put_line('Aantal: ' || v_aantal);
dbms_output.put_line('Tijd (ms): ' || (sysdate-v_start)*24*60*60*1000);
end;
/


Vervolgens kun je een samengestelde index maken en het script nogmaals uitvoeren. Op mijn database server is de tijdsduur zonder index 4 seconden en met index een aantal milli seconden. Je ziet dus een duidelijke vooruitgang die zeker in een grote systeem veel processor capaciteit scheelt en snelheid oplevert.
create index indx_test on test (kol01, kol02, kol03, kol04);


Snelheid kost opslagcapaciteit

Goed gecreëerde indexen versnellen het zoeken in tabellen aanzienlijk. Dit heeft echter ook een prijs. Met onderstaande queries kun je zien hoe groot de tabel is (in MB’s) en hoe groot de index. Snelheid kost in dit geval opslagruimte.
--grootte bepalen van de index
SELECT sum(bytes)/1048576 MB, segment_name
FROM user_extents
WHERE segment_name = 'INDX_TEST'
GROUP BY segment_name;
--grootte bepalen van de tabel
SELECT sum(bytes)/1048576 MB, segment_name
FROM user_extents
WHERE segment_name = 'TEST';
GROUP BY segment_name;


Bekijk het explain plan

Je kunt ook het Oracle explain plan gebruiken om te kijken op welke wijze Oracle de query uitvoert. Voer hiervoor eerst het utlxplain.sql script uit uit de rdbms/admin map. Dit script genereert een tabel waar de benodigde info kan worden opgeslagen. Vervolgens kun je met onderstaande query het explain plan genereren:
explain plan
set statement_id = 'TEST'
for
select kol05
from test
where kol01 = 'ABCDEFGHIJKLMNOPQRST'
and kol02 = 'ABCDEFGHIJKLMNOPQRST'
and kol03 = 'ABCDEFGHIJKLMNOPQRST'
and kol04 = 'ABCDEFGHIJKLMNOPQRST';

NB: Alleen het plan wordt gemaakt, de query wordt niet daadwerkelijk uitgevoerd.

Met onderstaande query kun je bekijken in welke stappen het statement wordt uitgevoerd:
select id id_plus_exp,
parent_id parent_id_plus_exp,
lpad(' ', 2*(level-1))||
Operation||
decode(other_tag,null,'','*')||
decode(options,null,'',' ('||options||')')||
decode(object_name,null,'',' of '''||object_name||'''')||
decode(object_type,null,'',' '||object_type)||
decode(id,0,decode(optimizer,null,'', ' optimizer='||optimizer))||
decode(cost,null,'',' (cost='||cost||
decode(cardinality, null, '', ' card='||cardinality)||
decode(bytes,null,'',' bytes='||bytes||')')) plan_plus_exp,
object_node object_node_plus_exp
from plan_table
start with id=0 and statement_id='TEST'
connect by prior id=Parent_id and statement_id='TEST';

Bekijk het verschil tussen de uitvoer met index en zonder index. Maak tussen de twee tests de plan_table leeg of geef het tweede plan een andere naam.

Met index:

0 SELECT STATEMENT optimizer=ALL_ROWS (cost=3 card=2 bytes=96)
1 0 INDEX (RANGE SCAN) of 'INDX_TEST' INDEX (cost=3 card=2 bytes=96)


Zonder index:

0 SELECT STATEMENT optimizer=ALL_ROWS (cost=4571 card=1 bytes=48)
1 0 TABLE ACCESS (FULL) of 'TEST' TABLE (cost=4571 card=1 bytes=48)