In the footsteps of Highload ++ Siberia 2019 - 8 tasks for Oracle
Hello!
June 24-25, Novosibirsk hosted the Highload ++ Siberia 2019 conference. Our guys were also there the report "Oracle Container Databases (CDB / PDB) and their practical use for software development," we will post the text version a little later. It was great, thanks to olegbunin for the organization, as well as to all who came.
In this post, we would like to share with you the tasks that were on our stand, so that you can test your knowledge in Oracle. Under the cut - 8 tasks, answers and explanations.
June 24-25, Novosibirsk hosted the Highload ++ Siberia 2019 conference. Our guys were also there the report "Oracle Container Databases (CDB / PDB) and their practical use for software development," we will post the text version a little later. It was great, thanks to olegbunin for the organization, as well as to all who came.
In this post, we would like to share with you the tasks that were on our stand, so that you can test your knowledge in Oracle. Under the cut - 8 tasks, answers and explanations.
What is the maximum value of the sequence, we will see as a result 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; - one
- five
- ten
- 25
- No, there will be a mistake
Answer
According to the Oracle documentation (cited from 8.1.6):
Within a single SQL statement, Oracle will increment the sequence only once per row. If the statement contains a statement, it will give you an error. If it is a statement, it contains the CURRVAL and NEXTVAL options, it is the same as the order for the statement.
')
Thus, the maximum value will correspond to the number of rows, that is, 5 .
Within a single SQL statement, Oracle will increment the sequence only once per row. If the statement contains a statement, it will give you an error. If it is a statement, it contains the CURRVAL and NEXTVAL options, it is the same as the order for the statement.
')
Thus, the maximum value will correspond to the number of rows, that is, 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
- four
- five
- 6
- 9
Answer
According to the Oracle documentation (cited from 11.2):
Before executing any SQL statement, Oracle marks an implicit savepoint (not available to you). Then, if you’re a statement, you’ll be able to go ahead. For example, the statement is rolled back.
The call to the client is also considered and processed as a single statement. Thus, the first CP call is successfully completed by inserting three entries; The second call to the HP ends with an error and rolls back the fourth record, which it managed to insert; the third call fails, and there are three entries in the table .
Before executing any SQL statement, Oracle marks an implicit savepoint (not available to you). Then, if you’re a statement, you’ll be able to go ahead. For example, the statement is rolled back.
The call to the client is also considered and processed as a single statement. Thus, the first CP call is successfully completed by inserting three entries; The second call to the HP ends with an error and rolls back the fourth record, which it managed to insert; the third call fails, and there are three entries 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; / - one
- 2
- 3
- four
- five
- 6
- 7
Answer
According to the Oracle documentation (cited from 11.2):
You must satisfy. TRUE or unknown (due to a null). When Oracle evaluates a check line for a particular row, it indicates.
Thus, the null value passes the check, and the anonymous block will be successfully executed until an attempt is made to insert the value 3. After this, the error-handling block will clear the exception, no rollback will occur, and the table will contain four rows with the values 1, null, 2 and again null.
You must satisfy. TRUE or unknown (due to a null). When Oracle evaluates a check line for a particular row, it indicates.
Thus, the null value passes the check, and the anonymous block will be successfully executed until an attempt is made to insert the value 3. After this, the error-handling block will clear the exception, no rollback will occur, and the table will contain four rows with the 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', '', 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
We present excerpts from the documentation (12.1.0.2) on the storage of various types of data in Oracle.
CHAR Data Type
The CHAR data type specifies a fixed-length character string in the database character set. You can specify your database character set. Oracle ensures that column lengths are measured in a column of semantics. If you insert the value of the column length
VARCHAR2 Data Type
The VARCHAR2 data type specifies a variable-length character string in the database character set. You can specify your database character set. If you’re logging in, you can’t give it a copy of it.
NUMBER Data Type
It is not necessary to make sure that it is a valid value. 10126, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes. It 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, we take an excerpt from the documentation about storing Null – values.
A value of a column. Nulls indicate missing, unknown, or inapplicable data. Nulls are stored between the lines with data values. In these cases, they require a column of (zero). There is no need to keep track of the number of lines. For example, if this is the case, the table is 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 occupy 2 bytes.
1) A and X, the value of the a 'Y' field is 1 byte, the value of the x 'D' field is 2 bytes
2) B and Y, 'Vasya' in b will be added 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 take 1 byte each.
4) C and Z. Both fields are NULL, but the Z field is the last in the table, so it does not take up space (0 bytes). Field C is 1 byte.
5) K and Z. Similar to the previous case. The value in the K field is 1 byte, in Z - 0.
6) I and J. According to the documentation, both values will take 2 bytes each. We consider 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.
So the correct options are: C and K, I and J, J and X.
CHAR Data Type
The CHAR data type specifies a fixed-length character string in the database character set. You can specify your database character set. Oracle ensures that column lengths are measured in a column of semantics. If you insert the value of the column length
VARCHAR2 Data Type
The VARCHAR2 data type specifies a variable-length character string in the database character set. You can specify your database character set. If you’re logging in, you can’t give it a copy of it.
NUMBER Data Type
It is not necessary to make sure that it is a valid value. 10126, then Oracle returns an error. Each NUMBER value requires from 1 to 22 bytes. It 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, we take an excerpt from the documentation about storing Null – values.
A value of a column. Nulls indicate missing, unknown, or inapplicable data. Nulls are stored between the lines with data values. In these cases, they require a column of (zero). There is no need to keep track of the number of lines. For example, if this is the case, the table is 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 occupy 2 bytes.
1) A and X, the value of the a 'Y' field is 1 byte, the value of the x 'D' field is 2 bytes
2) B and Y, 'Vasya' in b will be added 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 take 1 byte each.
4) C and Z. Both fields are NULL, but the Z field is the last in the table, so it does not take up space (0 bytes). Field C is 1 byte.
5) K and Z. Similar to the previous case. The value in the K field is 1 byte, in Z - 0.
6) I and J. According to the documentation, both values will take 2 bytes each. We consider 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.
So the correct options are: C and K, I and J, J and X.
What about 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
- About hundreds
- About a thousand
- About tens of thousands
Answer
According to the Oracle documentation (cited from 12.1):
For a B-tree index, this is the index.
If you are searching for a list of factors, it will help you. A low clustering factor indicates an efficient index scan.
In this case, it can be seen that the If the database performs a full table scan, then the database scans by the index key. The number of rows is scattered randomly across the database key. If the database performs a full table scan, then it won’t retrieve the rows.
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, it can be expected that the order of a thousand narrow number numbers will fit in one block, so the number of blocks, and as a result, the clustering factor will be of the order of tens .
For a B-tree index, this is the index.
If you are searching for a list of factors, it will help you. A low clustering factor indicates an efficient index scan.
In this case, it can be seen that the If the database performs a full table scan, then the database scans by the index key. The number of rows is scattered randomly across the database key. If the database performs a full table scan, then it won’t retrieve the rows.
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, it can be expected that the order of a thousand narrow number numbers will fit in one block, so the number of blocks, and as a result, the clustering factor will be of the order of tens .
At what values of N will the next script be successfully executed in a regular 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
Answer
According to the Oracle documentation (cited from 11.2):
Logical database limit
Thus, the total size of the indexed columns should not exceed 6Kb. Further depends on the selected encoding base. For the AL32UTF8 encoding, one character can occupy a maximum of 4 bytes, so 6 kilobytes in the worst case will fit around 1500 characters. Therefore, Oracle will prohibit the creation of an index with N = 400 (when the key length in the worst case is 1600 characters * 4 bytes + rowid length), while with N = 200 (and less), creating the index will work without problems.
Logical database limit
| Item | Type of Limit | Limit value |
| Indexes | Total size of indexed column | 75% of the database block minus some overhead |
Thus, the total size of the indexed columns should not exceed 6Kb. Further depends on the selected encoding base. For the AL32UTF8 encoding, one character can occupy a maximum of 4 bytes, so 6 kilobytes in the worst case will fit around 1500 characters. Therefore, Oracle will prohibit the creation of an index with N = 400 (when the key length in the worst case is 1600 characters * 4 bytes + rowid length), while with N = 200 (and less), creating the index will work without problems.
The INSERT statement with hint APPEND 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
- 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 fixed
Answer
In principle, this is a question of more logic. To find the right answer, I would suggest the following model of reasoning:
Thus, the expected response - the data will be loaded in normal (SQL) mode, the trigger will work.
According to the Oracle documentation (cited from 8.04):
If you want to use it, it will be possible to use it, it will use it, it will use it, Once there is a transaction in which you can receive error messages.
For example, if you are trying to use direct-load INSERT (serial or parallel), if you need to do this
- The insert in direct mode is performed by the direct formation of a data block, past the SQL engine, which ensures high speed. Thus, ensuring the execution of a trigger is very difficult, if not impossible, and there is no point in this, since it will still drastically slow down the insertion.
- Failure of the trigger will lead to the fact that with identical 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 data integrity and cannot be applied as a solution in production.
- 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, and if not, the operator is executed without taking into account the hint.
Thus, the expected response - the data will be loaded in normal (SQL) mode, the trigger will work.
According to the Oracle documentation (cited from 8.04):
If you want to use it, it will be possible to use it, it will use it, it will use it, Once there is a transaction in which you can receive error messages.
For example, if you are trying to use direct-load INSERT (serial or parallel), if you need to do this
What happens when the next 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
- The error associated with exceeding the maximum call nesting
- Error related to foreign key violation
- Lock error
Answer
The table and the 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 line, a successful trigger would trigger the insertion of the second line, so the trigger would work again, insert the third line, and so on until the statement crashes due to exceeding the maximum call nesting. However, another delicate moment works. At the time of execution of the trigger for the first inserted record is not yet committed. Therefore, a trigger that works in an autonomous transaction tries to insert a row in the table that is referenced by a foreign key to a record that is not yet committed. This leads to a wait (an autonomous transaction waits for the main commit to see if data can be inserted) and at the same time the main transaction waits for the autonomous commit to continue working after the trigger. There is a deadlock and as a result - an autonomous transaction is shot for the reason associated with locks .
After inserting the first line, a successful trigger would trigger the insertion of the second line, so the trigger would work again, insert the third line, and so on until the statement crashes due to exceeding the maximum call nesting. However, another delicate moment works. At the time of execution of the trigger for the first inserted record is not yet committed. Therefore, a trigger that works in an autonomous transaction tries to insert a row in the table that is referenced by a foreign key to a record that is not yet committed. This leads to a wait (an autonomous transaction waits for the main commit to see if data can be inserted) and at the same time the main transaction waits for the autonomous commit to continue working after the trigger. There is a deadlock and as a result - an autonomous transaction is shot for the reason associated with locks .
Source: https://habr.com/ru/post/459680/
All Articles