Linguagem SQL: Views, Sequências, Índices e Dicionarios

No universo dos bancos de dados, o uso de objetos como visões (views), sequências (sequences) e índices (indexes) é essencial para otimizar a estrutura e o desempenho das operações. Esses objetos auxiliam no armazenamento, consulta, e manipulação de dados, garantindo eficiência e praticidade.

Visões (Views)

Uma visão é uma tabela virtual baseada em uma ou mais tabelas reais. Elas são úteis para:

Restringir o acesso a dados sensíveis.
Simplificar consultas complexas.
Apresentar dados sob diferentes perspectivas.

Tipos de Visões

Visão Simples: Baseada em uma tabela, sem funções ou agrupamentos. Permite operações DML (INSERT, UPDATE, DELETE).
Visão Complexa: Baseada em múltiplas tabelas ou com uso de funções e agrupamentos. Nem sempre permite DML.

Criando Visões

Exemplo 1: Visão Simples

CREATE VIEW empvu10 AS
SELECT empno, ename, job
FROM scott.emp
WHERE deptno = 10;

Este comando cria uma visão chamada empvu10, que exibe os números (empno), nomes (ename) e funções (job) de empregados do departamento 10. É uma visão simples porque se baseia em uma única tabela (scott.emp) e não utiliza agregações ou funções.

Obs: Simplifica consultas ao limitar os resultados a apenas funcionários do departamento 10, evitando a necessidade de incluir a cláusula WHERE deptno = 10 repetidamente.

Exemplo 2: Visão Complexa

CREATE VIEW dept_sum_vu (name, minsal, maxsal, avgsal) AS
SELECT d.dname, MIN(e.sal), MAX(e.sal), AVG(e.sal)
FROM scott.emp e
JOIN scott.dept d ON e.deptno = d.deptno
GROUP BY d.dname;

Esta visão exibe o nome do departamento, menor salário, maior salário e salário médio de cada departamento. É uma visão complexa porque:

Junta duas tabelas (scott.emp e scott.dept).
Utiliza funções de agregação (MIN, MAX, AVG).
Inclui uma cláusula GROUP BY para agrupar os resultados por departamento.Uso: Ideal para relatórios de análise de salários por departamento.

Alterando e Excluindo Visões

Alterar definição:

CREATE OR REPLACE VIEW empvu10 AS
SELECT empno AS id_emp, ename AS nome, job AS função
FROM scott.emp
WHERE deptno = 10;

Excluir visão:

DROP VIEW empvu10;

Sequências (Sequences)

As sequências geram valores inteiros únicos automaticamente. São ideais para chaves primárias.

Criando Sequências

Exemplo:

CREATE SEQUENCE dept_deptno INCREMENT BY 1
 START WITH 91 MAXVALUE 100 NOCACHE NOCYCLE;

Cria uma sequência chamada dept_deptno que:

Começa no valor 91.
Incrementa de 1 em 1.
Tem como valor máximo 100.
Não utiliza cache, ou seja, os valores são gerados sem pré-alocação.
Não reinicia (NOCYCLE) quando atinge o valor máximo.Uso: Útil para gerar valores únicos, como chaves primárias, sem precisar manualmente incrementar os valores.

Usando Sequências

Inserir com NEXTVAL:

INSERT INTO dept(deptno, dname, loc)
VALUES (dept_deptno.NEXTVAL, 'MARKETING', 'SAN DIEGO');

Insere um novo departamento com um número único (deptno) gerado pela sequência.

Consultar valor atual com CURRVAL:

SELECT dept_deptno.CURRVAL FROM dual;

Obtém o último valor gerado pela sequência dept_deptno.

Alterando e Excluindo Sequências

Alterar:

ALTER SEQUENCE dept_deptno MAXVALUE 999999;

Excluir:

DROP SEQUENCE dept_deptno;

Índices (Indexes)

Os índices aumentam a velocidade das consultas, criando ponteiros para acessar dados rapidamente.

Recomenda-se criar índices em:

Campos frequentemente utilizados em cláusulas WHERE ou JOIN.
Tabelas grandes para buscas que retornam menos de 4% dos registros.

Criando Índices

CREATE INDEX emp_ename_idx ON emp(ename);

Excluindo Índices

DROP INDEX emp_ename_idx;

Por que Evitar Índices em Excesso?

Embora os índices melhorem o desempenho de consultas, eles também têm custos e desvantagens. Aqui estão algumas razões para não usar índices excessivamente:

Impacto em Operações DML:
- Toda vez que um registro é inserido, atualizado ou excluído, os índices associados precisam ser atualizados.
- Operações DML em tabelas com muitos índices se tornam mais lentas devido ao trabalho adicional.
Consumo de Espaço em Disco:
- Índices ocupam espaço no disco. Em tabelas grandes, muitos índices podem consumir recursos significativos.
Manutenção Adicional:
- Mais índices significam mais tempo gasto pelo banco de dados para manter esses índices sincronizados com os dados.
Riscos de Planos de Consulta Ineficientes:
- Em tabelas com múltiplos índices, o otimizador de consultas pode escolher um índice menos eficiente, impactando negativamente o desempenho.
Dificuldade de Gerenciamento:
- Gerenciar muitos índices torna-se complexo, especialmente em bancos de dados com alta carga de trabalho.

Dicionário de Dados

Informações sobre os objetos do banco de dados (como tabelas, colunas, índices, visões, sequências, usuários, privilégios e outros). Ele funciona como um catálogo centralizado que o próprio sistema gerenciador de banco de dados (SGBD) utiliza para operar.

Contém detalhes sobre a estrutura dos objetos no banco de dados, como:

Nome de tabelas e suas colunas.
Tipos de dados e restrições.
Relações entre tabelas (chaves estrangeiras).
Índices e visões.

1. Consultando Tabelas Criadas pelo Usuário

No Oracle, a visão USER_TABLES exibe todas as tabelas pertencentes ao usuário atual:

SELECT table_name FROM user_tables;

2. Consultando Colunas de uma Tabela

A visão USER_TAB_COLUMNS contém informações sobre as colunas de tabelas do usuário:

SELECT column_name, data_type, data_length
FROM user_tab_columns
WHERE table_name = 'EMP';

3. Consultando Índices

A visão USER_INDEXES armazena informações sobre os índices do usuário:

SELECT index_name, index_type, table_name FROM user_indexes WHERE 
index_name = 'EMP_ENAME_IDX';

4. Verificando Sequências

Use a visão USER_SEQUENCES para verificar sequências disponíveis:

SELECT sequence_name, min_value, max_value, increment_by
FROM user_sequences WHERE sequence_name = 'DEPT_DEPTNO';

5. Examinando Visões

A visão USER_VIEWS exibe as visões criadas pelo usuário:

SELECT view_name, text_length 
FROM user_views;