Funções analiticas LAG/LEAD
Ambas as funções tem como sua sintaxe parecidas:
LAG (value_expression [,offset] [,default]) OVER ([query_partition_clase] order_by_clause)
LEAD (value_expression [,offset] [,default]) OVER ([query_partition_clase] order_by_clause)
LAG
Esta é uma função analitica. Que provem o acesso a mais que um registro de uma tabela ao mesmo tempo sem precisar fazer um join na própria tabela self join. A busca irá retornar uma ou mais registros anteriores a da possição atual (current).
value_expression - É a expressão que irá retornar da query.
offset - É opcional, caso não seja informar o padrão é 1. Onde este é o campo que podes informar quantos registros deverá retornar.
default - É opcional, caso não seja informado o valor será null.
query_partition_clase - É opcional pois se tiveres particionamento podes colocar a clausula de particionamento .
order_by_clause - É opcional pois desta forma irá informar a ordem que queres pegar a informação anterior.
Exemplo para simplificar:
Para fins estatisticos podemos verificar quanto em estoque temos que ter de um determinado produto ou tivemos que ter entre um mês e outro.
drop table estoque;
create table estoque (produto char(1), dat date, qtde number);
insert into estoque select 'A',trunc(sysdate-((dbms_random.value(1,90))*30/10)),trunc(dbms_random.value(1,100)) from dual connect by level < = 100;
insert into estoque select 'B',trunc(sysdate-((dbms_random.value(1,90))*30/10)),trunc(dbms_random.value(1,100)) from dual connect by level <= 100;
insert into estoque select 'C',trunc(sysdate-((dbms_random.value(1,90))*30/10)),trunc(dbms_random.value(1,100)) from dual connect by level <= 100;
commit;
select produto,to_char(dat, 'FMYYYY/MM', 'nls_date_language=portuguese') mes,
sum(qtde) soma_normal,
sum(qtde) + nvl(lag(sum(qtde))
over(order by to_char(dat, 'FMYYYY/MM', 'nls_date_language=portuguese')),
0) ACUMULADO
from estoque
where produto ='A'
group by (produto,to_char(dat, 'FMYYYY/MM', 'nls_date_language=portuguese'))
order by produto,to_char(dat, 'FMYYYY/MM', 'nls_date_language=portuguese');
PRODUTO MES SOMA_NORMAL ACUMULADO
A 2008/1 514 514
A 2008/2 1073 1587
A 2008/3 805 1878
A 2008/4 287 1092
A 2008/5 633 920
A 2008/6 355 988
A 2008/7 236 591
A 2008/8 625 861
A 2008/9 457 1082
LEAD
Simplesmente é o contrario do LAG, ele irá pegar o proximo valor.
Categoria(s): Oracle, PL/SQL
Fake/Virtual Index (Indice Virtual)
Após muitas leituras sobre tuning e performance, achei algo interessante e rápido para postar aqui no site e que possa ser muito util para os programadores e administradores de Banco Oracle.
Preciso melhorar a performance do meu SQL, será que esse indice vai me ajudar?
Uma boa pergunta, pois até então criar um indice em uma tabela sempre há um custo.
Porem como irei saber se aquele indice vai ser útil mesmo sem poder testar na prática?
Uma das novas features do 11g é criar indices invisiveis, não interferindo no ambiente(produção como no exemplo), uhuuuu claro estou dizendo em produção nada em ambiente teste, pois nem todo mundo tem essa maravilha que é um ambiente de teste bom e legal p/ testar sua aplicação.
Ai vem o X da questão, nas versões anteriores(8i, 9i e 10g) não tem nada para nos ajudar?
Isso mesmo lendo um bucado achei o FAKE INDEX ou VIRTUAL INDEX.
Ok, agora vou mostrar com um exemplo simples do seu funcionamento.
1. Lembrando que esta prática serve para o Oracle informar, se o indice a ser criado, realmente será usado pelo otimizador e seu custo.
2. Ao ser criado, estara somente no dicionario de dados e não será criado o indice mesmo, não haverá segmentos (NOSEGMENT).
3. Há um parametro não documentado pela oracle, que faz parte do Oracle Enterprise Manager Tuning Wizard para que esta pratica seja bem sucessida.
Ex.:
Tabela teste com 398698 registros
SQL> DESC teste Nome Nulo? Tipo ----------------------------------------- -------- ---------------------------- CAMPO1 CHAR(10) CAMPO2 NOT NULL NUMBER(11) CAMPO3 VARCHAR2(4000) SQL> SELECT campo1,count(1) FROM teste 2 GROUP BY campo1; CAMPO1 COUNT(1) ---------- ---------- 111111111 57789 444444444 10000 BBBB 10000 CCCCCCCCCC 10000 DDDDDDDDDD 10000 EEEEEEEEEE 10000 FFFFFFFFFF 10000 GGGGGGGGG 10000 HHHHHHHHH 19999 JJJJJJJJJ 10000 RRRRRRRRR 10000 WWWWWWWWW 6551 ddddddddd 10000 hhhhhhhhh 10000 iiiiiiiii 10000 mmmmmmmmm 10000 nnnnnnnnn 10000 ppppppppp 10000 qqqqqqqqq 50000 rrrrrrrrr 10000 uuuuuuuuu 20000 vvvvvvvvv 10000 xxxxxxxxx 64359 yyyyyyyyy 10000
Agora conhecendo a tabela de teste
Código:
SQL> SET autotrace traceonly EXPLAIN SQL> SELECT * FROM teste WHERE campo1 = 'xxxxxxxxx'; Plano de Execução ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=116 Card=16612 Bytes=315628) 1 0 TABLE ACCESS (FULL) OF 'TESTE' (Cost=116 Card=16612 Bytes=315628)
Criando o Ãndice
Código:
SQL> CREATE INDEX teste_i01 ON teste(campo1) nosegment;
Agora ver se ele vai usar o indice criado:
SQL> SELECT * FROM teste WHERE campo1 = 'xxxxxxxxx'; Plano de Execução ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=116 Card=16612 Bytes=315628) 1 0 TABLE ACCESS (FULL) OF 'TESTE' (Cost=116 Card=16612 Bytes=315628)
Agora deves estar se perguntando porque o meu select não utilizou o meu indice?
Bingo agora vamos para o que nos interessa.
SQL> ALTER session SET "_use_nosegment_indexes" = true; Plano de Execução ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=36 Card=16612 Bytes=315628) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TESTE' (Cost=36 Card=16612 Bytes=315628) 2 1 INDEX (RANGE SCAN) OF 'TESTE_I01' (NON-UNIQUE) (Cost=2 Card=16612)
Obs.: Não esqueça de fazer um analyze na tabela.
Para utilizar por definitivo o indice, deve-se dropar o indice virtual e cria-lo sem a clausula NOSEGMENT.
Importante: Como o indice é virtual sem segmento, o tempo de execução não pode ser calculado pois o indice virtual serve somente para ver seu custo quando for criado realmente. No 11g a história muda.
ref.
Oracle 11g Database New Features - 1.2.9.4 Invisible Indexes
Creating an Invisible Indexes
Making an Index Invisible
Dica - Indices Virtuais
Categoria(s): Banco de Dados, Oracle, PL/SQL
Banco de Dados Oracle alcança novo recorde mundial
A Oracle anunciou o novo recorde mundial no benchmark TPC-C para o banco de dados Oracle® 11g Standard Edition One executado no Windows(1). Com esse resultado, a Oracle passa a deter as três primeiras posições na categoria das dez na relação preço x performance do benchmark TPC-C. Essa análise é mais uma demonstração do compromisso da Oracle em fornecer escalabilidade e desempenho com baixo custo para clientes de qualquer porte.
"O resultado do benchmark demonstra mais uma vez que a Oracle pode oferecer desempenho inigualável por um preço acessÃvel, independentemente do sistema operacional escolhido pelo cliente", afirma Sushil Kumar, diretor sênior da Gerência de Produto de Disponibilidade, Capacidade de Gerenciamento e Desempenho do Banco de Dados da Oracle. "O banco de dados Oracle 11g Standard Edition One que roda no servidor Dell PowerEdge 2900 fornece aos clientes benefÃcios inegáveis, com uma solução escalável e de alta performance para ambientes baseados em Windows."
O Oracle 11g Standard Edition One é um banco de dados acessÃvel e de recursos completos para servidores com até dois soquetes. Oferece desempenho e segurança corporativa, é simples de gerenciar, compatÃvel com versões superiores e pode ser dimensionado facilmente, conforme a demanda do cliente. Para obter mais informações sobre o banco de dados Oracle 11g Standard Edition One, visite o site www.oracle.com/database/std_one.html.
Sobre o TPC-C
O TPC-C é um benchmark de OLTP (processamento de transações on-line) desenvolvido pelo Transaction Processing Performance Council (TPC). O benchmark TPC-C define um padrão rigoroso para calcular o desempenho e a relação custo x performance medidas em transações por minuto (tpmC) e US$/tpmC, respectivamente. Mais informações estão disponÃveis no site www.tpc.org. As 10 mais do TPC-C por preço x performance: www.tpc.org/tpcc/results/tpcc_price_perf_results.asp.
Sobre o banco de dados Oracle 11g
O Oracle é o único banco de dados projetado para grid computing. Com mais de 400 novos recursos, 36 mil pessoas ao mês de desenvolvimento e 15 milhões de horas de testes, o banco de dados Oracle 11g torna a gestão das informações empresariais mais fácil, permitindo que os clientes dediquem mais tempo aos seus negócios e inovem com agilidade. O Oracle 11g oferece desempenho superior, escalabilidade, disponibilidade, segurança e facilidade de gerenciamento em um grid de servidores e armazenamento padrão de mercado.
Categoria(s): Banco de Dados, Oracle, Sistemas Operacionais, Windows
Query’s SQL para XML
Rapidinha: O oracle tem como default a função de transformar query's sql para XML
Exemplo:
SET long 5000 SET pagesize 5000 SET linesize 132 SELECT dbms_xmlgen.getxml(’SELECT * FROM user_tables’) XMLQUERY FROM dual;
Categoria(s): Banco de Dados, Oracle, PL/SQL, Programação
