A raíz de Highload++ Siberia 2019 — 8 tareas sobre Oracle

¡Hola!

Los días 24 y 25 de junio tuvo lugar en Novosibirsk la conferencia Highload++ Siberia 2019. Nuestro equipo también estuvo presente. informe «Bases de datos en contenedor de Oracle (CDB/PDB) y su uso práctico para el desarrollo de software», publicaremos la versión textual un poco más tarde. Fue genial, gracias. olegbunin por la organización, así como a todos los que asistieron.

En esta publicación, nos gustaría compartir con ustedes las tareas que estaban en nuestro stand, para que puedan comprobar sus conocimientos en Oracle. A continuación, están 8 tareas, opciones de respuesta y explicaciones.

¿Cuál es el valor máximo de la secuencia que veremos como resultado de la ejecución del siguiente 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
• Ninguno, habrá un error.

RespuestaSegún la documentación de Oracle (cita de 8.1.6):
Dentro de una sola declaración SQL, Oracle incrementará la secuencia solo una vez por fila. Si una declaración contiene más de una referencia a NEXTVAL para una secuencia, Oracle incrementa la secuencia una vez y devuelve el mismo valor para todas las ocurrencias de NEXTVAL. Si una declaración contiene referencias tanto a CURRVAL como a NEXTVAL, Oracle incrementa la secuencia y devuelve el mismo valor tanto para CURRVAL como para NEXTVAL, independientemente de su orden dentro de la declaración.

Por lo tanto, el valor máximo corresponderá al número de filas, es decir, 5..

¿Cuántas filas habrá en la tabla como resultado de la ejecución del siguiente 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

RespuestaSegún la documentación de Oracle (cita de 11.2):

Antes de ejecutar cualquier declaración SQL, Oracle marca un punto de salvaguarda implícito (no disponible para usted). Luego, si la declaración falla, Oracle lo revierte automáticamente y devuelve el código de error aplicable a SQLCODE en el SQLCA. Por ejemplo, si una declaración INSERT causa un error al intentar insertar un valor duplicado en un índice único, la declaración se revierte.

La llamada a una SP desde el cliente también se considera y se procesa como una declaración única. Por lo tanto, la primera llamada a la SP se completa con éxito, insertando tres registros; la segunda llamada a la SP finaliza con un error y revierte el cuarto registro que logró insertar; la tercera llamada termina con un error, y en la tabla se quedan tres registros..

¿Cuántas filas habrá en la tabla como resultado de la ejecución del siguiente 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('¡Ups!');
end;
/

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

RespuestaSegún la documentación de Oracle (cita de 11.2):

Una restricción de verificación te permite especificar una condición que cada fila en la tabla debe cumplir. Para satisfacer la restricción, cada fila en la tabla debe hacer que la condición sea verdadera o desconocida (debido a un nulo). Cuando Oracle evalúa una condición de restricción de verificación para una fila particular, cualquier nombre de columna en la condición se refiere a los valores de columna en esa fila.

Por lo tanto, el valor nulo pasará la verificación, y el bloque anónimo se ejecutará con éxito hasta que intente insertar el valor 3. Después de esto, el bloque de manejo de errores apagará la excepción, no habrá un retroceso y quedarán cuatro filas en la tabla con los valores 1, nulo, 2 y nuevamente nulo.

¿Qué pares de valores ocuparán el mismo espacio en el bloque?

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 y X
• B y Y
• C y K
• C y Z
• K y Z
• I y J
• J y X
• Todos los enumerados

RespuestaPresentamos extractos de la documentación (12.1.0.2) sobre el almacenamiento de diferentes tipos de datos en Oracle.

Tipo de dato CHAR
El tipo de dato CHAR especifica una cadena de caracteres de longitud fija en el conjunto de caracteres de la base de datos. Especificas el conjunto de caracteres de la base de datos cuando creas tu base de datos. Oracle asegura que todos los valores almacenados en una columna CHAR tienen la longitud especificada por el tamaño en la semántica de longitud seleccionada. Si insertas un valor que es más corto que la longitud de la columna, Oracle completará el valor hasta la longitud de la columna.

Tipo de dato VARCHAR2
El tipo de dato VARCHAR2 especifica una cadena de caracteres de longitud variable en el conjunto de caracteres de la base de datos. Especificas el conjunto de caracteres de la base de datos cuando creas tu base de datos. Oracle almacena un valor de carácter en una columna VARCHAR2 exactamente como lo especificas, sin ningún relleno en blanco, siempre que el valor no exceda la longitud de la columna.

