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

Hi!

În perioada 24-25 iunie, la Novosibirsk a avut loc conferința Highload++ Siberia 2019. Băieții noștri au fost și ei acolo raport „Bazele de date de containere Oracle (CDB/PDB) și utilizarea lor practică pentru dezvoltarea de software”, vom posta o versiune text puțin mai târziu. A fost misto, multumesc olegbunin pentru organizație, precum și pentru toți cei care au venit.

În această postare, am dori să vă împărtășim problemele pe care le-am avut la standul nostru, astfel încât să vă puteți testa cunoștințele Oracle. Sub tăietură sunt 8 probleme, opțiuni de răspuns și explicații.

Care este valoarea maximă a secvenței pe care o vom vedea ca rezultat al executării 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
• Nu, va fi o eroare

RăspundeConform documentației Oracle (citat din 8.1.6):
Într-o singură instrucțiune SQL, Oracle va incrementa secvența o singură dată pe 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 atât pentru CURRVAL, cât și pentru NEXTVAL, indiferent de ordinea acestora în cadrul instrucțiunii.

Astfel, valoarea maximă va corespunde numărului de linii, adică 5.

Câte rânduri vor fi în tabel ca urmare a rulării 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ăspundeConform documentației Oracle (citat din 11.2):

Înainte de a executa orice instrucțiune SQL, Oracle marchează un punct de salvare implicit (nu este disponibil pentru dvs.). Apoi, dacă instrucțiunea eșuează, Oracle o derulează înapoi automat și returnează codul de eroare aplicabil la SQLCODE din SQLCA. De exemplu, dacă o instrucțiune INSERT provoacă o eroare încercând să insereze o valoare duplicată într-un index unic, instrucțiunea este anulată.

Apelarea HP de la client este, de asemenea, considerată și procesată ca o singură declarație. Astfel, primul apel HP se finalizează cu succes, având introduse trei înregistrări; al doilea apel HP se încheie cu o eroare și derulează înapoi a patra înregistrare pe care a reușit să o introducă; al treilea apel eșuează, și există trei înregistrări în tabel.

Câte rânduri vor fi în tabel ca urmare a rulării 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('Oops!');
end;
/

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

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

O constrângere de verificare vă permite să specificați o condiție pe care trebuie să o îndeplinească fiecare rând din tabel. Pentru a satisface constrângerea, fiecare rând din tabel trebuie să facă condiția fie TRUE, fie necunoscută (din cauza unui nul). 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 inserare a valorii 3. După aceasta, blocul de tratare a erorilor va șterge excepția, nu va avea loc nicio derulare înapoi și vor rămâne patru rânduri în tabel cu valorile 1, nul, 2 și din nou nul.

Ce perechi de valori vor ocupa aceeași cantitate de spațiu î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', 'Вася', 10, 10, 'Д', 'Вася');

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

RăspundeIată fragmente din documentația (12.1.0.2) despre stocarea diferitelor tipuri de date în Oracle.

Tip de date CHAR
Tipul de date CHAR specifică un șir de caractere cu lungime fixă ​​în setul de caractere al bazei de date. Specificați setul de caractere al bazei de date atunci când vă 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ă introduceți o valoare care este mai scurtă decât lungimea coloanei, atunci Oracle completează valoarea la lungimea coloanei.

VARCHAR2 Tip de date
Tipul de date VARCHAR2 specifică un șir de caractere cu lungime variabilă în setul de caractere al bazei de date. Specificați setul de caractere al bazei de date atunci când vă creați baza de date. Oracle stochează o valoare de caracter într-o coloană VARCHAR2 exact așa cum o specificați, fără nicio completare goală, cu condiția ca valoarea să nu depășească lungimea coloanei.

