🥇По следите на Highload++ Siberia 2019 — 8 задачи по Oracle

Здравейте!

На 24-25 юни в Новосибирск се проведе конференцията Highload++ Siberia 2019. И нашите хора също бяха там доклад „Контейнерни бази Oracle (CDB/PDB) и тяхното практическо използване за разработка на софтуер“, ще публикуваме текстовата версия малко по-късно. беше страхотно, благодаря olegbunin за организацията и на всички, които дойдоха.

В този пост бихме искали да споделим с вас задачите, които бяха на нашия щанд, за да можете да проверите знанията си в Oracle. Под кат - 8 задачи, варианти за отговори и обяснения.

Какво максимално значение на секвенса ще видим в резултат на изпълнението на следния скрипт?

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
Никакво, ще има грешка

ОтговорСпоред документацията на Oracle (цитирана от 8.1.6):
В рамките на един SQL оператор, Oracle ще увеличи секвенса само веднъж на ред. Ако операторът съдържа повече от едно позоваване на NEXTVAL за секвенс, Oracle увеличава секвенса веднъж и връща същата стойност за всички появи на NEXTVAL. Ако операторът съдържа позовавания и на CURRVAL, и на NEXTVAL, Oracle увеличава секвенса и връща същата стойност и за CURRVAL, и за NEXTVAL, независимо от тяхната последователност в оператора.

Така че максималното значение ще съответства на броя редове, тоест 5.

Колко реда ще има в таблицата в резултат на изпълнението на следния скрипт?

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

ОтговорСпоред документацията на Oracle (цитирана от 11.2):

Преди да изпълни какъвто и да е SQL оператор, Oracle маркира имплицитна точка на запазване (не налична за вас). След това, ако операторът се провали, Oracle автоматично го отменя и връща приложимия код за грешка на SQLCODE в SQLCA. Например, ако операторът INSERT предизвика грешка, опитвайки се да вмъкне дублирана стойност в уникален индекс, операторът се отменя.

Повикването на ХП от клиента също се счита и обработва като един оператор. Така че, първото повикване на ХП успешно завършва, вмъквайки три записа; второто повикване на ХП завършва с грешка и отменя четвъртия запис, който е успял да вмъкне; третото повикване завършва с грешка, и в таблицата остават три записа.

Колко реда ще има в таблицата в резултат на изпълнението на следния скрипт?

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

ОтговорСпоред документацията на Oracle (цитирана от 11.2):

Ограничението на проверка ви позволява да зададете условие, което всяка редица в таблицата трябва да удовлетворява. За да отговори на ограничението, всяка редица в таблицата трябва да направи условието или ИСТИНА, или неизвестно (поради null). Когато Oracle оценява условие на ограничение за проверка за конкретна редица, всяко име на колона в условията отговаря на стойностите на колоните в тази редица.

По този начин стойността null ще премине проверката и анонимният блок ще се изпълнява успешно до опита за вмъкване на стойност 3. След това блокът за обработка на грешки ще утихне изключението, няма да има откат и в таблицата ще останат четири реда с стойности 1, null, 2 и отново null.

Кои двойки стойности ще заемат еднакви обеми място в блока?

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 и X
B и Y
C и K
C и Z
K и Z
I и J
J и X
Всички изброени

ОтговорНека представим фрагменти от документацията (12.1.0.2) за съхранение на различни типове данни в Oracle.

Тип данни CHAR
Типът данни CHAR определя фиксиран по дължина низ от символи в набор от символи на базата данни. Вие указвате набора от символи на базата данни, когато създавате базата си. Oracle гарантира, че всички стойности, съхранявани в колона CHAR, имат дължината, определена от размера в избраната семантика на дължина. Ако вмъкнете стойност, която е по-кратка от дължината на колоната, Oracle попълва стойността с интервали до дължината на колоната.

Тип данни VARCHAR2
Типът данни VARCHAR2 определя променлив по дължина низ от символи в набор от символи на базата данни. Вие указвате набора от символи на базата данни, когато създавате базата си. Oracle съхранява символна стойност в колона VARCHAR2 точно както я указвате, без никакво попълване с интервали, стига стойността да не надвишава дължината на колоната.

