Oracle: jak na LIMIT, testy rychlosti

Jelikož jsem v práci řešil velké množství problémů s databází Oracle, tak se rád podělím o jeden z nich.

Oracle, bůch ví proč, nemá LIMIT. Nelze tedy napsat vybírat určitý počet prvků jednoduše tak, jak to umí třeba MySQL nebo Postgresql:

SELECT * FROM table LIMIT 10, 10;

Rownum

Oracle má místo toho rozporuplný ROWNUM. Každý vybraný řádek má ještě navíc jeden sloupec, který jednoduše určuje číslo řádku v daném výběru.

SELECT * FROM table WHERE ROWNUM BETWEEN 10 AND 20

Bohužel tento dotaz nefunguje, tak jak by měl. Nevrátí totiž vůbec nic. Kde je chyba, jsem nenašel ani v dokumentaci, přitom jsou tohoto problému plná fóra. Přitom dotaz končící ROWNUM<=10 vrátí naprosto korektní výsledek.

Další problém se naskýta tradičně při řazení na konci dotazu. Pokud je zde uvedena formule pro řazení např. ORDER BY sloupec. dojde k logickému: nejprve vybereme deset prvků, které potom seřadíme. Cílem ovšem bylo nalezení druhých deseti seřazených prvků.

�?ešením jsou dvě možnosti:

SELECT table.* FROM (SELECT table.*, rownum as rnum FROM table)  WHERE rnum BETWEEN 10 AND 20

nebo

(SELECT table.* FROM table WHERE rnum <= 20) minus (SELECT table.* FROM table WHERE rnum <= 10)

Testy rychlosti

Sice je na první pohled zřejmé, která metoda by měla být rychlejší, je velice těžko říct, jak je Oracle optimalizovaný a jak to celé pracuje. Proto jsem otestoval na velkém množství dat obě dvě metody.

Tabulka měla 25 milionů položek, vždy klíč a určitou hodnotu, která byla reálným číslem. Primárním cílem bylo spočítat určitou hodnotu z druhých 12.5 milionů prvků tabulky.

Hodnoty pro sečtení počtu prvků pomocí COUNT pomocí minus: 

10:13:10 SQL> SELECT count(*) FROM
10:13:10   2  ((SELECT * FROM table
10:13:10   3      WHERE ROWNUM <= 25000000)
10:13:10   4  minus
10:13:10   5  (SELECT * FROM table
10:13:10   6      WHERE ROWNUM <= 12500000));

  COUNT
—————
  12500000

10:19:25 SQL>

Hodnoty pro sečtení počtu prvků pomocí COUNT pomocí ROWNUM:

10:24:46 SQL> SELECT COUNT FROM   
10:24:46   2  (SELECT table.*,rownum as rnum FROM table
4) 10:24:46   3  WHERE rnum BETWEEN 12500001 AND 25000000;

  COUNT
—————
  12500000

10:25:18 SQL>

Hodnoty pro sumě prvků pomocí SUM pomocí minus:

10:25:36 SQL> SELECT sum(value) FROM
10:29:36   2  ((SELECT * FROM BNSSYS.TBMATRIX_EWS_DEM_2004
10:29:36   3      WHERE ROWNUM <= 25000000)
10:29:36   4  minus
10:29:36   5  (SELECT * FROM BNSSYS.TBMATRIX_EWS_DEM_2004
10:29:36   6      WHERE ROWNUM <= 12500000));

SUM
—————
4862807240

10:35:33 SQL>

Hodnoty pro sumě prvků pomocí SUM pomocí ROWNUM:

10:35:45 SQL> SELECT sum(value) FROM   
10:36:45   2  (SELECT BNSSYS.TBMATRIX_EWS_DEM_2004.*,rownum as rnum FROM BNSSYS.TBMATRIX_EWS_DEM_200
4)
10:36:45   3  WHERE rnum BETWEEN 12500000 AND 25000000;

SUM
—————
4862807240

10:37:18 SQL>

Výsledky

První test trval 265 vteřin minusu vs. 32 vteřinám pro samostatným ROWNUM. Druhý dopadl obdobně: 297 vs. 33 vteřinám.

Je vidět, že použití minusu je řádově skoro desetkrát pomalejší než použití vnořeného selectu a rownumu.

Vhodné použití minusu je tam, kdy není zřejmé, co daný dotaz obsahuje. Třeba pokud je generován dotaz automaticky, nelze do něj nijak zásadně zasahovat je vhodné použít minus. Pro krátké operace rozdíl znatelný není.