По следам 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):
Within a single SQL statement, Oracle will increment the sequence only once per row. If a statement contains more than one reference to NEXTVAL for a sequence, Oracle increments the sequence once and returns the same value for all occurrences of NEXTVAL. If a statement contains references to both CURRVAL and NEXTVAL, Oracle increments the sequence and returns the same value for both CURRVAL and NEXTVAL regardless 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);

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

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

Before executing any SQL statement, Oracle marks an implicit savepoint (not available to you). Then, if the statement fails, Oracle rolls it back automatically and returns the applicable error code to SQLCODE in the SQLCA. For example, if an INSERT statement causes an error by trying to insert a duplicate value in a 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;
/

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

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

A check constraint lets you specify a condition that each row in the table must satisfy. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null). When Oracle evaluates a check constraint condition for a particular row, any column names in the condition refer to the 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
The CHAR data type specifies a fixed-length character string in the database character set. You specify the database character set when you create your database. Oracle ensures that all values stored in a CHAR column have the length specified by size in the selected length semantics. If you insert a value that is shorter than the column length, then Oracle blank-pads the value to column length.

VARCHAR2 Data Type
The VARCHAR2 data type specifies a variable-length character string in the database character set. You specify the database character set when you create your database. Oracle stores a character value in a VARCHAR2 column exactly as you specify it, without any blank-padding, provided the value does not exceed the length of the column.

NUMBER Data Type
The NUMBER data type stores zero as well as positive and negative fixed numbers with absolute values from 1.0 x 10-130 to but not including 1.0 x 10126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10126, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes. Taking this into account, the column size in bytes for a particular numeric data value NUMBER(p), where p is the precision of a 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–значений.

A null is the absence of a value in a column. Nulls indicate missing, unknown, or inapplicable data. Nulls are stored in the database if they fall between columns with data values. In these cases, they require 1 byte to store the length of the column (zero). Trailing nulls in a row require no storage because a new row header signals that the remaining columns in the previous row are null. For example, if the last three columns of a table are null, then no data is stored for these 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):

For a B-tree index, the index clustering factor measures the physical grouping of rows in relation to an index value.

The index clustering factor helps the optimizer decide whether an index scan or full table scan is more efficient for certain queries). A low clustering factor indicates an efficient index scan.

A clustering factor that is close to the number of blocks in a table indicates that the rows are physically ordered in the table blocks by the index key. If the database performs a full table scan, then the database tends to retrieve the rows as they are stored on disk sorted by the index key. A clustering factor that is close to the number of rows indicates that the rows are scattered randomly across the database blocks in relation to the index key. If the database performs a full table scan, then the database would not retrieve rows in any sorted order by this index key.

В данном случае данные идеально отсортированы, поэтому clustering factor будет равен или близок к количеству занятых блоков в таблице. Для стандартного размера блока в 8 килобайт можно ожидать, что в один блок поместится порядка тысячи узких number значений, поэтому количество блоков, и как следствие 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

Item
Type of Limit
Limit Value

Indexes
Total size of indexed column
75% of the 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-режиме, но триггер выполнен не будет
• Данные не будут загружены, будет зафиксирована ошибка

ОтветВ принципе, это вопрос больше на логику. Для нахождения правильного ответа я предложил бы следующую модель рассуждений:

1. Вставка в direct режиме выполняется прямым формированием блока данных, мимо SQL-движка, что и обеспечивает высокую скорость. Таким образом, обеспечить выполнение триггера весьма сложно, если вообще возможно, и смысла в этом нет, так как он всё равно кардинально затормозит вставку.
2. Невыполнение триггера приведёт к тому, что при одинаковых данных в таблице состояние базы в целом (других таблиц) будет зависеть от того, в каком именно режиме вставлены эти данные. Это очевидно разрушит целостность данных и не может быть применено как решение в production.
3. Невозможность выполнить запрошенную операцию, вообще говоря, трактуется как ошибка. Но здесь следует вспомнить о том, что APPEND – хинт, а общая логика хинтов заключается в том, что они учитываются если это возможно, если же нет – оператор выполняется без учёта хинта.

Таким образом, ожидаемый ответ – данные будут загружены в обычном (SQL) режиме, триггер сработает.

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

Violations of the restrictions will cause the statement to execute serially, using the conventional insert path, without warnings or error messages. An exception is the restriction on statements accessing the same table more than once in a transaction, which can cause error messages.
For example, if triggers or referential integrity are present on the table, then the APPEND hint will be ignored when you try to use direct-load INSERT (serial or parallel), as well as the PARALLEL hint or clause, if 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