von Markus Winand.

Oracle Skripts für „Insert, Delete und Update“


Dieser Abschnitt enthält die create und insert Anweisungen um die Beispiele aus Kapitel 8, „Schreiboperationen in einer Oracle Datenbank durchzuführen. Eine einzelne Abfrage liefert die Ergebnisse für alle drei Abschnitte dieses Kapitels.

CREATE TABLE scale_write_0 AS
  WITH generator AS (
                     SELECT --+ materialize
                            level n
                       FROM DUAL
                    CONNECT BY level <= 10000
) 
 SELECT rownum id1
      , CEIL(DBMS_RANDOM.VALUE(1000000,9999999)) id2
      , CEIL(DBMS_RANDOM.VALUE(1000000,9999999)) id3
      , CEIL(DBMS_RANDOM.VALUE(1000000,9999999)) id4
      , CEIL(DBMS_RANDOM.VALUE(1000000,9999999)) id5
   FROM generator, generator
  WHERE rownum <= 10000000;

CREATE TABLE scale_write_1 AS
SELECT * from scale_write_0;

CREATE TABLE scale_write_2 AS
SELECT * from scale_write_0;

CREATE TABLE scale_write_3 AS
SELECT * from scale_write_0;

CREATE TABLE scale_write_4 AS
SELECT * from scale_write_0;

CREATE TABLE scale_write_5 AS
SELECT * from scale_write_0;

CREATE INDEX scale_write_1_1 on scale_write_1(id1);

CREATE INDEX scale_write_2_1 on scale_write_2(id1);
CREATE INDEX scale_write_2_2 on scale_write_2(id2, id1);

CREATE INDEX scale_write_3_1 on scale_write_3(id1);
CREATE INDEX scale_write_3_2 on scale_write_3(id2, id1);
CREATE INDEX scale_write_3_3 on scale_write_3(id3, id2, id1);

CREATE INDEX scale_write_4_1 on scale_write_4(id1);
CREATE INDEX scale_write_4_2 on scale_write_4(id2, id1);
CREATE INDEX scale_write_4_3 on scale_write_4(id3, id2, id1);
CREATE INDEX scale_write_4_4 on scale_write_4(id4, id3, id2
                                            , id1);

CREATE INDEX scale_write_5_1 on scale_write_5(id1);
CREATE INDEX scale_write_5_2 on scale_write_5(id2, id1);
CREATE INDEX scale_write_5_3 on scale_write_5(id3, id2, id1);
CREATE INDEX scale_write_5_4 on scale_write_5(id4, id3, id2
                                           , id1);
CREATE INDEX scale_write_5_5 on scale_write_5(id5, id4, id3
                                           , id2, id1);

begin
 DBMS_STATS.GATHER_TABLE_STATS(user
                             , 'SCALE_WRITE_0', cascade=>true);
 DBMS_STATS.GATHER_TABLE_STATS(user
                             , 'SCALE_WRITE_1', cascade=>true);
 DBMS_STATS.GATHER_TABLE_STATS(user
                             , 'SCALE_WRITE_2', cascade=>true);
 DBMS_STATS.GATHER_TABLE_STATS(user
                             , 'SCALE_WRITE_3', cascade=>true);
 DBMS_STATS.GATHER_TABLE_STATS(user
                             , 'SCALE_WRITE_4', cascade=>true);
 DBMS_STATS.GATHER_TABLE_STATS(user
                             , 'SCALE_WRITE_5', cascade=>true);
end;
/
create or replace
PACKAGE test_write_scalability IS
  TYPE piped_output IS
             RECORD ( idxes   NUMBER
                    , cmnd    VARCHAR2(255)
                    , seconds NUMBER
                    , id1     NUMBER);
  TYPE piped_output_table IS TABLE OF piped_output;

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

create or replace
PACKAGE BODY test_write_scalability
IS
  TYPE tmp IS TABLE OF piped_output INDEX BY PLS_INTEGER;

FUNCTION run_insert(tbl IN NUMBER, d1 IN NUMBER)
                    RETURN VARCHAR2
