ProHoster > Blog > Administración > Seguindo os pasos de Highload++ Siberia 2019 - 8 tarefas en Oracle

Seguindo os pasos de Highload++ Siberia 2019 - 8 tarefas en Oracle

Ola!

Os días 24 e 25 de xuño celebrouse en Novosibirsk a conferencia Highload++ Siberia 2019. Os nosos rapaces tamén estiveron alí informe “Base de datos de contedores de Oracle (CDB/PDB) e o seu uso práctico para o desenvolvemento de software”, publicaremos unha versión de texto un pouco máis adiante. Foi xenial, grazas olegbunin para a organización, así como para todos os que acudiron.

Seguindo os pasos de Highload++ Siberia 2019 - 8 tarefas en Oracle
Nesta publicación, gustaríanos compartir con vostede os problemas que tivemos no noso stand para que poidades probar os seus coñecementos sobre Oracle. Debaixo do corte hai 8 problemas, opcións de resposta e explicación.

Cal é o valor máximo de secuencia que veremos como resultado de executar o seguinte 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
  • Non, haberá un erro

ResponderSegundo a documentación de Oracle (citada da 8.1.6):
Dentro dunha única instrución SQL, Oracle incrementará a secuencia só unha vez por fila. Se unha instrución contén máis dunha referencia a NEXTVAL para unha secuencia, Oracle incrementa a secuencia unha vez e devolve o mesmo valor para todas as ocorrencias de NEXTVAL. Se unha instrución contén referencias tanto a CURRVAL como a NEXTVAL, Oracle incrementa a secuencia e devolve o mesmo valor para CURRVAL e NEXTVAL independentemente da súa orde dentro da instrución.

Así, o o valor máximo corresponderá ao número de liñas, é dicir, 5.

Cantas filas haberá na táboa como resultado de executar o seguinte 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

ResponderSegundo a documentación de Oracle (citada da 11.2):

Antes de executar calquera instrución SQL, Oracle marca un punto de salvamento implícito (non dispoñible para vostede). Entón, se a instrución falla, Oracle revélaa automaticamente e devolve o código de erro aplicable a SQLCODE no SQLCA. Por exemplo, se unha instrución INSERT provoca un erro ao tentar inserir un valor duplicado nun índice único, a instrución retrovólvese.

A chamada a HP desde o cliente tamén se considera e procesa como unha única declaración. Así, a primeira chamada de HP finaliza con éxito, tendo inseridos tres rexistros; a segunda chamada de HP remata cun erro e retrotrae o cuarto rexistro que conseguiu inserir; a terceira chamada falla, e hai tres rexistros na táboa.

Cantas filas haberá na táboa como resultado de executar o seguinte 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

ResponderSegundo a documentación de Oracle (citada da 11.2):

Unha restrición de verificación permítelle especificar unha condición que debe cumprir cada fila da táboa. Para satisfacer a restrición, cada fila da táboa debe facer que a condición sexa VERDADEIRA ou descoñecida (debido a un valor nulo). Cando Oracle avalía unha condición de restrición de verificación para unha fila en particular, os nomes de columnas da condición fan referencia aos valores de columna desa fila.

Así, o valor nulo pasará a verificación e o bloque anónimo executarase con éxito ata que se intente inserir o valor 3. Despois diso, o bloque de tratamento de erros borrará a excepción, non se producirá ningunha reversión e quedarán catro filas na táboa cos valores 1, nulo, 2 e nulo de novo.

Que pares de valores ocuparán a mesma cantidade de espazo no 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 e X
  • B e Y
  • C e K
  • C e Z
  • K e Z
  • eu e J
  • J e X
  • Todos listados

ResponderAquí tes fragmentos da documentación (12.1.0.2) sobre o almacenamento de varios tipos de datos en Oracle.

Tipo de datos CHAR
O tipo de datos CHAR especifica unha cadea de caracteres de lonxitude fixa no conxunto de caracteres da base de datos. Especifica o conxunto de caracteres da base de datos cando crea a súa base de datos. Oracle garante que todos os valores almacenados nunha columna CHAR teñan a lonxitude especificada polo tamaño na semántica de lonxitude seleccionada. Se insire un valor que é máis curto que a lonxitude da columna, entón Oracle axusta o valor á lonxitude da columna.

VARCHAR2 Tipo de datos
O tipo de datos VARCHAR2 especifica unha cadea de caracteres de lonxitude variable no conxunto de caracteres da base de datos. Especifica o conxunto de caracteres da base de datos cando crea a súa base de datos. Oracle almacena un valor de carácter nunha columna VARCHAR2 exactamente como o especificas, sen ningún recheo en branco, sempre que o valor non exceda a lonxitude da columna.

