Muniqsoft Training

Auswahl  

Zeilenbegrenzung in 12c - Pagination leicht gemacht 

Oracle
PL/SQL
RDBMS 12.x
26.06.18 (MP)
26.06.18 (MP)
Oracle Neuerungen, SQL, PL/SQL

Body

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 Fensterhier 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 :-)

Besuchen Sie uns doch bei einer unsere über 40 Oracle Schulungen in München - Unterhaching.