오라클은 표준 ANSI SQL 문법과 오라클 스타일의 문법을 함께 사용할 수 있습니다. 두 문법의 가장 눈에 띄는 차이는 조인(Join) 문법입니다. 표현의 차이일 뿐 둘 다 기능과 성능은 동일한 것이 일반적이지만, 실행 계획이 다른 경우도 가끔 발견됩니다. 이 때는 실행 계획 차이로 성능이 다를 수 있습니다.
아래는 최근에 발견한 사례입니다. 참고로 테스트는 오라클 19c 버전에서 진행했습니다.
준비
먼저 아래와 같이 테스트 데이터를 생성합니다.
CREATE TABLE T1
AS
SELECT 1 C1, 'A' C2 FROM DUAL UNION ALL
SELECT 2 C1, 'B' C2 FROM DUAL UNION ALL
SELECT 3 C1, 'C' C2 FROM DUAL;
CREATE TABLE T2
AS
SELECT 1 C1, 'A' C2 FROM DUAL UNION ALL
SELECT 2 C1, 'B' C2 FROM DUAL;쿼리 1
오라클 조인 문법으로 CTAS(Create Table As Select) 문을 실행할 때는 SELECT 절에 사용한 힌트대로 잘 동작합니다.
t1-- 1. Oracle SQL
CREATE TABLE T3
AS
SELECT /*+ LEADING(A) USE_HASH(B) */
A.C1 AS X1
, A.C2 AS X2
, B.C1 AS X3
, B.C2 AS X4
FROM T1 A, T2 B
WHERE A.C1 = B.C1(+)
AND A.C2 IN ( 'A', 'B' );
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 2 | 20 | 7 (0)| 00:00:01 |
| 1 | LOAD AS SELECT | T3 | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 2 | 20 | 6 (0)| 00:00:01 |
|* 3 | HASH JOIN OUTER | | 2 | 20 | 6 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | T1 | 2 | 10 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 2 | 10 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."C1"="B"."C1"(+))
4 - filter("A"."C2"='A' OR "A"."C2"='B')쿼리 2
ANSI SQL 문법으로 실행했을 때는 실행 계획이 다른 것을 확인할 수 있습니다. 이 사례에서는 옵티마이저 힌트를 무시하고 소트머지 조인과 Lateral View를 이용하는 쿼리 변환이 발생했습니다. 실행 계획 결과 중 Hint Report를 보면 USE_HASH(B) 힌트를 사용하지 않았습니다.
CREATE TABLE T4
AS
SELECT /*+ LEADING(A) USE_HASH(B) */
A.C1 AS X1
, A.C2 AS X2
, B.C1 AS X3
, B.C2 AS X4
FROM T1 A LEFT OUTER JOIN T2 B
ON A.C1 = B.C1
AND A.C2 IN ( 'A', 'C' );
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 3 | 63 | 13 (0)| 00:00:01 |
| 1 | LOAD AS SELECT | T4 | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 3 | 63 | 12 (0)| 00:00:01 |
| 3 | MERGE JOIN OUTER | | 3 | 63 | 12 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 3 | 15 | 3 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 1 | 16 | 9 (0)| 00:00:01 |
| 6 | VIEW | VW_LAT_DF9E236D | 1 | 16 | 3 (0)| 00:00:01 |
|* 7 | FILTER | | | | | |
|* 8 | TABLE ACCESS FULL | T2 | 1 | 5 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
7 - filter("A"."C2"='A' OR "A"."C2"='C')
8 - filter("A"."C1"="B"."C1")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
8 - SEL$1 / B@SEL$1
U - USE_HASH(B)마무리
테스트 결과에서 보듯이 특정한 경우에는 오라클 스타일 SQL과 ANSI 스타일 SQL의 실행 계획이 다르게 나타날 수 있습니다.