Custo de Execução de Queries | Oracle
Valores booleanos em bases de dados
Em bases de dados não é nada aconselhável a utilização de booleanos. “Dados” como ‘Sim’ ou ‘Não’ não tem grande significado e geram múltiplas interpretações pelo que é sempre melhor atribuir valores explícitos, ou seja, que sejam clara e univocamente compreensíveis pelos utilizadores.
Na presença de valores deste tipo é frequente que os utilizadores tentem interpretá-los à sua maneira ou, pior ainda, fazendo suposições que estão longe do que o pretendido pelo sistema de informação.
Quando, por exemplo, uma tabela contém um campo denominado “Persistente” onde se pretende armazenar o facto de esse produto ter um carácter persistente após ter sido aplicado é bem mais explícito, e livre de falsas interpretações, se os valores forem do género ‘Muito persistente , ‘Não persistente” em vez de ‘Sim’, ‘Não’ ou ‘S’, ‘N’.
A Arte das Bases de Dados
O planeamento é a mãe de todo o sucesso.
Uma ideia tão simples que se expressa numa única linha, mas que no entanto não é habitualmente levada a sério.
Ao longo de cerca de um quarto de século dedicado ao ensino e investigação de matérias associadas com o mundo das bases de dados relacionais, tenho-me deparado com inúmeras situações em que a pressa e o desleixo na conceptualização destes sistemas têm conduzido a produtos imaturos, pouco rigorosos, e imediatamente desactualizados desde o seu primeiro dia de funcionamento.
Os resultados provocados nas organizações por esses maus produtos variam entre dois extremos: o completo desinteresse pelo conceito de «base de dados» até ao colapso organizativo e económico da entidade que encomendou uma base de dados e recebeu uma tulha de dados.
O «fazer» uma base de dados é mais do que construir meia dúzia de tabelas num modo ad hoc com a esperança de que a velocidade de desenvolvimento daí resultante consiga impressionar o utilizador final; a construção de uma base de dados é um processo sujeito a normas analíticas e técnicas precisas e bem conhecidas que devem ser seguidas em determinada ordem, desde a etapa de conceptualização até à fase de construção física da base de dados.
Assim como um cirurgião ortopedista segue um determinado procedimento para reparar uma fractura num osso, também o especialista em base de dados tem que obedecer a uma conduta tecnológica de modo a obter um produto final válido tecnicamente, e que devolva à organização um valor acrescentado.
O segredo no sucesso no desenvolvimento de Sistemas de Informação em geral e, em particular em Base de Dados Relacionais, é assim a organização.
O conteúdo deste livro destina-se a «meros mortais» como sejam, por exemplo, gestores ou investigadores e estudantes nas mais variadas áreas da ciência e tecnologia. Os temas são apresentados de uma forma simples, sem a complexidade desnecessária habitual em certos livros de informática, nem a superficialidade existente noutros.
Edições Sílabo
ISBN: 978-972-618-627-4
Trigger para validação de data | Oracle
Considerem-se as seguintes tabelas:
- Curso
- Disciplina
- Disciplina no Curso
Pretende-se impedir que sejam introduzidas na tabela “Disciplina no Curso” datas de cessação inferiores à data actual. Como em Oracle não se pode validar datas comprando-as com o SYSDATE através de CHECK, então uma outra possibilidade será o da utilização de um trigger insert/update que faça essa verificação.
CREATE OR REPLACE TRIGGER trg_check_data_cessacao
BEFORE INSERT OR UPDATE ON “Disciplina no Curso”
FOR EACH ROW
BEGIN
IF( :new.”Data de Cessação” > SYSDATE )
THEN
RAISE_APPLICATION_ERROR( -20001,
‘Data de Cessação inválida: esta data tem que ser maior que o valor actual da data.’);
END IF;
END;
SYSDATE numa restrição CHECK em Oracle
Como o valor de SYSDATE (data do sistema) é quase sempre diferente em cada momento de avaliação de uma cláusula CHECK então o Oracle não nos deixa fazer uma restrição desse tipo.
Imagine-se, por exemplo, que se quer inserir apenas valores que sejam maiores do que SYSDATE e considere-se que o valor de SYSDATE vai aumentado com o tempo:
[Data de Fim] > SYSDATE
30-JAN-2012 > 26-DEC-2011
………………………………………
30-JAN-2012 > 10-FEB-2013
O valor de SYSDATE vai aumentando até ultrapassar o valor inserido na coluna, tornando o CHECK inválido e, consequentemente, a base de dados inconsistente. Dai não ser possível utilizar este tipo de regra de (anti)-integridade.
A validação terá que ser feita por intermédio de um trigger do tipo BEFORE INSERT OR UPDATE.
DBA | Isolamento de transacções
As normas ANSI SQL determinam os seguintes três tipos de isolamento transaccional nos sistemas de gestão de bases de dados:
- Dirty Read. A transacção T1 modifica um facto. Outra transacção T2 lê esse mesmo facto antes que T1 faça commit ou rollback. Se T1 desistir da transacção (rollback) então T2 leu um facto que nunca foi confirmado e, como tal, nunca existiu.
- Non-Repeatable Reads. A transacção T1 lê um facto. Outra transacção T2 modifica ou apaga esse facto e faz commit. Se T1 renovar o pedido desse facto descobre que ele foi modificado ou que já não existe.
- Phantom. A transacção T1 lê um conjunto de factos de acordo com uma determinada condição. A transacção T2 insere novos factos, que satisfazem a condição de T1, e confirma-os na base de dados. Se T1 voltar a fazer a mesma leitura-condição obtém um conjunto de factos distintos dos da leitura inicial.
DBA | Gestão de Transacções
O papel da gestão de transacções é o de assegurar que as transacções são gravadas correctamente na base de dados. O gestor de transacções é o componente do SGBDR que processa esses movimentos de dados. Uma transacção é um conjunto de acções ou movimentos de dados considerados de modo a que sejam todos gravados na base de dados ou rejeitados na sua totalidade. Uma transacção é uma unidade atómica de trabalho em que todos os seus elementos têm que ser processados, caso contrário a base de dados ficará inconsistente. O pagamento de uma propina escolar, por exemplo, é uma transacção que se subdivide, em grandes linhas, em dois elementos: a actualização do saldo da conta de propinas da escola, e a actualização dos montantes pagos por esse aluno. A base de dados ficaria inconsistente se se alterasse um único desses elementos: ou se faz tudo ou não se faz nada.
O princípio da atomicidade transaccional exige que todo o conteúdo transaccional seja processado segundo a regra do “tudo-ou-nada”, e que cada conjunto de transacções seja transmissível em série / realizável (serializable). Quando uma transacção é interrompida antes do seu termo o gestor de transacções têm que repor a base de dados no estado em que se encontrava antes do início da operação, desfazendo todas as acções entretanto efectuadas. As transacções, por uma questão de eficiência e de eficácia, não devem durar mais do que o necessário de modo a assegurar a integridade do sistema. No caso da liquidação de uma propina o pagamento do aluno e o crédito na contabilidade da escola é a unidade mínima de trabalho necessária para manter as contas em dia.
Máquina do tempo…em Oracle
Acontece por vezes que alguns dados podem inadvertidamente ser apagados ou modificados e não há tempo para os restaurar a partir de um dispositivo de backup. O Oracle possui uma tecnologia bastante útil denominada Flashback que permite que se façam queries “a um passado recente”. O “quanto” é permitido recuar depende do espaço disponível em UNDO.
Suponha-se que na última hora se apagou qualquer linha da tabela Curso e que agora é imperioso saber que dados foram removidos:
SELECT * FROM “Curso” AS OF TIMESTAMP SYSDATE-1/24;
Através desta característica é possível fazer o seguinte:
- Pesquisar dados que já não existem (“do passado”);
- Analisar os metadados de modo a evidenciar o historial de alterações à base de dados;
- Recuperar tabelas ou linhas para um determinado ponto do tempo;
- Fazer rollback de transacções enquanto a base de dados de produção continua activa;
- Fazer a gestão de transacções.
Tabela dual ou dummy
Este tipo especial de tabela, denominada em inglês de dummy, serve o propósito especial de fazer algumas operações sobre conjuntos sem aceder a tabelas de dados propriamente ditas. Isso alivia a carga do sistema e impede, por exemplo, que se façam utilizações indevidas a tabelas com muitas linhas.
Este tipo de tabelas pode ser utilizado para trabalhar com triggers ou, mais simplesmente, para obter uma marca temporal. Convém que a tabela dummy tenha uma única linha para evitar que o resultado se multiplique num output com o mesmo número de linhas da tabela.
Como fazer e utilizar?
Em primeiro lugar deve criar-se a tabela:
——————————————————–
– DDL para a tabela LAMY
——————————————————–
CREATE TABLE lamy (
lam varchar(2)
);
Em segundo lugar insere-se uma linha na tabela:
INSERT INTO lamy (lam) VALUES (‘a’);
Aplicações:
- Obter a data actual do sistema:
- select SYSDATE from lamy;
- Resultado:
- Fazer um cálculo:
- SELECT (2+4) / 2 FROM lamy;
- Criar uma tabela com 100000 linhas com dados aleatórios:
- CREATE TABLE tabela_teste AS
SELECT LEVEL id, SYSDATE+DBMS_RANDOM.VALUE(-1000, 1000) valor_data, DBMS_RANDOM.string(‘A’, 20) valor_texto
FROM lamy
CONNECT BY LEVEL <= 100000;
- NOTA: O tempo necessário para inserir 100 000 linhas na tabela pode ser muito longo. Quem quiser pode experimentar com um número menor de linhas. O tempo de execução aumenta (quase) exponencialmente se a tabela lamy tiver mais do que uma linha.
- CREATE TABLE tabela_teste AS
