🥇Após o Highload++ Siberia 2019 — 8 desafios sobre Oracle

Olá!

Nos dias 24 e 25 de junho, ocorreu em Novosibirsk a conferência Highload++ Siberia 2019. Nossa equipe também esteve lá. palestra "Bancos de dados em contêineres Oracle (CDB/PDB) e seu uso prático para o desenvolvimento de software", publicaremos a versão textual um pouco mais tarde. Foi incrível, obrigado. olegbunin pela organização, e a todos que compareceram.

Neste post, gostaríamos de compartilhar com vocês as questões que estavam em nosso estande, para que vocês possam testar seus conhecimentos em Oracle. Abaixo — 8 questões, opções de respostas e explicações.

Qual é o valor máximo da sequência que veremos como resultado da execução do 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
Nenhum, haverá um erro

RespostaSegundo a documentação Oracle (citada da versão 8.1.6):
Dentro de uma única instrução SQL, o Oracle irá incrementar a sequência apenas uma vez por linha. Se uma instrução contiver mais de uma referência a NEXTVAL para uma sequência, o Oracle incrementa a sequência uma vez e retorna o mesmo valor para todas as ocorrências de NEXTVAL. Se uma instrução contém referências a tanto CURRVAL quanto NEXTVAL, o Oracle incrementa a sequência e retorna o mesmo valor para ambas, CURRVAL e NEXTVAL, independentemente da ordem dentro da instrução.

Portanto, o valor máximo corresponderá ao número de linhas, ou seja, 5.

Quantas linhas haverá na tabela como resultado da execução do 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);

RespostaSegundo a documentação Oracle (citada da versão 11.2):

Antes de executar qualquer instrução SQL, o Oracle marca um ponto de salvamento implícito (não disponível para você). Depois, se a instrução falhar, o Oracle reverte automaticamente e retorna o código de erro aplicável para SQLCODE no SQLCA. Por exemplo, se uma instrução INSERT causar erro ao tentar inserir um valor duplicado em um índice único, a instrução é revertida.

A chamada de PL/SQL a partir do cliente também é considerada e tratada como uma única instrução. Assim, a primeira chamada de PL/SQL é concluída com sucesso, inserindo três registros; a segunda chamada de PL/SQL resulta em um erro e reverte o quarto registro, que foi inserido; a terceira chamada resulta em erro, e na tabela permanecem três registros..

Quantas linhas haverá na tabela como resultado da execução do 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;
/

RespostaSegundo a documentação Oracle (citada da versão 11.2):

Uma restrição de verificação permite especificar uma condição que cada linha na tabela deve satisfazer. Para satisfazer a restrição, cada linha na tabela deve tornar a condição verdadeira ou desconhecida (devido a um valor nulo). Quando o Oracle avalia a condição da restrição de verificação para uma linha específica, quaisquer nomes de colunas na condição referem-se aos valores das colunas nessa linha.

Assim, o valor nulo passará na verificação, e o bloco anônimo será executado com sucesso até a tentativa de inserir o valor 3. Após isso, o bloco de tratamento de erros apagará a exceção, não ocorrerá rollback e a tabela permanecerá com quatro linhas com valores 1, nulo, 2 e novamente nulo.

Quais pares de valores ocuparão os mesmos volumes de espaço no bloco?

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', 'Vasiliy', 10, 10, 'D', 'Vasiliy');

A e X
B e Y
C e K
C e Z
K e Z
I e J
J e X
Todos os mencionados

RespostaVamos apresentar trechos da documentação (12.1.0.2) sobre o armazenamento de diferentes tipos de dados no Oracle.

Tipo de Dados CHAR
O tipo de dados CHAR especifica uma string de caracteres de comprimento fixo no conjunto de caracteres do banco de dados. Você especifica o conjunto de caracteres do banco de dados ao criar seu banco de dados. O Oracle garante que todos os valores armazenados em uma coluna CHAR tenham o comprimento especificado pelo tamanho na semântica de comprimento selecionada. Se você inserir um valor que é mais curto que o comprimento da coluna, então o Oracle preenche o valor com espaços até o comprimento da coluna.

Tipo de Dados VARCHAR2
O tipo de dados VARCHAR2 especifica uma string de caracteres de comprimento variável no conjunto de caracteres do banco de dados. Você especifica o conjunto de caracteres do banco de dados ao criar seu banco de dados. O Oracle armazena um valor de caractere em uma coluna VARCHAR2 exatamente como você o especifica, sem qualquer preenchimento com espaços, desde que o valor não exceda o comprimento da coluna.

