Following Highload ++ Siberia 2019 - 8 Oracle Tasks

    Hello!

    On June 24-25, the Highload ++ Siberia 2019 conference was held in Novosibirsk. Our guys were also there on the report “Oracle Container Bases (CDB / PDB) and Their Practical Use for Software Development,” we will post a text version a bit later. It was cool, thanks olegbunin for organizing, as well as everyone who came.


    In this post, we would like to share with you the tasks that were at our booth so that you can test your knowledge in Oracle. Under the cut - 8 tasks, answer options and explanation.

    What is the maximum value of the sequence we will see as a result of the following script?


    createsequence s startwith1;
    select s.currval, s.nextval, s.currval, s.nextval, s.currval
    from dual
    connectbylevel <= 5;
    

    • one
    • five
    • ten
    • 25
    • No, there will be a mistake

    Answer
    Согласно документации 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.

    How many rows will be in the table as a result of the following script?


    createtable t(i integercheck (i < 5));
    createprocedure p(p_from integer, p_to integer) asbeginfor i in p_from .. p_to loopinsertinto t values (i);
        endloop;
    end;
    /
    exec p(1, 3);
    exec p(4, 6);
    exec p(7, 9);

    • 0
    • 3
    • four
    • five
    • 6
    • 9

    Answer
    Согласно документации 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. Таким образом, первый вызов ХП благополучно завершается, вставив три записи; второй вызов ХП завершается с ошибкой и откатывает четвёртую запись, которую успел вставить; третий вызов завершается с ошибкой, и в таблице оказываются три записи.

    How many rows will be in the table as a result of the following script?


    createtable t(i integer, constraint i_ch check (i < 3));
    begininsertinto t values (1);
        insertinto t values (null);
        insertinto t values (2);
        insertinto t values (null);
        insertinto t values (3);
        insertinto t values (null);
        insertinto t values (4);
        insertinto t values (null);
        insertinto t values (5);
    exception
        when others then
            dbms_output.put_line('Oops!');
    end;
    /

    • one
    • 2
    • 3
    • four
    • five
    • 6
    • 7

    Answer
    Согласно документации 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.

    What pairs of values ​​will occupy the same amount of space in the block?


    createtable t (
        a char(1char),
        b char(10char),
        c char(100char),
        i number(4),
        j number(14),
        k number(24),
        x varchar2(1char),
        y varchar2(10char),
        z varchar2(100char));
    insertinto t (a, b, i, j, x, y)
        values ('Y', 'Вася', 10, 10, 'Д', 'Вася');
    

    • A and X
    • B and Y
    • C and K
    • C and Z
    • K and Z
    • I and J
    • J and X
    • All listed

    Answer
    Приведем выдержки из документации (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.


    What will the clustering factor of the T_I index be approximately?


    createtable t (i integer);
    insertinto t selectrownumfrom dual connectbylevel <= 10000;
    createindex t_i on t(i);
    

    • About dozens
    • About hundreds
    • Of the order of thousands
    • Of the order of tens of thousands

    Answer
    Согласно документации 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 будет порядка десятков.

    At what values ​​of N the following script will be successfully executed in a regular database with standard settings?


    createtable t (
        a varchar2(N char),
        b varchar2(N char),
        c varchar2(N char),
        d varchar2(N char));
    createindex t_i on t (a, b, c, d);
    

    • 100
    • 200
    • 400
    • 800
    • 1600
    • 3200
    • 6400

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

    Logical Database Limits

    ItemType of LimitLimit Value
    IndexesTotal size of indexed column75% of the database block size minus some overhead

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

    The INSERT statement with the APPEND hint is designed to load data in direct mode. What happens if it is applied to the table on which the trigger hangs?


    • Data will be loaded in direct mode, the trigger will work as it should
    • Data will be loaded in direct mode, but the trigger will not be executed
    • Data will be loaded in conventional mode, the trigger will work as it should
    • Data will be loaded in conventional mode, but the trigger will not be executed
    • Data will not be uploaded, error will be fixed

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

    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.

    What happens when executing the following script?


    createtable t(i integernotnull primary key, j integerreferences t);
    createtrigger t_a_i afterinserton t foreachrowdeclarepragma autonomous_transaction;
    begininsertinto t values (:new.i + 1, :new.i);
        commit;
    end;
    /
    insertinto t values (1, null);
    

    • Successful execution
    • Syntax error
    • Invalid offline transaction error
    • Error related to exceeding maximum call nesting
    • Foreign Key Violation Error
    • Lock Error

    Answer
    Таблица и триггер создаются вполне корректно и эта операция не должна привести к проблемам. Автономные транзакции в триггере также разрешены, иначе было бы невозможным, например, логирование.

    После вставки первой строки успешное срабатывание триггера привело бы к вставке второй строки, в связи с чем снова сработал бы триггер, вставил бы третью строку и так далее до тех пор, пока statement не упал бы из-за превышения максимальной вложенности вызовов. Однако, срабатывает ещё один тонкий момент. В момент выполнения триггера для первой вставленной записи ещё не выполнен commit. Поэтому триггер, работающий в автономной транзакции, пытается вставить в таблицу строку, ссылающуюся по внешнему ключу на ещё не закоммиченную запись. Это приводит к ожиданию (автономная транзакция ждёт коммита основной, чтобы понять, можно ли вставить данные) и одновременно основная транзакция ждёт коммита автономной, чтобы продолжить работу после триггера. Возникает deadlock и как следствие – автономная транзакция отстреливается по причине, связанной с блокировками.

    Only registered users can participate in the survey. Please come in.

    It was difficult to?


    Also popular now: