🥇À la suite de Highload++ Siberia 2019 — 8 tâches sur Oracle

Bonjour !

Les 24 et 25 juin à Novossibirsk s'est tenue la conférence Highload++ Siberia 2019. Nos équipes étaient également présentes. présentation «Bases de données de conteneurs Oracle (CDB/PDB) et leur utilisation pratique pour le développement de logiciels», nous publierons la version textuelle un peu plus tard. C'était génial, merci. olegbunin pour l'organisation, ainsi qu'à tous ceux qui sont venus.

Dans ce post, nous aimerions partager avec vous les tâches qui ont été présentées à notre stand, afin que vous puissiez tester vos connaissances en Oracle. Dans la suite, 8 tâches, options de réponses et explications.

Quelle est la valeur maximale que le séquenceur affichera comme résultat de l'exécution du script suivant?

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
Aucune, il y aura une erreur.

RéponseSelon la documentation Oracle (citées depuis 8.1.6):
Dans une seule instruction SQL, Oracle incrémente la séquence une seule fois par ligne. Si une instruction contient plus d'une référence à NEXTVAL pour une séquence, Oracle incrémente la séquence une fois et renvoie la même valeur pour toutes les occurrences de NEXTVAL. Si une instruction contient des références à CURRVAL et NEXTVAL, Oracle incrémente la séquence et renvoie la même valeur pour CURRVAL et NEXTVAL, peu importe leur ordre dans l'instruction.

Ainsi, la valeur maximale correspondra au nombre de lignes, soit 5..

Combien de lignes se trouveront dans la table à la suite de l'exécution du script suivant?

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);

RéponseSelon la documentation Oracle (citées depuis 11.2):

Avant d'exécuter une instruction SQL, Oracle marque un point de sauvegarde implicite (non accessible pour vous). Ensuite, si l'instruction échoue, Oracle revient automatiquement en arrière et renvoie le code d'erreur applicable à SQLCODE dans le SQLCA. Par exemple, si une instruction INSERT cause une erreur en essayant d'insérer une valeur dupliquée dans un index unique, l'instruction est annulée.

L'appel de la procédure stockée depuis le client est également considéré et traité comme une instruction unique. Ainsi, le premier appel est exécuté avec succès, insérant trois enregistrements ; le deuxième appel échoue avec une erreur et annule le quatrième enregistrement qu'il a réussi à insérer ; le troisième appel échoue avec une erreur, et la table contient donc trois enregistrements..

Combien de lignes se trouveront dans la table à la suite de l'exécution du script suivant?

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;
/

RéponseSelon la documentation Oracle (citées depuis 11.2):

Une contrainte de vérification vous permet de spécifier une condition que chaque ligne de la table doit satisfaire. Pour satisfaire la contrainte, chaque ligne de la table doit rendre la condition soit VRAIE, soit inconnue (en raison d'un null). Lorsque Oracle évalue une condition de contrainte de vérification pour une ligne particulière, les noms de colonnes dans la condition font référence aux valeurs de colonne de cette ligne.

Ainsi, la valeur null passera le contrôle, et le bloc anonyme s'exécutera avec succès jusqu'à la tentative d'insertion de la valeur 3. Après cela, le bloc de gestion des erreurs générera une exception, il n'y aura pas de retour en arrière et il restera quatre lignes dans la table avec les valeurs 1, null, 2 et à nouveau null.

Quelles paires de valeurs occuperont des volumes de place identiques dans le bloc ?

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', 'Vanya', 10, 10, 'D', 'Vanya');

A et X
B et Y
C et K
C et Z
K et Z
I et J
J et X
Tous les énumérés

RéponseDonnons des extraits de la documentation (12.1.0.2) sur le stockage des différents types de données dans Oracle.

TYPE DE DONNÉES CHAR
Le type de données CHAR spécifie une chaîne de caractères de longueur fixe dans le jeu de caractères de la base de données. Vous spécifiez le jeu de caractères de la base de données lorsque vous créez votre base de données. Oracle garantit que toutes les valeurs stockées dans une colonne CHAR ont la longueur spécifiée par la taille dans la sémantique de longueur choisie. Si vous insérez une valeur qui est plus courte que la longueur de la colonne, alors Oracle remplira la valeur avec des espaces jusqu'à la longueur de la colonne.

TYPE DE DONNÉES VARCHAR2
Le type de données VARCHAR2 spécifie une chaîne de caractères de longueur variable dans le jeu de caractères de la base de données. Vous spécifiez le jeu de caractères de la base de données lorsque vous créez votre base de données. Oracle stocke une valeur de caractère dans une colonne VARCHAR2 exactement comme vous l'avez spécifiée, sans aucun remplissage, à condition que la valeur ne dépasse pas la longueur de la colonne.

TYPE DE DONNÉES NUMBER
Le type de données NUMBER stocke zéro ainsi que des nombres fixes positifs et négatifs avec des valeurs absolues allant de 1.0 x 10-130 à mais n'incluant pas 1.0 x 10126. Si vous spécifiez une expression arithmétique dont la valeur a une valeur absolue supérieure ou égale à 1.0 x 10126, alors Oracle renvoie une erreur. Chaque valeur NUMBER nécessite entre 1 et 22 octets. En tenant compte de cela, la taille de colonne en octets pour une valeur numérique particulière NUMBER(p), où p est la précision d'une valeur donnée, peut être calculée à l'aide de la formule suivante : ROUND((length(p)+s) / 2))+1 où s est égal à zéro si le nombre est positif, et s est égal à 1 si le nombre est négatif.

De plus, prenons un extrait de la documentation concernant le stockage des valeurs Null.

Un null est l'absence de valeur dans une colonne. Les Nulls indiquent des données manquantes, inconnues ou non applicables. Les Nulls sont stockés dans la base de données s'ils se situent entre des colonnes avec des valeurs de données. Dans ces cas, ils nécessitent 1 octet pour stocker la longueur de la colonne (zéro). Les nulls en fin de ligne ne nécessitent aucun stockage car un nouvel en-tête de ligne signale que les colonnes restantes dans la ligne précédente sont null. Par exemple, si les trois dernières colonnes d'une table sont nulles, alors aucune donnée n'est stockée pour ces colonnes.

En nous basant sur ces données, nous développons des raisonnements. Nous supposons que la base de données utilise le codage AL32UTF8. Dans ce codage, les lettres russes occuperont 2 octets.

1) A et X, la valeur du champ a ‘Y’ occupe 1 octet, la valeur du champ x ‘D’ – 2 octets
2) B et Y, ‘Vanya’ dans b sera complété par des espaces jusqu'à 10 caractères et occupera 14 octets, ‘Vanya’ dans d occupera 8 octets.
3) C et K. Les deux champs ont une valeur NULL, après eux il y a des champs significatifs, donc ils occupent chacun 1 octet.
4) C et Z. Les deux champs ont une valeur NULL, mais le champ Z est le dernier dans la table, donc il n’occupe pas de place (0 octet). Le champ C occupe 1 octet.
5) K et Z. Comme dans le cas précédent. La valeur dans le champ K occupe 1 octet, dans Z – 0.
6) I et J. Selon la documentation, les deux valeurs prendront chacune 2 octets. La longueur est calculée selon la formule tirée de la documentation : round( (1 + 0) / 2) + 1 = 1 + 1 = 2.
7) J et X. La valeur dans le champ J occupera 2 octets, la valeur dans le champ X occupera 2 octets.