Tipo de datos NUMBER
O tipo de datos NUMBER almacena cero, así como números fixos positivos e negativos con valores absolutos de 1.0 x 10-130 a 1.0 x 10126, pero sen incluír. Se especifica unha expresión aritmética cuxo valor teña un valor absoluto maior ou igual a 1.0 x 10126, entón Oracle devolve un erro. Cada valor de NUMBER require de 1 a 22 bytes. Tendo isto en conta, o tamaño da columna en bytes para un determinado valor de datos numéricos NUMBER(p), onde p é a precisión dun valor determinado, pódese calcular mediante a seguinte fórmula: REDONDA((longitud(p)+s)/2))+1 onde s é igual a cero se o número é positivo e s é igual a 1 se o número é negativo.

Ademais, tomemos un extracto da documentación sobre o almacenamento de valores nulos.

Un nulo é a ausencia dun valor nunha columna. Os valores nulos indican datos faltantes, descoñecidos ou inaplicables. Os valores nulos almacénanse na base de datos se están entre columnas con valores de datos. Nestes casos, requiren 1 byte para almacenar a lonxitude da columna (cero). Os valores nulos posteriores nunha fila non requiren almacenamento porque un novo encabezado de fila indica que as columnas restantes da fila anterior son nulas. Por exemplo, se as tres últimas columnas dunha táboa son nulas, non se almacenan datos para estas columnas.

A partir destes datos, construímos razoamentos. Supoñemos que a base de datos usa a codificación AL32UTF8. Nesta codificación, as letras rusas ocuparán 2 bytes.

1) A e X, o valor do campo a "Y" leva 1 byte, o valor do campo x "D" leva 2 bytes
2) B e Y, "Vasya" en b o valor encherase con espazos de ata 10 caracteres e levará 14 bytes, "Vasya" en d levará 8 bytes.
3) C e K. Ambos campos teñen o valor NULL, despois deles hai campos significativos, polo que ocupan 1 byte.
4) C e Z. Ambos campos teñen o valor NULL, pero o campo Z é o último da táboa, polo que non ocupa espazo (0 bytes). O campo C ocupa 1 byte.
5) K e Z. Semellante ao caso anterior. O valor no campo K ocupa 1 byte, en Z – 0.
6) I e J. Segundo a documentación, ambos os valores levarán 2 bytes. Calculamos a lonxitude mediante a fórmula extraída da documentación: round( (1 + 0)/2) +1 = 1 + 1 = 2.
7) J e X. O valor do campo J levará 2 bytes, o valor do campo X levará 2 bytes.

En total, as opcións correctas son: C e K, I e J, J e X.

Cal será aproximadamente o factor de agrupación do í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);

  • Unhas decenas
  • Uns centos
  • Uns miles
  • Unhas decenas de miles

ResponderSegundo a documentación de Oracle (citada da 12.1):

Para un índice de árbore B, o factor de agrupación de índices mide a agrupación física de filas en relación cun valor de índice.

O factor de agrupación de índices axuda ao optimizador a decidir se unha exploración de índices ou de táboas completas é máis eficiente para determinadas consultas). Un factor de agrupación baixo indica unha exploración de índices eficiente.

Un factor de agrupación que se aproxima ao número de bloques nunha táboa indica que as filas están ordenadas fisicamente nos bloques da táboa pola clave de índice. Se a base de datos realiza unha exploración completa da táboa, entón a base de datos tende a recuperar as filas a medida que se almacenan no disco ordenadas pola clave de índice. Un factor de agrupación próximo ao número de filas indica que as filas están espalladas aleatoriamente polos bloques da base de datos en relación coa clave de índice. Se a base de datos realiza unha exploración completa da táboa, a base de datos non recuperaría as filas en ningunha orde ordenada por esta clave de índice.

Neste caso, os datos están ordenados idealmente, polo que o factor de agrupación será igual ou próximo ao número de bloques ocupados na táboa. Para un tamaño de bloque estándar de 8 kilobytes, pode esperar que uns mil valores de números estreitos encaixan nun bloque, polo que o número de bloques e, como resultado, o factor de agrupación será unhas decenas.

Con que valores de N executarase con éxito o seguinte script nunha 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

ResponderSegundo a documentación de Oracle (citada da 11.2):

Límites da base de datos lóxica

Elemento
Tipo de límite
Valor límite

Índices
Tamaño total da columna indexada
75% do tamaño do bloque da base de datos menos algunha sobrecarga

