🥇Слідами 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 Statement, Oracle буде вдосконалювати sequence only once per row. Якщо стан contain more than one reference to NEXTVAL for sequence, Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL. Якщо стан статей містить посилання на що CURRVAL і NEXTVAL, Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL negative of their order within the statement.

Таким чином, максимальне значення буде відповідати числу рядків, тобто 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 marks an implicit savepoint (не доступний для вас). Там, якщо мітки нестачі, Oracle rolls it back automatically and returns the applicable error code to SQLCODE in the SQLCA. Для прикладу, якщо INSERT statement causas error error trying to insert duplicate value in unique index, the statement is rolled back.

Виклик ХП з клієнта також розглядається та обробляється як single statement. Таким чином, перший виклик ХП успішно завершується, вставивши три записи; другий виклик ХП завершується з помилкою та відкочує четвертий запис, який встиг вставити; третій виклик завершується з помилкою, і в таблиці виявляються три записи.

Скільки рядків опиниться в таблиці в результаті наступного скрипту?

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

Check constraint lets you specify a condition that each row in the table must satisfy. Для того, щоб відстояти, будь-який рядок в таблиці повинен робити умови, що вони TRUE або unknown (due to a null). Коли Oracle виявляють check constraint condition for particular row, any column names in condition refer to column values in that row.

Таким чином, значення 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 data typ specifies fixed-length character string in database character set. Ви вважаєте, що параметри вашого параметра є параметром, коли ви створюєте вашу database. Oracle наслідки того, що всі значення встановлені в CHAR column мають значення, визначені відповідно до розміру у вибраному сегменті semantics. Якщо ви усвідомлюєте цінність, що це шорти, що не є аркушом літопису, то Oracle бланк-пам'яті клацніть на ланцюжку.

VARCHAR2 Data Type
VARCHAR2 type data specifies variable-length character string in database character set. Ви вважаєте, що параметри вашого параметра є параметром, коли ви створюєте вашу database. Oracle stores Character value in VARCHAR2 column exactly as you specify it, without any blank-padding, provided value does не exceed length of the column.

NUMBER Data Type
NUMBER data типи магазинів 1.0 як добре, як позитивні і negative fixed numbers with absolute values from 10 x 130-1.0 to no including 10126 x 1.0. x 10126, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes. Використовуючи це в акаунті, column size in bytes for particular numerical data value NUMBER(p), where p is precision of given value, can be calculated using the following formula: ROUND((length(p)+s)/2))+1 where s equals zero if the number is positive, and s equals 1 if the number is negative.

Крім того, візьмемо витяг з документації щодо зберігання Null-значень.

Null is the absence of a value in a column. Nulls indicate missing, unknown, чи inapplicable data. Nulls є stored in database if they fall between columns with data values. У цих випадках, вони потрібні 1 bytes to store length of column (zero). Перехідні nulls в ряді потреб не перевищувати ряд нових повідомлень сигналів, які керують колонками в попередньому рядку є null. Для прикладу, якщо останні три columnи з table є null, вони не використовуються для цих columns.

Виходячи з цих даних, будуємо міркування. Вважаємо, що у БД використовується кодування 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-tree index, index clustering factor measures physical grouping of rows in relation to index value.

index clustering factor helps optimizer decide whether index scan or full table scan is more efficient for certain queries). Низький clustering factor indicates an efficient index scan.

clustering factor that is close to number of blocks in table indicates that the rows are physically ordered in the table blocks by the index key. Якщо вхідні дані використовуються для повного сканування клавіатури, вони беруться за рамки, які вирівняні рядки, як вони знаходяться на диску, розташованому за index key. clustering factor that is close to number of rows indicates that the rows as scattered randomly across the database blocks in relation to the index key. Якщо вхідні файли використовуються для повного графіка сканування, тоді вхідні файли не можуть бути використані рядки в будь-який sorted order by this index key.

В даному випадку дані ідеально відсортовані, тому clustering factor дорівнюватиме або близький до кількості зайнятих блоків в таблиці. Для стандартного розміру блоку в 8 кілобайт можна очікувати, що в один блок поміститься близько тисячі вузьких номерів значень, тому кількість блоків, і як наслідок clustering factor буде порядку десятків.

При яких значеннях 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):

Logical Database Limits

пункт
Type of Limit
Граничне значення

Індекси
Total size of indexed column
75% of database block size minus some overhead

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

Оператор INSERT із хінтом APPEND призначений для завантаження даних у direct-режимі. Що станеться, якщо вона буде застосована до таблиці, на якій висить тригер?

