Criando Materialized View!!! ERRO ORA-01031

Meu objetivo neste post é descrever uma situação que aconteceu comigo e que consumiu algumas horas de dor de cabeça. Achei muito interessante e resolvi compartilhar aqui no blog.

Aparentemente a situação era bem simples:

No meu banco estava logado com meu usuário TAIRONE que possui perfil de DBA. Precisava criar uma materialized view em um outro esquema.

A MV era bem simples:

create materialized view TESTE.MV_DATA_ATUAL
   refresh complete on demand
   disable query rewrite as
select sysdate from dual;

Porém quando executei o comando deu o seguinte erro:


ERROR at line 1:
ORA-01031: insufficient privileges


Estranho né ... Como meu usuário que tem permissão de DBA não tem permissão de criar um materialized view...


Comecei a pesquisar sobre grants de MV e todos os grants que encontrava eu dava ao meu usuário, mas o erro continuava...

GRANT CREATE MATERIALIZED VIEW TO TAIRONE;
....
...
.

Foi aí que descobri o site abaixo:

http://www.krenger.ch/blog/create-materialized-view-ora-01031/


Ele informava um detalhe...

Na prática uma MV é uma tabela, cujo os dados são atualizados a partir de uma view de acordo com a configuração que for determinada na criação dela.

Eu estava tentando criar uma MV para um outro owner que não era o que eu estava logado. Esse owner não tinha permissão de criar tabelas. Foi aí que executei o seguinte comando:

GRANT CREATE TABLE TO TESTE;

E não é que funcionou!!!!!!

Quando tiver um tempinho vou estudar melhor a situação para entender o que aconteceu, mas fica aí a dica para a galera...

Grande abraços!!!

Usando CONNECT BY

O CONNECT BY é um comando muito interessante da linguagem PL/SQL, pois ele permite a criação uma árvore hierárquica entre as tuplas de uma consulta, definindo o registro pai e o registro filho.

Considere a tabela abaixo:



select * from
(select 5533 registro, 'João' nome, 5000 registro_pai from dual
union
select 5000 registro, 'Lima' nome, NULL registro_pai from dual
union
select 4444 registro, 'Zé' nome, 5533 registro_pai from dual
union
select 3358 registro, 'Souza' nome, 4444 registro_pai from dual) tabela;

REGISTRO	NOME	REGISTRO_PAI
3358	Souza	4444
4444	Zé	5533
5000	Lima
5533	João	5000

A consulta a seguir monta a árvore hierarquica para cada um dos registros em ordem crescente do menor filho para o maior pai:


select * from
(select 5533 registro, 'João' nome, 5000 registro_pai from dual
union
select 5000 registro, 'Lima' nome, NULL registro_pai from dual
union
select 4444 registro, 'Zé' nome, 5533 registro_pai from dual
union
select 3358 registro, 'Souza' nome, 4444 registro_pai from dual) tabela
connect by prior registro_pai = registro;


Resultado:

REGISTRO	NOME	REGISTRO_PAI
3358	Souza	4444
4444	Zé	5533
5533	João	5000
5000	Lima
4444	Zé	5533
5533	João	5000
5000	Lima
5000	Lima
5533	João	5000
5000	Lima

Se invertermos a cláusula CONNECT BY a ordem dos hierarquia é alterada para cada registros, passando a ser ordenado do maior pai para o menor filho:

select * from

(select 5533 registro, 'João' nome, 5000 registro_pai from dual
union
select 5000 registro, 'Lima' nome, NULL registro_pai from dual
union
select 4444 registro, 'Zé' nome, 5533 registro_pai from dual
union
select 3358 registro, 'Souza' nome, 4444 registro_pai from dual) tabela
connect by prior registro = registro_pai;


Resultado:

REGISTRO	NOME	REGISTRO_PAI
3358	Souza	4444
5533	João	5000
4444	Zé	5533
3358	Souza	4444
4444	Zé	5533
3358	Souza	4444
5000	Lima
5533	João	5000
4444	Zé	5533
3358	Souza	4444

Se incluírmos a cláusula START WITH podemos definir qual a árvore deve ser exibida excluindo todas as outras da consulta:


select * from
(select 5533 registro, 'João' nome, 5000 registro_pai from dual
union
select 5000 registro, 'Lima' nome, NULL registro_pai from dual
union
select 4444 registro, 'Zé' nome, 5533 registro_pai from dual
union
select 3358 registro, 'Souza' nome, 4444 registro_pai from dual) tabela
connect by prior registro = registro_pai
start with nome = 'Lima';


Resultado:

REGISTRO	NOME	REGISTRO_PAI
5000	Lima
5533	João	5000
4444	Zé	5533
3358	Souza	4444

Outra caracteristcia muito interessante desse comando é que ele permite a construção de uma estrutura de informações como podem ser observado nas consultas abaixo:

select level 
from dual 
connect by level <= 10;


Essa consulta retorna os números menor e igual a 10...

LEVEL
1
2
3
4
5
6
7
8
9
10

select level - 1 + TO_DATE('01/03/2013','DD/MM/RRRR') dias 
from dual 
connect by level <= last_day(TO_DATE('01/03/2013','DD/MM/RRRR')) - TO_DATE('01/03/2013','DD/MM/RRRR') + 1;


Essa consulta exibe uma estrutura com todos os dias de um determinado mês...

DIAS

01/03/2013

02/03/2013

03/03/2013

04/03/2013

05/03/2013

06/03/2013

07/03/2013

08/03/2013

09/03/2013

10/03/2013

11/03/2013

12/03/2013

13/03/2013

14/03/2013

15/03/2013

16/03/2013

17/03/2013

18/03/2013

19/03/2013

20/03/2013

21/03/2013

22/03/2013

23/03/2013

24/03/2013

25/03/2013

26/03/2013

27/03/2013

28/03/2013

29/03/2013

30/03/2013

31/03/2013

Espero ter contribuído como novos conhecimentos!

Em breve novas dicas ...

Dicas de Funções Genéricas

Esta função bem simples retorna a quantidade de um determinado dia da semana existente em um intervalo de datas passados como parâmetro:



CREATE OR REPLACE FUNCTION QTTY_DAY_WEEK(p_dt_start DATE, p_dt_end DATE, p_day_week NUMBER) RETURN NUMBER IS

  v_day NUMBER;
  v_count NUMBER := 0;
  v_start NUMBER;
  v_end NUMBER;
 

BEGIN

  v_start := TRUNC(SYSDATE) - p_dt_start;
  v_end := TRUNC(SYSDATE) - p_dt_end;

  FOR v_aux IN v_end .. v_start LOOP

    SELECT to_char(SYSDATE - v_aux ,'d')
      INTO v_day
      FROM dual;

    IF v_day IN (p_day_week) THEN -- 1 is Sunday, 2 is Monday, ...
       v_count:= v_count + 1;
    END IF;

  ENd LOOP;
 
  RETURN(v_count);

END;


onde:

p_dt_start é a data inicial
p_dt_end é a data final
p_day_week é o dia da semana (1 para Domingo, 2 para Segunda, 3 para Terça, ..., 7 para Sábado)

para testar:


select QTTY_DAY_WEEK(TO_DATE('01/03/2013','DD/MM/RRRR'), TO_DATE('20/03/2013','DD/MM/RRRR'), 1) QTD_DOMINGOS
from dual;

Resultado:
3

Até a próxima postagem...

Índices (Index)

Quando falamos de performance em banco de dados, o primeiro termo que devemos associar é Indice. Indice é um ponteiro que o SGBD utiliza para localizar um ou mais de um registro de uma determinada tabela em um banco de dados.

Para melhorar nosso entendimento, imagine uma fita métrica onde a cada centímetro foi escrito uma letra aleatória, podendo a mesma ser utilizada mais de 1 vez ao longo da fita :

-----------------------------------------
| a   f   e  a   t   f  e   r  s   a |        
-----------------------------------------
 1   2   3  4  5  6  7  8  9  10

Analogamente a uma tabela sem indice, ao ser executada uma consulta para trazer todas as letras "a" que existe na fita nós teriamos que ler toda a fita para descobrir que existem 3 letras "a" em toda sua extensão. Em banco de dados esse tipo de acesso a informação é denominado acesso FULL, ou seja, o SGBD faz uma leitura completa da tabela para encontrar as informações que são solicitadas.

Caso um índice fosse criado para essa fita, ele armazenaria o posicionamento de todas as letras que foram escritas ao longo dela. E quando uma consulta fosse executada para trazer todas as letras "a" que existe na fita, antes do acesso a fita, nós acessaríamos o índice que informaria a existência de uma letra "a" na posição 1, outra letra "a" na posição 4 e uma ultima letra "a" na posição 10, com isso nós não necessitaríamos ler toda a fita para descobrir quantas letras "a" existem na fita, o que tornaria o retorno da consulta muito mais rápido. Em banco de dados esse tipo de acesso a informação é denominado acesso BY INDEX.

Antes de sairmos criando índices em um banco de dados é necessário fazer algumas avaliações:

- Qual o foco principal da aplicação que acessa o banco de dados ?

- Qual a granularidade da informação onde queremos criar o índice ?

- Qual o índice ideal a ser criado ?

Todo índice é uma estrutura a mais no banco de dados que consome espaço e tempo de processamento. Espaço porque ele cria uma estrutura a parte da tabela com chaves e ponteiros apontando para os dados da tabela e tempo de processamento porque o índice sofre alteração quando sua tabela de referência sofre alteração.

Analisar o foco principal da aplicação é importante porque os índices melhoram a performance das consultas a uma base de dados, mas degrada a performance das inserções, deleções e atualizações. Toda inserção, deleção e atualização de registros em uma tabela implica em atualização dos indices associados a ela que dependendo da quantidade de índices, do tamanho e da reorganização deles podem consumir muito tempo de processamento. Se o foco principal da aplicação é gerar relatórios índices podem ser criados para tornar a performance dos relatórios melhores, porém se o foco da aplicação é o cadastro de informações é importante avaliar a real necessidade de se criar índices para ela.