AS
  r2 NUMBER := CEIL(DBMS_RANDOM.VALUE(1000000,9999999));
  r3 NUMBER := CEIL(DBMS_RANDOM.VALUE(1000000,9999999));
  r4 NUMBER := CEIL(DBMS_RANDOM.VALUE(1000000,9999999));
  r5 NUMBER := CEIL(DBMS_RANDOM.VALUE(1000000,9999999));
BEGIN
  CASE tbl
  WHEN 0 THEN 
         INSERT INTO scale_write_0 (id1, id2, id3, id4, id5)
                            VALUES ( d1,  r2,  r3,  r4,  r5);
  WHEN 1 THEN 
         INSERT INTO scale_write_1 (id1, id2, id3, id4, id5)
                            VALUES ( d1,  r2,  r3,  r4,  r5);
  WHEN 2 THEN 
         INSERT INTO scale_write_2 (id1, id2, id3, id4, id5)
                            VALUES ( d1,  r2,  r3,  r4,  r5);
  WHEN 3 THEN 
         INSERT INTO scale_write_3 (id1, id2, id3, id4, id5)
                            VALUES ( d1,  r2,  r3,  r4,  r5);
  WHEN 4 THEN 
         INSERT INTO scale_write_4 (id1, id2, id3, id4, id5)
                            VALUES ( d1,  r2,  r3,  r4,  r5);
  WHEN 5 THEN 
         INSERT INTO scale_write_5 (id1, id2, id3, id4, id5)
                            VALUES ( d1,  r2,  r3,  r4,  r5);
  END CASE;
  RETURN 'insert';
END;

FUNCTION run_delete(tbl IN NUMBER, d1 IN NUMBER)
RETURN VARCHAR2
AS
BEGIN
  CASE tbl
  WHEN 1 THEN 
         DELETE FROM scale_write_1 WHERE id1 = d1;
  WHEN 2 THEN 
         DELETE FROM scale_write_2 WHERE id1 = d1;
  WHEN 3 THEN 
         DELETE FROM scale_write_3 WHERE id1 = d1;
  WHEN 4 THEN 
         DELETE FROM scale_write_4 WHERE id1 = d1;
  WHEN 5 THEN 
         DELETE FROM scale_write_5 WHERE id1 = d1;
  ELSE NULL;
  END CASE;
  IF SQL%ROWCOUNT > 0 THEN RETURN 'delete';
  ELSE RETURN NULL; END IF;
END;

FUNCTION run_update_all(tbl IN NUMBER, d1 IN NUMBER)
RETURN VARCHAR2
AS 
  r2 NUMBER := CEIL(DBMS_RANDOM.VALUE(1000000,9999999));
  r3 NUMBER := CEIL(DBMS_RANDOM.VALUE(1000000,9999999));
  r4 NUMBER := CEIL(DBMS_RANDOM.VALUE(1000000,9999999));
  r5 NUMBER := CEIL(DBMS_RANDOM.VALUE(1000000,9999999));
BEGIN
  CASE tbl
  WHEN 1 THEN 
         UPDATE scale_write_1
            SET id2 = r2, id3=r3, id4=r4, id5=r5 WHERE id1=d1;
  WHEN 2 THEN 
         UPDATE scale_write_2
            SET id2 = r2, id3=r3, id4=r4, id5=r5 WHERE id1=d1;
  WHEN 3 THEN 
         UPDATE scale_write_3
            SET id2 = r2, id3=r3, id4=r4, id5=r5 WHERE id1=d1;
  WHEN 4 THEN 
         UPDATE scale_write_4
            SET id2 = r2, id3=r3, id4=r4, id5=r5 WHERE id1=d1;
  WHEN 5 THEN 
         UPDATE scale_write_5
            SET id2 = r2, id3=r3, id4=r4, id5=r5 WHERE id1=d1;
  ELSE NULL;
  END CASE; 
  IF SQL%ROWCOUNT > 0 THEN RETURN 'update all';
  ELSE RETURN NULL; END IF;
END;

FUNCTION run_update_one(tbl IN NUMBER, d1 IN NUMBER)
RETURN VARCHAR2
AS
  r NUMBER := CEIL(DBMS_RANDOM.VALUE(1000000,9999999));
