OBJETIVOS
• Conhecer e implementar objetos do tipo Índice; • Conhecer e implementar objetos do tipo Visão; • Conhecer e implementar objetos do tipo Seqüência; • Conhecer e implementar objetos do tipo Sinônimo.
ARQUIVOS NECESSÁRIOS
• Nenhum
PRÉ-REQUISITOS
• Conectar-se ao Servidor via SQL*Plus Worksheet; • Criar tabelas (comando CREATE TABLE);
• Alterar a estrutura de tabelas previamente criadas (comando ALTER TABLE);
• Eliminar tabelas (comando DROP TABLE); • Inserir dados em tabelas (comando INSERT);
• Consultar dados já armazenados (comando SELECT); • Pesquisar o Dicionário de Dados;
• Alterar conteúdos de linhas (comando UPDATE);
• Eliminar linhas (comandos DELETE e TRUNCATE TABLE);
• Conhecer o conceito de transação e manipular os comandos relacionados (COMMIT, ROLLBACK);
• Elaborar relatórios com SQL*Plus Worksheet;
• Gerar scripts de comandos SQL a partir de outros comandos SQL.
TEORIA
6.1 - Fundamentos
Até o momento foi criado explicitamente apenas um tipo de objeto: tabela. O Oracle oferece inúmeras outras estruturas de dados que são muito úteis no dia-a-dia do banco, a começar pelos índices, já criados via constraints, que aceleram bastante o acesso aos dados.
Depois de criar explicitamente índices, será visto como construir views que permi-tem visualizar, de uma forma simples, dados que estão em tabelas diferentes. Por exemplo, seria interessante que o nome dos instrutores e os alunos de suas turmas pudessem estar "juntos".
Seguem as sequences que geram números seqüenciais. Isto é
particularmente útil quando existem campos cujos conteúdos, geralmente chaves primárias, são números que não podem ser repetidos. A utilização de sequences permite que o conteúdo dessas chaves seja gerado
automaticamente.
O capítulo termina com synonyms que representam uma forma mais econômica de referência a tabelas que pertençam a outros usuários e até a outros bancos.
6.2 - Índices
Imagine o seguinte comando que cria uma tabela com 26 campos com 30 bytes cada um:
create table grande ( a char (30)
b char (30) ...
z char (30));
Ao acessar o campo d, o Oracle precisa carregar as colunas a, b, c, ..., z. Como
cada registro ocupa 26 x 30 = 780 bytes mais o identificador de cada linha (rowid) que possui 10 bytes, a cada 10 registros seriam necessários 7,7 Kb. Agora imagine que fosse possível criar uma estrutura "auxiliar" que contivesse dois campos: d e outro com endereços para as linhas da tabela (rowid). Cada registro ocuparia 40 bytes, assim os mesmos 7,7 Kb
permitiriam carregar 197 linhas! Quase vinte vezes mais!
Índices (index) são estruturas especiais inseridas no banco de dados com o objetivo de melhorar o desempenho de acesso às tabelas. Sua função é reduzir o I/O em disco utilizando uma estrutura de árvore-B (B*Tree) para localizar rapidamente os dados. Ao varrer a árvore-B, ele identifica a chave e recupera o seu rowid, ponteiro para acesso ao dado, localizando o registro rapidamente. O Oracle cria automaticamente um índice do tipo unique ao criar uma chave primária, o qual recebe o mesmo nome da constraint.
Índices são utilizados durante comandos select (cláusulas whereorderbye
group by), portanto, quando houver índices compostos, deve-se colocar
primeiro a coluna mais usada.
Uma analogia clara para índices seriam índices remissivos de livros. O comando para a criação de índices é o create index, cuja sintaxe é:
CREATE [UNIQUE] INDEX índice
ON tabela (coluna [ASC | DESC] [,coluna,...);
Os colchetes representam cláusulas opcionais. A tabela seguinte explica a sintaxe do comando:
Opções Descrição
Unique Informa que o índice em questão não aceitará valores repetidos.
Índice Nome do índice a ser criado.
Tabela Nome da tabela sobre a qual será criado o índice.
Coluna
Nome das colunas que comporão o índice. Este será usado automaticamente em cláusulas where, order byou group bysempre que a primeira coluna for mencionada.
asc | desc Especifica a classificação: ascendente (asc), default, ou descendente
(desc).
Primeiro exemplo: Criação de um índice para turmas
create index turmas_cod_curso_idx on turmas (cod_curso);
Pode-se confirmar a existência de índices por meio de uma visão do dicionário de dados do Oracle user_indexes, que armazena informações sobre os índices do usuário. Também podem-se verificar as colunas envolvidas em um índice utilizando user_ind_columns.
Segundo exemplo: Confirmando a existência de índices
select index_name from user_indexes; INDEX_NAME --- ALUNOS_PK ... TURMAS_COD_CURSO_IDX TURMAS_PK
E para verificar as colunas:
select index_name, column_name, column_position, column_length from user_ind_columns
where table_name = 'TURMAS';
INDEX_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH --- --- --- --- TURMAS_PK COD_TURMA 1 22
TURMAS_COD_CURSO_IDX COD_CURSO 1 22
Para eliminar um índice, usa-se o comando drop index.
Terceiro exemplo: Eliminando um índice
drop index turmas_cod_curso_idx;
Deve-se lembrar que este comando não elimina índices criados a partir de constraints.
Concluindo, deve-se criar um índice quando:
• A tabela é volumosa, muito consultada e pouco atualizada;
• A coluna ou colunas são utilizadas freqüentemente em cláusulas
where, orderby, group byou em joins;
• A coluna contém uma grande quantidade de valores nulos;
• A tabela é grande e a maior parte das queries retorna menos de 2 a 4% das linhas. Na verdade, isto significa que, quando houver muitos valores diferentes (a maior incidência como 2 a 4%), é bom indexar. A situação ótima para indexar ocorre quando não há valores repetidos (índices únicos);
• Deve-se considerar também a possibilidade de criar índices em campos cujos conteúdos sejam consultados em forma de intervalo. Por exemplo: datas, siglas, códigos de chaves estrangeiras, etc.
Quando uma tabela for muito consultada segundo um determinado critério, vale a pena pensar em Index-Organized Tables (IOTs), que consistem em objetos capazes de armazenar dados como acontece nos índices, ou seja, em forma de árvore. Há ganhos de performance pois a tabela é o próprio índice. Veremos este recurso em detalhes no capítulo 22.
E não se deve criar índice quando: • A tabela é muito atualizada; • A tabela é pequena;
• A coluna ou colunas são pouco utilizadas como condição em queries;
• A coluna ou colunas já possuem um constraint primary key ou unique;
• A maior parte das queries retorna mais de 2 a 4% das linhas; • A coluna é do tipo raw, lob, longou long raw. O Oracle não permite
indexar campos destes tipos.
6.3 - Views
Uma view (visão) é uma tabela virtual na qual os dados não estão
de uma ou mais tabelas do banco. A utilização de visões proporciona as seguintes vantagens:
Evitar que usuários não autorizados tenham acesso a todos os dados de uma tabela. Para isto, cria-se uma visão com apenas alguns campos necessários;
Associar a uma visão consultas que utilizam critérios de seleção complexos, e assim, facilitar ao usuário.
Para criar uma visão, deve-se utilizar o comando create view. Sua sintaxe simplificada é:
CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW nome_visão [alias,...] AS subquery
[WITH CHECK OPTION [CONSTRAINT nome_constraint] [WITH READ ONLY];
em que:
Opções Descrição
Replace Recria a view, se ela já existir.
Force Cria a view, mesmo que a tabela base não exista.
Noforce Só cria a view se a tabela base existir. Opção default.
Subquery Sentença select na qual será baseada a view. Não é possível
especificar a cláusula orderby.
with check option
Caso exista algum filtro especificado na cláusula WHERE do comando SELECT, a condição será utilizada para impedir atualizações realizadas diretamente sobre a View que contrariem este filtro. Sem esta cláusula, as alterações poderiam ser feitas, porém não seriam vistas ao listar o conteúdo retornado pela View. Opcionalmente pode-se especificar o nome do constraint que determina a restrição à alteração.
with read only
Indica que não podem ser executados comandos de DML (insert, delete, update) sobre a view.
Os dados de uma view nem sempre podem ser alterados. Vejamos situações em que as atualizações normalmente são proibidas:
• Tentativa de inserção em uma view que não possua todos os campos obrigatórios da tabela base (chave primária e não nulos); • Tentativa de atualização de um campo calculado ou chave da
tabela primária;
• Exclusão de registros que possuam outros relacionados.
Como veremos em breve, essas restrições podem ser burladas graças aos Triggers do tipo Instead of. Mais detalhes no capítulo 16.
Primeiro exemplo: Criação de uma view
create view arrecadado as
select nome_curso CURSO, t.cod_turma TURMA, sum(preco) ARRECADADO from cursos c inner join turmas t using (cod_curso)
join historico h on t.cod_turma = h.cod_turma group by nome_curso, t.cod_turma
having sum(preco) >0;
E para visualizar os dados por meio da view:
select * from arrecadado order by arrecadado desc;
Saída16:
CURSO TURMA ARRECADADO --- --- --- Fundamentos da Modelagem de Dados 3 9500 Fundamentos da Modelagem de Dados 9 9500 Introdução à Lógica de Programação 1 8000 Introdução à Lógica de Programação 6 8000 Introdução à Lógica de Programação 2 8000 Delphi: Acesso a Bancos de Dados 7 4000 6 linhas selecionadas.
Quando o comando select que gera a View possuir grande complexidade, a performance nas consultas poderá ser consideravelmente degradada. Neste caso, seria aconselhável criar um outro tipo de objeto: Materialized View. Trata-se de uma tabela temporária contendo os dados da Visão. No
capítulo 22 veremos como criar e alimentar tal objeto.
A estrutura da view pode ser vista pelo já conhecido comando describe ou pela também visão user_views.
Segundo exemplo: Confirmando a existência de views
desc arrecadado
Name Null? Type --- --- ----
CURSO NOT NULL VARCHAR2(60) TURMA NUMBER(3) ARRECADADO NUMBER
O corpo:
select view_name, text_length, text from user_views; VIEW_NAME TEXT_LENGTH TEXT
--- --- --- ARRECADADO 219 select nome_curso CURSO,...
Para eliminar uma visão, usa-se o comando drop view.
Terceiro exemplo: Eliminando uma view
drop view arrecadado;
16 Os dados seguintes foram obtidos após a execução dos Scripts Monta_Tabelas.sql e
Cria_Alimenta_Historico.sql. A Stored Procedure AlimentaHistorico foi executada com os argumentos: 10, 10.
Quarto exemplo: Cláusula WITH CHECK OPTION
Imagine que se tenha criado uma visão representando os cursos com carga horária igual ou maior que 32:
CREATE view vCursosLongos
as select cod_curso, nome_curso, carga_horaria from cursos
where carga_horaria >= 32 with check option;
Note que a cláusula with check option impedirá que, por meio da view, sejam inseridos ou alterados cursos com carga horária menor que 32. De fato:
insert into vCursosLongos (cod_curso, nome_curso, carga_horaria) values ('Outro Curso', 10);
Produzirá a mensagem seguinte:
insert into vCursosLongos (cod_curso, nome_curso, carga_horaria) *
ERRO na linha 1:
ORA-01402: violação da cláusula where da view WITH CHECK OPTION
6.4 - Sequences
Muitas aplicações necessitam utilizar um valor numérico seqüencial como chave primária em uma tabela. Com o uso de Sequences (seqüências) esses valores podem ser gerados automaticamente pelo Oracle. A sequence gera valores para o mnemônico nextval, que pode ser utilizado no comando
insert. A sintaxe do comando para criação de sequences é:
CREATE SEQUENCE seqüencia [INCREMENT BY n] [START WITH n] [MAXVALUE n | NOMAXVALUE] [CYCLE | NOCYCLE] [CACHE n | NOCACHE]; em que: Opção Descrição
Seqüencia Nome da seqüência que não pode ser o mesmo de uma
tabela ou view.
Increment by n Especifica de quanto será o incremento ou decremento da
seqüên-cia. O default é 1.
start with n Especifica o primeiro número a ser gerado. O default é 1.
maxvalue n Especifica o valor máximo que a seqüência pode assumir. O
default é nomaxvalue, indo até 1027.
minvalue n Especifica o valor mínimo para seqüências que estejam
Opção Descrição
cycle | nocycle Indica que, quando atingir o valor máximo, a numeração
continuará a partir do valor inicial. O default é nocycle.
Cache n | nocache Especifica quantos valores o Oracle pré-aloca e mantém em
memória. O default é 20.
Primeiro exemplo: Criação de uma sequence
create sequence id_curso increment by 1
start with 11 maxvalue 999 nocycle;
Segundo exemplo: Confirmando a existência de sequences
select sequence_name, increment_by,max_value, last_number from user_sequences;
E a saída:
sequence_name increment_by max_value last_number --- --- --- --- id_curso 1 999 11
Observação
Se for executado um rollback, o número da seqüência não volta atrás! Duas pseudocolunas são utilizadas nas seqüências: nextval, que retorna o próximo número da seqüência, e currval, valor corrente.
Terceiro exemplo: nextval
insert into cursos (cod_curso, nome_curso)
values (id_curso.nextval, 'Oracle: Administração');
Constatando:
select cod_curso, nome_curso from cursos;
Saída:
COD_CURSO NOME_CURSO
--- --- 1 Introdução à Lógica de Programação
... 11 Oracle: Administração
select id_curso.currval from dual;
Saída:
CURRVAL --- 11
Quinto exemplo: Eliminando uma sequence
drop sequence id_curso;
Outra forma de gerar valores automaticamente consiste em utilizar o tipo raw para garantir unicidade, e a função sys_guid() para obter novos valores. Por exemplo, o comando seguinte cria uma tabela com dois campos e depois insere-lhe uma linha.
create table gerada (cod raw(32), num int);
insert into gerada (cod, num) values (sys_guid(),1); select * from gerada;
O resultado do último comando seria:
COD NUM --- --- 906499066237483298EDB761D9A88182 1
O conteúdo retornado pela função sys_guid() resulta da concatenação de vários dados e ocupa 16 bytes. Sua utilização é recomendada quando a unicidade for crítica, ou seja, uma linha deve possuir conteúdo único no Database.
6.5 - Synonyms
Tipicamente, um Banco de Dados Oracle é utilizado por dezenas de
usuários que têm vários objetos. Acontece freqüentemente que um usuário precisa de uma tabela, índice, view ou sequence de outro e, para facilitar a manipulação desses objetos, cria synonyms (sinônimos).
O comando que cria synonyms é o seguinte:
CREATE [PUBLIC] SYNONYM sinônimo FOR objeto
em que:
Opção Descrição
public Especifica que o synonym pode ser usado por todos. O default é que
somente o criador possa utilizá-lo.
objeto Informa o nome do objeto sobre o qual se aplica o sinônimo. Pode
Atenção
Para que seja possível criar um sinônimo público, o usuário deve ter recebido explicitamente o privilégio para tal. Este assunto será detalhado no capítulo 24, que trata sobre segurança.
Primeiro exemplo: Criação de um synonym
create public synonym cursos_dba for cursos;
Supondo que o usuário corrente se chamasse DbaAcad e houvesse outro usuário denominado Pedro, este poderia fazer referência à tabela cursos de DbaAcad como:
select * from cursos_dba
em vez de:
select * from dbaacad.cursos
Isto, claro, após terem sido concedidas as permissões adequadas. Este assunto também será tratado no capítulo 24, Segurança.
Segundo exemplo: Confirmando a existência de synonyms
Os synonyms públicos podem ser vistos em all_synonymse os privativos, em user_synonyms:
select synonym_name, table_owner from all_synonyms
where table_name = 'CURSOS';
Saída:
SYNONYM_NAME TABLE_OWNER
--- --- CURSOS_DBA DBAACAD
Terceiro exemplo: Eliminando um synonym
drop public synonym cursos_dba;
EXERCÍCIOS
1. Crie um índice com as características seguintes e depois confirme sua existência:
• Tabela Alunos;
2. Em quais comandos seguintes o índice recém-criado seria utilizado?
a. select uf, nome_aluno from alunos order by 1,2 desc; b. select nome_aluno from alunos order by 1;
c. select nome_aluno from alunos order by uf;
3. Crie uma view que informe o quanto cada instrutor arrecadou.
4. Monte uma view que revele alunos, cursos e total pago por cada um. 5. Faça uma view que mostre a média vendida por curso.
6. Crie sequences para que os códigos nas chaves primárias das tabelas "de ponta" possam ser gerados automaticamente.
7. Monte uma view que sirva como base para a Lista de Presenças. Devem constar: nome do aluno, nome do curso, carga horária, nome do instrutor e sala.
REFERÊNCIA RÁPIDA
Tarefa Como fazer
Criar índices Utilize o comando create index. Para comprovar o sucesso da operação, investigue IND ou USER_INDEXES.
Eliminar índices Utilize o comando drop index.
Criar views Utilize o comando create view. Para comprovar o sucesso da ope-ração, investigue USER_VIEWS.
Eliminar views Utilize o comando drop view.
Criar sequences Utilize o comando create sequence. Para comprovar o sucesso da operação, investigue USER_SEQUENCES.
Eliminar
sequences Utilize o comando drop sequence.
Criar synonyms Utilize o comando create synonym. Para comprovar o sucesso da operação, investigue USER_SYNONYMS.
Eliminar
Capítulo 7 – SUBQUERIES
OBJETIVOS
• Conhecer o conceito de subquery;
• Identificar situações em que a utilização de uma subquery é necessária;
• Construir subqueries de uma linha; • Construir subqueries de múltiplas linhas; • Construir subqueries de múltiplas colunas; • Construir subqueries correlatas.
ARQUIVOS NECESSÁRIOS
• Monta_Tabelas_Salas.sql.
PRÉ-REQUISITOS
• Conectar-se ao Servidor via SQL*Plus Worksheet; • Criar tabelas (comando CREATE TABLE);
• Alterar a estrutura de tabelas previamente criadas (comando ALTER TABLE);
• Eliminar tabelas (comando DROP TABLE); • Inserir dados em tabelas (comando INSERT);
• Consultar dados já armazenados (comando SELECT); • Pesquisar o Dicionário de Dados;
• Alterar conteúdos de linhas (comando UPDATE);
• Eliminar linhas (comandos DELETE e TRUNCATE TABLE);
• Conhecer o conceito de transação e manipular os comandos relacionados (COMMIT, ROLLBACK);
• Criar e manipular views.
TEORIA
7.1 - Fundamentos
Uma subquery consiste em uma consulta utilizada dentro de um comando SQL. Há várias situações em que as subqueries são necessárias:
• Atualizar em 10% o preço do curso mais caro;
• Selecionar os alunos cujo estado de origem seja o mesmo de qualquer aluno de sobrenome "Silva";
• Atualizar conteúdos de campos com base em outros;
• Mostrar nomes de instrutores que não ministraram cursos17; etc. Uma subquery pode ser utilizada dentro de um comando select, update, create table, deleteou insert.
Existem os seguintes tipos de subqueries:
Tipo Descrição
Uma Linha Retorna apenas um valor.
Múltiplas Linhas Podem retornar um ou mais valores, mas em uma coluna.
Múltiplas Colunas Retornam um ou mais valores em mais de uma coluna. Correlatas Utilizam campos da consulta externa.
Trabalhando com subqueries, é possível utilizar vários operadores:
Operador Descrição
=, <>, >, >=, <,
<= Relacionais usados em subqueries do tipo Uma Linha. in Testa se um valor pertence a um conjunto de valores.
not in Testa se um valor não pertence a um conjunto de valores.
any Verifica se um determinado argumento casa com qualquer
membro de um conjunto.
all Verifica se um determinado argumento casa com todos os
membros de um conjunto.
exists Usado em subqueries correlatas, vê se um dado valor existe em um conjunto, levando em conta valores nulos. Este fato não é
considerado pelo operador in.
not exists Negação do anterior.
7.2 - Subqueries de Uma Linha
Primeiro exemplo: selectselect nome_curso, preco from cursos
where preco = (select max(preco) from cursos);
Saída:
17 Este problema, assim como outros, pode ser resolvido sem subqueries, por meio de
NOME_CURSO PRECO --- --- Fundamentos da Modelagem de Dados 950
Esta consulta mostra o nome do curso mais caro. Observe:
A presença do operador relacional =;
Que, de fato, a subquery somente retorna um valor; Como a subquery é colocada entre parênteses.
O Servidor executa primeiro as consultas mais internas.
Cuidado
Uma subquery não pode conter cláusula order by.
Segundo exemplo: update
Atualiza(m)-se o(s) curso(s) mais caro(s) em 10%:
update cursos
set preco = 1.1 * preco
where preco = (select max(preco) from cursos);
Terceiro exemplo: create table
Cria uma tabela na qual constem os cursos cujo preço seja maior que a média:
create table maiores as select nome_curso, preco from cursos
where preco > (select avg(preco) from cursos);
Observe que, caso existam constraints associados aos campos nome_curso e preco, eles NÃO serão incorporados à nova tabela. Note também que, apesar de existirem dois comandos SELECT, apenas um representa subquery.
Quarto exemplo: delete
Na nova tabela, elimina o mais barato dos maiores:
delete from maiores
where preco = (select min(preco) from maiores);
Quinto exemplo: insert
Alimenta a nova tabela com os cursos cujo preço seja menor que a média.
insert into maiores (nome_curso, preco) select nome_curso, preco from cursos
Cuidado
A construção de subqueries constitui tarefa claramente mais intuitiva do que a de joins. Isto pode levar ao mau costume de tentar resolver todo e qualquer problema via subqueries, esquecendo-se da melhor performance de joins. Portanto, antes de imaginar qualquer subquery, pergunte ao problema se um join cai bem.
O que há de errado com este comando?
select nome_curso
from cursos where cod_curso in
(select cod_curso from turmas t inner join instrutores i using(cod_instrutor) where nome_instrutor like '%Sirio')
7.3 - Subqueries de Múltiplas Linhas
Primeiro exemplo: select com inselect nome_curso, carga_horaria from cursos
where carga_horaria in (select carga_horaria
from cursos where upper(nome_curso) like 'ORACLE%') and upper(nome_curso) not like 'ORACLE%';
Saída:
NOME_CURSO CARGA_HORARIA --- --- Delphi: Recursos Básicos 24 Delphi: Acesso a bancos de Dados 24 Introdução à Lógica de Programação 32
A consulta anterior mostra os cursos que têm a mesma carga horária de qualquer um que tenha "Oracle" em seu nome.
Segundo exemplo: select com not in
select nome_curso, carga_horaria from cursos
where carga_horaria not in
(select carga_horaria
from cursos where upper(nome_curso) like 'ORACLE%' and carga_horaria is not null)
and upper(nome_curso) not like 'ORACLE%';