Jak podzielić duży SELECT (i dlaczego ROWNUM jest zakazany)

Mamy jakieś duże, długo się wykonujące lub generujące olbrzymi raport zapytanie SELECT. Chcielibyśmy je przyspieszyć, zmniejszyć wykorzystanie zasobów, generować raport po kawałku (być może piszemy program generujący strony WWW), możemy też chcieć uniknąć problemów z przepełnieniem segmentu wycofania. W każdym razie: chcemy pobierać wyniki po trochu, np. najpierw pierwszych 30 rekordów, potem następne 30, następne 30 itd aż do końca albo aż użytkownik przestanie klikać Next Page.

Dla zorientowania uwagi przyjmijmy, że mamy tablicę PRODUCT(NAME, DESCRPT, PRICE) zawierającą dużą ilość rekordów i chcemy je prezentować (w kolejności według NAME) w paczkach po 30 sztuk.

Czego nie wolno robić

Najpierw ostrzeżenie: absolutnie nie należy próbować rozwiązań opartych o pseudokolumnę ROWNUM.

Zapytanie typu

   /** Tak nie zadziała, nie próbuj tego **/ 
  SELECT name, descrpt, price FROM product
  WHERE ROWNUM >= 60 AND ROWNUM < 90
  ORDER BY name

nie zwróci żadnej odpowiedzi! Wynika to z faktu, że warunek ROWNUM >= 60 (a także inne podobne, jak ROWNUM > 1 czy ROWNUM BETWEEN 3 AND 10) nigdy nie jest spełniony. Oracle realizując zapytanie pobierze pierwszy rekord nadając mu numer 1. Sprawdzi warunek - stwierdzając, że nie jest on spełniony. Zatem zignoruje dany rekord i pobierze następny - także nadając mu numer 1! I tak dalej...

Podziękowania dla Adama Kurka za zwrócenie mi uwagi na powyższy fakt (w pierwszej wersji tej porady o nim zapomniałem).

Także pozbawione powyższego błędu próby typu

   /** Nie rób tak **/ 
  SELECT name, descrpt, price FROM product
  WHERE ROWNUM < 30
  ORDER BY name

(mające na celu zwrot wiodącej części odpowiedzi) nie są zbyt sensowne. Dlaczego? Ano dlatego, że ROWNUM jest przypisywany poszczególnym rekordom na dosyć przypadkowych zasadach. Wykonując kilka razy to samo zapytanie, możemy mieć zupełnie inaczej poustawiane ROWNUM i dla tych samych danych dostać inne odpowiedzi. Powyższe zapytanie zwróci przypadkowych trzydzieści rekordów a nie pierwszych 30 rekordów przy uporządkowaniu po name!

Dzieje się tak dlatego, że Oracle najpierw sprawdza warunek WHERE a dopiero potem sortuje wyniki. Realizując powyższe zapytanie najpierw będzie pobierać rekordy w bliżej nie ustalonej kolejności, wybierze z nich pierwszych 30 a dopiero potem je posortuje. Jeśli do realizacji zapytania zostanie wybrany indeks po name, rekordy mogą być przypadkiem posortowane odpowiednio ale nie mamy gwarancji że będzie tak zawsze (patrz też uwaga o zapewnianiu kolejności).

Co możemy

Chcąc rozwiązać problem musimy zapomnieć o ROWNUM i szukać innego rozwiązania. Nie jest to trudne. Pomoże nam zapytanie w stylu

    SELECT /*+FIRST_ROWS*/ name, descrpt, price FROM product
    WHERE name > 'Basket ball'
    ORDER BY name

(gdzie za pierwszym razem warunek name > ... pomijamy, a za każdym kolejnym żądamy by name było większe niż klucz ostatniego rekordu z poprzedniej paczki). Przy tym, realizując powyższe sczytujemy naszych pierwszych 30 odpowiedzi po czym zamykamy zapytanie. Jeśli mamy indeks na name, Oracle prawdopodobnie i tak go użyje ale profilaktycznie dodatkowo prosimy go przy pomocy 'hintu' FIRST_ROWS by rozwiązując zapytanie miało na celu jak najszybsze dostarczenie pierwszych odpowiedzi. Jest to ważne zwłaszcza, gdy nasze zapytanie łączy kilka tabel - zależy nam na schemacie wykonania sterowanym indeksem i dobierającym dane przy pomocy zagnieżdżonych pętli (nested loops), chcemy zaś uniknąć wszelkich pełnych skanów tablic i operacji merge join.

Powyższy schemat działa bardzo gładko, może nam sprawić tylko jeden problem - nie jesteśmy odporni na efekty działania innych transakcji. Jeśli w trakcie gdy wyświetlamy jedną z początkowych paczek ktoś zrealizuje transakcję zmieniającą nazwę pewnego produktu z 'Zxxxx' na 'Axxxx', danego rekordu nigdy nie pokażemy ('A' już było i się nie załapał, gdy dojdziemy do 'Z' już go tam nie będzie). Analogiczne anomalie mogą wystąpic przy innych operacjach modyfikujących bazę danych. Niemniej jednak, nasz program zachowa się w takiej sytuacji stabilnie, nie zgłosi błędu ani nie wykona dziwnych skoków w nieoczekiwane miejsca danych. A jeśli potrzebujemy mieć pełną spójność - nie ma cudów, musimy realizować jedno duże zapytanie (przy optymalizacji dla WWW możemy co najwyżej wygenerować pierwszą stronę od razu a resztę danych przepisać w tymczasowe miejsce i stamtąd je później pobierać).

Jeszcze drobna uwaga na boku: oczywiście produkcyjnie pisałbym

    SELECT /*+FIRST_ROWS*/ name, descrpt, price FROM product
    WHERE name > ?
    ORDER BY name

(i wiązał parametr przy pomocy coraz to innych wartości). Trochę czasu w ten sposób można oszczędzić.

• «Zmienne środowiskowe Oracle i parę innych drobiazgów
• Darmowe narzędzia dla użytkownika Oracle»
• ↑Kilka porad na temat Oracle

komentarze obsługiwane przez Disqus