🥇In den Nachklängen von Highload++ Siberia 2019 — 8 Aufgaben zu Oracle

Hallo!

Vom 24. bis 25. Juni fand in Nowosibirsk die Konferenz Highload++ Siberia 2019 statt. Auch unsere Leute waren dort. mit einem Vortrag Die Präsentation „Container Datenbanken Oracle (CDB/PDB) und ihre praktische Anwendung zur Softwareentwicklung“ werden wir etwas später in schriftlicher Form bereitstellen. Es war großartig, danke. olegbunin Für die Organisation und auch an alle, die gekommen sind.

In diesem Beitrag möchten wir die Aufgaben teilen, die an unserem Stand waren, damit Sie Ihr Wissen über Oracle testen können. Unter dem Beitrag finden Sie 8 Aufgaben, Antwortmöglichkeiten und Erklärungen.

Welchen maximalen Wert des Sequences sehen wir nach der Ausführung des folgenden Skripts?

create sequence s start with 1;
 
select s.currval, s.nextval, s.currval, s.nextval, s.currval
from dual
connect by level <= 5;

1
5
10
25
Keinen, es wird ein Fehler auftreten.

AntwortLaut der Oracle-Dokumentation (zitiert aus 8.1.6):
Innerhalb einer einzigen SQL-Anweisung erhöht Oracle die Sequence nur einmal pro Zeile. Wenn eine Anweisung mehr als einen Verweis auf NEXTVAL für eine Sequence enthält, erhöht Oracle die Sequence einmal und gibt denselben Wert für alle Vorkommen von NEXTVAL zurück. Wenn eine Anweisung sowohl Verweise auf CURRVAL als auch NEXTVAL enthält, erhöht Oracle die Sequence und gibt denselben Wert für sowohl CURRVAL als auch NEXTVAL zurück, unabhängig von ihrer Reihenfolge innerhalb der Anweisung.

So wurde Der maximale Wert entspricht der Anzahl der Zeilen, also 5..

Wie viele Zeilen befinden sich in der Tabelle nach der Ausführung des folgenden Skripts?

create table t(i integer check (i < 5));
 
create procedure p(p_from integer, p_to integer) as
begin
    for i in p_from .. p_to loop
        insert into t values (i);
    end loop;
end;
/
 
exec p(1, 3);
exec p(4, 6);
exec p(7, 9);

AntwortLaut der Oracle-Dokumentation (zitiert aus 11.2):

Vor der Ausführung einer SQL-Anweisung markiert Oracle einen impliziten Savepoint (für Sie nicht verfügbar). Wenn die Anweisung fehlschlägt, wird sie automatisch zurückgesetzt, und der entsprechende Fehlercode wird SQLCODE in der SQLCA zurückgegeben. Wenn beispielsweise eine INSERT-Anweisung einen Fehler verursacht, weil sie versucht, einen doppelten Wert in einen einzigartigen Index einzufügen, wird die Anweisung zurückgesetzt.

Der Aufruf des gespeicherten Verfahrens (HP) vom Client wird ebenfalls als einzelner Befehl betrachtet und behandelt. Daher wird der erste Aufruf der HP erfolgreich abgeschlossen und fügt drei Datensätze ein; der zweite Aufruf der HP endet mit einem Fehler und rollt den vierten Datensatz zurück, der eingefügt werden konnte; der dritte Aufruf endet mit einem Fehler, und in der Tabelle befinden sich drei Datensätze..

Wie viele Zeilen befinden sich in der Tabelle nach der Ausführung des folgenden Skripts?

create table t(i integer, constraint i_ch check (i < 3));
 
begin
    insert into t values (1);
    insert into t values (null);
    insert into t values (2);
    insert into t values (null);
    insert into t values (3);
    insert into t values (null);
    insert into t values (4);
    insert into t values (null);
    insert into t values (5);
exception
    when others then
        dbms_output.put_line('Oops!');
end;
/

AntwortLaut der Oracle-Dokumentation (zitiert aus 11.2):

