CLOB #2 - NVL 함수

SECUREFILE로 생성된 CLOB 타입에 NVL 함수를 사용하면 I/O가 증가한다는 문의가 있었다.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 (c1 NUMBER, c2 CLOB);

INSERT INTO t1
SELECT ROWNUM AS c1, TO_CLOB (LPAD ('X', 4000, 'X')) || TO_CLOB (LPAD ('X', 4000, 'X')) AS c2
  FROM XMLTABLE ('1 to 100');

COMMIT;

아래와 같이 쿼리를 수행하고 AUTOTRACE로 실행 통계를 수집하자. 2-2 쿼리만 통계 값에 차이가 있다.

-- 2
SET LONG 80
SET LONGCHUNKSIZE 80
SET AUTOTRACE TRACEONLY

-- 2-1
SELECT * FROM t1;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        306  consistent gets
        400  physical reads
          0  redo size
      48460  bytes sent via SQL*Net to client
      58708  bytes received via SQL*Net from client
        102  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

-- 2-2
SELECT c1, NVL (c2, 'X') AS c2 FROM t1;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        406  consistent gets
        200  physical reads
          0  redo size
      81460  bytes sent via SQL*Net to client
      64208  bytes received via SQL*Net from client
        302  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

-- 2-3
SELECT c1, COALESCE (c2, TO_CLOB ('X')) AS c2 FROM t1;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        306  consistent gets
        400  physical reads
          0  redo size
      48460  bytes sent via SQL*Net to client
      58708  bytes received via SQL*Net from client
        102  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

-- 2-4
SELECT c1, CASE WHEN c2 IS NOT NULL THEN c2 ELSE TO_CLOB ('X') END AS c2 FROM t1;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        306  consistent gets
        400  physical reads
          0  redo size
      48460  bytes sent via SQL*Net to client
      58708  bytes received via SQL*Net from client
        102  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

2-1, 2-2 쿼리를 비교해보면 consistent gets 항목에서 100의 차이가 발생하는 것을 확인할 수 있다. 반대로 physical read total IO requests 항목은 NVL 함수를 사용한 2-2 쿼리가 200만큼 감소한 것을 확인할 수 있다. SQL*Net roundtrips to/from client 항목이 200만큼 증가한 것도 흥미롭다.

-- 3
NAME                              2-1     2-2
--------------------------------- ------- -------
logical read bytes from cache     2506752 3325952
consistent gets                       306     406 -- !
consistent gets from cache            306     406
shared io pool buffer get success     400     100 -- !
physical read total bytes         3276800 1638400
physical read total IO requests       400     200 -- !
physical reads direct (lob)           400     200
securefile direct read bytes      3276800 1638400
securefile direct read ops            400     200
SQL*Net roundtrips to/from client     102     302 -- !

정확한 원인은 알 수 없으나 SECUREFILE로 생성된 CLOB 타입에 NVL 함수를 사용하면 일부 Call이 BASICFILE로 동작하는 것으로 유추할 수 있다. CLOB 값에는 NVL 함수보다 COALESCE 함수를 사용하는 편이 바람직해보인다.