NVL 함수와 스칼라 서브 쿼리

NVL 함수와 스칼라 서브 쿼리에 대해 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자. c2 열은 20행 중 10행이 널이다.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 AS
SELECT ROWNUM AS c1, CASE WHEN ROWNUM <= 10 THEN ROWNUM END AS c2
  FROM XMLTABLE ('1 to 20');

아래 실행 계획의 2번 줄 Starts에서 스칼라 서브 쿼리가 20번 수행된 것을 확인할 수 있다.

-- 2
SELECT (SELECT /*+ NO_UNNEST */ MAX (c2) FROM t1 x WHERE x.c1 = a.c1) AS c2
  FROM t1 a;

-----------------------------------------------------
| Id  | Operation          | Name | Starts | A-Rows |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |     20 |
|   1 |  SORT AGGREGATE    |      |     20 |     20 |
|*  2 |   TABLE ACCESS FULL| T1   |     20 |     20 |
|   3 |  TABLE ACCESS FULL | T1   |      1 |     20 |
-----------------------------------------------------

아래 쿼리는 NVL 함수의 두 번째 인수에 스칼라 서브 쿼리를 사용했다. 스칼라 서브쿼리가 10번만 수행될 것 같지만 20번 수행된 것을 확인할 수 있다.

-- 3
SELECT NVL (a.c2, (SELECT /*+ NO_UNNEST */ MAX (c2) FROM t1 x WHERE x.c1 = a.c1)) AS c2
  FROM t1 a;

-----------------------------------------------------
| Id  | Operation          | Name | Starts | A-Rows |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |     20 |
|   1 |  SORT AGGREGATE    |      |     20 |     20 |
|*  2 |   TABLE ACCESS FULL| T1   |     20 |     20 |
|   3 |  TABLE ACCESS FULL | T1   |      1 |     20 |
-----------------------------------------------------

NVL 함수의 첫 번째 인수를 리터럴도 변경해도 스칼라 서브 쿼리가 20번 수행된다. NVL 함수의 두 번째 인수에 사용된 표현식이 무조건 수행되는 것을 유추할 수 있다.

-- 4
SELECT NVL (0, (SELECT /*+ NO_UNNEST */ MAX (c2) FROM t1 x WHERE x.c1 = a.c1)) AS c2
  FROM t1 a;

-----------------------------------------------------
| Id  | Operation          | Name | Starts | A-Rows |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |     20 |
|   1 |  SORT AGGREGATE    |      |     20 |     20 |
|*  2 |   TABLE ACCESS FULL| T1   |     20 |     20 |
|   3 |  TABLE ACCESS FULL | T1   |      1 |     20 |
-----------------------------------------------------

NVL2 함수의 세 번째 인수도 NVL 함수의 두 번째 인수와 동일하게 동작한다.

-- 5
SELECT NVL2 (a.c2, a.c2, (SELECT /*+ NO_UNNEST */ MAX (c2) FROM t1 x WHERE x.c1 = a.c1)) AS c2
  FROM t1 a;

-----------------------------------------------------
| Id  | Operation          | Name | Starts | A-Rows |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |     20 |
|   1 |  SORT AGGREGATE    |      |     20 |     20 |
|*  2 |   TABLE ACCESS FULL| T1   |     20 |     20 |
|   3 |  TABLE ACCESS FULL | T1   |      1 |     20 |
-----------------------------------------------------

아래 쿼리는 COALESCE 함수를 사용했다. 스칼라 서브 쿼리가 10번만 수행되는 것을 확인할 수 있다.

-- 6
SELECT COALESCE (a.c2, (SELECT /*+ NO_UNNEST */ MAX (c2) FROM t1 x WHERE x.c1 = a.c1)) AS c2
  FROM t1 a;

-----------------------------------------------------
| Id  | Operation          | Name | Starts | A-Rows |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |     20 |
|   1 |  SORT AGGREGATE    |      |     10 |     10 |
|*  2 |   TABLE ACCESS FULL| T1   |     10 |     10 |
|   3 |  TABLE ACCESS FULL | T1   |      1 |     20 |
-----------------------------------------------------

CASE 표현식에 사용된 스칼라 서브 쿼리도 10번만 수행되는 것을 확인할 수 있다.

-- 7
SELECT CASE WHEN a.c2 IS NOT NULL
            THEN a.c2
            ELSE (SELECT /*+ NO_UNNEST */ MAX (c2) FROM t1 x WHERE x.c1 = a.c1)
       END AS c2
  FROM t1 a;

-----------------------------------------------------
| Id  | Operation          | Name | Starts | A-Rows |
-----------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |     20 |
|   1 |  SORT AGGREGATE    |      |     10 |     10 |
|*  2 |   TABLE ACCESS FULL| T1   |     10 |     10 |
|   3 |  TABLE ACCESS FULL | T1   |      1 |     20 |
-----------------------------------------------------

NVL 함수의 두 번째 인수와 NVL2 함수의 세 번째 인수에 스칼라 서브 쿼리를 사용하면 해당 스칼라 서브 쿼리가 무조건 수행되는 것으로 보인다. 이러한 동작은 성능 저하의 원인이 될 수 있다. 가급적 COALESCE 함수나 CASE 표현식을 사용하도록 하자.