Дані будуть завантажені в direct-режимі, тригер спрацює як має
Дані будуть завантажені в direct-режимі, але тригер виконано не буде
Дані будуть завантажені в conventional-режимі, тригер спрацює як повинен
Дані будуть завантажені в conventional-режимі, але тригер виконаний не буде
Дані не будуть завантажені, буде зафіксовано помилку

відповідьУ принципі це питання більше на логіку. Для знаходження правильної відповіді я запропонував би таку модель міркувань:

Вставка в direct режимі виконується прямим формуванням блоку даних, повз SQL-движка, що забезпечує високу швидкість. Таким чином, забезпечити виконання тригера дуже складно, якщо взагалі можливо, і сенсу в цьому немає, тому що він все одно кардинально загальмує вставку.
Невиконання тригера призведе до того, що з однакових даних у таблиці стан бази загалом (інших таблиць) залежатиме від цього, у якому режимі вставлені ці дані. Це очевидно зруйнує цілісність даних і не може бути застосоване як рішення у виробництві.
Неможливість виконати цю операцію, взагалі кажучи, трактується як помилка. Але тут слід згадати про те, що APPEND – хінт, а загальна логіка хінтів полягає в тому, що вони враховуються якщо це можливо, якщо ні – оператор виконується без урахування хінту.

Таким чином, очікувана відповідь – дані будуть завантажені у звичайному (SQL) режимі, тригер спрацює.

Згідно з документацією Oracle (цитується з 8.04):

Violations of restrictions будуть спричиняти стан до виконання рішуче, використовуючи конвенційні insert path, без поразки або помилок messages. За винятком є обмеження на станах, застосовуючи ті ж самі table more, than once in a transaction, which can cause error messages.
Для прикладу, якщо тригери або референтна внутрішність є існуючим на табличці, то для того, щоб виявити, що не буде ignorовано, коли ви намагаєтеся використовувати direct-load INSERT (serial or parallel), як добре, як PARALLEL знижується або повторює, якщо any.

Що відбудеться при виконанні наступного сценарію?

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

Успішне виконання
Збій через синтаксичну помилку
Помилка, пов'язана з неприпустимістю автономної транзакції
Помилка, пов'язана з перевищенням максимальної вкладеності викликів
Помилка пов'язана з порушенням зовнішнього ключа
Помилка, пов'язана з блокуванням

відповідьТаблиця та тригер створюються цілком коректно і ця операція не повинна призвести до проблем. Автономні транзакції у тригері також дозволені, інакше було б неможливим, наприклад, логування.

Після вставки першого рядка успішне спрацювання тригера призвело б до вставки другого рядка, у зв'язку з чим знову спрацював би тригер, вставив би третій рядок і так далі, поки statement не впав би через перевищення максимальної вкладеності викликів. Однак спрацьовує ще один тонкий момент. У момент виконання тригера для першого вставленого запису не виконаний commit. Тому тригер, що працює в автономній транзакції, намагається вставити в таблицю рядок, що посилається на зовнішній ключ на ще не закоммічений запис. Це призводить до очікування (автономна транзакція чекає на комміт основний, щоб зрозуміти, чи можна вставити дані) і одночасно основна транзакція чекає на комміт автономної, щоб продовжити роботу після тригера. Виникає deadlock і як наслідок – автономна транзакція відстрілюється через причину, пов'язану з блокуваннями..

Тільки зареєстровані користувачі можуть брати участь в опитуванні. Увійдіть, будь ласка.

Важко було?

Як два пальці, одразу вирішив все правильно.
Не надто, помилився в парі питань.
Вирішив половину правильно.
Двічі вгадав відповідь!
Напишу у коментарях

Проголосували 14 користувачів. Утрималися 10 користувачів.

Джерело: habr.com

По слідах Highload++ Siberia 2019 - 8 завдань з Oracle

Яке максимальне значення сіквенсу ми побачимо в результаті наступного скрипту?

Скільки рядків опиниться в таблиці в результаті наступного скрипту?

Скільки рядків опиниться в таблиці в результаті наступного скрипту?

Які пари значень займуть однакові об'єми місця у блоці?

Яким буде приблизно clustering factor у індексу T_I?

При яких значеннях N наступний скрипт успішно виконається у звичайній базі даних зі стандартними налаштуваннями?

Оператор INSERT із хінтом APPEND призначений для завантаження даних у direct-режимі. Що станеться, якщо вона буде застосована до таблиці, на якій висить тригер?

Що відбудеться при виконанні наступного сценарію?

Важко було?

Додати коментар або відгук Скасувати відповідь