En tout, les options correctes sont : C et K, I et J, J et X.

Quel sera environ le facteur de clustering de l'index T_I ?

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

De l'ordre de dizaines
De l'ordre de centaines
De l'ordre de milliers
De l'ordre de dizaines de milliers

RéponseSelon la documentation Oracle (cité de 12.1) :

Pour un index B-arbre, le facteur de clustering de l'index mesure le regroupement physique des lignes par rapport à une valeur d'index.

Le facteur de clustering de l'index aide l'optimiseur à décider si un scan d'index ou un scan complet de table est plus efficace pour certaines requêtes). Un faible facteur de clustering indique un scan d'index efficace.

Un facteur de clustering proche du nombre de blocs dans une table indique que les lignes sont physiquement ordonnées dans les blocs de la table par la clé de l'index. Si la base de données effectue un scan complet de la table, alors elle tend à récupérer les lignes telles qu'elles sont stockées sur le disque triées par la clé de l'index. Un facteur de clustering proche du nombre de lignes indique que les lignes sont dispersées de manière aléatoire à travers les blocs de la base de données par rapport à la clé de l'index. Si la base de données effectue un scan complet de la table, alors elle ne récupérerait pas les lignes dans un ordre trié par cette clé d'index.

Dans ce cas, les données sont parfaitement triées, donc le facteur de clustering sera égal ou proche du nombre de blocs occupés dans la table. Pour une taille de bloc standard de 8 kilooctets, on peut s'attendre à ce qu'un bloc contienne environ mille valeurs de type number étroites, donc le nombre de blocs, et en conséquence le facteur de clustering sera de l'ordre de dizaines.

Pour quelles valeurs de N le script suivant s'exécutera-t-il avec succès dans une base de données normale avec les paramètres par défaut ?

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

RéponseSelon la documentation Oracle (citées depuis 11.2):

Limites logiques de la base de données

Article
Type de limite
Valeur limite

Indexes
Taille totale de la colonne indexée
75 % de la taille du bloc de la base de données moins certaines surcharges

Ainsi, la taille totale des colonnes indexées ne doit pas dépasser 6 Ko. Tout le reste dépend de l'encodage choisi pour la base de données. Pour l'encodage AL32UTF8, un caractère peut occuper un maximum de 4 octets, donc dans 6 kilooctets, au pire des cas, il y a place pour environ 1500 caractères. Par conséquent, Oracle interdira la création d'un index lorsque N = 400 (lorsque la longueur de la clé dans le pire des cas sera de 1600 caractères * 4 octets + la longueur du rowid), tandis que pour N = 200 (et moins) la création de l'index se fera sans problème.

L'instruction INSERT avec l'indice APPEND est destinée à charger des données en mode direct. Que se passera-t-il si elle est appliquée à une table sur laquelle un trigger est suspendu ?

