Pe urmele Highload++ Siberia 2019 — 8 sarcini pe Oracle

Bună!

În perioada 24-25 iunie, conferința Highload++ Siberia 2019 s-a desfășurat în Novosibirsk. Echipa noastră a fost și ea prezentă. cu o prezentare «Baze de date containerizate Oracle (CDB/PDB) și utilizarea lor practică în dezvoltarea software-ului», vom publica versiunea textului ceva mai târziu. A fost grozav, mulțumim. olegbunin pentru organizare, dar și tuturor celor care au venit.

În această postare, am dori să vă împărtășim sarcinile care au fost la standul nostru, pentru a putea să vă verificați cunoștințele în Oracle. Sub tag — 8 sarcini, variante de răspunsuri și explicații.

Care este valoarea maximă a secvenței pe care o vom vedea în urma execuției următorului script?

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
• Nicio valoare, va apărea o eroare.

RăspunsConform documentației Oracle (citat din 8.1.6):
În cadrul unei singure instrucțiuni SQL, Oracle va incrementa secvența doar o dată per rând. Dacă o instrucțiune conține mai multe referințe la NEXTVAL pentru o secvență, Oracle incrementează secvența o dată și returnează aceeași valoare pentru toate aparițiile lui NEXTVAL. Dacă o instrucțiune conține referințe atât la CURRVAL, cât și la NEXTVAL, Oracle incrementează secvența și returnează aceeași valoare pentru atât CURRVAL, cât și NEXTVAL, indiferent de ordinea lor în cadrul instrucțiunii.

Astfel, valoarea maximă va corespunde numărului de rânduri, adică 5..

Câte rânduri vor exista în tabel ca urmare a execuției următorului script?

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

• 0
• 3
• 4
• 5
• 6
• 9

RăspunsConform documentației Oracle (citat din 11.2):

Înainte de a executa orice instrucțiune SQL, Oracle marchează un punct de salvare implicit (neavizat pentru tine). Apoi, dacă instrucțiunea eșuează, Oracle efectuează automat rollback și returnează codul de eroare aplicabil în SQLCODE în SQLCA. De exemplu, dacă o instrucțiune INSERT cauzează o eroare prin încercarea de a insera o valoare duplicată într-un index unic, instrucțiunea este anulată.

Apelul procedurii stocate din client este, de asemenea, considerat și procesat ca o instrucțiune unică. Astfel, primul apel al procedurii se finalizează cu succes, inserând trei înregistrări; al doilea apel al procedurii se finalizează cu o eroare și anulează a patra înregistrare pe care a reușit să o insereze; al treilea apel se finalizează cu o eroare, iar în tabel rămân trei înregistrări..

Câte rânduri vor exista în tabel ca urmare a execuției următorului script?

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('Ups!');
end;
/

• 1
• 2
• 3
• 4
• 5
• 6
• 7

RăspunsConform documentației Oracle (citat din 11.2):

O constrângere de verificare vă permite să specificați o condiție pe care fiecare rând din tabel trebuie să o satisfacă. Pentru a îndeplini constrângerea, fiecare rând din tabel trebuie să facă ca condiția să fie fie ADEVĂRATĂ, fie necunoscută (din cauza unui null). Când Oracle evaluează o condiție de constrângere de verificare pentru un anumit rând, orice nume de coloană din condiție se referă la valorile coloanei din acel rând.

Astfel, valoarea null va trece verificarea, iar blocul anonim va fi executat cu succes până la încercarea de a insera valoarea 3. După aceasta, blocul de gestionare a erorilor va anula excepția, nu va exista o revocare și în tabel vor rămâne patru rânduri cu valorile 1, null, 2 și din nou null.

Ce perechi de valori vor ocupa volume de spațiu identice în 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', 'Vasile', 10, 10, 'D', 'Vasile');

• A și X
• B și Y
• C și K
• C și Z
• K și Z
• I și J
• J și X
• Toate enumerate

RăspunsSă prezentăm extrase din documentația (12.1.0.2) privind stocarea diferitelor tipuri de date în Oracle.

Tip de date CHAR
Tipul de date CHAR specifică un șir de caractere de lungime fixă în setul de caractere al bazei de date. Specificați setul de caractere al bazei de date atunci când creați baza de date. Oracle se asigură că toate valorile stocate într-o coloană CHAR au lungimea specificată de dimensiune în semantica lungimii selectate. Dacă inserați o valoare care este mai scurtă decât lungimea coloanei, atunci Oracle completează valoarea cu caractere goale până la lungimea coloanei.

Tip de date VARCHAR2
Tipul de date VARCHAR2 specifică un șir de caractere de lungime variabilă în setul de caractere al bazei de date. Specificați setul de caractere al bazei de date atunci când creați baza de date. Oracle stochează o valoare de caracter într-o coloană VARCHAR2 exact așa cum o specificați, fără niciună completare cu caractere goale, atâta timp cât valoarea nu depășește lungimea coloanei.

