DDevelopment & Design

Indeksy na kolumnach z wartościami nullowymi cz. 1

A tak dokładnie aby zadziałał, gdy chcemy zrobić zapytanie:

select * from tablica where kolumna is null;

Zakładamy wtedy indeks funkcyjny: 

create index kolumna_idx on tablica (NVL(kolumna,TO_DATE(' 4000-01-01', 'syyyy-mm-dd')))

I wtedy musimy tak konstruować zapytanie, aby mogło wykorzystać powyższy indeks: 

select * from tablica   where NVL(kolumna,TO_DATE('4000-01-01', 'syyyy-mm-dd')) = TO_DATE('4000-01-01', 'syyyy-mm-dd')

Dla porównania koszt tradycyjnego zapytania: 

select count(*) from dh_monitor.events where valid_to is null

to 8122. Dla porównania, zapytanie które wykorzystuje indeks ma koszt 3!!!

select count(*) from  events where NVL(VALID_TO,TO_DATE('4000-01-01 ', 'syyyy-mm-dd')) = TO_DATE('4000-01-01', 'syyyy-mm-dd')

Dla tabeli, gdzie stosunek ilości nullowych wierszy do wszystkich w tabeli wynosi: 6.568/5.999.617

— Previous article

Hibernate Envers with Grails 2.1.0

Next article —

Indeksy na kolumnach z wartościami nullowymi cz. 2

You May Also Like
DDevelopment & Design

Wicket form submit not safe for redirecting to intercept page

The problem When you have a form, that anybody can see, but only logged on users can POST, you may want to redirect the user to the login page, and back to the form after login Using wicket 1.3/1.4, if you do that using redirectToInterceptPage(loginP...The problem When you have a form, that anybody can see, but only logged on users can POST, you may want to redirect the user to the login page, and back to the form after login Using wicket 1.3/1.4, if you do that using redirectToInterceptPage(loginP...
DDevelopment & Design

Journal.IO 1.3 released

AboutJust a moment ago (in February 17th) Journal.IO 1.3 has been released. Journal.IO (https://github.com/sbtourist/Journal.IO) is a lightweight, zero-dependency journal storage implementation written in Java. We use it in our project for storing appl...AboutJust a moment ago (in February 17th) Journal.IO 1.3 has been released. Journal.IO (https://github.com/sbtourist/Journal.IO) is a lightweight, zero-dependency journal storage implementation written in Java. We use it in our project for storing appl...