von Markus Winand.

Oracle Skripte für „Teilergebnisse“


Dieser Abschnitt enthält die create und insert Anweisungen um die Beispiele aus Kapitel 7, „Teilergebnisse in einer Oracle Datenbank durchzuführen.

Top-N Zeilen Abfragen

Der Skalierungstest wird nach derselben Methode wie im Kapitel „Testen und Skalierbarkeit“ durchgeführt.

Zuerst mit ein pipelined Top-N mit einem Index der die order by-Klausel abdeckt:

CREATE INDEX scale_slow ON scale_data (section, id1, id2);

SELECT *
  FROM TABLE(test_scalability.run(
       'SELECT * FROM (SELECT id2, id1 '
                    ||  'FROM scale_data '
                    || 'WHERE section=:1 '
                    || 'ORDER BY id2, id1) '
    || ' WHERE rownum <= 100', 10));

Danach mit einem Index, der nur die where-Klausel abdeckt:

  DROP INDEX scale_slow;
CREATE INDEX scale_fast ON scale_data (SECTION, id2, id1);

SELECT *
  FROM TABLE(test_scalability.run(
       'SELECT * FROM (SELECT id2, id1 '
                    ||  'FROM scale_data '
                    || 'WHERE section=:1 '
                    || 'ORDER BY id2, id1) '
    || ' WHERE rownum <= 100', 10));

Durch Ergebnisse Blättern

CREATE OR REPLACE
PACKAGE test_topn_scalability IS
  TYPE piped_output IS
             RECORD ( section  NUMBER
                    , mde      NUMBER
                    , page     NUMBER
                    , seconds  INTERVAL DAY TO SECOND);
  TYPE piped_output_table IS TABLE OF piped_output;

  FUNCTION run(n IN number)
    RETURN test_topn_scalability.piped_output_table PIPELINED;
END;
/

CREATE OR REPLACE
PACKAGE BODY test_topn_scalability
IS
  TYPE tmp IS TABLE OF piped_output INDEX BY PLS_INTEGER;

FUNCTION run(n IN NUMBER)
  RETURN test_topn_scalability.piped_output_table PIPELINED
IS
  TYPE last_fetched IS RECORD (id2 NUMBER, id1 NUMBER);
  last last_fetched;
  rec  test_topn_scalability.piped_output;
  TYPE sec_array IS TABLE OF last_fetched INDEX BY PLS_INTEGER;

  iter NUMBER;
  sec  NUMBER;
  strt TIMESTAMP(9);
  mde  NUMBER;
  page NUMBER;
  cont sec_array;

  CURSOR s_restart (sec IN NUMBER, page IN NUMBER) 
      IS SELECT id2, id1
           FROM (SELECT id2, id1, rownum rn
                   FROM scale_data
                  WHERE section = sec
                  ORDER BY id2, id1)
          WHERE rownum <= 100
            AND rn > page*100;        

  CURSOR s_continue (sec IN NUMBER, c IN last_fetched)
      IS SELECT *
           FROM (SELECT id2, id1
                   FROM scale_data
                  WHERE section = sec
                    AND id2 >= c.id2
                    AND (   
                            (id2 = c.id2 AND id1 > c.id1)
                         OR 
                            (id2 > c.id2)
                        )
                  ORDER BY id2, id1)
          WHERE rownum <= 100;
BEGIN
  iter := 0;
  WHILE iter <= n LOOP
    FOR mde IN 0 .. 1 LOOP
      FOR page IN 0 .. 100 LOOP
        FOR sec IN 0 .. 300 LOOP
          strt := systimestamp;
          IF (mde = 0 OR page = 0) THEN
            FOR r IN s_restart (sec, page) LOOP
              last := r;
            END LOOP;
          ELSE
            FOR r IN s_continue (sec, cont(sec)) LOOP
              last := r;
            END LOOP;
          END IF;
         
          rec.seconds := (systimestamp - strt);
          rec.section := sec;
          rec.page    := page;
          rec.mde     := mde;
          PIPE ROW(rec);

          cont(sec) := last;

        END LOOP;
      END LOOP;
    END LOOP;
    iter := iter +1;
  END LOOP;
  RETURN;
END run;
END test_topn_scalability;
/

SELECT section, mde, page, sum(extract(second from seconds))
  FROM TABLE(test_topn_scalability.run(10))
 WHERE section = 10
 GROUP BY section, mde, page
 ORDER BY section, mde, page;

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Kaufen Sie sein Buch bei Amazon

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Holen Sie sich Markus

…für ein Training ins Büro.

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz