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

You May Also Like

Simple trick to DRY your Grails controller

Grails controllers are not very DRY. It's easy to find duplicated code fragments in default generated controller. Take a look at code sample below. It is duplicated four times in show, edit, update and delete actions:

class BookController {
def show() {
def bookInstance = Book.get(params.id)
if (!bookInstance) {
flash.message = message(code: 'default.not.found.message', args: [message(code: 'book.label', default: 'Book'), params.id])
redirect(action: "list")
return
}
[bookInstance: bookInstance]
}
}

Why is it duplicated?

There is a reason for that duplication, though. If you move this snippet to a method, it can redirect to "list" action, but it can't prevent controller from further execution. After you call redirect, response status changes to 302, but after method exits, controller still runs subsequent code.

Solution

At TouK we've implemented a simple trick to resolve that situation:

  1. wrap everything with a simple withStoppingOnRender method,
  2. whenever you want to render or redirect AND stop controller execution - throw EndRenderingException.

We call it Big Return - return from a method and return from a controller at once. Here is how it works:

class BookController {
def show(Long id) {
withStoppingOnRender {
Book bookInstance = Book.get(id)
validateInstanceExists(bookInstance)
[bookInstance: bookInstance]
}
}

protected Object withStoppingOnRender(Closure closure) {
try {
return closure.call()
} catch (EndRenderingException e) {}
}

private void validateInstanceExists(Book instance) {
if (!instance) {
flash.message = message(code: 'default.not.found.message', args: [message(code: 'book.label', default: 'Book'), params.id])
redirect(action: "list")
throw new EndRenderingException()
}
}
}

class EndRenderingException extends RuntimeException {}

Example usage

For simple CRUD controllers, you can use this solution and create some BaseController class for your controllers. We use withStoppingOnRender in every controller so code doesn't look like a spaghetti, we follow DRY principle and code is self-documented. Win-win-win! Here is a more complex example:

class DealerController {
@Transactional
def update() {
withStoppingOnRender {
Dealer dealerInstance = Dealer.get(params.id)
validateInstanceExists(dealerInstance)
validateAccountInExternalService(dealerInstance)
checkIfInstanceWasConcurrentlyModified(dealerInstance, params.version)
dealerInstance.properties = params
saveUpdatedInstance(dealerInstance)
redirectToAfterUpdate(dealerInstance)
}
}
}