Eine Überprüfungseinschränkung ermöglicht es Ihnen, eine Bedingung festzulegen, die jede Zeile in der Tabelle erfüllen muss. Um die Einschränkung zu erfüllen, muss jede Zeile in der Tabelle die Bedingung entweder auf TRUE oder unbekannt (aufgrund eines null-Wertes) auswerten. Wenn Oracle eine Überprüfungseinschränkung für eine bestimmte Zeile evaluiert, beziehen sich alle Spaltennamen in der Bedingung auf die Spaltenwerte in dieser Zeile.

So wird der null-Wert die Überprüfung bestehen, und der anonyme Block wird erfolgreich bis zum Versuch ausgeführt, den Wert 3 einzufügen. Danach wird der Fehlerbehandlungsblock die Ausnahme abfangen, es wird keine Rückführung stattfinden, und in der Tabelle bleiben vier Zeilen mit den Werten 1, null, 2 und erneut null.

Welche Wertpaare belegen im Block den gleichen Platz?

create table t (
    a char(1 char),
    b char(10 char),
    c char(100 char),
    i number(4),
    j number(14),
    k number(24),
    x varchar2(1 char),
    y varchar2(10 char),
    z varchar2(100 char));
 
insert into t (a, b, i, j, x, y)
    values ('Y', 'Wanja', 10, 10, 'D', 'Wanja');

A und X
B und Y
C und K
C und Z
K und Z
I und J
J und X
Alle genannten

AntwortHier sind Auszüge aus der Dokumentation (12.1.0.2) zur Speicherung verschiedener Datentypen in Oracle.

CHAR Datentyp
Der CHAR-Datentyp gibt eine Zeichenkette fester Länge im Zeichensatz der Datenbank an. Sie geben den Zeichensatz der Datenbank an, wenn Sie Ihre Datenbank erstellen. Oracle stellt sicher, dass alle Werte, die in einer CHAR-Spalte gespeichert sind, die Länge haben, die durch die Größe in den gewählten Längensemantiken angegeben ist. Wenn Sie einen Wert einfügen, der kürzer als die Spaltenlänge ist, wird der Wert von Oracle mit Leerzeichen auf die Spaltenlänge aufgefüllt.

VARCHAR2 Datentyp
Der VARCHAR2-Datentyp gibt eine Zeichenkette variabler Länge im Zeichensatz der Datenbank an. Sie geben den Zeichensatz der Datenbank an, wenn Sie Ihre Datenbank erstellen. Oracle speichert einen Zeichenwert in einer VARCHAR2-Spalte genau so, wie Sie ihn angeben, ohne Leerzeichen, vorausgesetzt, der Wert überschreitet nicht die Länge der Spalte.

NUMBER Datentyp
Der NUMBER-Datentyp speichert null sowie positive und negative feste Zahlen mit absoluten Werten von 1,0 x 10-130 bis, aber nicht einschließlich, 1,0 x 10^126. Wenn Sie einen arithmetischen Ausdruck angeben, dessen Wert einen absoluten Wert größer oder gleich 1,0 x 10^126 hat, gibt Oracle einen Fehler zurück. Jeder NUMBER-Wert benötigt zwischen 1 und 22 Byte. Unter Berücksichtigung dessen kann die Spaltengröße in Bytes für einen bestimmten numerischen Datenwert NUMBER(p), wobei p die Genauigkeit eines bestimmten Wertes ist, mit der folgenden Formel berechnet werden: ROUND((length(p)+s)\/2))+1 wobei s null ist, wenn die Zahl positiv ist, und s eins ist, wenn die Zahl negativ ist.

Außerdem nehmen wir einen Auszug aus der Dokumentation zur Speicherung von Null-Werten.

Ein null ist das Fehlen eines Wertes in einer Spalte. Nullwerte zeigen fehlende, unbekannte oder nicht anwendbare Daten an. Nullwerte werden in der Datenbank gespeichert, wenn sie zwischen Spalten mit Datenwerten liegen. In diesen Fällen benötigen sie 1 Byte, um die Länge der Spalte (null) zu speichern. Nachfolgende null-Werte in einer Zeile benötigen keinen Speicherplatz, da ein neuer Zeilenheader signalisiert, dass die verbleibenden Spalten in der vorhergehenden Zeile null sind. Wenn zum Beispiel die letzten drei Spalten einer Tabelle null sind, wird kein Datenmaterial für diese Spalten gespeichert.

Aus diesen Daten ergeben sich Überlegungen. Wir gehen davon aus, dass in der DB die Codierung AL32UTF8 verwendet wird. In dieser Codierung belegen russische Buchstaben 2 Byte.

1) A und X, der Wert des Feldes a ‘Y’ belegt 1 Byte, der Wert des Feldes x ‘D’ – 2 Byte
2) B und Y, ‚Vasya‘ wird in b durch Leerzeichen auf 10 Zeichen ergänzt und belegt 14 Byte, ‚Vasya‘ in d – belegt 8 Byte.
3) C und K. Beide Felder haben den Wert NULL, danach gibt es bedeutende Felder, weshalb sie jeweils 1 Byte belegen.
4) C und Z. Beide Felder haben den Wert NULL, aber das Feld Z ist das letzte in der Tabelle, deshalb belegt es keinen Platz (0 Byte). Feld C belegt 1 Byte.
5) K und Z. Ähnlich wie im vorherigen Fall. Der Wert im Feld K belegt 1 Byte, im Z – 0.
6) I und J. Laut Dokumentation belegen beide Werte je 2 Byte. Die Länge berechnen wir nach der aus der Dokumentation entnommenen Formel: round((1 + 0)/2) + 1 = 1 + 1 = 2.
7) J und X. Der Wert im Feld J belegt 2 Byte, der Wert im Feld X belegt 2 Byte.

Insgesamt sind die korrekten Varianten: C und K, I und J, J und X.

Wie hoch wird ungefähr der Clustering-Faktor des Index T_I sein?

create table t (i integer);
 
insert into t select rownum from dual connect by level <= 10000;
 
create index t_i on t(i);

Im Bereich von Zehnern
Im Bereich von Hunderten
Im Bereich von Tausenden
Im Bereich von Zehntausenden

AntwortLaut der Oracle-Dokumentation (zitiert aus 12.1):

Für einen B-Baum-Index misst der Clustering-Faktor des Index die physische Gruppierung von Zeilen in Relation zu einem Indexwert.

Der Clustering-Faktor des Index hilft dem Optimierer zu entscheiden, ob ein Index-Scan oder ein vollständiger Tabellen-Scan für bestimmte Abfragen effizienter ist. Ein niedriger Clustering-Faktor zeigt einen effizienten Index-Scan an.

Ein Clustering-Faktor, der nahe der Anzahl der Blöcke in einer Tabelle liegt, deutet darauf hin, dass die Zeilen physisch in den Tabellenblöcken nach dem Indexschlüssel angeordnet sind. Wenn die Datenbank einen vollständigen Tabellen-Scan durchführt, neigt die Datenbank dazu, die Zeilen in der Reihenfolge abzurufen, wie sie auf der Festplatte sortiert sind, basierend auf dem Indexschlüssel. Ein Clustering-Faktor, der nahe der Anzahl der Zeilen liegt, zeigt an, dass die Zeilen zufällig über die Datenbankblöcke in Beziehung zum Indexschlüssel verteilt sind. Wenn die Datenbank einen vollständigen Tabellen-Scan durchführt, würde die Datenbank die Zeilen nicht in einer beliebigen sortierten Reihenfolge nach diesem Indexschlüssel abrufen.

In diesem Fall sind die Daten ideal sortiert, daher wird der Clustering-Faktor gleich oder nahe der Anzahl der belegten Blöcke in der Tabelle liegen. Bei einer Standardblockgröße von 8 Kilobyte kann man erwarten, dass in einen Block etwa tausend schmale Number-Werte passen, daher wird die Anzahl der Blöcke und folglich der Clustering-Faktor im Bereich von Zehnern.

Bei welchen Werten von N wird das folgende Skript erfolgreich in einer normalen Datenbank mit Standardkonfigurationen ausgeführt?

create table t (
    a varchar2(N char),
    b varchar2(N char),
    c varchar2(N char),
    d varchar2(N char));
 
create index t_i on t (a, b, c, d);

100
200
400
800
1600
3200
6400

AntwortLaut der Oracle-Dokumentation (zitiert aus 11.2):

Logische Datenbankgrenzen

Artikel
Art der Grenze
Grenzwert

Indizes
Gesamtgröße der indizierten Spalte
75% der Datenbankblockgröße minus einigen Overhead

Somit sollte die Gesamtlänge der indizierten Spalten 6 KB nicht überschreiten. Das Weitere hängt von der gewählten Zeichencodierung der Datenbank ab. Bei der Codierung AL32UTF8 kann ein Zeichen maximal 4 Byte belegen, sodass in 6 Kilobyte im schlimmsten Fall etwa 1500 Zeichen Platz finden. Daher wird Oracle die Erstellung eines Indexes bei N = 400 (wenn die Schlüsselgröße im schlimmsten Fall 1600 Zeichen * 4 Byte + Länge des ROWID beträgt) untersagen, während bei N = 200 (und weniger) die Erstellung eines Indexes problemlos funktioniert.

Der INSERT-Befehl mit dem Hint APPEND dient dazu, Daten im Direktmodus zu laden. Was passiert, wenn er auf eine Tabelle angewendet wird, an der ein Trigger hängt?

Die Daten werden im Direktmodus geladen, der Trigger wird wie vorgesehen ausgelöst.
Die Daten werden im Direktmodus geladen, aber der Trigger wird nicht ausgeführt.
Die Daten werden im Conventional-Modus geladen, der Trigger wird wie vorgesehen ausgelöst.
Die Daten werden im Conventional-Modus geladen, aber der Trigger wird nicht ausgeführt.
Die Daten werden nicht geladen, ein Fehler wird aufgezeichnet.

AntwortIm Prinzip ist dies eher eine Frage der Logik. Um die richtige Antwort zu finden, würde ich folgendes Denkmodell vorschlagen:

Das Einfügen im Direktmodus erfolgt durch direkte Blockgenerierung ohne SQL-Engine, was hohe Geschwindigkeit gewährleistet. Daher ist es äußerst schwierig, den Trigger auszuführen, wenn überhaupt, und es macht keinen Sinn, da es die Einfügung erheblich verlangsamen würde.
Das Nichterfüllen des Triggers führt dazu, dass der Zustand der Datenbank insgesamt (anderer Tabellen) bei identischen Daten in der Tabelle davon abhängt, in welchem Modus diese Daten eingefügt wurden. Dies würde die Datenintegrität offensichtlich gefährden und kann nicht als Lösung in der Produktion angewendet werden.
Die Unmöglichkeit, die angeforderte Operation auszuführen, wird allgemein als Fehler interpretiert. Hier sollte jedoch angemerkt werden, dass APPEND ein Hint ist, und die allgemeine Logik von Hints besagt, dass sie berücksichtigt werden, wenn möglich; wenn nicht, wird der Befehl ohne Berücksichtigung des Hints ausgeführt.

Somit ist die erwartete Antwort: Die Daten werden im normalen (SQL) Modus geladen, der Trigger wird ausgelöst.

Laut der Oracle-Dokumentation (zitiert aus 8.04):

Verstöße gegen die Beschränkungen führen dazu, dass die Anweisung seriell ausgeführt wird, indem der konventionelle Einfügepfad ohne Warnungen oder Fehlermeldungen verwendet wird. Eine Ausnahme bildet die Beschränkung von Anweisungen, die in einer Transaktion mehr als einmal auf dieselbe Tabelle zugreifen, was Fehlermeldungen auslösen kann.
Wenn beispielsweise Trigger oder referenzielle Integrität in der Tabelle vorhanden sind, wird der APPEND-Hinweis ignoriert, wenn Sie versuchen, ein direkt geladenes INSERT (seriell oder parallel) zu verwenden, sowie der PARALLEL-Hinweis oder die Klausel, falls vorhanden.

