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.

I have been working as a system developer, data modeler and DBA on the Oracle platform using SQL, Pl/SQL, Forms, Reports, JSP, HTML and Javascript since 1996, and have been involved in both client/server and web-based projects for 25+ different customers. I'm normally known as a problem solver