NUMBER Tip de date
Tipul de date NUMĂR stochează zero, precum și numere fixe pozitive și negative cu valori absolute de la 1.0 x 10-130 până la 1.0 x 10126, dar fără a include. Dacă specificați o expresie aritmetică a cărei valoare are o valoare absolută mai mare sau egală cu 1.0 x 10126, apoi Oracle returnează o eroare. Fiecare valoare NUMĂR necesită de la 1 la 22 de octeți. Ținând cont de acest lucru, dimensiunea coloanei în octeți pentru o anumită valoare a datelor numerice NUMĂR(p), unde p este precizia unei valori date, poate fi calculată folosind următoarea formulă: ROUND((lungime(p)+s)/2))+1 unde s este egal cu zero dacă numărul este pozitiv și 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 nul este absența unei valori într-o coloană. Nulurile indică date lipsă, necunoscute sau inaplicabile. Nulurile sunt stocate în baza de date dacă se încadrează între coloanele cu valori de date. În aceste cazuri, au nevoie de 1 octet pentru a stoca lungimea coloanei (zero). Nulele finale dintr-un rând nu necesită stocare, deoarece un nou antet de rând semnalează că coloanele rămase din rândul anterior sunt nule. De exemplu, dacă ultimele trei coloane ale unui tabel sunt nule, atunci nu sunt stocate date pentru aceste coloane.

Pe baza acestor date, construim raționament. Presupunem că baza de date utilizează codificarea AL32UTF8. În această codificare, literele rusești vor ocupa 2 octeți.

1) A și X, valoarea câmpului a „Y” are 1 octet, valoarea câmpului x „D” ia 2 octeți
2) B și Y, „Vasya” în b valoarea va fi completată cu spații de până la 10 caractere și va lua 14 octeți, „Vasya” în d va lua 8 octeți.
3) C și K. Ambele câmpuri au valoarea NULL, după ele apar câmpuri semnificative, deci ocupă 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âmpul C ocupă 1 octet.
5) K și Z. Similar cu cazul precedent. Valoarea din câmpul K ocupă 1 octet, în Z – 0.
6) I și J. Conform documentației, ambele valori vor lua 2 octeți. Calculăm lungimea folosind formula luată din documentație: round( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J și X. Valoarea din câmpul J va lua 2 octeți, valoarea din câmpul X va lua 2 octeți.

În total, opțiunile corecte sunt: ​​C și K, I și J, J și X.

Care va fi aproximativ factorul de grupare 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);

• Cam zeci
• Cam sute
• Cam mii
• Aproximativ zeci de mii

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

Pentru un index B-tree, factorul de grupare a indicilor măsoară gruparea fizică a rândurilor în raport cu o valoare a indicelui.

Factorul de grupare a indexului îl ajută pe optimizator să decidă dacă o scanare a indexului sau o scanare completă a tabelului este mai eficientă pentru anumite interogări). Un factor de grupare scăzut indică o scanare eficientă a indexului.

Un factor de grupare care este aproape de numărul de blocuri dintr-un tabel indică faptul că rândurile sunt ordonate fizic în blocurile de tabel după cheia de index. Dacă baza de date efectuează o scanare completă a tabelului, atunci baza de date tinde să recupereze rândurile pe măsură ce sunt stocate pe disc, sortate după cheia de index. Un factor de grupare care este aproape de numărul de rânduri indică faptul că rândurile sunt împrăștiate aleatoriu în blocurile bazei de date în raport cu cheia de index. Dacă baza de date efectuează o scanare completă a tabelului, atunci baza de date nu ar prelua rândurile în nicio ordine sortată după această cheie de index.

În acest caz, datele sunt sortate în mod ideal, astfel încât factorul de grupare va fi egal sau apropiat de numărul de blocuri ocupate din tabel. Pentru o dimensiune standard de bloc de 8 kiloocteți, vă puteți aștepta ca aproximativ o mie de valori numerice înguste să se încadreze într-un singur bloc, astfel încât numărul de blocuri și, ca urmare, factorul de grupare va fi cam zeci.

La ce valori de N va fi executat cu succes următorul script î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ăspundeConform documentației Oracle (citat din 11.2):

Limitele bazei de date logice

Articol
Tip de limită
Valoarea limită

Indexuri
Dimensiunea totală a coloanei indexate
75% din dimensiunea blocului bazei de date minus o suprasarcină

Astfel, dimensiunea totală a coloanelor indexate nu trebuie să depășească 6Kb. Ce se întâmplă în continuare depinde de codificarea de bază selectată. Pentru codificarea AL32UTF8, un caracter poate ocupa maximum 4 octeți, așa că în cel mai rău caz, aproximativ 6 de caractere se vor încadra în 1500 kiloocteți. Prin urmare, Oracle va interzice crearea de index la N = 400 (când lungimea cheii în cel mai rău caz este de 1600 de caractere * 4 octeți + lungimea rowid), în timp ce la N = 200 (sau mai puțin) crearea indexului va funcționa fără probleme.

