ProHoster > Blog > administración > Siguiendo los pasos de Highload++ Siberia 2019: 8 tareas en Oracle

Siguiendo los pasos de Highload++ Siberia 2019: 8 tareas en Oracle

Hi!

Los días 24 y 25 de junio se celebró en Novosibirsk la conferencia Highload++ Siberia 2019. Nuestros muchachos también estuvieron presentes reporte “Bases de datos de contenedores Oracle (CDB/PDB) y su uso práctico para el desarrollo de software”, publicaremos una versión de texto un poco más tarde. estuvo genial, gracias olegbunin para la organización, así como para todos los que vinieron.

Siguiendo los pasos de Highload++ Siberia 2019: 8 tareas en Oracle
En esta publicación nos gustaría compartir contigo los problemas que tuvimos en nuestro stand para que puedas poner a prueba tus conocimientos de Oracle. Debajo del corte hay 8 problemas, opciones de respuesta y explicación.

¿Cuál es el valor máximo de secuencia que veremos como resultado de ejecutar el 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
  • No, habrá un error.

respuestaSegún la documentación de Oracle (citada en 8.1.6):
Dentro de una única 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 apariciones de NEXTVAL. Si una declaración contiene referencias a CURRVAL y NEXTVAL, Oracle incrementa la secuencia y devuelve el mismo valor para CURRVAL y NEXTVAL independientemente de su orden dentro de la declaración.

Por lo tanto, la el valor máximo corresponderá al número de líneas, es decir 5.

¿Cuántas filas habrá en la tabla como resultado de ejecutar el 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 (citada en 11.2):

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

Las llamadas a HP desde el cliente también se consideran y procesan como una declaración única. Por lo tanto, la primera llamada de HP se completa con éxito después de haber insertado tres registros; la segunda llamada de HP finaliza con un error y revierte el cuarto registro que logró insertar; la tercera llamada falla, y hay tres registros en la tabla.

¿Cuántas filas habrá en la tabla como resultado de ejecutar el 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('Oops!');
end;
/

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

respuestaSegún la documentación de Oracle (citada en 11.2):

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

Por lo tanto, el valor nulo pasará la verificación y el bloque anónimo se ejecutará exitosamente hasta que se intente insertar el valor 3. Después de esto, el bloque de manejo de errores borrará la excepción, no se producirá ninguna reversión y Quedarán cuatro filas en la tabla. con valores 1, nulo, 2 y nulo nuevamente.

¿Qué pares de valores ocuparán la misma cantidad de 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', 'Вася', 10, 10, 'Д', 'Вася');

  • A y X
  • B e Y
  • C y K
  • C y Z
  • K y Z
  • yo y j
  • J y X
  • Todo listado

respuestaA continuación se muestran extractos de la documentación (12.1.0.2) sobre el almacenamiento de varios tipos de datos en Oracle.

Tipo de datos CARBÓN
El tipo de datos CHAR especifica una cadena de caracteres de longitud fija en el juego de caracteres de la base de datos. Usted especifica el juego de caracteres de la base de datos cuando crea su base de datos. Oracle garantiza que todos los valores almacenados en una columna CHAR tengan la longitud especificada por tamaño en la semántica de longitud seleccionada. Si inserta un valor que es más corto que la longitud de la columna, Oracle rellena el valor con la longitud de la columna.

Tipo de datos VARCHAR2
El tipo de datos VARCHAR2 especifica una cadena de caracteres de longitud variable en el juego de caracteres de la base de datos. Usted especifica el juego de caracteres de la base de datos cuando crea su base de datos. Oracle almacena un valor de carácter en una columna VARCHAR2 exactamente como lo especifica, sin espacios en blanco, siempre que el valor no exceda la longitud de la columna.

NÚMERO Tipo de datos
El tipo de datos NUMBER almacena cero, así como números fijos positivos y negativos con valores absolutos desde 1.0 x 10-130 hasta 1.0 x 10126, pero sin incluirlo. Si especifica una expresión aritmética cuyo valor tiene un valor absoluto mayor o igual a 1.0 x 10126, luego Oracle devuelve 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 de datos numéricos particular NÚMERO (p), donde p es la precisión de un valor dado, se puede calcular usando la siguiente fórmula: REDONDO((longitud(p)+s)/2))+1 donde s es igual a cero si el número es positivo y s es igual a 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 valores nulos indican datos faltantes, desconocidos o inaplicables. Los valores nulos se almacenan en la base de datos si se encuentran entre columnas con valores de datos. En estos casos requieren 1 byte para almacenar la longitud de la columna (cero). Los valores nulos finales de una fila no requieren almacenamiento porque un nuevo encabezado de fila indica que las columnas restantes de la fila anterior son nulas. Por ejemplo, si las últimas tres columnas de una tabla son nulas, no se almacenan datos para estas columnas.

A partir de estos datos construimos razonamientos. Suponemos que la base de datos utiliza 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' ocupa 2 bytes
2) B e Y, 'Vasya' en b, el valor se rellenará con espacios de hasta 10 caracteres y ocupará 14 bytes, 'Vasya' en d ocupará 8 bytes.
3) C y K. Ambos campos tienen el valor NULL, después de ellos hay campos significativos, por lo que ocupan 1 byte.
4) C y Z. Ambos campos tienen el valor NULL, pero el campo Z es el último de la tabla, por lo que 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. Calculamos la longitud usando 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);

  • Acerca de las decenas
  • alrededor de cientos
  • alrededor de miles
  • Alrededor de decenas de miles

