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

Custom SonarQube rules for Unit Tests

It's a tutorial about creating new rules for SonarQube analysis to be applied to Unit Tests. It is not trivial and involves a few tricky database steps, so I want to share my tutorial about it.It's a tutorial about creating new rules for SonarQube analysis to be applied to Unit Tests. It is not trivial and involves a few tricky database steps, so I want to share my tutorial about it.
DDevelopment & Design

Visualizing GIS data in JavaFX 2.0 beta using GeoTools

Geographic data mostly comprises of polygon coordinates sets along with attributes, like country or city name, etc. This is quite easy to visualize in JavaFX, which supports rendering for SVG paths. In the article, I show how to read such GIS data from...Geographic data mostly comprises of polygon coordinates sets along with attributes, like country or city name, etc. This is quite easy to visualize in JavaFX, which supports rendering for SVG paths. In the article, I show how to read such GIS data from...