A granularidade de um campo diz respeito a variação de informações que ele representa. Vamos imaginar uma tabela de clientes onde existem apenas 3 campos RG, Primeiro Nome e Sexo. O campo Sexo só pode ter 2 opções masculino e feminino. Esse campo é considerado com granularidade baixa pois a variação de informações que ele representa são apenas duas. Campos com granularidade baixa não são indicados para criação de índices, pois o custo de acesso ao índice mais o custo de acesso a grande quantidade de dados retornados não compensa em relação a um acesso FULL a tabela. Imagine uma tabela com 50% de clientes do sexo masculino e 50% do sexo feminino, se fossemos consultar todos os clientes com sexo masculino a quantidade de registros retornados é metade da tabela. Caso a tabela tivesse 90% de clientes do sexo masculino e 10% do sexo feminino essa consulta retornaria praticamente todos os registros da tabela e ainda perderíamos o tempo de acesso ao índice. Já o campo RG possui uma variação muito grande de valores, praticamente um valor exclusivo para cada registro. Esse tipo de campo possui uma granularidade muito alta e é o tipo de campo mais indicado para criação de um índice. O desempenho de qualquer consulta que utiliza índices em campos de granularidade alta é excelente, pois a quantidade de acesso a disco é muito pequena e quanto menos acesso a disco uma consulta fizer mais rápido é o tempo de resposta dela. O campo Primeiro Nome é considerado com granularidade média, pois os valores podem se repetir muitas vezes mas a variação de valores também é muito grande. Esse tipo de campo também é indicado para criação de índices, apesar de não ser perfeito ele é muito bom.

O conhecimento do negócio é um fator determinante para tomadas de decisões em relação a criação de índices ou não. Apesar de campos com granularidade baixa não serem indicados para criação de índices existem processos onde eles funcionam muito bem. Como exemplo, podemos citar um processo que recebe uma carga de registros todos os dias e esses registros são processados logo em seguida. No registro existe um campo de status que informa se aquele registro já foi processado ou não, sendo "N" igual a "Não processado" e "S" igual a "Processado". Apesar da granularidade baixa, pois o campo recebe somente 2 valores, se o processamento é feito apenas nos registros com valores igual a "N", a tendencia é que a quantidade de registros com "N" seja muito menor que a quantidade de registros com "S" e a criação de um índice para esse campo pode tornar o processo muito mais rápido do que o acesso FULL a tabela. Porém somente a criação do índice e a execução de uma consulta que utiliza o campo do índice não implica em utilização do índice pela banco. Quem determina a utilização ou não do índice é o SGBD de acordo com o melhor plano de execução montado para aquela consulta e normalmente índices com granularidade baixa são desconsiderados pelo SGBD, nesses casos é necessário a utilização de um HINT para forçar a utilização do índice para a consulta. Em outro post falaremos sobre HINTS.

Espero ter ajudado um pouco e em breve retornarei com outras dicas!

Abraços

      

Introdução

Existem muitas formas de se criar um determinado programa, porém a forma escolhida pode ser o céu ou o inferno para muitos procedimentos. O exemplo mais fácil de entender pode ser visto analisando uma simples consulta para contar a quantidade de registros de uma tabela.

Podemos separar duas formas de consulta.

forma 1:
select count(*) from tabela;

forma 2:
select count(1) from tabela;

A segunda consulta é muito mais rápida que a primeira pois o banco não precisa recuperar todas as informações da tabela em disco. Quando a tabela analisada é pequena essa diferença de tempo pode não ser percebida, mas em tabelas grandes essa diferença pode causar grandes problemas.

Com um conhecimento mais avançado de banco de dados, dependendo da situação, podemos conseguir essa informação de uma forma muito mais rápida.

Imaginem que as consultas acima levem mais que 4 horas para informar a quantidade de registros de uma tabela muito grande e você precisa dessa informação o mais breve possivel. Sabendo que no seu banco de dados é executado um analyze da tabela de tempos em tempos, que pode ser diariamente, ou semanalmente, ou mensalmente, com a simples consulta abaixo você obtem a quantidade de registros aproximada da tabela, que pode já atender as suas necessidades, de uma forma quase que instantanea.

select num_rows, last_analyzed
from all_tables
where table_name like 'TABELA';

O num_rows é a quantidade de registros que existiam na tabela quando o ultimo analyze foi executado, cuja dia e hora é armazenado na coluna last_analyzed.

Meu objetivo aqui no blog é trazer dicas e técnicas que permitem resolver problema de performance, como também problemas do dia a dia dos projetos de banco de dados.

Espero que tenham gostado e em breve estarei postando outras dicas.

Tragam seus problemas que tentaremos encontrar uma solução...

Patrocinado por Taisoft Consultoria em Informática