자동 형변환
오라클에서는 자동 형변환이 있습니다 예를 들어서 컬럼이 타입은 varchar(Stirng) 타입이지만 사용자가 숫자를 넣어도 오라클은 알아서 형변활을 하게 됩니다 이를 실행계획 보면 이렇게 보입니다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
EXPLAIN PLAN FOR
SELECT *
FROM Ex5_Student
WHERE birthday > 20050605
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------+
Plan hash value: 185264460 |
|
---------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 7859 | 94308 | 11 (0)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX5_STUDENT | 7859 | 94308 | 11 (0)| 00:00:01 ||
---------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter(TO_NUMBER("BIRTHDAY")>20050605) |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
오퍼레이션은 뒤에 보고 아래 Predicate Information 이 부분을 보게 되면 1 - filter(TO_NUMBER(“BIRTHDAY”)>20050605) 현재 옵티마이저가 알아서 BIRTHDAY 을 TO_NUMBER로 변환해서 비교하는 모습을 보고 있습니다 마약 이 컬럼이 인덱스 선두 컬럼이라면 선두 컬럼 가공으로 인한 Index Range Scan 이 되지 않습니다
소스
https://gitlab.com/kimdongy1000/sqltuningproject
이곳에서 Ex5_student , Ex6_student 를 찾자 아래 .sql 파일도 스키마도 마련해두었습니다
간단한 테이블 스키마
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DROP TABLE Ex5_Student
CREATE TABLE Ex5_Student(
name varchar(9),
birthday varchar(8)
)
CREATE INDEX Ex5_Student_idx ON Ex5_Student (birthday , name);
DROP TABLE Ex6_Student
CREATE TABLE Ex6_Student(
name varchar(9),
birthday number
)
CREATE INDEX Ex6_Student_idx ON Ex6_Student (birthday , name);
앞으로 소스코드는 생략하겠습니다 소스코드는 git 을 참조해 주세요 소스코드로 1만 명의 학생을 만들었습니다 이때 인덱스는 birthday 컬럼이 선두 컬컬럼이 되게 됩니다 그럼 다시 위에 있는 실행계획을 가져오겠습니다
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
EXPLAIN PLAN FOR
SELECT *
FROM Ex5_Student
WHERE birthday > 20050605
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------+
Plan hash value: 185264460 |
|
---------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 7859 | 94308 | 11 (0)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX5_STUDENT | 7859 | 94308 | 11 (0)| 00:00:01 ||
---------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter(TO_NUMBER("BIRTHDAY")>20050605) |
|
Note |
----- |
- dynamic statistics used: dynamic sampling (level=2) |
인덱스를 보고 다시 봐버리니 선두 컬럼의 형변환이 일어나게 되어서 인덱스를 타지 못하고 Full scan 이 발생하는 것을 볼 수 있습니다 이는 오라클의 형변환의 최대 단점 중에 하나입니다 그렇기에 만약 인덱스 칼럼을 where 절에 써야 한다면 컬럼의 타입과 일치하게 비교를 해주어야 합니다 그렇지 않으면 옵티마이저는 지금처럼 쿼리를 아예 변형시켜서 만들어놓은 인덱스를 사용 못 할 수 있습니다
숫자형 VS 문자형
지금 좌변 BIRTHDAY 문자형이고 우변 20050605는 숫자이다 오라클에서 문자형과 숫자형이 만나면 숫자형 기준으로 비교를 하게 된다 즉 문자형인 좌변이 변환에 당첨된다 사실인지 확인을 해보자
Ex6_Student
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
DROP TABLE Ex6_Student
CREATE TABLE Ex6_Student(
name varchar(9),
birthday number
)
SELECT * FROM Ex6_Student
CREATE INDEX Ex6_Student_idx ON Ex6_Student (birthday , name);
EXPLAIN PLAN FOR
SELECT *
FROM Ex6_Student
WHERE birthday > '20050605'
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------+
Plan hash value: 3762763606 |
|
---------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 7932 | 123K| 11 (0)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX6_STUDENT | 7932 | 123K| 11 (0)| 00:00:01 ||
---------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter("BIRTHDAY">20050605) |
1 - filter(“BIRTHDAY”>20050605) 문자 ‘20050605’ 에서 숫자 20050605 로 변경된것을 볼 수 있다
문자형 vs 날짜형
이와 같은 대결에서도 날짜형이 문자형을 앞서기에 문자를 날짜로 변환을 시키게 되는데 다음의 예제를 보자
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
EXPLAIN PLAN FOR
SELECT *
FROM EX5_STUDENT
WHERE BIRTHDAY >= TO_DATE('20020101' , 'yyyymmdd')
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------+
Plan hash value: 185264460 |
|
---------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 500 | 9500 | 11 (0)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX5_STUDENT | 500 | 9500 | 11 (0)| 00:00:01 ||
---------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter(INTERNAL_FUNCTION("BIRTHDAY")>=TO_DATE(' 2002-01-01 |
00:00:00', 'syyyy-mm-dd hh24:mi:ss')) |
여기서 보면 EX5_STUDENT BIRTHDAY는 문자형이다 이때 날짜를 비교하는 TO_DATE(‘20020101’ , ‘yyyymmdd’)를 넣어버리니 옵티마이저는 ` 1 - filter(INTERNAL_FUNCTION(“BIRTHDAY”)>=TO_DATE(‘ 2002-01-01 |00:00:00’, ‘syyyy-mm-dd hh24:mi:ss’))|` 이렇게 좌변 컬럼을 변경 즉 인덱스 선두 컬럼을 변경하게 된 것이다 그렇기에 인덱스를 활용하지 못하고 마찬가지로 Table Full Access 가 발생하게 됩니다
LIKE 문자열 VS 숫자
이 LIKE는 좀 이상하다 다음의 예제를 보자
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
EXPLAIN PLAN FOR
SELECT *
FROM EX5_STUDENT
WHERE BIRTHDAY LIKE 2010 || '%';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
------------------------------------------------------------------------------------+
Plan hash value: 2638959364 |
|
------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 21 | 399 | 2 (0)| 00:00:01 ||
|* 1 | INDEX RANGE SCAN| EX5_STUDENT_IDX | 21 | 399 | 2 (0)| 00:00:01 ||
------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - access("BIRTHDAY" LIKE '2010%') |
filter("BIRTHDAY" LIKE '2010%') |
이 경우에는 희한하게 Index Range Scan 을 하고 있는 모습을 보이고 있다 앞에 숫자 + 문자열 연결 + ‘%’를 하게 되는데도 말이다 이 이유는 숫자 + 문자열 연결 + ‘%’ 자체가 문자열하고 똑같다고 보기 때문이다 그렇기에 access를 보게 되면 access("BIRTHDAY" LIKE '2010%') 이렇게 합쳐져서 보기 때문에 문자열 하나로 보기 때문이다 반대로 한번 보자
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
EXPLAIN PLAN FOR
SELECT *
FROM EX6_STUDENT
WHERE BIRTHDAY LIKE '2010' || '%'
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
---------------------------------------------------------------------------------+
Plan hash value: 3762763606 |
|
---------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
---------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 500 | 8000 | 11 (0)| 00:00:01 ||
|* 1 | TABLE ACCESS FULL| EX6_STUDENT | 500 | 8000 | 11 (0)| 00:00:01 ||
---------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - filter(TO_CHAR("BIRTHDAY") LIKE '2010%') |
여기서 보면 EX6_STUDENT BIRTHDAY 컬럼은 숫자이기 때문에 이때 like 가 아닌 =에서는 문자열인 우변이 변해야 하지만 LIKE에서는 지금 본인이 TO_CHAR로 되는 것을 보고 있습니다 그래서 인덱스를 활용하지 못하는 상황을 보고 있습니다 그럼 날짜 또한 어떻게 되는지 확인을 해보겠습니다
LIKE 문자열 VS 날짜
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
EXPLAIN PLAN FOR
SELECT *
FROM EX5_STUDENT
WHERE BIRTHDAY LIKE TO_CHAR('2010' , 'YYYY') || '%'
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT |
------------------------------------------------------------------------------------+
Plan hash value: 2638959364 |
|
------------------------------------------------------------------------------------|
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time ||
------------------------------------------------------------------------------------|
| 0 | SELECT STATEMENT | | 500 | 9500 | 2 (0)| 00:00:01 ||
|* 1 | INDEX RANGE SCAN| EX5_STUDENT_IDX | 500 | 9500 | 2 (0)| 00:00:01 ||
------------------------------------------------------------------------------------|
|
Predicate Information (identified by operation id): |
--------------------------------------------------- |
|
1 - access("BIRTHDAY" LIKE TO_CHAR(2010,'YYYY')||'%') |
filter("BIRTHDAY" LIKE TO_CHAR(2010,'YYYY')||'%') |
동등 비교(=) 일 때는 문자열이 날짜로 변하는 과정을 거쳤지만 역시나 마찬가지로 ("BIRTHDAY" LIKE TO_CHAR(2010, 'YYYY')||'%') 문자로 보기 때문에 Index Range Scan 을 사용하고 있습니다 그렇기에 LIKE는 = 하고는 다르다는 것을 알고 있어야 합니다
자동형변환 문제점
자동 형변환이 개발자의 SQL 을 편리하게 해주는 경우도 있지만 지금처럼 인덱스를 사용하지 못하게 하는 경우도 있지만 실제 개발에서 문제가 생기는 경우가 발생한다 예를 들어서 다음과 같은 경우이다 동등 비교(=) 인 경우 문자가 숫자로 변환되어서 비교를 해주는데 이때 변환되는 쪽이 숫자로 사용되지 못하는 경우이다 이 경우는 숫자를 제외한 모든 문자를 말하는 것이다 이 부분은 숫자 vs 문자에 한정적인 것이 아니라 자동 형변환을 지원하는 모든 타입을 말한다
자동형변환 DECODE , CASE WHEN 의 차이점
DECODE 와 CASE WHEN 은 둘 다 프로그래밍언어에서 if 와 동일하다 먼저 DECODE를 보자 예제 스키마는 EX4_CUSTOM 을 사용할 것이다 스키마와 소스는
https://gitlab.com/kimdongy1000/sqltuningproject 에서 찾으면 된다 EX4_CUSTOM
1
2
3
4
5
6
7
8
SELECT CUSTOMAPPLYDATE
,CUSTOMNAME
,decode(CUSTOMSEX , 'M' , '남자' , '여자') CUSTOMSEX_DECODE
,CASE WHEN CUSTOMSEX = 'M' THEN '남자' ELSE '여자' END AS CUSTOMSEX_CASE
,CUSTOMAGE
FROM EX4_CUSTOM ;
쿼리 공부를 하다 보면 이런 단순한 DECODE는 식은 죽 먹기이다 CUSTOMSEX = ‘M’ 이면 ‘남자’ 아니면 ‘여자’ 이렇게 반환하는 건데 이를 프로그래밍언어로 나타내면 이렇게 보일 것이다
1
2
3
4
5
6
7
8
9
10
String CUSTOMSEX_DECODE = "";
if(CUSTOMSEX == "M"){
CUSTOMSEX_DECODE = "남자"
}else{
CUSTOMSEX_DECODE = "여자"
}
이렇게 CASE WHEN 하고 동일하다 하지만 실무를 하다 보면 DECODE보다는 CASE WHEN 을 많이 사용한다 CASE WHEN 은 DECODE 보다 입력해야 하는 문구가 많은데도 말이다 이는 사실 길이만 비교하기에는 좀 그렇다 사실 DECODE는 동등 비교만 가능하다 그렇기에 실무에서는 DECODE보다는 CASE WHEN 을 훨씬 더 많이 사용한다 그리고 그것 말고도 DECODE는 치명적인 약점이 한 가지 있다 다음을 보자
1
2
3
4
5
6
7
8
SELECT MAX(CUSTOMAPPLYDATE)
,MAX(DECODE(CUSTOMSEX , 'W' , NULL , CUSTOMAGE))
FROM EX4_CUSTOM
지금 EX4_CUSTOM 테이블에는 CUSTOMAGE 가 9인 데이터와 CUSTOMAGE 80이 넘지 않는 데이터로 이루어져 있을 때 지금 이 쿼리는 CUSTOMSEX M인 데이터 CUSTOMAGE의 MAX 값과 CUSTOMAPPLYDATE MAX 값을 찾는 것이다 결과는 어떻게 나올까?
1
2
3
4
5
6
MAX(CUSTOMAPPLYDATE)|MAX(DECODE(CUSTOMSEX,'W',NULL,CUSTOMAGE))|
--------------------+-----------------------------------------+
20241226 |9 |
결과가 이상하다 고작 최고 MAX 가 9 일 수가 없다 하지만 결과 창을 보면 현재 이 타입이 무슨 타입인지 알 수 있다 좌측에 배치되었으니 이는 문자형이다 즉 문자형에서 9보다 앞선 숫자는 엎기 때문에 이런 현상이 발생한다 이것이 DECODE의 자동 형변환 함정이다 DECODE는 세 번째 인자에 의해서 타입이 결정되는데 지금 세 번째 컬럼은 NULL 을 반환하라고 했으니 NULL 은 문자형 타입으로 취급받는다 그래서 반환받는 CUSTOMAGE 가 문자형으로 자동으로 형변환하게 된다 그렇기에 이런 결과가 나온다 만약 이것을 CASE WHEN으로 바꿔보자
1
2
3
4
5
6
7
8
SELECT MAX(CUSTOMAPPLYDATE)
,MAX(CASE WHEN CUSTOMSEX = 'W' THEN NULL ELSE CUSTOMAGE END)
FROM EX4_CUSTOM ;
MAX(CUSTOMAPPLYDATE)|MAX(CASEWHENCUSTOMSEX='W'THENNULLELSECUSTOMAGEEND)|
--------------------+--------------------------------------------------+
20241226 | 79|
이렇게 내가 원하는 데이터가 나오는 것을 볼 수 있습니다 만약 DECODE를 써야 한다면 세 번째 타입을 우리가 결정해 주는 게 좋다 예를 들어서 다음처럼 말이다
1
2
3
4
5
6
7
SELECT MAX(CUSTOMAPPLYDATE)
,MAX(DECODE(CUSTOMSEX , 'W' , NULL , CUSTOMAGE))
,MAX(CASE WHEN CUSTOMSEX = 'W' THEN NULL ELSE CUSTOMAGE END)
,MAX(DECODE(CUSTOMSEX , 'W' , TO_NUMBER(NULL) , CUSTOMAGE))
,MAX(DECODE(CUSTOMSEX , 'W' , 0 , CUSTOMAGE))
FROM EX4_CUSTOM ;
이렇게 말이다 TO_NUMBER를 쓰거나 그냥 0으로 넣으면 숫자형 타입으로 결정이 되는 것이기 때문에 위와 같은 오류를 피할 수 있다