Tipo de dato NUMBER
El tipo de dato NUMBER almacena cero, así como números fijos positivos y negativos con valores absolutos desde 1.0 x 10-130 hasta, pero sin incluir, 1.0 x 10126. Si especificas una expresión aritmética cuyo valor tenga un valor absoluto mayor o igual a 1.0 x 10126, Oracle devolverá un error. Cada valor NUMBER requiere de 1 a 22 bytes. Teniendo esto en cuenta, el tamaño de la columna en bytes para un valor numérico particular NUMBER(p), donde p es la precisión de un valor dado, puede calcularse usando la siguiente fórmula: ROUND((length(p)+s)/2))+1 donde s es cero si el número es positivo, y s es 1 si el número es negativo.

Además, tomemos un extracto de la documentación sobre el almacenamiento de valores nulos.

Un nulo es la ausencia de un valor en una columna. Los nulos indican datos faltantes, desconocidos o no aplicables. Los nulos se almacenan en la base de datos si caen entre columnas con valores de datos. En estos casos, requieren 1 byte para almacenar la longitud de la columna (cero). Los nulos finales en una fila no requieren almacenamiento porque un nuevo encabezado de fila señala que las columnas restantes en la fila anterior son nulas. Por ejemplo, si las últimas tres columnas de una tabla son nulas, entonces no se almacena ningún dato para estas columnas.

A partir de estos datos, construimos razonamientos. Supongamos que en la base de datos se utiliza la codificación AL32UTF8. En esta codificación, las letras rusas ocuparán 2 bytes.

1) A y X, el valor del campo a 'Y' ocupa 1 byte, el valor del campo x 'D' – 2 bytes
2) B y Y, ‘Vasya’ en b se complementará con espacios hasta 10 caracteres y ocupará 14 bytes, ‘Vasya’ en d – ocupará 8 bytes.
3) C y K. Ambos campos tienen valor NULL, después de ellos hay campos significativos, por lo que ocupan 1 byte cada uno.
4) C y Z. Ambos campos tienen valor NULL, pero el campo Z es el último de la tabla, por lo tanto, no ocupa espacio (0 bytes). El campo C ocupa 1 byte.
5) K y Z. Similar al caso anterior. El valor en el campo K ocupa 1 byte, en Z – 0.
6) I y J. Según la documentación, ambos valores ocuparán 2 bytes. La longitud se calcula según la fórmula extraída de la documentación: round((1 + 0) / 2) + 1 = 1 + 1 = 2.
7) J y X. El valor en el campo J ocupará 2 bytes, el valor en el campo X ocupará 2 bytes.

En total, las opciones correctas son: C y K, I y J, J y X.

¿Cuál será aproximadamente el factor de agrupamiento del índice 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);

• Cientos.
• Miles.
• Decenas de miles.
• Cientos de miles.

RespuestaSegún la documentación de Oracle (cita de la 12.1):

Para un índice B-tree, el factor de agrupamiento del índice mide el agrupamiento físico de las filas en relación con un valor de índice.

El factor de agrupamiento del índice ayuda al optimizador a decidir si un escaneo de índice o un escaneo completo de la tabla es más eficiente para ciertas consultas. Un factor de agrupamiento bajo indica un escaneo de índice eficiente.

Un factor de agrupamiento que se acerca al número de bloques en una tabla indica que las filas están ordenadas físicamente en los bloques de la tabla por la clave del índice. Si la base de datos realiza un escaneo completo de la tabla, entonces tiende a recuperar las filas tal como están almacenadas en disco, ordenadas por la clave del índice. Un factor de agrupamiento que se acerca al número de filas indica que las filas están dispersas aleatoriamente a través de los bloques de la base de datos en relación con la clave del índice. Si la base de datos realiza un escaneo completo de la tabla, no recuperaría las filas en ningún orden ordenado por esta clave del índice.

En este caso, los datos están perfectamente ordenados, por lo que el factor de agrupamiento será igual o cercano al número de bloques ocupados en la tabla. Para un tamaño estándar de bloque de 8 kilobytes, se puede esperar que en un bloque quepan alrededor de mil valores numéricos estrechos, por lo que el número de bloques, y como consecuencia el factor de agrupamiento, será decenas..

¿Para qué valores de N se ejecutará con éxito el siguiente script en una base de datos normal con configuraciones estándar?

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

RespuestaSegún la documentación de Oracle (cita de 11.2):

Límites Lógicos de la Base de Datos

Ítem
Tipo de Límite
Valor del Límite

Índices
Tamaño total de la columna indexada
75% del tamaño del bloque de la base de datos menos algunos gastos generales.

