Oracle Skripts für „Die Where-Klausel“

Der Gleichheitsoperator

Künstliche Schlüssel

Das folgende Skript erstellt die EMPLOYEES Tabelle mit 1000 Einträgen.

CREATE TABLE employees (
   employee_id   NUMBER         NOT NULL,
   first_name    VARCHAR2(1000) NOT NULL,
   last_name     VARCHAR2(1000) NOT NULL,
   date_of_birth DATE           NOT NULL,
   phone_number  VARCHAR2(1000) NOT NULL,
   CONSTRAINT employees_pk PRIMARY KEY (employee_id)
);

INSERT INTO employees (employee_id,  first_name,
                       last_name,    date_of_birth, 
                       phone_number)
SELECT level, 
       DBMS_RANDOM.STRING('u', 1) || 
            DBMS_RANDOM.STRING('l', DBMS_RANDOM.value(2,10)),
       DBMS_RANDOM.STRING('u', 1) || 
            DBMS_RANDOM.STRING('l', DBMS_RANDOM.value(2,10)),
       SYSDATE - (DBMS_RANDOM.normal() * 365 * 10) - 40 * 365,
       TRUNC(DBMS_RANDOM.VALUE(1000,10000))
  FROM DUAL 
  CONNECT BY level <= 1000;

UPDATE employees 
   SET first_name='MARKUS', 
       last_name='WINAND'
 WHERE employee_id=123;

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;

Bemerkungen:

• Die JUNK-Spalte wird verwendet, um eine realistische Zeilenlänge zu erhalten. Im Gegensatz zu VARCHAR2 belegt der CHAR-Datentyp immer den angegebenen Platz ‒ 1000 Byte in diesem Fall. Ohne diese Spalte wäre die Tabelle unrealistisch klein und viele Beispiele würden nicht funktionieren.

• Die Tabelle wird mit Zufallsdaten gefüllt. Danach wird mein Eintrag durch ein update geändert.

• Die Tabellen und Index Statistiken werden erstellt, sodass der Optimizer einen Überblick über den Tabelleninhalt hat.

Zusammengesetzte Schlüssel

Das nächste Skript ändert die EMPLOYEES Tabelle so, dass sie die Situation nach der Zusammenlegung mit „Sehr Große Firma“ widerspiegelt.

-- add subsidiary_id and update existing records
ALTER TABLE employees ADD subsidiary_id NUMBER;

UPDATE      employees SET subsidiary_id = 30;

ALTER TABLE employees MODIFY subsidiary_id NOT NULL;

-- change the PK
ALTER TABLE employees DROP PRIMARY KEY;

ALTER TABLE employees ADD CONSTRAINT employees_pk 
      PRIMARY KEY (employee_id, subsidiary_id);

-- generate more records (Very Big Company)
INSERT INTO employees (employee_id,  first_name, 
                       last_name,    date_of_birth, 
                       phone_number, subsidiary_id)
SELECT level, 
       DBMS_RANDOM.STRING('u', 1) || 
            DBMS_RANDOM.STRING('l', DBMS_RANDOM.value(2,10)),
       DBMS_RANDOM.STRING('u', 1) || 
            DBMS_RANDOM.STRING('l', DBMS_RANDOM.value(2,10)),
       SYSDATE - (DBMS_RANDOM.normal() * 365 * 10) - 40 * 365,
       TRUNC(DBMS_RANDOM.VALUE(1000,10000)), 
       TRUNC(DBMS_RANDOM.VALUE(1,level/9000*29))
FROM DUAL CONNECT BY level <= 9000;

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;

Bemerkungen:

• Der Primärschlüssel wurde um die SUBSIDIARY_ID Spalte erweitert. Die EMPLOYEE_ID Spalte bleibt an erster Stelle.

• Die neuen Einträge werden zufällig auf die Töchter 1 bis 29 aufgeteilt.

• Die Tabelle und Indizes werden neu analysiert, damit der Optimizer das neue Mengengerüst kennt.

Das nächste Skript erstellt den Index auf SUBSIDIARY_ID, um Abfragen nach allen Angestellten eines bestimmten Subunternehmens zu unterstützen:

CREATE INDEX emp_sub_id ON employees (subsidiary_id);

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;

Bemerkungen:

• Die Tabelle und Indizes werden erneut analysiert. In diesem Fall wäre es ausreichend, nur den neuen Index zu analysieren.

Obwohl der Index gute Antwortzeiten ermöglicht, ist es besser den Primärschlüssel-Index umzubauen, sodass er diese Abfrage ebenfalls unterstützen kann:

Oracle 11g

-- use tmp index to support the PK
CREATE INDEX employee_pk_tmp 
    ON employees (subsidiary_id, employee_id, 1);

ALTER TABLE employees 
      MODIFY CONSTRAINT employees_pk 
      USING INDEX employee_pk_tmp;

-- recreate the pk index as needed (automatically done)
--DROP   INDEX employee_pk;

CREATE UNIQUE INDEX employee_pk 
    ON employees (subsidiary_id, employee_id);