Тип данни NUMBER
Типът данни NUMBER съхранява нула, както и положителни и отрицателни фиксирани числа с абсолютни стойности от 1.0 x 10-130 до, но не включително, 1.0 x 10126. Ако зададете аритметично израз, чиято стойност има абсолютна стойност, по-голяма или равна на 1.0 x 10126, Oracle ще върне грешка. Всяка стойност NUMBER изисква от 1 до 22 байта. Взимайки това предвид, размерът на колоната в байтове за определена числова стойност NUMBER(p), където p е прецизността на дадената стойност, може да бъде изчислен с помощта на следната формула: ROUND((length(p)+s)\/2))+1 където s е равно на нула, ако числото е положително, и s е равно на 1, ако числото е отрицателно.

Освен това, нека вземем фрагмент от документацията относно съхранението на Null-стойности.

Null е отсъствие на стойност в колона. Null стойностите указват липсващи, неизвестни или неподходящи данни. Null стойностите се съхраняват в базата данни, ако попадат между колони с данни. В тези случаи те изискват 1 байт за съхранение на дължината на колоната (нула). Завършващите null стойности в редица не изискват съхранение, тъй като новата заглавка на реда сигнализира, че останалите колони в предишния ред са null. Например, ако последните три колони на таблица са null, то няма данни, съхранявани за тези колони.

Основавайки се на тези данни, изграждаме разсъждения. Предполагаме, че в БД се използва кодировката AL32UTF8. В тази кодировка руски букви ще заемат 2 байта.

1) A и X, стойността на полето a ‘Y’ заема 1 байт, стойността на полето x ‘Д’ – 2 байта
2) B и Y, ‘Вася’ в b значение ще се допълни с интервали до 10 символа и ще заеме 14 байта, ‘Вася’ в d – ще заеме 8 байта.
3) C и K. И двете полета имат значение NULL, след тях има значими полета, затова заемат по 1 байт.
4) C и Z. И двата полета имат значение NULL, но полето Z е последно в таблицата, затова не заема място (0 байта). Поле С заема 1 байт.
5) K и Z. По аналогия с предишния случай. Стойността в поле K заема 1 байт, в Z – 0.
6) I и J. Според документацията, и двете стойности ще заемат по 2 байта. Дължината се брои по формулата взета от документацията: round( (1 + 0) / 2) + 1 = 1 + 1 = 2.
7) J и X. Стойността в поле J ще заеме 2 байта, стойността в поле X ще заеме 2 байта.

Итого, правилните варианти: С и K, I и J, J и X.

Какъв ще бъде приблизително clustering factor на индекса 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);

Порядъка на десетки
Порядъка на стотици
Порядъка на хиляди
Порядъка на десетки хиляди

ОтговорСпоред документацията на Oracle (цитирана от 12.1):

За B-дерево индекс, индекс класиращият фактор измерва физическото групиране на редовете относно стойността на индекса.

Индекс класиращият фактор помага на оптимизатора да реши дали индексното сканиране или пълното сканиране на таблицата е по-ефективно за определени запитвания). Нисък класиращ фактор показва ефективно индексно сканиране.

Класиращият фактор, близък до броя на блоковете в таблицата, показва, че редовете са физически подредени в блоковете на таблицата по ключа на индекса. Ако базата данни извърши пълно сканиране на таблицата, тогава базата данни ще извлече редовете по начина, по който са записани на диска, сортирани по ключа на индекса. Класиращият фактор, близък до броя на редовете, показва, че редовете са разпръснати произволно в блоковете на базата данни във връзка с ключа на индекса. Ако базата данни извърши пълно сканиране на таблицата, базата данни няма да извлече редовете в никакъв сортиран ред по този ключ на индекса.

В този случай данните са идеално сортирани, затова класиращият фактор ще бъде равен или близък до броя на заетите блокове в таблицата. За стандартен размер на блока от 8 килобайта може да се очаква, че в един блок ще се поберат порядъка на хиляда тънки number стойности, затова броят на блоковете, и следователно класиращият фактор, ще бъде порядъка на десетки.

При какви стойности на N следният скрипт ще се изпълни успешно в обикновена БД със стандартни настройки?

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

ОтговорСпоред документацията на Oracle (цитирана от 11.2):

Логически ограничения на базата данни

Артикул
Тип на лимита
Стойност на лимита

Индекси
Общ размер на индексираната колона
75% от размера на блока на базата данни минус малко допълнително пространство

По този начин, общият размер на индексируемите колони не трябва да надвишава 6Кб. По-нататък зависи от избраната кодировка на базата. За кодировката AL32UTF8 един символ може да заема максимум 4 байта, така че в 6 килобайта в най-лошия случай могат да се съберат около 1500 символа. Поради това, Oracle ще забрани създаването на индекс при N = 400 (когато дължината на ключа в най-лошия случай ще бъде 1600 символа * 4 байта + дължина на rowid), докато при N = 200 (и по-малко) създаването на индекс ще премине без проблеми.

Операторът INSERT с подсказка APPEND е предназначен за зареждане на данни в директен режим. Какво ще се случи, ако бъде приложен към таблица, на която има тригер?

Данните ще бъдат заредени в директен режим, тригерът ще се задейства както трябва
Данните ще бъдат заредени в директен режим, но тригерът няма да бъде изпълнен
Данните ще бъдат заредени в конвенционален режим, тригерът ще се задейства както трябва
Данните ще бъдат заредени в конвенционален режим, но тригерът няма да бъде изпълнен
Данните няма да бъдат заредени, ще бъде записана грешка

ОтговорВ принцип, това е въпрос, свързан повече с логика. За намиране на правилния отговор, бих предложил следния модел на разсъждение:

Вмъкването в директен режим се извършва чрез директно формиране на блока с данни, заобикаляйки SQL движещия механизъм, което осигурява висока скорост. По този начин, осигуряването на изпълнението на тригера е много трудно, ако изобщо е възможно, и няма смисъл в това, тъй като той все пак значително ще забави вмъкването.
Неизпълнението на тригера ще доведе до това, че при идентични данни в таблицата състоянието на базата в общи линии (други таблици) ще зависи от това, в какъв именно режим са вмъкнати тези данни. Това очевидно ще разрушат целостта на данните и не може да бъде прието като решение в продукция.
Невъзможността да се извърши исканата операция, поставяна на прост език, се тълкува като грешка. Но тук трябва да се припомни, че APPEND е подсказка, а общата логика на подсказките е, че те се вземат предвид, ако е възможно; ако не – операторът се изпълнява без да се взима предвид подсказката.

По този начин, очакваният отговор е – данните ще бъдат заредени в обикновен (SQL) режим, тригерът ще се задейства.

Съгласно документацията на Oracle (цитираме от 8.04):

Нарушенията на ограниченията ще доведат до изпълнение на изявлението последователно, използвайки конвенционалния път за вмъкване, без предупреждения или съобщения за грешка. Изключение е ограничението за оператори, достъпващи същата таблица повече от веднъж в транзакция, което може да доведе до съобщения за грешка.
Например, ако на таблицата има задействия или референтна цялост, тогава подсказката APPEND ще бъде игнорирана, когато опитате да използвате директно зареждане с INSERT (серийнo или паралелно), както и подсказката или клаузата PARALLEL, ако има.

Какво ще се случи при изпълнение на следния скрипт?

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

Успешно изпълнение
Сбой поради синтактична грешка
Грешка, свързана с недопустимост на автономната транзакция
Грешка, свързана с надвишаване на максималната вложеност на извикванията
Грешка, свързана с нарушаване на външен ключ
Грешка, свързана с блокировки

ОтговорТаблицата и тригерът се създават напълно коректно и тази операция не трябва да доведе до проблеми. Автономните транзакции в тригера също са разрешени, в противен случай не би било възможно, например, логването.

След вмъкването на първия ред, успешното сработване на тригера би довело до вмъкване на втори ред, поради което тригерът отново би сработил, вмъквайки трети ред и така нататък до момента, в който заявката не би паднала поради надвишаване на максималната вложеност на извикванията. Въпреки това, има още един фин момент. В момента на изпълнение на тригера за първоначално вмъкнатата запись все още не е извършен commit. Следователно тригерът, работещ в автономна транзакция, се опитва да вмъкне в таблицата ред, който се отнася към все още не потвърдена запись. Това води до очакване (автономната транзакция чака commit на основната, за да определи дали може да вмъкне данни) и едновременно основната транзакция чака commit на автономната, за да продължи работа след тригера. Възниква deadlock и в резултат на това автономната транзакция се унищожава по причина, свързана с блокировки.

Само регистрирани потребители могат да участват в анкетата. Влезте, моля.