Als PDF Downloaden!

Tipps & Tricks: April 2014
Bereich:
SQL, PL/SQL
Erstellung:
04/2014 HA
Versionsinfo:
12.1
Letzte Überarbeitung:
04/2014 HA
Zeilenbegrenzung in 12c - Pagination leicht gemacht
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 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)
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 4
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;
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 4
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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 4
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 :-)
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 4 von 4