꿈꾸는 개발자, DBA 커뮤니티 구루비

여러가지 데이터타입
'0'으로 문자 앞쪽을 채울 때의 문제점(Leading zeros)
치명적인 문제를 유발하는 디폴트 값
이산(discrete)값의 위험성
놀라운 sysdate
함수의 특성
상관관계에 있는 컬럼
이행적 폐쇄
제약이 만든 조건
요약

이 장에서는 표준 선택도가 부적절하게 계산되는 이유와, 이 문제를 피해가는 방법을 논의하고, 옵티마이저가 조건절을 잘못 처리하는 몇가지 기능을 논의한다.

여러가지 데이터타입

선택도 기본공식

선택도 = (required range) / (column high value - column low value) + N / num_distinct

예) 1부터 1000까지 서로 다른 1000개의 정수값을 가진 컬럼에 대해서 colX > 10 and colX <= 20 조건절을 이용하여 머리로 계산하면,
1000개중에 10개 즉, 10/1000 공식으로 계산하면,
(20 - 10) / (1000 - 1)\+ 1/1000 = 10/99 + 1/1000 = 0.011

날짜값

예) 2000-01-01 ~ 2004-12-31까지의 날짜만 가지는 컬럼에서 date_col between 2002-12-30 and 2003-01-05 조건절을 이용하여
머리로 계산하면, 1827일중에 7일 즉, 7/1827
공식으로 계산하면, (2003년 01월 05일 - 2002년 12월 30일) / (2004년 12월31일 - 2000년 1월 1일) + 2/1827 = 6/1826 \+2/1827

=> 데이터에 distinct값이 많아질수록 계산결과의 오차는 작아진다. 그니까, distinct값이 적다면 주의하라고\!\!*

문자열값

문자열을 숫자형태로 표현하고 있는 user_tab_histogram 뷰를 이용하여, 문자열의 전체 범위 측정에 대해서 의미를 살펴보자면,
문자열 앞쪽의 6-7개을 제외한 나머지 글자는 옵티마이저가 사용하는 숫자 형태의 값에 아무런 영향을 주지 못하므로,
다중 바이트 문자 세트를 선택하거나, 문자열이 길어지면, 문자열의 범위기반 쿼리에 정확도가 떨어져서 예측한 결과를 가져오지 못 할 수 있다.


Col   LOW                  LOW_VALUE                HIGH                 HIGH_VALUE
----- -------------------- ------------------------ -------------------- ------------------------
V10   Aardvark             416172647661726B         Zymurgy              5A796D75726779

C10   Aardvark             416172647661726B2020     Zymurgy              5A796D75726779202020


2 개의 행이 선택되었습니다.


PL/SQL 처리가 정상적으로 완료되었습니다.


Col   End no                                        End Value End act val
----- ------ ------------------------------------------------ --------------------------------------
C10        1  339,475,752,638,459,000,000,000,000,000,000,000
           2  339,779,832,781,209,000,000,000,000,000,000,000
           3  344,891,393,972,447,000,000,000,000,000,000,000
           4  469,769,561,047,943,000,000,000,000,000,000,000

V10        1  339,475,752,638,459,000,000,000,000,000,000,000
           2  339,779,832,781,057,000,000,000,000,000,000,000
           3  344,891,393,972,447,000,000,000,000,000,000,000
           4  469,769,561,047,943,000,000,000,000,000,000,000


8 개의 행이 선택되었습니다.

ASCII   256 to the power 15 - N
-----   =======================
65       337,499,295,804,763,795,854,482,261,399,306,240
97         1,967,393,731,554,212,031,122,883,374,743,552
114            9,032,010,526,626,134,485,664,010,338,304
100               30,948,500,982,134,506,872,478,105,600
118                  142,653,246,714,526,242,615,328,768
97                       458,069,548,838,355,585,728,512
114                        2,102,928,824,402,888,884,224
107                            7,710,162,562,058,289,152
32                                 9,007,199,254,740,992
32                                    35,184,372,088,832
0                                                      0
0                                                      0
0                                                      0
0                                                      0
0                                                      0

        Summed value
        ============
         339,475,752,638,459,043,065,991,628,037,554,176

PL/SQL 처리가 정상적으로 완료되었습니다.

부적절한 데이터 타입

예) 2000-01-01 ~ 2004-12-31까지의 날짜만 가지는 컬럼에서 date_col between 2002-12-30 and 2003-01-05 조건절을 이용한다.
단, 날짜를 저장할때, date, number, character 형식으로 각각 저장하고 카디널리티를 비교

date 값으로 저장한 경우, 비교적 정확한 카디널리티가 나오나, number나 character 형식은 부정확한 결과가 나온다.
(단, 버전이 올라가면서 많이 달라지겠지..)

