In the footsteps of Highload++ Siberia 2019 — 8 Oracle tasks

Hi!

On June 24-25, the Highload++ Siberia 2019 conference was held in Novosibirsk. Our guys were also there report "Oracle container databases (CDB/PDB) and their practical use for software development", we will post a text version a little later. It was great, thanks 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 that you can test your knowledge in Oracle. Under the cut - 8 tasks, answer options and an explanation.

What is the maximum value of the sequence we will see as a result of the execution of 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

ResponseAccording to the 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.

In this way, the maximum value will correspond to the number of rows, i.e. 5.

How many rows will be in the table as a result of 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

ResponseAccording to the 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 in a unique index, the statement is rolled back.

Calling HP from the client is also considered and processed as a single statement. Thus, the first call to HP completes successfully, inserting three records; the second call to HP fails and rolls back the fourth record that it managed to insert; the third call fails, and there are three records in the table.

How many rows will be in the table as a result of 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

ResponseAccording to the 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 test, and the anonymous block will succeed until the attempt to insert the value 3. After that, the error handling block will throw the exception, no rollback will occur, and the table will have four rows with values ​​1, null, 2 and null again.

What pairs of values ​​will take up 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', 'Вася', 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

ResponseHere are excerpts from the documentation (12.1.0.2) on storing various 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 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.

In addition, let's take an excerpt from the documentation about storing 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 these 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 the field a 'Y' occupies 1 byte, the value of the field x 'D' is 2 bytes
2) B and Y, 'Vasya' in b value will be padded with spaces up to 10 characters and will take 14 bytes, 'Vasya' in d will take 8 bytes.
3) C and K. Both fields are NULL, there are significant fields after them, so they occupy 1 byte each.
4) C and Z. Both fields are NULL, but the Z field is the last one in the table, so it takes no space (0 bytes). The C field is 1 byte.
5) K and Z. Similar to the previous case. The value in the K field occupies 1 byte, in Z - 0.
6) I and J. According to the documentation, both values ​​will take 2 bytes each. We calculate the length according to the formula taken from the documentation: round( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J and X. The value in the J field will take 2 bytes, the value in the X field will take 2 bytes.

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

What will be the clustering factor of the T_I index?

create table t (i integer);
 
insert into t select rownum from dual connect by level <= 10000;
 
create index t_i on t(i);

• About tens
• Hundreds
• About a thousand
• About tens of thousands

ResponseAccording to the 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 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.

In this case, the data is perfectly sorted, so the clustering factor will be equal to or close to the number of occupied blocks in the table. For a standard block size of 8 kilobytes, one can expect that about a thousand narrow number values ​​will fit in one block, so the number of blocks, and as a result, the clustering factor will be dozens.

For what values ​​of N will the following script be successfully executed in a normal database 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

ResponseAccording to the 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. Further depends on the selected base encoding. For AL32UTF8 encoding, one character can take a maximum of 4 bytes, so in the worst case, 6 kilobytes will fit about 1500 characters. Therefore, Oracle will forbid index creation at N = 400 (when the key length is 1600 characters * 4 bytes + rowid length in the worst case), while at N = 200 (or less) creation of an index will work without problems.

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?

• The data will be loaded in direct mode, the trigger will work 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, the trigger will work as it should
• The data will be loaded in conventional mode, but the trigger will not be executed
• Data will not be loaded, an error will be generated

ResponseBasically, it is a question more on logic. To find the correct answer, I would suggest the following reasoning model:

1. An insert in direct mode is performed by direct formation of a data block, bypassing the SQL engine, which ensures high speed. Thus, it is very difficult, if not impossible, to ensure the execution of a trigger, and there is no point in this, since it will drastically slow down the insertion anyway.
2. Failure of the trigger will lead to the fact that, with the same data in the table, the state of the database as a whole (other tables) will depend on the mode in which this data is inserted. This will obviously destroy the integrity of the data and cannot be applied as a solution in production.
3. The inability to perform the requested operation is generally treated as 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, but if not, the statement is executed without taking into account the hint.

So the expected answer is - the data will be loaded in normal (SQL) mode, the trigger will fire.

According to the Oracle 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 happens when the following script is executed?

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 syntax error
• Autonomous Transaction Invalid Error
• Error related to exceeding the maximum nesting of calls
• Foreign key violation error
• Lock related error

ResponseThe table and trigger are created quite correctly and this operation should not lead to problems. Autonomous transactions in the trigger are also allowed, otherwise it would be impossible, for example, logging.

After inserting the first row, a successful firing of the trigger would result in the insertion of the second row, causing the trigger to fire again, inserting the third row, and so on until statement would fail due to exceeding the maximum call nesting. However, another subtle point comes into play. At the time the trigger is executed, the first inserted record has not yet been committed. Therefore, a trigger operating in an autonomous transaction tries to insert into the table a row that refers to a record that has not yet been committed by foreign key. This leads to a wait (the autonomous transaction is waiting for the main commit to see if data can be inserted) and at the same time the main transaction is waiting for the autonomous commit to continue after the trigger. A deadlock occurs and, as a result, an autonomous transaction is fired for a reason related to locks.

Only registered users can participate in the survey. Sign in, you are welcome.

It was difficult to?

• Like two fingers, I immediately decided everything was right.

• Not really, I made a mistake in a couple of questions.

• Got half right.

• Guessed the answer twice!

• I'll write in the comments

14 users voted. 10 users abstained.

Source: habr.com