Así, o tamaño total das columnas indexadas non debe superar os 6 Kb. O que ocorre despois depende da codificación base seleccionada. Para a codificación AL32UTF8, un carácter pode ocupar un máximo de 4 bytes, polo que no peor dos casos, uns 6 caracteres encaixarán en 1500 kilobytes. Polo tanto, Oracle non permitirá a creación de índices en N = 400 (cando a lonxitude da clave no peor dos casos é de 1600 caracteres * 4 bytes + lonxitude de rowid), mentres en N = 200 (ou menos) a creación do índice funcionará sen problemas.

O operador INSERT coa suxestión APPEND está deseñado para cargar datos en modo directo. Que pasa se se aplica á mesa na que pendura o gatillo?

  • Os datos cargaranse en modo directo, o disparador funcionará como se esperaba
  • Os datos cargaranse en modo directo, pero o disparador non se executará
  • Os datos cargaranse en modo convencional, o disparador funcionará como debería
  • Os datos cargaranse en modo convencional, pero o disparador non se executará
  • Non se cargarán os datos, rexistrarase un erro

ResponderBasicamente, isto é máis unha cuestión de lóxica. Para atopar a resposta correcta, suxeriría o seguinte modelo de razoamento:

  1. A inserción en modo directo realízase mediante a formación directa dun bloque de datos, evitando o motor SQL, o que garante unha alta velocidade. Así, garantir a execución do gatillo é moi difícil, se non imposible, e iso non ten sentido, xa que aínda ralentizará radicalmente a inserción.
  2. Se non se executa o disparador, se os datos da táboa son os mesmos, o estado da base de datos no seu conxunto (outras táboas) dependerá do modo en que se inserisen estes datos. Obviamente, isto destruirá a integridade dos datos e non se pode aplicar como solución na produción.
  3. A incapacidade de realizar a operación solicitada trátase xeralmente como un erro. Pero aquí debemos lembrar que APPEND é unha suxestión, e a lóxica xeral das suxestións é que se teñan en conta se é posible, pero se non, o operador execútase sen ter en conta a suxestión.

Entón, a resposta esperada é os datos cargaranse en modo normal (SQL), o disparador dispararase.

Segundo a documentación de Oracle (citada da 8.04):

As violacións das restricións farán que a instrución se execute en serie, utilizando a ruta de inserción convencional, sen avisos nin mensaxes de erro. Unha excepción é a restrición de que as declaracións accedan á mesma táboa máis dunha vez nunha transacción, o que pode provocar mensaxes de erro.
Por exemplo, se os disparadores ou a integridade referencial están presentes na táboa, ignorarase a suxestión APPEND cando intente utilizar INSERT de carga directa (en serie ou paralela), así como a suxestión ou cláusula PARALLEL, se hai.

Que ocorrerá cando se execute o seguinte 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);

  • Finalización exitosa
  • Fallo debido a un erro de sintaxe
  • Erro: a transacción autónoma non é válida
  • Erro relacionado coa superación do aniñamento máximo de chamadas
  • Erro de violación da clave estranxeira
  • Erro relacionado cos bloqueos

ResponderA táboa e o disparador créanse correctamente e esta operación non debería xerar problemas. Tamén se permiten transaccións autónomas nun disparador, se non, o rexistro non sería posible, por exemplo.

Despois de inserir a primeira fila, un disparo exitoso do disparador faría que se inserise a segunda fila, provocando que o disparador se dispare de novo, inserindo unha terceira fila, e así sucesivamente ata que fallou a instrución debido a que se superou o máximo de aniñamento de chamadas. Porén, outro punto sutil entra en xogo. No momento en que se executa o disparador, a confirmación aínda non se completou para o primeiro rexistro inserido. Polo tanto, un disparador que se executa nunha transacción autónoma tenta inserir na táboa unha fila que fai referencia a unha chave estranxeira a un rexistro que aínda non foi confirmado. Isto dá lugar a unha espera (a transacción autónoma espera a que a transacción principal se comprometa para ver se pode inserir datos) e ao mesmo tempo a transacción principal agarda a que a transacción autónoma siga funcionando despois do desencadenamento. Prodúcese un punto morto e, como resultado, a transacción autónoma cancela por motivos relacionados cos bloqueos.

Só os usuarios rexistrados poden participar na enquisa. Rexístrate, por favor.

Foi difícil?

  • Como dous dedos, inmediatamente decidín todo correctamente.

  • En realidade non, equivoqueime nun par de preguntas.

  • Resolvín a metade correctamente.

  • Adivinei a resposta dúas veces!

  • Vou escribir nos comentarios

Votaron 14 usuarios. 10 usuarios abstivéronse.

Fonte: www.habr.com

Engadir un comentario