개선안) 히스토그램을 생성하여 잘못된 카디널리티를 보완하거나, 해당 컬럼에 function-based index를 생성하여 조정을 하는 방법을 사용할수도 있다.

'0'으로 문자 앞쪽을 채울 때의 문제점(Leading zeros)

적절한 PK대신, 숫자의 앞쪽에 '0'을 붙여 문자열로 저장하여 데이터를 사용하는 경우, 범위기반 조건을 사용하는 경우 알수 없는 성능 문제가 나타난다.
개선안) 히스토그램을 생성하여 잘못된 카디널리티를 보완하거나, 해당 컬럼에 function-based index를 생성하여 조정을 하는 방법을 사용할수도 있다.
버전이 올라가면서, 위와 같은 경우는 옵티마이저가 알아서숫자컬럼을 다룰때 처럼 선택도를 계산하나, '0'대신'a'와 같은 문자열로 채워서 사용한다면,위와 같은 문제가 일어날 것이다.

치명적인 문제를 유발하는 디폴트 값

날짜 입력시 NULL로 남겨두는 대신에4000-12-31등으로 세팅한다면, 선택도를 외곡시킬수 있다.

예) 2001-01-01 ~ 2004-12-31치의 데이터에서 2003년도 데이터를 조회하고자 한다면,
머리로 계산하면, 5년치 데이터 중에 1년치 데이터 이므로 1/5(20%)
공식으로 계산하면, 4000년 12월 31일도 날짜의 범주로 생각하므로 (2003년 12월 31일 - 2003년 1월 1일) / (4000년 12월 31일 - 2000년 1월 1일) + 2/1828 = 0.00159 (약 0.15%)

히스토그램을 통해 데이터 분포가 특이하다는 사실을 알려주어, 보정한다.

이산(discrete)값의 위험성

NULL을 대체할 Default값의 용도 이외에도, 범주에 벗어나는 특수한 경우를 저장하기 위해 범위에서 멀리 벗어난 값을 사용하는데 이도 같은 문제점을 발생시킨다.

예를 들어, 주기를 저장하는 컬럼이 있다. 이는 일반적으로 1~12의 값을 저장한다.
하지만 특수한 값을 표시하고자, 99라는 값을 추가적으로 사용했을 경우에 비효율적인 실행계획을 유발 할 수 있다.

이것도, 히스토그램을 통해 데이터 분포가 특이하다는 사실을 알려주어, 보정한다.

놀라운 sysdate

sysdate를 이용한 조건절에서, 이상한 카디널리티가 잘못되어서 나온다.
(10g에서는 이런 문제점이 알려지고 해결되었다.)

함수의 특성

SQL에 가공한 컬럼을 조건절로 사용한다면, 옵티마이저는 바인드변수에 적용하는 고정된 백분율을 이용하여 계산한다.

표 6-6

조건절 예시	처리방법
function(colx) = 'SMITH'	고정된 1% 선택도
not function(colx) = 'SMITH'	고정된 5% 선택도
function(colx) > 'SMITH'	고정된 5% 선택도
not function(colx) > 'SMITH'	고정된 5% 선택도
function(colx) >= 'SMITH' and function(colx) < 'SMITI'	유도된 0.25% 선택도(5%*5%)
function(colx) between 'SMITHA' and 'SMITHZ'	유도된 0.25% 선택도(5%*5%)
not function(colx) between 'SMITHA' and 'SMITHZ'	유도된 9.75% 선택도 (5%+5%-(5%*5%))
function(colx) like 'SMITH%'	고정된 5% 선택도
not fucntion(colx) like 'SMITH%'	고정된 5% 선택도
function(colx) in ('SMITH', 'JONES')	유도된 1.99%(1% + 1% - (1%*1%))

만약,function-based index를 생성하면, 가상컬럼에 인덱스를 생성하는 것과 같고, 통계정보를 수집한다면, 동시에 가상 컬럼에 대해서도 통계정보가 수집된다.
그러므로 이러한 경우는 위의 표와 무관하며 일반 선택도 계산식을 적용할 것이다.

상관관계에 있는 컬럼

같은 테이블 내에서 상관관계에 있는 컬럼이 where절에서 함께 사용되면 항상 문제를 일으킨다.
이는 경우에 따라 optimizer_dynamic)sampling 파라미터 또는 dynamic_sampling 힌트를 사용하여 해결할 수있는데, 이렇게 하면 주요 테이블에 대해 실행시점에 32개 블록을 샘플링함으로써 where 조건에 일치하는 로우의 비율을 할 수 있다.
옵티마이저 프로파일 또는 옵티마이저 프로파일과 같은 역할을 하는 힌트를 사용하여 그러한 효과를 낼 수 있다.

이행적 폐쇄