-- change the constraint to use the new index
ALTER TABLE employees 
      MODIFY CONSTRAINT employees_pk 
      USING INDEX employee_pk;

-- drop old indexes
DROP INDEX employee_pk_tmp;

DROP INDEX emp_sub_id;

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;

Oracle 12c

CREATE UNIQUE INDEX employee_pk_new 
    ON employees (subsidiary_id, employee_id);

ALTER TABLE employees 
      MODIFY CONSTRAINT employees_pk 
      USING INDEX employee_pk_new;

-- drop old indexes
DROP INDEX emp_sub_id;

-- note: employee_pk is automatically dropped

-- rename new PK index
ALTER INDEX employee_pk_new RENAME TO employee_pk;

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;

Bemerkungen:

• Ein temporärer Index mit einer Dummy-Spalte wird angelegt, um den Primärschlüssel zu unterstützen.

  Dieser Index wird benötigt, da die Oracle Datenbank es nicht erlaubt zwei Indizes mit denselben Spalten anzulegen (nicht einmal, wenn die Spaltenreihenfolge anders ist!).

• Sobald der alte Primärschlüssel-Index nicht mehr für den Constraint benötigt wird, kann er gelöscht werden. Anschliessend wird ein neuer Index mit der neuen Spaltenreihenfolge angelegt.

• Der Constraint wird auf den neuen Index geändert. Der Temporärindex, und auch der Index auf SUBSIDIARY_ID, können gelöscht werden.

Langsame Indizes, Teil II

Das folgende Statement löscht die Statistiken, damit die Demonstration funktioniert.

BEGIN
      DBMS_STATS.DELETE_COLUMN_STATS
       (null, 'EMPLOYEES', 'SUBSIDIARY_ID');
END

Die Statistiken können mit derselben Methode wie zuvor wieder angelegt werden.

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END

Zuletzt noch der Index auf LAST_NAME, inklusive Statistiken.

CREATE INDEX emp_name ON employees (last_name)

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END

Funktionen

Groß- und Kleinschreibung ignorieren

Die zufälligen Einträge wurden bereits mit Groß-, Kleinschreibung angelegt. Nur mein Eintrag muss korrigiert werden.

UPDATE employees 
   SET first_name = 'Markus'
     , last_name  = 'Winand'
 WHERE employee_id   = 123
   AND subsidiary_id = 30;;

Den Funktionsbasierten-Index anlegen:

CREATE INDEX emp_up_name ON employees (UPPER(last_name));;
DROP INDEX emp_name;;

Bemerkungen:

• Ich verstoße mit Absicht gegen meine Gewohnheit die Tabelle und alle Indizes neu zu analysieren. Ab Oracle 10g wird der neue Index automatisch analysiert.

Das folgende Statement wird, ab Version 11g, auch die erweiterten Statistiken auf dem Funktionsbasierten-Index sammeln.

BEGIN
     DBMS_STATS.GATHER_TABLE_STATS(null, 'EMPLOYEES', 
     METHOD_OPT=>'for all indexed columns', CASCADE => true);
END;
/

Benutzerdefinierte Funktionen

Die folgende PL/SQL-Funktion berechnet das Alter. Die Verwendung dieser Funktion in einem Index ist unzulässig.

CREATE FUNCTION get_age(date_of_birth DATE) 
RETURN NUMBER
AS
BEGIN
    RETURN TRUNC(MONTHS_BETWEEN(SYSDATE, DATE_OF_BIRTH)/12);
END;
/

CREATE INDEX invalid ON EMPLOYEES (get_age(date_of_birth));

Das CREATE INDEX Statement sollte den Fehler „ORA-30553: The function is not deterministic“ liefern.

Nach Bereichen Suchen

Den EMP_TEST Index anlegen:

CREATE INDEX emp_test
     ON employees (date_of_birth, subsidiary_id);;

Und mit der besseren Spalten-Reihenfolge:

CREATE INDEX emp_test
     ON employees (subsidiary_id, date_of_birth);;

NULL Indizieren

Das folgende Statement stellt den ursprünglichen Index wieder her, nachdem Du das Beispiel aus dem Buch gemacht hast.

-- for demo purpose we drop the NOT NULL constraint
ALTER TABLE employees MODIFY date_of_birth NULL;;
CREATE INDEX emp_dob ON employees (date_of_birth);;

DROP INDEX emp_dob_upname;;

CREATE INDEX emp_dob ON employees (date_of_birth, '1');;

Partielle Indizes emulieren

CREATE TABLE messages AS
SELECT level id
     , CASE WHEN DBMS_RANDOM.NORMAL() < 0.09 THEN 'N' ELSE 'Y' END processed
     , trunc(DBMS_RANDOM.VALUE(0,100)) receiver
     , RPAD('junk', 200) message
  FROM dual 
CONNECT BY level < 999999;;

Man muss unbedingt Statistiken sammeln, damit der Funktions-basierende Index benutzt wird. Ohne Statistiken, kein CBO und kein FBI.

begin
   DBMS_STATS.GATHER_TABLE_STATS( user
                                ,'MESSAGES'
                                , cascade=>true);
end;
/