Tip de date NUMBER
Tipul de date NUMBER stochează zero, precum și numere fixe pozitive și negative cu valori absolute de la 1.0 x 10-130 la dar fără a include 1.0 x 10126. Dacă specificați o expresie aritmetică a cărei valoare are o valoare absolută mai mare sau egală cu 1.0 x 10126, atunci Oracle returnează o eroare. Fiecare valoare NUMBER necesită de la 1 la 22 de bytes. Ținând cont de acest lucru, dimensiunea coloanei în bytes pentru o valoare numerică particulară NUMBER(p), unde p este precizia unei valori date, poate fi calculată folosind următoarea formulă: ROUND((length(p)+s)\/2))+1 unde s este egal cu zero dacă numărul este pozitiv, iar s este egal cu 1 dacă numărul este negativ.

În plus, să luăm un extras din documentația despre stocarea valorilor Null.

Un null este absența unei valori într-o coloană. Nullurile indică date lipsă, necunoscute sau neaplicabile. Nullurile sunt stocate în baza de date dacă se află între coloane cu valori de date. În aceste cazuri, ele necesită 1 byte pentru a stoca lungimea coloanei (zero). Nullurile de la sfârșitul unui rând nu necesită stocare deoarece un nou antet de rând semnalează că coloanele rămase din rândul anterior sunt null. De exemplu, dacă ultimele trei coloane ale unui tabel sunt null, atunci nu se stochează date pentru aceste coloane.

Pe baza acestor date, formulăm raționamente. Considerăm că în Baza de Date se folosește codificarea AL32UTF8. În această codificare, literele rusești vor ocupa 2 bytes.

1) A și X, valoarea câmpului a 'Y' ocupă 1 byte, valoarea câmpului x 'D' – 2 bytes
2) B și Y, ‘Вася’ în b va fi completat cu spații până la 10 caractere și va ocupa 14 octeți, ‘Вася’ în d – va ocupa 8 octeți.
3) C și K. Ambele câmpuri au valoarea NULL, după ele există câmpuri semnificative, deci ocupă câte 1 octet.
4) C și Z. Ambele câmpuri au valoarea NULL, dar câmpul Z – este ultimul din tabel, deci nu ocupă spațiu (0 octeți). C câmpul ocupă 1 octet.
5) K și Z. Similar cazului anterior. Valoarea din câmpul K ocupă 1 octet, în Z – 0.
6) I și J. Conform documentației, ambele valori vor ocupa câte 2 octeți. Lungimea se calculează conform formula luată din documentație: round( (1 + 0) / 2) + 1 = 1 + 1 = 2.
7) J și X. Valoarea din câmpul J va ocupa 2 octeți, valoarea din câmpul X va ocupa 2 octeți.

În total, variantele corecte sunt: C și K, I și J, J și X.

Care va fi aproximativ clustering factor al indicelui 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);

• Ordinea zecilor
• Ordinea sutelor
• Ordinea miilor
• Ordinea zecilor de mii

RăspunsConform documentației Oracle (citată din 12.1):

Pentru un indice B-tree, clustering factor-ul indicelui măsoară gruparea fizică a rândurilor în relație cu o valoare de index.

Clustering factor-ul indicelui ajută optimizatorul să decidă dacă un scan pe index sau un scan complet al tabelului este mai eficient pentru anumite interogări). Un clustering factor scăzut indică un scan eficient al indexului.

Un clustering factor care este apropiat de numărul de blocuri dintr-un tabel indică faptul că rândurile sunt ordonate fizic în blocurile tabelului după cheia indexului. Dacă baza de date efectuează un scan complet al tabelului, atunci tendința bazei de date este de a recupera rândurile așa cum sunt stocate pe disc sortate după cheia indexului. Un clustering factor care este apropiat de numărul de rânduri indică faptul că rândurile sunt dispersate aleatoriu în blocurile bazei de date în relație cu cheia indexului. Dacă baza de date efectuează un scan complet al tabelului, atunci baza de date nu ar recupera rânduri în niciun ordin sortat după această cheie de index.

În acest caz, datele sunt perfect sortate, așa că clustering factor-ul va fi egal sau aproape de numărul de blocuri ocupate în tabel. Pentru dimensiunea standard a blocului de 8 kilobyte, putem anticipa că într-un bloc vor încăpea aproximativ o mie de valori de tip number subțiri, deci numărul de blocuri, și prin urmare clustering factor-ul va fi ordinea zecilor.

Pentru ce valori N următorul script va fi executat cu succes într-o bază de date obișnuită cu setări standard?

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ăspunsConform documentației Oracle (citat din 11.2):

Limitele logice ale bazei de date

Element
Tipul de limită
Valoarea limită

Indici
Dimensiunea totală a coloanei indexate
75% din dimensiunea blocului bazei de date minus unele costuri