Les données seront chargées en mode direct, le trigger se déclenchera comme prévu
Les données seront chargées en mode direct, mais le trigger ne sera pas exécuté
Les données seront chargées en mode conventionnel, le trigger se déclenchera comme prévu
Les données seront chargées en mode conventionnel, mais le trigger ne sera pas exécuté
Les données ne seront pas chargées, une erreur sera enregistrée

RéponseEn principe, c'est une question plus logique. Pour trouver la bonne réponse, je proposerais le modèle de raisonnement suivant :

L'insertion en mode direct se fait par la formation directe d'un bloc de données, sans passer par le moteur SQL, ce qui assure une grande vitesse. Ainsi, il est très difficile, voire impossible, d'assurer l'exécution du trigger, et cela n'a pas de sens, car cela ralentirait de toute façon l'insertion de manière radicale.
Le non-exécution du trigger entraînera le fait qu'avec des données identiques dans la table, l'état de la base dans son ensemble (d'autres tables) dépendra du mode dans lequel ces données ont été insérées. Cela détruira manifestement l'intégrité des données et ne peut pas être utilisé comme solution en production.
L'impossibilité d'exécuter l'opération demandée est généralement interprétée comme une erreur. Mais il faut se rappeler que APPEND est un indice, et la logique générale des indices est qu'ils sont pris en compte si possible, sinon l'instruction s'exécute sans tenir compte de l'indice.

Ainsi, la réponse attendue est les données seront chargées en mode normal (SQL), le trigger se déclenchera.

Selon la documentation d'Oracle (citée à partir de 8.04) :

Les violations des restrictions entraîneront l'exécution de l'instruction en série, en utilisant le chemin d'insertion conventionnel, sans avertissements ni messages d'erreur. Une exception est la restriction sur les instructions accédant à la même table plus d'une fois dans une transaction, ce qui peut entraîner des messages d'erreur.
Par exemple, si des déclencheurs ou une intégrité référentielle sont présents sur la table, l'indice APPEND sera ignoré lorsque vous essayez d'utiliser l'INSERT de chargement direct (série ou parallèle), ainsi que l'indice ou clause PARALLEL, le cas échéant.

Que se passera-t-il lors de l'exécution du script suivant?

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);

Exécution réussie
Échec en raison d'une erreur de syntaxe
Erreur liée à l'impossibilité de la transaction autonome
Erreur liée au dépassement de la profondeur d'appel maximale
Erreur liée à la violation de la clé étrangère
Erreur liée aux blocages

RéponseLa table et le déclencheur sont créés tout à fait correctement et cette opération ne devrait pas poser de problèmes. Les transactions autonomes dans le déclencheur sont également autorisées, sinon il serait impossible, par exemple, de faire du logging.

Après l'insertion de la première ligne, l'activation réussie du déclencheur entraînerait l'insertion d'une deuxième ligne, ce qui déclencherait à nouveau le déclencheur, insérerait une troisième ligne, et ainsi de suite jusqu'à ce que l'instruction échoue en raison du dépassement de la profondeur d'appel maximale. Cependant, il y a un autre point délicat. Au moment de l'exécution du déclencheur pour la première ligne insérée, le commit n'a pas encore été effectué. Par conséquent, le déclencheur fonctionnant dans une transaction autonome tente d'insérer dans la table une ligne se référant par clé étrangère à un enregistrement encore non engagé. Cela entraîne un blocage (la transaction autonome attend le commit principal pour savoir si elle peut insérer des données) et en même temps, la transaction principale attend le commit autonome pour continuer après le déclencheur. Un deadlock se produit et par conséquent, la transaction autonome est annulée en raison de problèmes liés aux blocages..

Seuls les utilisateurs enregistrés peuvent participer au sondage. Connectez-vous, s'il vous plaît.

C'était compliqué?

Comme deux doigts dans le nez, j'ai résolu tout correctement.
Pas vraiment, je me suis trompé sur quelques questions.
J'ai résolu la moitié correctement.
J'ai deviné la réponse deux fois!
J'écrirai dans les commentaires

14 utilisateurs ont voté. 10 utilisateurs se sont abstenus.

Source : habr.com

À la suite de Highload++ Sibérie 2019 — 8 tâches sur Oracle

Quelle est la valeur maximale que le séquenceur affichera comme résultat de l'exécution du script suivant?

Combien de lignes se trouveront dans la table à la suite de l'exécution du script suivant?

Combien de lignes se trouveront dans la table à la suite de l'exécution du script suivant?

Quelles paires de valeurs occuperont des volumes de place identiques dans le bloc ?

Quel sera environ le facteur de clustering de l'index T_I ?

Pour quelles valeurs de N le script suivant s'exécutera-t-il avec succès dans une base de données normale avec les paramètres par défaut ?

L'instruction INSERT avec l'indice APPEND est destinée à charger des données en mode direct. Que se passera-t-il si elle est appliquée à une table sur laquelle un trigger est suspendu ?

Que se passera-t-il lors de l'exécution du script suivant?

C'était compliqué?