Indeksy na kolumnach z wartościami nullowymi cz. 2

Jest jeszcze jeden sposób indeksowania, który pozwala nam na bardzo wygodne korzystanie z indeksów i wyszukiwanie wartości nullowych. Mianowicie indeks jest prostym indeksem składającym się z dwóch kolumn – tej po której chcemy wyszukiwać i wartości stałej. Np:

create index kolumna_IDX on tabela (kolumna, TO_DATE('4000-01-01', 'yyyy-mm-dd')); 

i wtedy możemy pisać zapytania:

select * from tabela where kolumna is null 

i indeks zadziała! Zrobiłam też porównanie dla tabeli, gdzie stosunek ilości nullowych wierszy do wszystkich w tabeli wynosi: 1.002/131.002 I założyłam indeks

create index AA2_IDX on AAAAA2_TEST (NVL(MODIFY_DATE,TO_DATE('4000-01-01', 'yyyy-mm-dd'))) 

Koszt zapytania:

select COUNT(*) from aaaaa2_test WHERE NVL(modify_DATE,TO_DATE('4000-01-01', 'syyyy-mm-dd')) = TO_DATE('4000-01-01', 'syyyy-mm-dd') 

wyniósł 38 zaś dla indeksu:

create index AA_IDX on AAAAA2_TEST (FROM_DATE, TO_DATE('4000-01-01', 'yyyy-mm-dd')) 

i zapytania:

select COUNT(*) from aaaaa2_test WHERE FROM_DATE IS NULL

wyniósł 28. Dla większej ilości nullowych rekordów tzn. 11002/131.002 stosunek kosztów wyniósł 43/28, a więc znów zdecydowanie na korzyść indeksu dwukolumnowego.

You May Also Like

Confitura 2013 afterthoughts

Confitura, the biggest free-of-charge Java conference in Europe, took place on the 6th of July in Warsaw. TouK's presence was heavy, with 5 separate talks, all chosen in call for papers, no sponsored bullshit. We were sponsoring deck chairs during the...Confitura, the biggest free-of-charge Java conference in Europe, took place on the 6th of July in Warsaw. TouK's presence was heavy, with 5 separate talks, all chosen in call for papers, no sponsored bullshit. We were sponsoring deck chairs during the...

JCE keystore and untrusted sites

Recently at work I was in need of connecting to a web service exposed via HTTPS. I've been doing this from inside Servicemix 3.3.1, which may seem a bit inhibiting, but that was a requirement. Nevertheless I've been trying my luck with the included ser...Recently at work I was in need of connecting to a web service exposed via HTTPS. I've been doing this from inside Servicemix 3.3.1, which may seem a bit inhibiting, but that was a requirement. Nevertheless I've been trying my luck with the included ser...