Tipo de Dados NUMBER
O tipo de dados NUMBER armazena zero, bem como números fixos positivos e negativos com valores absolutos de 1.0 x 10-130 a, mas não incluindo, 1.0 x 10126. Se você especificar uma expressão aritmética cujo valor tenha um valor absoluto maior ou igual a 1.0 x 10126, então o Oracle retorna um erro. Cada valor NUMBER requer de 1 a 22 bytes. Tendo isso em conta, o tamanho da coluna em bytes para um valor numérico específico NUMBER(p), onde p é a precisão de um determinado valor, pode ser calculado usando a seguinte fórmula: ROUND((length(p)+s)/2))+1 onde s é igual a zero se o número for positivo, e s é igual a 1 se o número for negativo.

Além disso, vamos apresentar um trecho da documentação sobre o armazenamento de valores nulos.

Um nulo é a ausência de um valor em uma coluna. Nulos indicam dados faltantes, desconhecidos ou não aplicáveis. Os nulos são armazenados no banco de dados se caírem entre colunas com valores de dados. Nesses casos, eles requerem 1 byte para armazenar o comprimento da coluna (zero). Nulos finais em uma linha não requerem armazenamento porque um novo cabeçalho de linha sinaliza que as colunas restantes na linha anterior estão nulas. Por exemplo, se as três últimas colunas de uma tabela estão nulas, então nenhum dado é armazenado para essas colunas.

Com base nesses dados, elaboramos raciocínios. Consideramos que no DB está sendo usada a codificação AL32UTF8. Nessa codificação, letras russas ocuparão 2 bytes.

1) A e X, o valor do campo a ‘Y’ ocupa 1 byte, o valor do campo x ‘D’ – 2 bytes.
2) B e Y, 'Vasja' em b será preenchido com espaços até 10 caracteres e ocupará 14 bytes, 'Vasja' em d ocupará 8 bytes.
3) C e K. Ambos os campos têm valor NULL, e após eles existem campos significativos, então ocupam 1 byte cada.
4) C e Z. Ambos os campos têm valor NULL, mas o campo Z é o último na tabela, portanto não ocupa espaço (0 bytes). O campo C ocupa 1 byte.
5) K e Z. Similar ao caso anterior. O valor no campo K ocupa 1 byte, enquanto em Z ocupa 0.
6) I e J. De acordo com a documentação, ambos os valores ocuparão 2 bytes. A duração é calculada pela fórmula da documentação: round((1 + 0) / 2) + 1 = 1 + 1 = 2.
7) J e X. O valor no campo J ocupará 2 bytes, e o valor no campo X ocupará 2 bytes.

Portanto, as opções corretas são: C e K, I e J, J e X.

Qual será aproximadamente o fator de clustering do índice T_I?

create table t (i integer);
 
inserir em t selecionar rownum de dual conectar por nível <= 10000;
 
create index t_i on t(i);

Cerca de dezenas
Cerca de centenas
Cerca de milhares
Cerca de dezenas de milhares

RespostaDe acordo com a documentação da Oracle (citada a partir da versão 12.1):

Para um índice B-tree, o fator de clustering do índice mede o agrupamento físico das linhas em relação a um valor de índice.

O fator de clustering do índice ajuda o otimizador a decidir se uma varredura de índice ou uma varredura completa da tabela é mais eficiente para certas consultas. Um fator de clustering baixo indica uma varredura de índice eficiente.

Um fator de clustering próximo ao número de blocos em uma tabela indica que as linhas estão ordenadas fisicamente nos blocos da tabela pelo valor da chave do índice. Se o banco de dados realizar uma varredura completa da tabela, ele tende a recuperar as linhas na ordem em que estão armazenadas no disco, ordenadas pela chave do índice. Um fator de clustering próximo ao número de linhas indica que as linhas estão espalhadas aleatoriamente across os blocos do banco de dados em relação à chave do índice. Se o banco de dados realizar uma varredura completa da tabela, ele não recuperaria as linhas em nenhuma ordem ordenada por essa chave do índice.

Neste caso, os dados estão perfeitamente ordenados, portanto o fator de clustering será igual ou próximo ao número de blocos ocupados na tabela. Para um tamanho padrão de bloco de 8 kilobytes, pode-se esperar que em um bloco caberão cerca de mil valores de número estreito, portanto o número de blocos, e consequentemente o fator de clustering, será cerca de dezenas.

