Gå til hovedsiden

Indeksering a NULL værdier i Oracle databasen

Hvornår giver indeksering af NULL værdier i Oracle databasen mening? Hvad kan man vinde ved det? Og hvordan gør man det i praksis? Disse spørgsmål forsøger jeg at besvare nedenfor.

Oracle databasen er karakteriseret ved at dens NULL værdi er ”ingenting” i sin reneste form. Det er en udefineret størrelse, som man ikke kan spørge efter på normal vis, og som ikke fylder noget i databasen.

Det sidste betyder i praksis, at selvom man indekserer kolonner der indeholder NULL værdier, bliver NULL ikke indekseret. Søgninger på kolonner på andre værdier vil godt kunne anvende et sådant indeks, men søgninger på NULL (med IS NULL funktionaliteten) kan ikke. Det vil i mange tilfælde være helt ok, men i tilfælde hvor langt de fleste rækker er udfyldt for kolonnen, men nogle få har NULL værdier og man netop vil finde disse, får man ingen hjælp af indekset.

Her er et praktisk eksempel:

Vi laver en tabel til formålet, og indekserer dens kolonner:

create table testnull(id number not null, navn varchar2(50) not null, speciel_status varchar2(10));
alter table testnull add constraint testnull_pk primary key (id);
create sequence testnull_seq;
create index testnull_navn_i on testnull(navn);
create index testnull_speciel_status_i on testnull(speciel_status);  

Dernæst hælder vi 100.000 udfyldte testdata i + 3 rækker med NULL værdier, og indsamler statistik til optimizeren med det samme:

Begin
for i in 1..100000 loop
insert into testnull(id,navn,speciel_status)
values(testnull_seq.nextval,’Navn ‘||to_char(i),’BENYTTES’);
end loop;
end;             

insert into testnull(id,navn,speciel_status)values(testnull_seq.nextval,’Navn A’,null);
insert into testnull(id,navn,speciel_status)values(testnull_seq.nextval,’Navn B’,null);
insert into testnull(id,navn,speciel_status)values(testnull_seq.nextval,’Navn C’,null);

exec dbms_stats.gather_table_stats(user,tabname=>’TESTNULL’,estimate_percent=>100);
exec dbms_stats.gather_index_stats(null, ‘TESTNULL_NAVN_I’, null, DBMS_STATS.AUTO_SAMPLE_SIZE);
exec dbms_stats.gather_index_stats(null, ‘TESTNULL_SPECIEL_STATUS_I’, null, DBMS_STATS.AUTO_SAMPLE_SIZE);

Vi kan tjekke at vores indekser virker ved at søge på navnet:

select count(*) from TESTNULL where navn=’Navn 17′ ;
Explain plan step 1: Index range scan – index testnull_navn_i
Cost i alt: 3

Det samme gør sig i øvrigt gældende hvis vi søger på ” speciel_status=’BENYTTES’ ”, selvom optimizeren kunne vælge at læse hele tabellen, da denne værdi udgør 99,99% af rækkerne.

Men søger vi på rækkerne med NULL i speciel_status benyttes indekset ikke. Det ville ellers være smart, da kun 0,003 promille af rækkerne matcher IS NULL:

select count(*) from TESTNULL where speciel_status is null;
Explain plan step 1: Table Access full – table TESTNULL
Cost I alt: 112

Men Oracle databasen har en ret ukendt feature på create index, som muliggør at NULL bliver indekseret. Denne kan bruges på oprettelses-tidspunktet ved at påsætte ”,-1”. Vi ændrer eksemplet til at bruge denne feature, og opsamler statistik:

drop index testnull_speciel_status_i;
create index testnull_speciel_status_i on testnull(speciel_status,-1);
exec dbms_stats.gather_index_stats(null, ‘TESTNULL_SPECIEL_STATUS_I’, null, DBMS_STATS.AUTO_SAMPLE_SIZE);

Så gentager vi vores test:

select count(*) from TESTNULL where speciel_status is null;
Explain plan step 1: Index range scan – index testnull_special_status_i
Cost I alt: 3

For en sikkerhed skyld kan vi lige tjekke at databasen ikke rent faktisk opfatter værdien -1 som en reel værdi i indekset:

select count(*) from TESTNULL where speciel_status =’-1′;
No rows returned

Så ovenstående feature kan virkelig hjælpe hvis man gerne vil kunne søge effektivt på NULL værdier. Ulempen ved at benytte den er naturligvis, at NULL værdier nu også optager plads i indekset. I dette tilfælde vil det dog være en minimal forøgelse, da der kun er tale om 3 værdier ud af 100.003 værdier.

Så i praksis kan denne feature være guld værd, hvis tabellen er stor og man ved, at man har få NULL værdier i sin indekserede kolonne og man samtidigt hyppigt har brug for at finde dem.

Ovenstående eksempel er afprøvet på Oracle 10gR2 og Oracle 11gR2.

Mest læste

  • Visma trækker i trøjen for en god sag

    I dag er det 1. marts og dermed også forårets første dag i kalenderen. Det har dog ikke været det, der har været det store samtaleemne i Visma House i Carlsberg Byen i dag men i stedet har vi markeret #fodboldtrøjefredag med farverige fodboldtrøjer, konkurrencer og stadionplatte for at støtte om Børnecancerfondens årlige indsamlingsdag.

  • Ret til provision under ferien?

    Får din medarbejder provision, skal du være opmærksom på, at vedkommende har ret til at blive kompenseret for den mistede provision under ferien. Men hvordan skal løn under ferie beregnes, når medarbejderne også får udbetalt provision? Er du nysgerrig, så læs mere her.

  • Omvendt betalingspligt – hvordan er det nu?

    Fra d. 1 juli 2014 har nye regler været gældende for moms ved køb af mobiltelefoner, PC’er, tablets m.v. Vi gennemgår her hvad det betyder både for dig som køber og sælger af mobil- og IT-udstyr.