respuestaSegún la documentación de Oracle (citada en 12.1):

Para un índice de árbol B, el factor de agrupamiento del índice mide la agrupación física de filas en relación con un valor de índice.

El factor de agrupación de índices ayuda al optimizador a decidir si una exploración de índice o una exploración completa de la tabla es más eficiente para determinadas consultas). Un factor de agrupamiento bajo indica una exploración de índice eficiente.

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

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

¿A qué valores de N se ejecutará con éxito el siguiente script en una base de datos normal con configuración 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 (citada en 11.2):

Límites de la base de datos lógica

Asunto
Tipo de límite
Valor 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 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 base seleccionada. Para la codificación AL32UTF8, un carácter puede ocupar un máximo de 4 bytes, por lo que, en el peor de los casos, unos 6 caracteres cabrán en 1500 kilobytes. Por lo tanto, Oracle no permitirá la creación de índices en N = 400 (cuando la longitud de clave en el peor de los casos es 1600 caracteres * 4 bytes + longitud de ID de fila), mientras que en N = 200 (o menos) La creación del índice funcionará sin problemas.

El operador INSERT con la sugerencia APPEND está diseñado para cargar datos en modo directo. ¿Qué pasa si se aplica a la mesa de la que cuelga el gatillo?

  • Los datos se cargarán en modo directo, el disparador funcionará como se esperaba
  • Los datos se cargarán en modo directo, pero el disparador no se ejecutará
  • Los datos se cargarán en modo convencional, el disparador funcionará como debería
  • Los datos se cargarán en modo convencional, pero el disparador no se ejecutará
  • Los datos no se cargarán, se registrará un error.

respuestaBásicamente, esto es más una cuestión de lógica. Para encontrar la respuesta correcta, sugeriría el siguiente modelo de razonamiento:

  1. La inserción en modo directo se realiza mediante la formación directa de un bloque de datos, sin pasar por el motor SQL, lo que garantiza una alta velocidad. Por lo tanto, garantizar la ejecución del disparador es muy difícil, si no imposible, y esto no tiene sentido, ya que de todos modos ralentizará radicalmente la inserción.
  2. Si no se ejecuta el disparador, si los datos de la tabla son los mismos, el estado de la base de datos en su conjunto (otras tablas) dependerá del modo en que se insertaron estos datos. Obviamente, esto destruirá la integridad de los datos y no se puede aplicar como solución en producción.
  3. La imposibilidad de realizar la operación solicitada generalmente se trata como un error. Pero aquí debemos recordar que APPEND es una sugerencia, y la lógica general de las sugerencias es que se tienen en cuenta si es posible, pero si no, el operador se ejecuta sin tener en cuenta la sugerencia.

Entonces la respuesta esperada es los datos se cargarán en modo normal (SQL), el disparador se activará.

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

Las violaciones de las restricciones harán que la declaración se ejecute en serie, utilizando la ruta de inserción convencional, sin advertencias ni mensajes de error. Una excepción es la restricción de que las declaraciones accedan a la misma tabla más de una vez en una transacción, lo que puede provocar mensajes de error.
Por ejemplo, si hay activadores o integridad referencial en la tabla, entonces la sugerencia APPEND se ignorará cuando intente utilizar INSERT de carga directa (serie o paralelo), así como la sugerencia o cláusula PARALLEL, si la hubiera.

¿Qué pasará cuando se ejecute 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);

  • Completar con exito
  • Fallo debido a error de sintaxis
  • Error: la transacción autónoma no es válida
  • Error relacionado con exceder el anidamiento máximo de llamadas
  • Error de infracción de clave externa
  • Error relacionado con cerraduras

respuestaLa tabla y el disparador se crean bastante correctamente y esta operación no debería generar problemas. También se permiten transacciones autónomas en un disparador; de lo contrario, por ejemplo, no sería posible el registro.

Después de insertar la primera fila, una activación exitosa del disparador provocaría que se insertara la segunda fila, lo que provocaría que el disparador se disparara nuevamente, insertando una tercera fila, y así sucesivamente hasta que la declaración fallara debido a que se excedió el anidamiento máximo de llamadas. Sin embargo, entra en juego otro punto sutil. En el momento en que se ejecuta el activador, la confirmación aún no se ha completado para el primer registro insertado. Por lo tanto, un disparador que se ejecuta en una transacción autónoma intenta insertar en la tabla una fila que hace referencia a una clave externa a un registro que aún no se ha confirmado. Esto da como resultado una espera (la transacción autónoma espera a que la transacción principal se confirme para ver si puede insertar datos) y, al mismo tiempo, la transacción principal espera a que la transacción autónoma continúe funcionando después del disparador. Se produce un punto muerto y, como resultado, la transacción autónoma se cancela por motivos relacionados con los bloqueos..

Solo los usuarios registrados pueden participar en la encuesta. Registrarsepor favor

¿Fue difícil?

  • Como dos dedos, inmediatamente decidí todo correctamente.

  • Realmente no, me equivoqué en un par de preguntas.

  • Resolví la mitad correctamente.

  • ¡Adiviné la respuesta dos veces!

  • escribo en los comentarios

14 usuarios votaron. 10 usuarios se abstuvieron.

Fuente: habr.com

Añadir un comentario