Quais valores de N permitirão que o seguinte script seja executado com sucesso em um banco de dados padrão com configurações padrão?

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

RespostaSegundo a documentação Oracle (citada da versão 11.2):

Limites de Banco de Dados Lógico

Item
Tipo de Limite
Valor do Limite

Índices
Tamanho total da coluna indexada
75% do tamanho do bloco do banco de dados menos alguma sobrecarga

Assim, o tamanho total das colunas indexadas não deve exceder 6 KB. O que vem a seguir depende da codificação escolhida para o banco de dados. Para a codificação AL32UTF8, um caractere pode ocupar no máximo 4 bytes; assim, em 6 kilobytes, no pior cenário, caberão cerca de 1500 caracteres. Portanto, o Oracle proibirá a criação do índice quando N = 400 (quando o comprimento da chave, no pior caso, for 1600 caracteres * 4 bytes + o comprimento do rowid), enquanto para N = 200 (ou menos) a criação do índice funcionará sem problemas.

O operador INSERT com o hint APPEND é destinado a carregar dados em modo direto. O que acontecerá se ele for aplicado a uma tabela que tem um trigger?

Os dados serão carregados em modo direto, o trigger será acionado como deveria
Os dados serão carregados em modo direto, mas o trigger não será executado
Os dados serão carregados em modo convencional, o trigger será acionado como deveria
Os dados serão carregados em modo convencional, mas o trigger não será executado
Os dados não serão carregados, um erro será registrado

RespostaNa verdade, esta questão é mais lógica. Para encontrar a resposta correta, eu sugeriria o seguinte modelo de raciocínio:

A inserção em modo direto é realizada criando diretamente o bloco de dados, saltando o motor SQL, o que garante alta velocidade. Assim, é muito difícil garantir a execução do trigger, se é que é possível, e não faz sentido, pois isso de qualquer forma atrasará drasticamente a inserção.
A não execução do trigger fará com que, com dados idênticos na tabela, o estado do banco de dados como um todo (outras tabelas) dependa de qual modo esses dados foram inseridos. Isso obviamente quebrará a integridade dos dados e não pode ser aplicado como solução em produção.
A impossibilidade de realizar a operação solicitada, de um modo geral, é considerada um erro. Mas aqui é preciso lembrar que APPEND é um hint, e a lógica geral dos hints é que eles são considerados quando possível, se não, o operador é executado sem levar em conta o hint.

Assim, a resposta esperada é os dados serão carregados no modo normal (SQL), o trigger será acionado.

De acordo com a documentação do Oracle (citado de 8.04):

Violações das restrições farão com que a instrução seja executada em série, usando o caminho de inserção convencional, sem avisos ou mensagens de erro. Uma exceção é a restrição em instruções que acessam a mesma tabela mais de uma vez em uma transação, o que pode causar mensagens de erro.
Por exemplo, se existem disparadores ou integridade referencial na tabela, o hint APPEND será ignorado quando você tentar usar INSERT de carregamento direto (serial ou paralelo), assim como o hint ou cláusula PARALLEL, se houver.

O que acontecerá ao executar 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);

Execução bem-sucedida
Falha devido a erro de sintaxe
Erro relacionado à impossibilidade de transação autônoma
Erro relacionado ao limite máximo de chamadas aninhadas excedido
Erro relacionado à violação de chave estrangeira
Erro relacionado a bloqueios

RespostaA tabela e o disparador são criados corretamente e essa operação não deve causar problemas. Transações autônomas em disparadores também são permitidas; caso contrário, não seria possível, por exemplo, registrar eventos.

Após a inserção da primeira linha, a ativação bem-sucedida do disparador levaria à inserção de uma segunda linha, o que acionaria novamente o disparador, insertando uma terceira linha e assim por diante, até que a instrução falhasse devido à excedência do limite máximo de chamadas aninhadas. No entanto, surge outro ponto sutil. No momento da execução do disparador para o primeiro registro inserido, o commit ainda não foi realizado. Portanto, o disparador, que funciona em uma transação autônoma, tenta inserir na tabela uma linha que referencia uma chave estrangeira de um registro que ainda não foi comitado. Isso leva a uma espera (a transação autônoma aguarda o commit principal para entender se os dados podem ser inseridos) e, simultaneamente, a transação principal aguarda o commit da autônoma para continuar o trabalho após o disparador. Ocorre um deadlock e, como resultado, a transação autônoma é abortada devido a problemas relacionados a bloqueios..

Apenas usuários registrados podem participar da pesquisa. Entre, por favor.