Following Highload++ Siberia 2019 — 8 tasks on Oracle

Hello!

On June 24-25 in Novosibirsk, the Highload++ Siberia 2019 conference took place. Our team was also present. presentation «Container Databases Oracle (CDB/PDB) and their practical use for software development», we will post the text version a bit later. It was great, thank you olegbunin for the organization, as well as to everyone who came.

In this post, we would like to share with you the tasks that were at our booth so you can test your knowledge in Oracle. Below are 8 tasks, options for answers, and explanations.

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

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
• None, there will be an error

AnswerAccording to Oracle documentation (quoted from 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.

Thus, the maximum value will correspond to the number of rows, which is 5..

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

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

AnswerAccording to Oracle documentation (quoted from 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 into a unique index, the statement is rolled back.

A call to the stored procedure from the client is also considered and handled as a single statement. Thus, the first call to the stored procedure successfully completes, inserting three records; the second call to the stored procedure fails with an error and rolls back the fourth record it managed to insert; the third call fails with an error, and the table ends up with three records..

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

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

AnswerAccording to Oracle documentation (quoted from 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.

Thus, the null value will pass the check, and the anonymous block will execute successfully until an attempt is made to insert the value 3. After that, the error handling block will catch the exception, and a rollback will not occur. Four rows will remain in the table. with values 1, null, 2, and again null.

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

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', 'Vasya', 10, 10, 'D', 'Vasya');

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

AnswerLet's provide excerpts from the documentation (12.1.0.2) on storing different data types in 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 10^126. If you specify an arithmetic expression whose value has an absolute value greater than or equal to 1.0 x 10^126, 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.

In addition, let's take an excerpt from the documentation regarding the storage of Null values.

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.

Based on this data, we build reasoning. We assume that the database uses the AL32UTF8 encoding. In this encoding, Russian letters will take 2 bytes.

1) A and X, the value of field a 'Y' takes 1 byte, the value of field x 'D' takes 2 bytes.
2) B and Y, 'Vasya' in b will be filled with spaces up to 10 characters and will occupy 14 bytes, 'Vasya' in d will occupy 8 bytes.
3) C and K. Both fields contain a NULL value, and since there are significant fields after them, they occupy 1 byte each.
4) C and Z. Both fields contain a NULL value, but field Z is the last in the table, so it does not occupy space (0 bytes). Field C occupies 1 byte.
5) K and Z. Similarly to the previous case. The value in field K occupies 1 byte, and in Z it occupies 0 bytes.
6) I and J. According to the documentation, both values will occupy 2 bytes each. The length is calculated using the formula from the documentation: round((1 + 0)/2) +1 = 1 + 1 = 2.
7) J and X. The value in field J will occupy 2 bytes, and the value in field X will also occupy 2 bytes.

In total, the correct options are: C and K, I and J, J and X.

What will be the approximate clustering factor of the index 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);

• On the order of tens
• On the order of hundreds
• On the order of thousands
• On the order of tens of thousands

AnswerAccording to Oracle documentation (quoted from 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 it 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 randomly scattered across the database blocks in relation to the index key. If the database performs a full table scan, it would not retrieve rows in any sorted order by this index key.

In this case, the data is perfectly sorted, so the clustering factor will equal or be close to the number of occupied blocks in the table. For a standard block size of 8 kilobytes, it can be expected that approximately a thousand narrow number values will fit in one block, therefore the number of blocks, and consequently the clustering factor will be on the order of tens.

For which values of N will the following script successfully execute in a regular DB with standard settings?

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

AnswerAccording to Oracle documentation (quoted from 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

Thus, the total size of indexed columns should not exceed 6KB. What follows depends on the chosen database encoding. For AL32UTF8 encoding, one character can occupy a maximum of 4 bytes, so in 6 kilobytes, in the worst case, about 1500 characters can fit. Therefore, Oracle will prohibit the creation of an index when N = 400 (when the key length in the worst case would be 1600 characters * 4 bytes + rowid length), whereas when N = 200 (and less) the creation of the index will proceed without problems.

The INSERT statement with the APPEND hint is intended for loading data in direct mode. What will happen if it is applied to a table that has a trigger?

• The data will be loaded in direct mode, and the trigger will fire as it should.
• The data will be loaded in direct mode, but the trigger will not be executed.
• The data will be loaded in conventional mode, and the trigger will fire as it should.
• The data will be loaded in conventional mode, but the trigger will not be executed.
• The data will not be loaded, and an error will be recorded.

AnswerIn principle, this question is more about logic. To find the correct answer, I would suggest the following reasoning model:

1. Insertion in direct mode occurs by directly forming a data block, bypassing the SQL engine, which ensures high speed. Thus, ensuring the execution of the trigger is quite difficult, if at all possible, and there is no point in it, as it would drastically slow down the insertion anyway.
2. The non-execution of the trigger will lead to the fact that with identical data in the table, the overall state of the database (other tables) will depend on the mode in which this data was inserted. This will obviously destroy data integrity and cannot be applied as a solution in production.
3. The inability to perform the requested operation is generally considered an error. But here it should be remembered that APPEND is a hint, and the general logic of hints is that they are taken into account if possible; if not, the statement executes without considering the hint.

Thus, the expected answer is the data will be loaded in the regular (SQL) mode, and the trigger will fire.

According to Oracle's documentation (quoted from 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 will happen when executing the following script?

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

• Successful execution
• Failed due to a syntax error
• Error related to the invalidity of the autonomous transaction
• Error related to exceeding the maximum call nesting
• Error related to foreign key violation
• Error related to locks

AnswerThe table and trigger are created correctly, and this operation should not cause issues. Autonomous transactions in the trigger are also allowed; otherwise, logging, for example, would not be possible.

After inserting the first row, the successful execution of the trigger would lead to the insertion of a second row, which would again trigger the insertion of a third row, and so on until the statement fails due to exceeding the maximum nesting of calls. However, there's another subtlety. At the time of executing the trigger for the first inserted record, the commit has not yet been executed. Therefore, the trigger working in an autonomous transaction tries to insert into the table a row that references a not-yet-committed record through a foreign key. This leads to a deadlock (the autonomous transaction waits for the main transaction commit to know whether it can insert the data), and simultaneously, the main transaction waits for the autonomous transaction commit to continue processing after the trigger. A deadlock occurs, and consequently, the autonomous transaction is terminated due to a locking issue..

Only registered users can participate in the survey. Please log in, please.

Was it difficult?

• Like two fingers, I quickly resolved everything correctly.

• Not really, I made a couple of mistakes.

• I solved half correctly.

• I guessed the answer twice!

• I will write in the comments

14 users voted. 10 users abstained.

Source: habr.com