ProHoster > Блог > Администриране > По следите на Highload++ Siberia 2019 — 8 задачи по Oracle

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

Здравейте!

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

По следите на Highload++ Siberia 2019 — 8 задачи по Oracle
В този пост искаме да споделим с вас задачите, които бяха на нашия щанд, за да можете да проверите знанията си в 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);

  • 0
  • 3
  • 4
  • 5
  • 6
  • 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('Упс!');
end;
/

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

ОтговорСпоред документацията на 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 Data Type
Тип данных CHAR задает символьную строку фиксированной длины в наборе символов базы данных. Вы задаете набор символов базы данных при создании вашей базы данных. Oracle гарантирует, что все значения, хранящиеся в столбце CHAR, имеют длину, указанную размером в выбранной семантике длины. Если вы вставите значение, которое короче длины столбца, то Oracle заполнит значение пробелами до длины столбца.

VARCHAR2 Data Type
Тип данных VARCHAR2 задает символьную строку переменной длины в наборе символов базы данных. Вы задаете набор символов базы данных при создании вашей базы данных. Oracle хранит значение символа в столбце VARCHAR2 точно так, как вы его указываете, без каких-либо добавленных пробелов, при условии, что значение не превышает длину столбца.

NUMBER Data Type
Тип данных NUMBER хранит ноль, а также положительные и отрицательные фиксированные числа с абсолютными значениями от 1.0 x 10-130 до, но не включая, 1.0 x 10^126. Если вы укажете арифметическое выражение, значение которого имеет абсолютное значение, большее или равное 1.0 x 10^126, то 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 байта). Поле C заема 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 байта.

Итого, правилните варианти: C и 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 е предназначен за зареждане на данни в режим на директна обработка. Какво ще се случи, ако той бъде приложен на таблица, на която има тригер?

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

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

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

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

Според документацията на Oracle (цитирана от 8.04):

Нарушаването на ограниченията ще доведе до това, че заявката ще се изпълнява последователно, използвайки обичайната пътека за вмъкване, без предупреждения или съобщения за грешки. Изключение е ограничението за изявления, достъпващи същата таблица повече от веднъж в транзакция, което може да доведе до съобщения за грешки.
Например, если в таблице присутствуют триггеры или ссылочная целостность, то подсказка APPEND будет проигнорирована, когда вы попытаетесь использовать вставку прямой загрузки (последовательным или параллельным образом), а также подсказка или условие 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);

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

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

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

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

Беше ли сложно?

  • Като два пръста, веднага реших всичко правилно.

  • Не особено, сбърках в няколко въпроса.

  • Реших половината правилно.

  • Два пъти познах отговора!

  • Ще напиша в коментарите

Гласували са 14 потребители. Въздържали са се 10 потребители.

Източник: habr.com

Купете надежден хостинг за сайтове със защита от DDoS, VPS и VDS сървъри 🔥 Купете надежден хостинг за сайтове със защита от DDoS, VPS и VDS сървъри | ProHoster