Lendas de Oracle Tuning – SQL: Sintaxe ANSI x Sintaxe Oracle

SEREIA Sabe qual a melhor sintaxe em termos de desempenho? Não importa. Você está se preocupando com a coisa errada. Eu executei os seguintes JOINs: INNER, LEFT OUTER e RIGHT OUTER, primeiro com a Sintaxe Oracle, e depois com a Sintaxe ANSI, registrando todos em um trace 10053 (o trace do CBO).

SQL> CONN SCOTT/TIGER
Connected.
SQL> SET PAGES 1000
SQL> SET LINES 210
SQL> ALTER SESSION SET EVENTS '10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

Session altered.

SQL> SELECT E.ENAME, E.JOB, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

ENAME	   JOB	     DNAME
---------- --------- --------------
CLARK	   MANAGER   ACCOUNTING
KING	   PRESIDENT ACCOUNTING
MILLER	   CLERK     ACCOUNTING
JONES	   MANAGER   RESEARCH
FORD	   ANALYST   RESEARCH
ADAMS	   CLERK     RESEARCH
SMITH	   CLERK     RESEARCH
SCOTT	   ANALYST   RESEARCH
WARD	   SALESMAN  SALES
TURNER	   SALESMAN  SALES
ALLEN	   SALESMAN  SALES
JAMES	   CLERK     SALES
BLAKE	   MANAGER   SALES
MARTIN	   SALESMAN  SALES

14 rows selected.

SQL> SELECT E.ENAME, E.JOB, D.DNAME FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

ENAME	   JOB	     DNAME
---------- --------- --------------
CLARK	   MANAGER   ACCOUNTING
KING	   PRESIDENT ACCOUNTING
MILLER	   CLERK     ACCOUNTING
JONES	   MANAGER   RESEARCH
FORD	   ANALYST   RESEARCH
ADAMS	   CLERK     RESEARCH
SMITH	   CLERK     RESEARCH
SCOTT	   ANALYST   RESEARCH
WARD	   SALESMAN  SALES
TURNER	   SALESMAN  SALES
ALLEN	   SALESMAN  SALES
JAMES	   CLERK     SALES
BLAKE	   MANAGER   SALES
MARTIN	   SALESMAN  SALES

14 rows selected.

SQL> SELECT E.ENAME, E.JOB, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO(+);

ENAME	   JOB	     DNAME
---------- --------- --------------
MILLER	   CLERK     ACCOUNTING
KING	   PRESIDENT ACCOUNTING
CLARK	   MANAGER   ACCOUNTING
FORD	   ANALYST   RESEARCH
ADAMS	   CLERK     RESEARCH
SCOTT	   ANALYST   RESEARCH
JONES	   MANAGER   RESEARCH
SMITH	   CLERK     RESEARCH
JAMES	   CLERK     SALES
TURNER	   SALESMAN  SALES
BLAKE	   MANAGER   SALES
MARTIN	   SALESMAN  SALES
WARD	   SALESMAN  SALES
ALLEN	   SALESMAN  SALES

14 rows selected.

SQL> SELECT E.ENAME, E.JOB, D.DNAME FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

ENAME	   JOB	     DNAME
---------- --------- --------------
MILLER	   CLERK     ACCOUNTING
KING	   PRESIDENT ACCOUNTING
CLARK	   MANAGER   ACCOUNTING
FORD	   ANALYST   RESEARCH
ADAMS	   CLERK     RESEARCH
SCOTT	   ANALYST   RESEARCH
JONES	   MANAGER   RESEARCH
SMITH	   CLERK     RESEARCH
JAMES	   CLERK     SALES
TURNER	   SALESMAN  SALES
BLAKE	   MANAGER   SALES
MARTIN	   SALESMAN  SALES
WARD	   SALESMAN  SALES
ALLEN	   SALESMAN  SALES

14 rows selected.

SQL> SELECT E.ENAME, E.JOB, D.DNAME FROM EMP E, DEPT D WHERE E.DEPTNO(+) = D.DEPTNO;