BEGIN
  CASE tbl
  WHEN 1 THEN -- no index updated
         UPDATE scale_write_1 SET id2 = r WHERE id1=d1;
  WHEN 2 THEN -- one index updated
         UPDATE scale_write_2 SET id2 = r WHERE id1=d1;
  WHEN 3 THEN -- one index updated
         UPDATE scale_write_3 SET id3 = r WHERE id1=d1;
  WHEN 4 THEN -- one index updated
         UPDATE scale_write_4 SET id4 = r WHERE id1=d1;
  WHEN 5 THEN -- one index updated
         UPDATE scale_write_5 SET id5 = r WHERE id1=d1;
  ELSE NULL;
  END CASE; 
  IF SQL%ROWCOUNT > 0 THEN RETURN 'update one';
  ELSE RETURN NULL; END IF;
END;

FUNCTION run(n IN NUMBER)
  RETURN test_write_scalability.piped_output_table PIPELINED
IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  rec  test_write_scalability.piped_output;

  id1  NUMBER;
  tbl  NUMBER;
  strt TIMESTAMP(9);
  cmnd NUMBER;
  d1   NUMBER;
  q    NUMBER;
  begn NUMBER;
  iter NUMBER;
  r    NUMBER;
  tmp  DATE;

BEGIN
  SELECT CEIL((max(id1)-min(id1))/4) into q FROM scale_write_1;

  iter := n;
  WHILE iter > 0 LOOP
    FOR cmd IN 0 .. 3 LOOP
      r := TRUNC(DBMS_RANDOM.VALUE(0, q));
      FOR tbl IN 0 .. 5 LOOP
        strt := systimestamp;
        rec.cmnd := 
        CASE cmd
        WHEN 0 THEN run_update_all(tbl, r + cmd*q)
        WHEN 1 THEN run_insert    (tbl, r + cmd*q)
        WHEN 2 THEN run_update_one(tbl, r + cmd*q)
        WHEN 3 THEN run_delete    (tbl, r + cmd*q)
        END;
        IF rec.cmnd IS NOT NULL THEN
          COMMIT;
          -- magic: convert INTERVAL DAYS TO SECONDS
          -- to NUMERIC (seconds)
          tmp := sysdate;
          rec.seconds := tmp 
                       + (systimestamp - strt)*86400
                       - tmp;         
          rec.idxes   := tbl;
          rec.id1     := r + cmd*q;
          PIPE ROW(rec);
        END IF;
      END LOOP;
    END LOOP;
    iter := iter - 1;
  END LOOP;
  COMMIT;
  RETURN;
END run;
END test_write_scalability;
SELECT *
  FROM (SELECT idxes, cmnd, seconds
          FROM TABLE (test_write_scalability.run(1000)
       )
 PIVOT (AVG(seconds)
   FOR cmnd
    IN ('insert', 'delete', 'update all', 'update one')
       );
Vorherige SeiteNächste Seite

Du kannst nicht alles an einem Tag lernen. Abonniere den Newsletter via E-Mail, Bluesky oder RSS um sukzessive aufzuholen. Und sieh dir auch modern-sql.com an.

Über den Autor

Foto von Markus Winand

Markus Winand gibt auf modern-sql.com Einblick in SQL und zeigt, wie es von verschiedenen Systemen unterstützt wird. Zuvor machte er use-the-index-luke.com, was er noch immer wartet. Markus kann als Trainer, Sprecher und Berater auf winand.at engagiert werden.

Sein Buch kaufen

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

Die Essenz: SQL-Tuning auf 200 Seiten

Jetzt Kaufen
(Taschenbuch und/oder PDF)

Sein Training

Markus verwandelt veraltetes SQL-92-Wissen in solides und zeitgemäßes SQL-Know-how

Erfahren Sie mehr»

Neuigkeiten Abonnieren

Markus Winands MailinglistenMarkus Winands RSS-FeedMarkus Winand auf LinkedInMarkus Winand auf XINGMarkus Winand auf TwitterMarkus Winand auf Bluesky
Copyright 2010-2025 Markus Winand. Alle Rechte vorbehalten.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz und DSGVO