Was passiert bei der Ausführung des folgenden Skripts?

create table t(i integer not null primary key, j integer references t);
 
create trigger t_a_i after insert on t for each row
declare
    pragma autonomous_transaction;
begin
    insert into t values (:new.i + 1, :new.i);
    commit;
end;
/
 
insert into t values (1, null);

Erfolgreiche Ausführung
Fehler aufgrund eines Syntaxfehlers
Fehler, der mit der Ungültigkeit der autonomen Transaktion verbunden ist
Fehler, der mit der Überschreitung der maximalen Nestung von Aufrufen verbunden ist
Fehler, der mit der Verletzung des Fremdschlüssels verbunden ist
Fehler, der mit Blockierungen verbunden ist

AntwortDie Tabelle und der Trigger werden vollkommen korrekt erstellt, und dieser Vorgang sollte nicht zu Problemen führen. Autonome Transaktionen in Triggern sind ebenfalls erlaubt, andernfalls wäre es beispielsweise unmöglich, ein Logging durchzuführen.

Nach dem Einfügen der ersten Zeile würde das erfolgreiche Auslösen des Triggers zur Einfügung einer zweiten Zeile führen, was wiederum den Trigger auslösen und eine dritte Zeile einfügen würde, und so weiter, bis die Anweisung aufgrund der Überschreitung der maximalen Nestung von Aufrufen fehlerhaft würde. Allerdings gibt es noch einen weiteren subtilen Punkt. Zum Zeitpunkt der Ausführung des Triggers für den ersten eingefügten Datensatz ist das Commit noch nicht ausgeführt. Daher versucht der Trigger, der in einer autonomen Transaktion arbeitet, eine Zeile in die Tabelle einzufügen, die auf einen noch nicht committeten Datensatz verweist. Dies führt zu einer Blockierung (die autonome Transaktion wartet auf das Commit der Haupttransaktion, um zu verstehen, ob die Daten eingefügt werden können), während die Haupttransaktion auf das Commit der autonomen Einheit wartend bleibt, um nach dem Trigger fortzufahren. Es tritt ein Deadlock auf, und als Folge wird die autonome Transaktion wegen der Blockierungen abgebrochen..

Nur registrierte Benutzer können an der Umfrage teilnehmen. Bitte einloggen.

War es schwer?

So leicht wie zwei Finger, ich habe alles sofort richtig gelöst.
Nicht besonders, ich habe ein paar Fragen falsch beantwortet.
Ich habe die Hälfte richtig gelöst.
Ich habe die Antwort zweimal erraten!
Ich werde in den Kommentaren schreiben

14 Benutzer haben abgestimmt. 10 Benutzer haben sich enthalten.

Quelle: habr.com

Nach Highload++ Siberia 2019 - 8 Aufgaben zu Oracle

Welchen maximalen Wert des Sequences sehen wir nach der Ausführung des folgenden Skripts?

Wie viele Zeilen befinden sich in der Tabelle nach der Ausführung des folgenden Skripts?

Wie viele Zeilen befinden sich in der Tabelle nach der Ausführung des folgenden Skripts?

Welche Wertpaare belegen im Block den gleichen Platz?

Wie hoch wird ungefähr der Clustering-Faktor des Index T_I sein?

Bei welchen Werten von N wird das folgende Skript erfolgreich in einer normalen Datenbank mit Standardkonfigurationen ausgeführt?

Der INSERT-Befehl mit dem Hint APPEND dient dazu, Daten im Direktmodus zu laden. Was passiert, wenn er auf eine Tabelle angewendet wird, an der ein Trigger hängt?

Was passiert bei der Ausführung des folgenden Skripts?

War es schwer?