DDevelopment & Design

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.

— Previous article

Indeksy na kolumnach z wartościami nullowymi cz. 1

Next article —

How to use mocks in controller tests

You May Also Like
DDevelopment & Design

Use asInstanceOf[T] carefully!

BackgroundScala has nice static type checking engine but from time to time there are situations when we must downcast some general object. If this casting is not possible we expect that virtual machine will throw ClassCastExeption as fast as possible. ...
DDevelopment & Design

Grails with Spock unit test + IntelliJ IDEA = No thread-bound request found

During my work with Grails project using Spock test in IntelliJ IDEA I've encountered this error:

java.lang.IllegalStateException: No thread-bound request found: Are you referring to request attributes outside of an actual web request, or processing a request outside of the originally receiving thread? If you are actually operating within a web request and still receive this message, your code is probably running outside of DispatcherServlet/DispatcherPortlet: In this case, use RequestContextListener or RequestContextFilter to expose the current request.
 at org.springframework.web.context.request.RequestContextHolder.currentRequestAttributes(RequestContextHolder.java:131)
 at org.codehaus.groovy.grails.plugins.web.api.CommonWebApi.currentRequestAttributes(CommonWebApi.java:205)
 at org.codehaus.groovy.grails.plugins.web.api.CommonWebApi.getParams(CommonWebApi.java:65)
... // and few more lines of stacktrace ;)

It occurred when I tried to debug one of test from IDEA level. What is interesting, this error does not happen when I'm running all test using grails test-app for instance.

So what was the issue? With little of reading and tip from Tomek Kalkosiński (http://refaktor.blogspot.com/) it turned out that our test was missing @TestFor annotation and adding it solved all problems.

This annotation, according to Grails docs (link), indicates Spock what class is being tested and implicitly creates field with given type in test class. It is somehow strange as problematic test had explicitly and "manually" created field with proper controller type. Maybe there is a problem with mocking servlet requests?