Pessoal, segue abaixo um pequeno tutorial e exemplo de como utilizar a cláusula WITH no Oracle Database.
A cláusula WITH, existente no Oracle Database a partir da versão 9i release 2, é genericamente conhecida como Commom Table Expression (CTE) e faz parte do padrão ANSI SQL 99. Ela pode ser utilizada para otimizar a performance de consultas SQL , possibilitando a reutilização de blocos de subquery ou tabelas que são referenciadas N vezes dentro da mesma query, criando uma espécie de tabela temporária. Essa tabela temporária existe somente no escopo da instrução SQL em que a cláusula WITH está contida e ela é armazenada em memória ou em um tablespace temporário, que possui acesso mais rápido que um tablespace comum.
A cláusula WITH permite a redução do uso de recursos do Banco de Dados ao executar queries complexas ou remotas que referenciam na mesma instrução uma ou mais tabelas, N vezes. Isso é muito comum em queries que geram relatórios e que possuem muitas subqueries. Segue abaixo 1 exemplo de query que consulta dados no schema de exemplo HR do Oracle 10G, alterada para utilizar a cláusula WITH.
1- QUERY ORIGINAL:
SELECT D.DEPARTMENT_NAME,
SUM(E.SALARY) AS DEPT_TOTAL
FROM HR.EMPLOYEES E
INNER JOIN HR.DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME
HAVING SUM(E.SALARY) > (
SELECT SUM(DEPT_TOTAL)/COUNT(*)
FROM (
SELECT D.DEPARTMENT_NAME,
SUM(E.SALARY) AS DEPT_TOTAL
FROM HR.EMPLOYEES E
INNER JOIN HR.DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME))
ORDER BY department_name;
2- QUERY ALTERADA COM CLÁUSULA WITH:
WITH
DEPT_COSTS AS (
SELECT D.DEPARTMENT_NAME,
SUM(E.SALARY) AS DEPT_TOTAL
FROM HR.EMPLOYEES E
INNER JOIN HR.DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME),
AVG_COST AS (
SELECT SUM(DEPT_TOTAL)/COUNT(*) AS DEPT_AVG
FROM HR.DEPT_COSTS)
SELECT *
FROM DEPT_COSTS
WHERE DEPT_TOTAL > ( SELECT DEPT_AVG
FROM AVG_COST)
ORDER BY department_name;
Observem que na query 2, blocos de SQL redundantes foram eliminados através da criação da tabela temporária DEPT_COSTS. Em testes que eu fiz, o custo médio de execução dessa query caiu aproximadamente 30%, após utilizar a cláusula WITH (query 2).
parabéns, estamos precisando testar numa situação parecida. Não sei se ajuda, mas foi legal a dica ..
ResponderExcluirLegal, fico contente que o artigo tenha te ajudado! Abs.
ResponderExcluirMuito Bom!
ResponderExcluirSuper útil! Principalmente na sintuação em que é preciso verificar uma série de consistências nas consultas, nas quais são feitas várias subqueries, várias vezes cada uma.
Essa, realmente, é uma solução!
Ótima solução.
Obrigado Beatriz pelos comentários!
ResponderExcluirSensacional, Fábio! Artigo de fácil entendimento e de grande utilidade. Obrigada.
ResponderExcluirDe nada Unknown. Que bom que será útil!
ExcluirFábio, boa tarde, tudo bem?
ResponderExcluirParabéns pelo post.
Me tira uma dúvida. Percebo que a cláusula WITH é interessante quando você usa subqueries redundantes.
Em qual ponto isso seria interessante utilizar a cláusula WITH num bloco PL/SQL, substituindo Cursores por isso, sem ter subqueries redundantes?
Que eu saiba, a cláusula WITH deverá ser usada, de qualquer forma, num retorno em um ROWTYPE, array, etc.
Abs e parabéns mais uma vez pelo site!!!
Maia.
Maia, não sei se entendi muito bem o contexto do seu problema, mas vou responder do jeito que entendi. Não há sentido em usar a cláusula WITH se vc não usa uma ou mais tabelas N vezes dentro de uma instrução SQL.
ExcluirQto ao substituir um cursor por uma cláusula WITH, se vc puder fazer isso, faça. EVITE SEMPRE PL/SQL se vc puder fazer o mesmo com SQL PURO. Quando vc cria um cursor vc está usando um bloco PL/SQL. Se vc não puder evitar o cursor, mas tiver uma tabela que é referenciada N vezes dentro da instrução SQL, utilize a cláusula WITH (por que não?), com certeza vc irá otimizá-la!
[]s
Fábio, muito obrigado.
ExcluirEra isso mesmo que você entendeu. Tem uma pessoa, que se diz "responsável" por tunning de uma empresa, teimando que o WITH pode ser usado sem referenciarmos N vezes uma tabela ou X vezes a mesma subquery. Mas enfim!!!
Abs e sucesso!
Maia.