ENAME	   JOB	     DNAME
---------- --------- --------------
CLARK	   MANAGER   ACCOUNTING
KING	   PRESIDENT ACCOUNTING
MILLER	   CLERK     ACCOUNTING
JONES	   MANAGER   RESEARCH
FORD	   ANALYST   RESEARCH
ADAMS	   CLERK     RESEARCH
SMITH	   CLERK     RESEARCH
SCOTT	   ANALYST   RESEARCH
WARD	   SALESMAN  SALES
TURNER	   SALESMAN  SALES
ALLEN	   SALESMAN  SALES
JAMES	   CLERK     SALES
BLAKE	   MANAGER   SALES
MARTIN	   SALESMAN  SALES
		     OPERATIONS

15 rows selected.

SQL> SELECT E.ENAME, E.JOB, D.DNAME FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

ENAME	   JOB	     DNAME
---------- --------- --------------
CLARK	   MANAGER   ACCOUNTING
KING	   PRESIDENT ACCOUNTING
MILLER	   CLERK     ACCOUNTING
JONES	   MANAGER   RESEARCH
FORD	   ANALYST   RESEARCH
ADAMS	   CLERK     RESEARCH
SMITH	   CLERK     RESEARCH
SCOTT	   ANALYST   RESEARCH
WARD	   SALESMAN  SALES
TURNER	   SALESMAN  SALES
ALLEN	   SALESMAN  SALES
JAMES	   CLERK     SALES
BLAKE	   MANAGER   SALES
MARTIN	   SALESMAN  SALES
		     OPERATIONS

15 rows selected.

SQL>