Operatorul INSERT cu indicația APPEND este conceput pentru a încărca date în modul direct. Ce se întâmplă dacă este aplicat pe masa de care atârnă declanșatorul?

• Datele vor fi încărcate în modul direct, declanșatorul va funcționa conform așteptărilor
• Datele vor fi încărcate în modul direct, dar declanșatorul nu va fi executat
• Datele vor fi încărcate în modul convențional, declanșatorul va funcționa așa cum ar trebui
• Datele vor fi încărcate în modul convențional, dar declanșatorul nu va fi executat
• Datele nu vor fi încărcate, va fi înregistrată o eroare

RăspundePractic, aceasta este mai mult o chestiune de logică. Pentru a găsi răspunsul corect, aș sugera următorul model de raționament:

1. Inserarea în modul direct se realizează prin formarea directă a unui bloc de date, ocolind motorul SQL, care asigură viteză mare. Astfel, asigurarea execuției declanșatorului este foarte dificilă, dacă nu imposibilă, și nu are rost în acest sens, deoarece încă va încetini radical inserția.
2. Neexecutarea declanșatorului va duce la faptul că, dacă datele din tabel sunt aceleași, starea bazei de date în ansamblu (alte tabele) va depinde de modul în care au fost introduse aceste date. Acest lucru va distruge în mod evident integritatea datelor și nu poate fi aplicat ca soluție în producție.
3. Incapacitatea de a efectua operația solicitată este, în general, tratată ca o eroare. Dar aici ar trebui să ne amintim că APPEND este un indiciu, iar logica generală a indicii este că acestea sunt luate în considerare dacă este posibil, dar dacă nu, operatorul este executat fără a lua în considerare indiciu.

Deci răspunsul așteptat este datele vor fi încărcate în modul normal (SQL), declanșatorul se va declanșa.

Conform documentației Oracle (citat din 8.04):

Încălcările restricțiilor vor face ca instrucțiunea să fie executată în serie, folosind calea de inserare convențională, fără avertismente sau mesaje de eroare. O excepție este restricția privind accesul de mai multe ori la același tabel în cadrul unei tranzacții, ceea ce poate provoca mesaje de eroare.
De exemplu, dacă declanșatorii sau integritatea referențială sunt prezenți pe tabel, atunci indicația APPEND va fi ignorată atunci când încercați să utilizați INSERT cu încărcare directă (serial sau paralel), precum și sugestia sau clauza PARALLEL, dacă există.

Ce se va întâmpla când va fi executat următorul 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);

• Finalizarea cu succes
• Eșec din cauza unei erori de sintaxă
• Eroare: tranzacția autonomă nu este validă
• Eroare legată de depășirea imbricației maxime a apelurilor
• Eroare de încălcare a cheii externe
• Eroare legată de încuietori

RăspundeTabelul și declanșatorul sunt create destul de corect și această operațiune nu ar trebui să ducă la probleme. Tranzacțiile autonome într-un declanșator sunt de asemenea permise, altfel înregistrarea nu ar fi posibilă, de exemplu.

După inserarea primului rând, o declanșare reușită a declanșatorului ar determina inserarea celui de-al doilea rând, determinând declanșarea din nou, inserarea unui al treilea rând și așa mai departe până când declarația nu a eșuat din cauza depășirii imbricației maxime de apeluri. Cu toate acestea, un alt punct subtil intră în joc. În momentul în care declanșatorul este executat, commit-ul nu a fost încă finalizat pentru prima înregistrare inserată. Prin urmare, un declanșator care rulează într-o tranzacție autonomă încearcă să insereze în tabel un rând care face referire la o cheie străină la o înregistrare care nu a fost încă comisă. Aceasta are ca rezultat o așteptare (tranzacția autonomă așteaptă ca tranzacția principală să se angajeze pentru a vedea dacă poate introduce date) și, în același timp, tranzacția principală așteaptă ca tranzacția autonomă să continue să funcționeze după declanșare. Are loc un blocaj și, ca urmare, tranzacția autonomă este anulată din motive legate de blocări.

Numai utilizatorii înregistrați pot participa la sondaj. Loghează-te, Vă rog.

A fost greu?

• Ca două degete, am decis imediat totul corect.

• Nu chiar, m-am înșelat la câteva întrebări.

• Am rezolvat jumatate corect.

• Am ghicit răspunsul de două ori!

• O sa scriu in comentarii

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

Sursa: www.habr.com