Por lo tanto, el tamaño total de las columnas indexadas no debe exceder los 6 KB. Lo que sucede a continuación depende de la codificación de la base de datos elegida. Para la codificación AL32UTF8, un carácter puede ocupar un máximo de 4 bytes, por lo que en 6 kilobytes, en el peor de los casos, caben aproximadamente 1500 caracteres. Por lo tanto, Oracle prohibirá la creación del índice cuando N = 400 (cuando la longitud de la clave en el peor de los casos será de 1600 caracteres * 4 bytes + la longitud de rowid), mientras que cuando N = 200 (y menos) la creación del índice funcionará sin problemas.

El operador INSERT con el hint APPEND está diseñado para cargar datos en modo directo. ¿Qué sucederá si se aplica a una tabla que tiene un trigger?

• Los datos se cargarán en modo directo, el trigger se activará como debe
• Los datos se cargarán en modo directo, pero el trigger no se ejecutará
• Los datos se cargarán en modo convencional, el trigger se activará como debe
• Los datos se cargarán en modo convencional, pero el trigger no se ejecutará
• Los datos no se cargarán, se registrará un error

RespuestaEn principio, esta es una cuestión más lógica. Para encontrar la respuesta correcta, propongo el siguiente modelo de razonamiento:

1. La inserción en modo directo se realiza generando directamente el bloque de datos, desviando el motor SQL, lo que garantiza una alta velocidad. Por lo tanto, es bastante difícil garantizar la ejecución del trigger, si es que es posible, y no tiene sentido, ya que de todos modos ralentizaría drásticamente la inserción.
2. La no ejecución del trigger hará que, con los mismos datos en la tabla, el estado de la base de datos en general (otras tablas) dependa de en qué modo se insertaron esos datos. Esto obviamente destruirá la integridad de los datos y no puede aplicarse como solución en producción.
3. La imposibilidad de ejecutar la operación solicitada, en términos generales, se interpreta como un error. Pero aquí hay que recordar que APPEND es un hint, y la lógica general de los hints es que se tienen en cuenta si es posible, si no, la operación se ejecuta sin tener en cuenta el hint.

Por lo tanto, la respuesta esperada es: los datos se cargarán en modo normal (SQL), el trigger se activará.

Según la documentación de Oracle (cita de 8.04):

Las violaciones de las restricciones harán que la instrucción se ejecute de forma secuencial, utilizando la ruta de inserción convencional, sin advertencias ni mensajes de error. Una excepción es la restricción sobre las declaraciones que acceden a la misma tabla más de una vez en una transacción, lo que puede causar mensajes de error.
Por ejemplo, si hay triggers o integridad referencial en la tabla, entonces la sugerencia APPEND será ignorada cuando intentes utilizar INSERT de carga directa (serial o paralelo), así como la sugerencia o cláusula PARALLEL, si la hay.

¿Qué sucederá al ejecutar el siguiente 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);

• Ejecución exitosa
• Error debido a un error de sintaxis
• Error relacionado con la no permitibilidad de transacciones autónomas
• Error relacionado con el exceso de la profundidad máxima de llamadas
• Error relacionado con la violación de clave externa
• Error relacionado con bloqueos

RespuestaLa tabla y el trigger se crean correctamente y esta operación no debería causar problemas. Las transacciones autónomas en el trigger también están permitidas, de lo contrario, no sería posible, por ejemplo, la auditoría.

Después de insertar la primera fila, la exitosa activación del trigger conduciría a la inserción de una segunda fila, por lo que el trigger se activaría de nuevo, insertando una tercera fila y así sucesivamente hasta que la sentencia fallara por exceso de la profundidad máxima de llamadas. Sin embargo, hay otro punto sutil. En el momento de la ejecución del trigger para el primer registro insertado, aún no se ha realizado el commit. Por lo tanto, el trigger, que opera en una transacción autónoma, intenta insertar en la tabla un registro que hace referencia a una fila aún no confirmada. Esto lleva a una espera (la transacción autónoma espera el commit principal para saber si puede insertar los datos) y al mismo tiempo la transacción principal espera el commit de la autónoma para continuar después del trigger. Se produce un deadlock y, como consecuencia, la transacción autónoma se aborta debido a problemas de bloqueos.

Solo los usuarios registrados pueden participar en la encuesta. Inicie sesión, por favor.

¿Fue difícil?

• Como dos dedos, lo resolví todo correctamente de inmediato.

• No tanto, cometí un par de errores.

• Resolvió la mitad correctamente.

• ¡Adiviné la respuesta dos veces!

• Escribiré en los comentarios

14 usuarios votaron. 10 usuarios se abstuvieron.

Fuente: habr.com