2.2.7 자동 형변환

1. 테이블 생성 (Oracle 기준)

 

CREATE TABLE customer (
    birth_date CHAR(8) NOT NULL,       -- Format: 'YYYYMMDD'
    customer_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name VARCHAR2(50),
    email VARCHAR2(100)
);

-- 생년월일 인덱스
CREATE INDEX idx_customer_birth_date ON customer(birth_date);

 

2. 랜덤 데이터 100만 건 삽입

 

Oracle에서는 DBMS_RANDOM과 CONNECT BY를 활용해 데이터를 생성할 수 있습니다.

 

BEGIN
  FOR i IN 1..100 LOOP
    INSERT /*+ APPEND */ INTO customer (birth_date, name, email)
    SELECT
      TO_CHAR(DATE '1970-01-01' + TRUNC(DBMS_RANDOM.VALUE(0, 13150)), 'YYYYMMDD'),
      'Customer' || LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 100000))), 5, '0'),
      'user' || LPAD(TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(1, 1000000))), 6, '0') || '@test.com'
    FROM dual
    CONNECT BY LEVEL <= 10000;  -- 100 * 10,000 = 1,000,000 rows
    COMMIT;
  END LOOP;
END;
/

 

 

3. 생년월일이 선두 컬럼인 인덱스 존재

 

다음과 같이 조회했을 때 나오는 실행계획 SQL> select * from customer         where birth_date=19950627; 71 rows selected. Total elapsed time 00:00:00.096286   SQL ID: apv1tfwtpsww9 Child number: 112 Plan hash value: 3696328097 Execution Plan -------------------------------------------------------------------------------------------------------- 1  TABLE ACCESS (FULL): CUSTOMER (Cost:3150, %%CPU:0, Rows:80) Predicate Information -------------------------------------------------------------------------------------------------------- 1 - filter: ("CUSTOMER"."BIRTH_DATE" = 19950627) (0.000) Note -------------------------------------------------------------------------------------------------------- 1 - dynamic sampling used for this table (98 blocks) NAME                                VALUE ------------------------------ ---------- db block gets                         489 consistent gets                      7444 physical reads                       7299 redo size                              76 sorts (disk)                            0 sorts (memory)                          2 rows processed                         71

 

인덱스가 있음에도 불구하고 TABLE ACCESS (FULL) 스캔을 했다.

이유는 무엇일까?

해당 테이블의 birth_date는 CHAR 형식이라 문자형으로 저장되어 있고, 인덱스도 문자열을 기준으로 정렬되어 있다.

그러나 조회 쿼리에서는 조건절에 상수형으로 주었다.

 

즉 자동 형변환이 일어나게 된 것이다.
* 오라클에서 숫자형과 문자형이 만나면 숫자형이 이긴다
* 하지만 like 연산자일때는 다르다. Like 자체가 문자열 비교 함수라, 상수가 들어오면 문자열로 바뀌게 된다.

 

SQL> select * from customer where birth_date='19950627';   71 rows selected. Total elapsed time 00:00:00.012057 SQL ID: a7rkhtfvfs320 Child number: 131 Plan hash value: 3497942325 Execution Plan --------------------------------------------------------------------------------------------------------    1  TABLE ACCESS (ROWID): CUSTOMER (Cost:84, %%CPU:0, Rows:80) 00    2    INDEX (RANGE SCAN): IDX_CUSTOMER_BIRTH_DATE (Cost:3, %%CPU:0, Rows:80) Predicate Information --------------------------------------------------------------------------------------------------------    2 - access: ("CUSTOMER"."BIRTH_DATE" = '19950627') (0.000) Note --------------------------------------------------------------------------------------------------------    2 - dynamic sampling used for this table (98 blocks)

 

위와 같이 조건절을 문자형으로 주게 되면 인덱스 사용에 전혀 문제가 없다.

속도도 9배 빨라졌음을 볼 수 있고 INDEX RANGE SCAN을 사용하는 걸 확인할 수 있다.

 

SQL 성능은 형변환을 줄이는 데서 나오는 것이 아니라 '블록 I/O를 줄일 수 있느냐 없느냐' 에서 나온다.

위에서 확인했듯 개발자가 형변환 함수를 생략해도 옵티마이저가 자동으로 생성한다.