이 장에서는 표준 선택도가 부적절하게 계산되는 이유와, 이 문제를 피해가는 방법을 논의하고, 옵티마이저가 조건절을 잘못 처리하는 몇가지 기능을 논의한다.
선택도 기본공식
선택도 = (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를 생성하여 조정을 하는 방법을 사용할수도 있다.
개선안) 히스토그램을 생성하여 잘못된 카디널리티를 보완하거나, 해당 컬럼에 function-based index를 생성하여 조정을 하는 방법을 사용할수도 있다.
=>버전이 올라가면서, 위와 같은 경우는 옵티마이저가 알아서 숫자컬럼을 다룰때 처럼 선택도를 계산하나, '0'대신 'a'와 같은 문자열로 채워서 사용한다면, 위와 같은 문제가 일어날 것이다.
예) 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%)
=> 히스토그램을 통해 데이터 분포가 특이하다는 사실을 알려주어, 보정한다.
예를 들어, 주기를 저장하는 컬럼이 있다. 이는 일반적으로 1~12의 값을 저장한다.
하지만 특수한 값을 표시하고자, 99라는 값을 추가적으로 사용했을 경우에 비효율적인 실행계획을 유발 할 수 있다.
=> 이것도, 히스토그램을 통해 데이터 분포가 특이하다는 사실을 알려주어, 보정한다.
(10g에서는 이런 문제점이 알려지고 해결되었다.)
\ 표 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를 생성하면, 가상컬럼에 인덱스를 생성하는 것과 같고, 통계정보를 수집한다면, 동시에 가상 컬럼에 대해서도 통계정보가 수집된다.
그러므로 이러한 경우는 위의 표와 무관하며 일반 선택도 계산식을 적용할 것이다.
이는 경우에 따라 optimizer_dynamic)sampling 파라미터 또는 dynamic_sampling 힌트를 사용하여 해결할 수있는데, 이렇게 하면 주요 테이블에 대해 실행시점에 32개 블록을 샘플링함으로써 where 조건에 일치하는 로우의 비율을 할 수 있다.
옵티마이저 프로파일 또는 옵티마이저 프로파일과 같은 역할을 하는 힌트를 사용하여 그러한 효과를 낼 수 있다.
[방법]
\- 테이블 생성시 특정 컬럼에 제약조건을 추가하여 테이블 생성
\- 특정 컬럼에 일반 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만 적용가능하며, 바인드 변수를 이용하면 무용지물이다.
버전마다 계속 진화하므로 확인요망.
\- 쿼리문에 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에서 설명...)