옵티마이저는 이행적 폐쇄(transitive closure)로 알려진 매커니즘을 사용하여, 사용자가 명시하지 않은 몇개의 조건을 생성한다.
이는 유용하게, 때로는 뜻밖의 부장용을 나타내기도 한다.

제약이 만든 조건

(CASE 1) 컬럼에 함수를 적용하여, 인덱스를 사용하지 못하는 경우.

방법

테이블 생성시 특정 컬럼에 제약조건을 추가하여 테이블 생성
특정 컬럼에 일반 B-Tree 인덱스 생성
쿼리를 수행할때, 인덱스를 생성한 컬럼에 대해서 UPPER()를 사용하여 조회
실행계획 확인


Predicate: upper(v1) = upper('SMITH')


Execution Plan
----------------------------------------------------------
Plan hash value: 1429545322

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    34 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    34 |     2 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |     1 |       |     1 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("V1"='SMITH')
       filter(UPPER("V1")='SMITH')

Note
-----
   - cpu costing is off (consider enabling it)


Predicate: upper(v1) = 'SMITH'


Execution Plan
----------------------------------------------------------
Plan hash value: 1429545322

---------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    34 |     2 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    34 |     2 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |     1 |       |     1 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("V1"='SMITH')
       filter(UPPER("V1")='SMITH')

Note
-----
   - cpu costing is off (consider enabling it)


Predicate: upper(v1) = upper(:bind_var)
Uses index in 9.2, but not in 10.1.0.4


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  3400 |     7 |
|*  1 |  TABLE ACCESS FULL| T1   |   100 |  3400 |     7 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(UPPER("V1")=UPPER(:BIND_VAR))

Note
-----
   - cpu costing is off (consider enabling it)


Predicate: upper(v1) = :bind_var
Uses index in 9.2, but not in 10.1.0.4


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  3400 |     7 |
|*  1 |  TABLE ACCESS FULL| T1   |   100 |  3400 |     7 |
----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(UPPER("V1")=:BIND_VAR)

Note
-----
   - cpu costing is off (consider enabling it)

결과

옵티마이저는 테이블 생성시 추가한 제약조건을 쿼리에 적용한수 새로운 조건을 만들어낸다.
그리하여, 원래는 컬럼에 함수를 적용하여 사용하지 못할뻔 한 인덱스를 사용할 수 있게 되는것이다.

예외

단, Literal만 적용가능하며, 바인드 변수를 이용하면 무용지물이다.
버전마다 계속 진화하므로 확인요망.

(CASE 2) NULL이 아닌 컬럼 값에 대해서 NULL값으로 리턴가능한 내장함수를 포함하는 CHECK 제약에 의해 잘못된 결과가 나타는 경우

쿼리문에 IS NOT NULL을 명시적으로 포함해서 조회한다.


alter table t2 add constraint t2_ck_n1 check (n1 between 0 and 199);

explain plan for
select
	count(t1.v1)	ct_v1,
	count(t2.v1)	ct_v2
from 	t1, t2
where	t2.n2 = 15
and	t1.n2 = t2.n2 
and	t1.n1 = t2.n1 
;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 906334482

------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |   444 |    25 |
|   1 |  SORT AGGREGATE     |      |     1 |   444 |       |
|*  2 |   HASH JOIN         |      |    15 |  6660 |    25 |
|*  3 |    TABLE ACCESS FULL| T1   |    15 |  3330 |    12 |
|*  4 |    TABLE ACCESS FULL| T2   |    15 |  3330 |    12 |
------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."N2"="T2"."N2" AND "T1"."N1"="T2"."N1")
   3 - filter("T1"."N2"=15 AND "T1"."N1">=0 AND "T1"."N1"<=199)
   4 - filter("T2"."N2"=15)

결과

더 정확한 조인 카디널리티를 계산할 수 있다.

요약

옵티마이저가 카디널리티를 계산할 때 놀랄만한 동작을 하는데는 다 이유가 있다. 왜 그런 문제가 발생하는지 안다면, 문제를 현명히 해결할 수있다.

옵티마이저는 문자열에 대한 range scan을 잘 처리하지 못한다.
잘못된 데이터 타입에 데이터를 저장하면 문제가 발생한다.
null값 대신 특별한 값을 사용하여 저장한 컬럼에 범위기반 조건을 사용하면 부적절한 실행계획이 만들어진다.
function-based index를 정의하지 않은 컬럼에 함수를 적용하면 예상치 못한 선택도가 나타날수 있다.
이행적 폐쇄(transitive closure)에 의해 조건이 만들어지거나 사라질수 있다.

대부분, 히스토그램을 통하여 언급한 문제점들에 대해서 획기적인 개선을 확인할 수 있다. (히스토그램은 Chapter 7에서 설명...)