No arquito de trace, vemos que internamente o Oracle troca os JOINs escritos em ANSI para a Sintaxe… Oracle! Este é SQL do INNER JOIN em Sintaxe Oracle, depois de compilado pelo CBO:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "E"."ENAME" "ENAME","E"."JOB" "JOB","D"."DNAME" "DNAME" FROM "SCOTT"."EMP" "E","SCOTT"."DEPT" "D" WHERE "E"."DEPTNO"="D"."DEPTNO"
kkoqbc: optimizing query block SEL$1 (#0)

E este é o INNER JOIN em Sintaxe ANSI. Ele foi reescrito automaticamente com a Sintaxe Oracle:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "E"."ENAME" "ENAME","E"."JOB" "JOB","D"."DNAME" "DNAME" FROM "SCOTT"."EMP" "E","SCOTT"."DEPT" "D" WHERE "E"."DEPTNO"="D"."DEPTNO"
kkoqbc: optimizing query block SEL$58A6D7F6 (#0)

Este é o LEFT OUTER JOIN, Sintaxe Oracle:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "E"."ENAME" "ENAME","E"."JOB" "JOB","D"."DNAME" "DNAME" FROM "SCOTT"."EMP" "E","SCOTT"."DEPT" "D" WHERE "E"."DEPTNO"="D"."DEPTNO"(+)
kkoqbc: optimizing query block SEL$1 (#0)

E este é o LEFT OUTER JOIN, Sintaxe ANSI, também reescrito internamente:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "E"."ENAME" "ENAME","E"."JOB" "JOB","D"."DNAME" "DNAME" FROM "SCOTT"."EMP" "E","SCOTT"."DEPT" "D" WHERE "E"."DEPTNO"="D"."DEPTNO"(+)
kkoqbc: optimizing query block SEL$2BFA4EE4 (#0)

Este é o RIGHT OUTER JOIN, Sintaxe Oracle:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "E"."ENAME" "ENAME","E"."JOB" "JOB","D"."DNAME" "DNAME" FROM "SCOTT"."EMP" "E","SCOTT"."DEPT" "D" WHERE "E"."DEPTNO"(+)="D"."DEPTNO"
kkoqbc: optimizing query block SEL$1 (#0)

Este é o RIGHT OUTER JOIN, Sintaxe ANSI, também reescrito internamente:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "E"."ENAME" "ENAME","E"."JOB" "JOB","D"."DNAME" "DNAME" FROM "SCOTT"."EMP" "E","SCOTT"."DEPT" "D" WHERE "E"."DEPTNO"(+)="D"."DEPTNO"
kkoqbc: optimizing query block SEL$2BFA4EE4 (#0)

Mesmo o ANSI FULL OUTER JOIN (que não pode ser feito diretamente na sintaxe Oracle – é necessário escrever o LEFT OUTER e o RIGHT OUTER separadamente, e junta-los por UNION), é reescrito para algo que você não esperava: ele é alterado para uma VIEW, e é feito o SELECT a partir dele.

SQL> SELECT E.ENAME, E.JOB, D.DNAME FROM EMP E FULL OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

ENAME	   JOB	     DNAME
---------- --------- --------------
SMITH	   CLERK     RESEARCH
ALLEN	   SALESMAN  SALES
WARD	   SALESMAN  SALES
JONES	   MANAGER   RESEARCH
MARTIN	   SALESMAN  SALES
BLAKE	   MANAGER   SALES
CLARK	   MANAGER   ACCOUNTING
SCOTT	   ANALYST   RESEARCH
KING	   PRESIDENT ACCOUNTING
TURNER	   SALESMAN  SALES
ADAMS	   CLERK     RESEARCH
JAMES	   CLERK     SALES
FORD	   ANALYST   RESEARCH
MILLER	   CLERK     ACCOUNTING
		     OPERATIONS

15 rows selected.

SQL>
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "VW_FOJ_0"."ENAME_0" "ENAME","VW_FOJ_0"."JOB_1" "JOB","VW_FOJ_0"."DNAME_2" "DNAME" FROM  (SELECT "E"."ENAME" "ENAME_0","E"."JOB" "JOB_1","D"."DNAME" "DNAME_2" FROM "SCOTT"."DEPT" "D" FULL OUTER JOIN "SCOTT"."EMP" "E" ON "E"."DEPTNO"="D"."DEPTNO") "VW_FOJ_0"
kkoqbc: optimizing query block SEL$1 (#0)

7 comments

  1. Portilho ,que coincidência cara precisei ontem fazer uma procedure pra fazer um relatório OLAP ,eis que no relacionamento de duas tabelas A e B precisava de campos da tabela B que não estavam contidos na Tabela A ,rapaz um campo boleano da Tabela A não retornava nada da tabela B,pois bem fiz rigth outer join na Tabela A e um left outer join na Tabela B e problema foi resolvido,já vi gente achando que o (+) no select era apenas para enfeite.

  2. Eu sou um dos que ainda usa a sintaxe Oracle ao invés de ANSI, lembro quando estava começando a desenvolver em SQL no Oracle 9i, uma das coisas que influenciava na performance do select era a ordem das tabelas no from, então seguíamos um certo padrão para escrita dos SQLs.

    1. Outra coisa que pode influenciar no desempenho, e ajudar a alimentar estas lendas, é que quando o texto tem alguma diferença (por exemplo, trocando a sintaxe), o SQL é novamente compilado, o que pode gerar um novo plano de execução.

    1. Realmente a escrita em ANSI tem várias vantagens estéticas, e a própria Oracle recomenda a Sintaxe ANSI.
      Sobre a estética, qualquer programador deve concordar que este símbolo (+) é muito esquisito.

      Oracle recommends that you use the FROM clause OUTER JOIN syntax rather than the Oracle join operator. Outer join queries that use the Oracle join operator (+) are subject to the following rules and restrictions, which do not apply to the FROM clause OUTER JOIN syntax.
      Fonte: http://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52356

      Mas o curioso é que “por baixo dos panos”, o mais rápido é a Sintaxe Oracle, pois é esta que é executada na verdade.

      É como o RBO, que não é mais suportado, mas é utilizado por SQLs internos do Oracle.

      O motivo deste artigo é mostrar que em termos de desempenho, não há por que se preocupar com isto. A preocupação deve ter base estética ou funcional. Sabe-se lá pelo que o Oracle estará trocando a sintaxe com a qual o desenvolvedor se preocupou tanto.

      Já no caso do FULL OUTER JOIN, não há uma “Sintaxe Oracle”. Ela não existe, assim como o operador (+) tem diversas outras restrições:
      Fonte: http://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52356

      O que pode ser feito é executar os dois SELECTs separadamente e uni-los. Isto é outra coisa completamente diferente.
      Seria então como comparar comer sopa com colher e com garfo. O garfo não suporta sopa, mas você pode tentar, e comer um pouco, mas terá um resultado pior.

      Além disso, o último JOIN que o desenvolvedor irá precisar deverá ser este. Ou há algo bem estranho com o modelo de dados…

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.