Wer je Daten für eine Web-Applikation bereitstellen musste, kennt das Problem der Pagination. In der Regel holt das Frontend ja nur die Daten, die auf eine Seite passen, und erst wenn mehr angefordert werden, werden auch mehr geholt. Das stellt den Programmierer vor die Schwierigkeit, die Daten entprechend "mundgerecht" zu liefern. Entscheidend dabei ist in jedem Fall eine absolut eindeutige Sortier-Reihenfolge.
Ein klassischer von Tom Kyte u. a. hier Öffnet externen Link in neuem Fenster hier beschriebener Ansatz über ROWNUM sieht dann so aus:
select *
from ( select /*+ FIRST_ROWS(n) */
a.*, ROWNUM rnum
from ( your_query_goes_here,
with order by ) a
where ROWNUM <=
:MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
Anmerkung: Für die folgenden Beispiele wurde eine Tabelle OBJECT_TAB als Kopie von ALL_OBJECTS erstellt.
Angewendet auf diese Tabelle sähe ein Select, der die Zeilen 11 bis 20 holt, z. B. so aus:
SELECT object_id, object_name
FROM (SELECT a.*, ROWNUM rnum
FROM ( SELECT *
FROM object_tab
ORDER BY object_id) a
WHERE ROWNUM <= 20)
WHERE rnum >= 11;
Der Ausführungsplan zeigt, dass der Optimizer hier mit Stopkeys arbeitet:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=1872 Card=20 Bytes=1 K)
1 0 VIEW (Cost=1872 Card=20 Bytes=1 K)
2 1 COUNT STOPKEY
3 2 VIEW (Cost=1872 Card=65 K Bytes=4 M)
4 3 SORT ORDER BY STOPKEY (Cost=1872 Card=65 K Bytes=6 M)
5 4 TABLE ACCESS FULL OBJECT_TAB (Cost=290 Card=65 K Bytes=6 M)
Anmerkung: Um Zeilenumbrüche in der Darstellung zu verhindern, wurden überflüssige Leerzeichen und Schema-Angaben aus dem Ausführungsplan entfernt.
Im PL/SQL-Umfeld würde eine entsprechende Prozedur - stark vereinfacht - z. B. so aussehen:
CREATE PROCEDURE test_1 (p_from IN NUMBER,
p_until IN NUMBER,
p_result OUT SYS_REFCURSOR)
AS
BEGIN
OPEN p_result FOR
SELECT object_id, object_name
FROM (SELECT a.*, ROWNUM rnum
FROM ( SELECT object_id, object_name
FROM object_tab
ORDER BY object_id) a
WHERE ROWNUM <= p_until)
WHERE rnum >= p_from;
END test_1;
/
Da man jedoch gerade bei Suchmasken in der Regel mit dynamischen SQL arbeiten muss, ist das wohl etwas näher dran:
CREATE PROCEDURE test_2 (p_from IN NUMBER,
p_until IN NUMBER,
p_result OUT SYS_REFCURSOR)
AS
v_sql VARCHAR2 (4000 CHAR);
BEGIN
v_sql := 'SELECT object_id, object_name
FROM (SELECT a.*, ROWNUM rnum
FROM ( SELECT object_id, object_name
FROM object_tab
-- WHERE .....
ORDER BY object_id) a
WHERE ROWNUM <= :1)
WHERE rnum >= :2';
OPEN p_result FOR v_sql USING p_until, p_from;
END test_2;
/
Das funktioniert zwar, ist aber nicht direkt intuitiv.
Mit Version 12c hat nun Oracle eine neue Klausel zur Begrenzung der Zeilen eingeführt, die die Syntax in solchen Fällen deutlich vereinfacht.
Angegeben werden können dabei:
- OFFSET: Ein Startpunkt, ab wo geliefert werden soll; wird diese Angabe weggelassen, gilt OFFSET = 0, also Beginn beim ersten Datensatz der Ergebnismenge
- FETCH: Anzahl oder Prozentsatz an Zeilen, die geholt werden sollen; wird diese Angabe weggelassen, so werden alle Zeilen ab <OFFEST + 1> geholt
Das obige Beispiel würde in 12c so aussehen:
SELECT object_id, object_name
FROM object_tab
ORDER BY object_id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
Glaubt man dem Ausführungsplan, so ist diese Abfrage nicht nur wesentlich lesbarer, sondern auch noch performanter:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer Mode=ALL_ROWS (Cost=860 Card=65 K Bytes=6 M)
1 0 VIEW (Cost=860 Card=65 K Bytes=6 M)
2 1 WINDOW SORT PUSHED RANK (Cost=860 Card=65 K Bytes=1 M)
3 2 TABLE ACCESS FULL OBJECT_TAB (Cost=289 Card=65 K Bytes=1 M)
Anmerkung: Auch hier wurden überflüssige Leerzeichen und Schema-Angaben aus dem Ausführungsplan entfernt.
Eine Prozedur analog zu oben würde dann so aussehen:
CREATE OR REPLACE PROCEDURE test (p_offset IN NUMBER,
p_lines IN NUMBER,
p_result OUT SYS_REFCURSOR)
AS
v_sql VARCHAR2 (4000 CHAR);
BEGIN
v_sql := ' SELECT object_id,object_name
FROM object_tab
-- WHERE .....
ORDER BY object_id
OFFSET :1 ROWS FETCH NEXT :2 ROWS ONLY';
OPEN p_result FOR v_sql USING p_offset, p_lines;
END test;
/
Wesentlich lesbarer, oder?
Welche Möglichkeiten bietet die neue Klausel noch? Neben dem oben angegebenen <n> ROWS kann auch ein Prozentsatz mitgegeben werden mit <n> PERCENT ROWS. Das würde dann so aussehen:
SELECT object_id, object_name
FROM object_tab
ORDER BY object_id
OFFSET 10 ROWS FETCH NEXT 10 PERCENT ROWS ONLY;
Sollte die Sortierung nicht eindeutig sein (und auch nicht sein müssen), so kann man angeben, dass alle Datenätze mit ausgegeben werden sollen, die den gleichen Wert haben wie der zuletzt geholte. Dazu gibt man statt ONLY an WITH TIES. Der Effekt sei hier gezeigt an der allseits bekannten Tabelle SCOTT.EMP:
SELECT ename, sal
FROM scott.emp
ORDER BY sal DESC
FETCH NEXT 2 ROWS ONLY;
ENAME SAL
---------- ----------
KING 5000
SCOTT 3000
SELECT ename, sal
FROM scott.emp
ORDER BY sal DESC
FETCH NEXT 2 ROWS WITH TIES;
ENAME SAL
---------- ----------
KING 5000
SCOTT 3000
FORD 3000
Diese neue Klausel ist prinzipell nicht abhängig von der ORDER BY-Klausel. Ihre vollen Möglichkeiten entfaltet sie aber nur hier.
Mehr zu diesem Thema erfahren Sie in unserer Schulung Oracle Neuerungen 12c, schauen Sie doch einfach vorbei :-)