Prin urmare, dimensiunea totală a coloanelor indexate nu trebuie să depășească 6KB. Totul depinde de encodarea bazei de date aleasă. În encodarea AL32UTF8, un caracter poate ocupa maxim 4 bytes, astfel, în 6 kilobiți, în cel mai prost caz pot încăpea aproximativ 1500 de caractere. De aceea, Oracle va interzice crearea indexului când N = 400 (când lungimea cheii în cel mai prost caz va fi 1600 de caractere * 4 bytes + lungimea rowid), în timp ce când N = 200 (și mai puțin) crearea indexului va funcționa fără probleme.

Operatorul INSERT cu hint APPEND este destinat încărcării datelor în modul direct. Ce se întâmplă dacă este aplicat pe o masă care are un trigger?

• Datele vor fi încărcate în modul direct, triggerul va funcționa așa cum trebuie
• Datele vor fi încărcate în modul direct, dar triggerul nu va fi executat
• Datele vor fi încărcate în modul conventional, triggerul va funcționa așa cum trebuie
• Datele vor fi încărcate în modul conventional, dar triggerul nu va fi executat
• Datele nu vor fi încărcate, va fi generată o eroare

RăspunsÎn principiu, aceasta este o întrebare mai mult de logică. Pentru a găsi răspunsul corect, aș propune următorul model de raționament:

1. Inserția în modul direct se realizează prin crearea directă a unui bloc de date, ocolind motorul SQL, ceea ce asigură o viteză mare. Prin urmare, asigurarea executării triggerului este foarte dificilă, dacă nu imposibilă, și nu are sens, deoarece oricum va încetini drastic inserția.
2. Nefuncționarea triggerului va duce la faptul că, având aceleași date în masă, starea bazei de date în ansamblu (a celorlalte mese) va depinde de modul în care aceste date au fost inserate. Aceasta va distruge evident integritatea datelor și nu poate fi aplicată ca soluție în producție.
3. Imposibilitatea de a executa operațiunea solicitată este, în general, interpretată ca o eroare. Dar aici trebuie să ne amintim că APPEND este un hint, iar logica generală a hinturilor este că acestea sunt luate în considerare dacă este posibil; dacă nu, operatorul este executat fără a ține cont de hint.

Prin urmare, răspunsul așteptat este datele vor fi încărcate în modul obișnuit (SQL), triggerul va funcționa.

Conform documentației Oracle (citat din 8.04):

Încălcarea restricțiilor va determina ca instrucțiunea să fie executată în mod serial, folosind calea de inserție convențională, fără avertizări sau mesaje de eroare. O excepție este restricția privind instrucțiunile care accesează aceeași masă de mai multe ori într-o tranzacție, ceea ce poate cauza mesaje de eroare.
De exemplu, dacă există declanșatoare sau integritate referențială în tabel, atunci sugestia APPEND va fi ignorată atunci când încerci să folosești INSERT cu încărcare directă (seriat sau paralel), precum și sugestia sau clauza PARALLEL, dacă există.

Ce se va întâmpla la executarea următorului script?

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

• Executare reușită
• Eșec din cauza unei erori de sintaxă
• Eroare legată de inexistența unei tranzacții autonome
• Eroare legată de depășirea nivelului maxim de apeluri încrucișate
• Eroare legată de încălcarea cheii externe
• Eroare legată de blocaje

RăspunsTabelul și declanșatorul sunt create corect și această operațiune nu ar trebui să cauzeze probleme. Tranzacțiile autonome în declanșatoare sunt, de asemenea, permise, altfel ar fi imposibil, de exemplu, să se facă logarea.

După inserarea primei linii, executarea reușită a declanșatorului ar conduce la inserarea celei de-a doua linii, ceea ce ar declanșa din nou declanșatorul, ar insera a treia linie și așa mai departe, până când instrucțiunea s-ar opri din cauza depășirii nivelului maxim de apeluri încrucișate. Totuși, există un alt aspect delicat. În momentul executării declanșatorului pentru prima înregistrare inserată, commit-ul nu a fost încă efectuat. Prin urmare, declanșatorul, care funcționează într-o tranzacție autonomă, încearcă să insereze în tabel o linie care se referă prin cheie externă la o înregistrare care nu a fost încă confirmată. Acest lucru duce la așteptare (tranzacția autonomă așteaptă commit-ul principal pentru a determina dacă datele pot fi inserate) și, în același timp, tranzacția principală așteaptă commit-ul autonom pentru a continua după declanșator. Apasă un deadlock și, ca urmare, tranzacția autonomă este anulată din cauza blocajelor.

Numai utilizatorii înregistrați pot participa la sondaj. Conectați-vă, vă rugăm.

A fost greu?

• Ca doi degete, am rezolvat totul corect din prima.

• Nu foarte, am greșit la câteva întrebări.

• Am rezolvat corect jumătate.

• Am ghicit răspunsul de două ori!

• Voi scrie în comentarii

14 utilizatori au votat. 10 utilizatori s-au abținut.

Sursa: habr.com