CHAPTER 6. 선택도 이슈

여러 가지 데이터 타입 142/이태경

앞선 범위기반 조건의 선택도
(required range) / (column high value - column low value) + N / num_distinct

일반적인 두가지 데이터 타입(char,vchar)에 대한 선택도에 대해 알아본다.

날짜 값 142 /이태경

날짜값을 사용한 조건절 (between '2002-12-30' and '2003-01-05')

Selectivity
= (5th Jan 2003 - 30th Dec 2002 ) / ( 31st Dec 2004 - 1st Jan 2000 ) +2/(number of different dates)
= 6 / 1826 + 2/1827

선택도의 오차

• 오라클은 날짜에 대한 계산은 정확하다. 그러나, 이산데이터도 연속적인 데이터로 계산해서 약간의 오차가 발생한다.

*주의)distinct 값이 적다면 오차가 크게 날 수 있다.

문자열 값 143 /이태경

문자열 컬럼의 전체 범위를 측정한다는 것

• 입력한 문자열에 대한 히스토그램을 포함하여 통계정보를 생성한다.(user_tab_histograms View, user_tab_columns View)

select
column_name,
endpoint_number,
endpoint_value
from
user_tab_histograms
where
table_name = 'T1'
order by
column_name,
endpoint_Number
;
 Col  End no   End Value
----- ---------- ------------------------------------------------------------
C10   1         339,475,752,638,459,000,000,000,000,000,000,000 -- 'Aardvark '
        2         339,779,832,781,209,000,000,000,000,000,000,000 -- 'Apple '
        3         344,891,393,972,447,000,000,000,000,000,000,000 -- 'Blueberry '
        4         469,769,561,047,943,000,000,000,000,000,000,000 -- 'Zymurgy '

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

문자열 앞쪽 6~7개를 제외한 나머지 글자는 옵티마이저가 사용하는 숫자 형태의 값에 아무런 영향을 주지 못한다.
위 예제에서 Apple을 제외한(글자수 6개 이상) 나머지 글자에 대한 공백이 들어간 글자와 들어가지 않은 글자의 endpoint_number는 동일.

NLS 확대에 따른 문제점

• --

부적절한 데이터 타입 145 /이태경

select *
from t1
where d1 between to_date('30-Dec-2002','dd-mon-yyyy')
and to_date('05-Jan-2003','dd-mon-yyyy')
;
select *
from t1
where n1 between 20021230 and 20030105
;
select *
from t1
where v1 between '20021230' and '20030105'
;

Column Type	Cardinality(8.1.7.4)	Cardinality(9.2.0.4/10.1.0.2)	Cardinality(9.2.0.6 /10.1.0.4)
Date	9	8	8
Numeric	397	396	396
Character	457	457	396

컬럼 타입에 따른 Cardinality 차이

• 옵티마이저는 조건절이 숫자와 문자형 일 경우 조건의 값이 날짜를 의미하는 줄 모른다.
• 따라서 옵티마이저는 날짜의 독특한 특성을 가진 데이터 집합에 다음과 같은 표준 계산식을 사용한다.
• varchar2 컬럼과 조건 값을 마치 숫자인 것처럼 취급
  

Selectivity = (required range) / (high value - low value) + 2/num_distinct
= (20030105 - 20021230) / (20041231 - 20000101) + 2/1827
= 8875 / 41130 + 2/1827
= 0.215779 + 0.001095
= 0.216874
Cardinality = 396.228(=1827*0.216874)

해결책

• 히스토그램을 생성하여 오차를 줄 일 수 있다.

function-based index(FBI)를 사용한 조정

create index t1_v1 on t1(to_date(v1,'yyyymmdd'));
begin
dbms_stats.gather_index_stats(
ownname => user,
indname =>'T1_V1',
estimate_percent => null
);
end;
/
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname =>'T1',
cascade => false,
estimate_percent => null,
method_opt => 'for all hidden columns size 1'
);
end;
/

ch_06_selectivity_02\nchar_types.sql
rem    Notes:
rem    Repeats char_types.sql - but using a two-byte national character set.
rem    Note how the end_point_values are remarkably different.
(아래와 같이 실험했으나 달라진 점 발견 못 함. 설정해서 발견하신 분 말씀해주세요.)

1. sql>update sys.props$ set value$ = 'WE8MSWIN1252' where name = 'NLS_CHARACTERSET';
2. sql>update sys.props$ set value$ = 'KO16KSC5601' where name = 'NLS_CHARACTERSET';
   

rem    The optimizer uses a fixed number of BYTES from the
rem    string, not a fixed number of characters.

SQL> begin
  2     dbms_stats.gather_table_stats(
  3             ownname                 => user,
  4             tabname                 => 't1',
  5             cascade                 => true,
  6             estimate_percent        => null,
  7             method_opt              =>'for all columns size 1'
  8     );
  9  end;
 10  /

SQL> select     *
  2  from       t1
  3  where      d1 between to_date('30-Dec-2002','dd-mon-yyyy')
  4        and     to_date('05-Jan-2003','dd-mon-yyyy')
  5  ;

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

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0  | SELECT STATEMENT |     |     8 |   184 |     3 |
|*  1 |  TABLE ACCESS FULL| T1 |    8 |   184 |     3 |
----------------------------------------------------------

SQL> begin
  2     dbms_stats.gather_table_stats(
  3             ownname                 => user,
  4             tabname                 => 't1',
  5             cascade                 => true,
  6             estimate_percent        => null,
  7             method_opt              =>'for all columns size 120'
  8     );
  9  end;
 10  /

SQL> select     *
  2  from       t1
  3  where      d1 between to_date('30-Dec-2002','dd-mon-yyyy')
  4        and     to_date('05-Jan-2003','dd-mon-yyyy')
  5  ;

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

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     6 |   138 |     3 |
|*  1|  TABLE ACCESS FULL | T1  |     6 |   138 |     3 |
----------------------------------------------------------

‘0’으로 문자 앞쪽을 채울 때의 문제점(leading zeros) 150 /남송휘

• 인조키생성시 앞쪽에 0을 채워서 고정할경우 옵티마이저는 마치 숫자 컬럼을 다룰때 처럼 선택도를 계산할수도 있음

char_seq / 9.2.0.4

Base data with simple statistics

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=659 Card=17 Bytes=374)
   1    0   TABLE ACCESS (FULL) OF 'T1' (Cost=659 Card=17 Bytes=374)

Histogram with 75 buckets created on ID column

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=659 Card=8924 Bytes=196328)
   1    0   TABLE ACCESS (FULL) OF 'T1' (Cost=659 Card=8924 Bytes=196328)


Virtual column created on to_number(ID) by
including the expression in an index. NOT
just as the single column in the index.
No histogram

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=659 Card=10002 Bytes=230046)
   1    0   TABLE ACCESS (FULL) OF 'T1' (Cost=659 Card=10002 Bytes=230046)



※히스토그램을 생성하면 근사값에 가까운값이 나오며 fbi를 사용하면 오류를 줄일수 있음

char_seq / 10.2.0.1

COLUMN_NAME          NUM_DISTINCT    DENSITY
-------------------- ------------ ----------
GRP_ID                        200       .005
ID                        2000000   .0000005


Base data with simple statistics


Execution Plan
----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10002 |   214K|   659 |
|*  1 |  TABLE ACCESS FULL| T1   | 10002 |   214K|   659 |
----------------------------------------------------------

Histogram with 75 buckets created on ID column


Execution Plan
----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10000 |   214K|   659 |
|*  1 |  TABLE ACCESS FULL| T1   | 10000 |   214K|   659 |
----------------------------------------------------------


Virtual column created on to_number(ID) by
including the expression in an index. NOT
just as the single column in the index.
No histogram


Execution Plan
----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10002 |   224K|   659 |
|*  1 |  TABLE ACCESS FULL| T1   | 10002 |   224K|   659 |
----------------------------------------------------------

치명적인 문제를 유발하는 디폴트 값 152 /남송휘

예)  날짜값을 null로 두지않고 미래 (4000-12-31) 로 한경우

create table t1
as
/*
with generator as (
    select    --+ materialize
        rownum     id
    from    all_objects
    where    rownum <= 2000
)
*/
select
    /*+ ordered use_nl(v2) */
    decode(
        mod(rownum - 1,1000),
            0,to_date('31-Dec-4000'),
              to_date('01-Jan-2000') + trunc((rownum - 1)/100)
    )    date_closed
from
    generator    v1,
    generator    v2
where
    rownum <= 1827 * 100
;

9.2.0.4.0 / defaults.sql

Column stats with no histogram

COLUMN_NAME          NUM_DISTINCT    DENSITY
-------------------- ------------ ----------
DATE_CLOSED                  1828 .000547046

1 row selected.


Cardinality with default dates, no histogram

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=33 Card=291 Bytes=2328)
   1    0   TABLE ACCESS (FULL) OF 'T1' (Cost=33 Card=291 Bytes=2328)

Column stats with histogram (very little change in density)

COLUMN_NAME          NUM_DISTINCT    DENSITY
-------------------- ------------ ----------
DATE_CLOSED                  1828 .000547258

1 row selected.

Histogram values - note the very large range of bucket 11

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              0        2451545
              1        2451711
              2        2451877
              3        2452043
              4        2452210
              5        2452376
              6        2452542
              7        2452708
              8        2452875
              9        2453041
             10        2453207
             11        3182395

12 rows selected.


Default dates, with histogram

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=33 Card=36320 Bytes=290560)
   1    0   TABLE ACCESS (FULL) OF 'T1' (Cost=33 Card=36320 Bytes=290560)



Change the default date to null

183 rows updated.


Real nulls, no histogram

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=33 Card=36583 Bytes=256081)
   1    0   TABLE ACCESS (FULL) OF 'T1' (Cost=33 Card=36583 Bytes=256081)


Approximate calculation of cardinality with default date

SELECTIVITY CARDINALITY
----------- -----------
 .001592142  290.884365

1 row selected.

Approximate calculation of cardinality with proper nulls
(Num_rows - Num_nulls) * selectivity

SELECTIVITY CARDINALITY
----------- -----------
 .200437517  36583.2542

1 row selected.


※히스토 그램을 생성하거나 null을 사용하여야만 적절한 선택도를 가짐

이산(discrete) 값의 위험성 154 /남송휘

사용자들이 특별한 용도로 사용하는 독특한 값에 의해서도 동일한 효과가 나타남

예) 1부터 12값과 함께 조정을 위한 99의 값이 저장되는 주기컬럼을 가진 회계시스템

create table t1
as
/*
with generator as (
    select    --+ materialize
        rownum     id
    from    all_objects
    where    rownum <= 1000
)
*/
select
    /*+ ordered use_nl(v2) */
    mod(rownum-1,13)    period_01,
    mod(rownum-1,13)    period_02
from
    generator    v1,
    generator    v2
where
    rownum <= 13000
;


update t1 set
    period_01 = 99,
    period_02 = 13
where
    period_01 = 0;
;

• 표준 카디널리티 공식
  num_rows * ( ( our_high-our_low) / (table_high - table_low) +2 / num_distinct) )
  이므로 잘못된 계산 결과가 나타남
  

period_01 between 1 and 3         cardinality = 1,265 ..
period_01 between 4 and 6         cardinality = 1,663 ..
period_01 between 7 and 9         cardinality = 1,929 ..
period_01 between 10 and 12         cardinality = 2,265 ..

표 6-3 특이한 패턴을 가진 범위기반 조건

• 표분석결과

• 해당 컬럼에 between 또는 greater than/less than 형태의 조건절을 사용하면 부정확해짐
• 히스토그램을 생성하면 문제가 사라지고 정확한 답을 얻을수 있음

10g 업데이트 159 /남송휘

10.1.0.4 버전의 변화

• 1000에서 1까지 선형적으로 감소

놀라운 sysdate 159 /이창헌

 

create table t1 as
  select
         rownum     id,
         trunc(sysdate - 2) + (rownum-1)/1440 minutes,
         lpad(rownum,10)    small_vc,
         rpad('x',100)    padding
from
         all_objects
where
        rownum <= 6480

 

4.5일에 해당하는 분을 가진 테이블

where minutes between sysdate and sysdate + 1
where minutes between trunc(sysdate) and trunc(sysdate + 1)
where minutes between sysdate-1 and sysdate
where minutes between trunc(sysdate-1) and trunc(sysdate)
where minutes between sysdate - 1 and sysdate + 1

 

8,9I
옵티마이저는 파싱 타인에(sysdate(그리고 trunc(sysdate)를 비롯하여 몇 가지 함수를 적용한 경우)를
이미 아는 상수로 취급함

sysdate + N은 모르는 값인 바인드 변수로 취급함  이는 고정된 선택도 5%를 적용함

해결 방범 = 리터럴(literal)조건을 사용하면 상당한 이득을 볼 수 있음..

10g
적절한 카디널리티가 나타남.

 

함수의 특성 162 /이창헌

 

조건절 예시  처리방법 
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를 생성

상관관계에 있는 컬럼 164 /박우창

? 2 컬럼 사이에 통계적인 상관관계가 있을 때 - (dependent.sql)

테이블구조 t1

 

열이름	값 list	분포 ( 전체 10,000개)	선택도
n1	0..24	각각 약 400개씩	1/25
ind_pad	‘x'	모두 같은값, rpad	1
n2	0..19	각각 약 500개씩	1/20
small_vc	1..10,000	1개씩	1/10000
padding	‘x'	모두 같은값, rpad	1

- (실험) : 상관관계만들기, n2 열을 n1과 같게, 인덱스 t1_i1 생성, 테이블 387블럭

update t1 set n2=n1;

create index t1_i1 on t1(n1, ind_pad, n2)

 

인덱스 통계정보 비교	after(n2=n1)	before
blevel	2	2
leaf_blocks	1107	1111
distinct_key	25	500
clustering_factor	6153	9745
avg_leaf_blocks_per_day	44	2
avg_data_blocks_per_day	246	19

- (질의문)

select /*+ index(t1) */ small_vc

from t1

whereind_pad = rpad('x',40) and n1 = 2 and n2 = 2

- (실험결과)

실제로는 400개의 결과가 나와야하나 속성의 연관성을 계산 못하기 때문에 실행계획에서는 16개가 나오는 것으로 계산한다.(update 문을 인지 못함)

- (다른예)- where ind_pad = rpad('x',40) and n1 = 1 and n2 = 3

   실제로는 결과가 없음에도 결과를 16개로 계산한다.

- 비용에 대한 잘못된 계산은(과대나 과소 평가) 조인 연산에서 드라이빙 테이블을 잘못 계산할 수 있다.

- optimizer_dynamic_sampling 힌트를 사용하면 32개 블록을 미리 샘플링함.

- Wolfgang 공식을 사용하지 않고 데이터베이스에 저장된 수치를 사용하면 더 정확할 수 있다.
    (iser_indexes 테이블에 저장된  avg_leaf_blocks_per_key, avg_data_blocks_per_key 사용)
    cost 추정치 = blevel +avg_leaf_blocks_per_key +avg_data_blocks_per_key

 

동적 샘플링 167 /박우창

- 앞절의 예를 dynamic sampling을 사용하면 card가 정답 400에 근접한다.

- (실험) : dynamic sampling 힌트 사용전과 후 비용 비교

without dynamic sampling	with dynamic sampling
/*+ index(t1) */	/*+ index(t1) dynamic_sampling(t1 1) */
cost=14, card=16	cost=288, card=393

- 10053 trace 파일 테스트 - 트레이스 파일에 32개 블록을 조사

  9i => dynamic selectivity estimation = 0.04420550으로 계산했지만 카디널리티에만 사용하고 비용계산에는 사용하지 않았다.

  10g => dynamic selectivity estimation = 0.03928171으로 계산, 카디널리티와 비용계산에 모두 사용하였다.

 

 

옵티마이저 프로파일 170 /박우창

? 옵티마이저 프로파일

- 10g의 튜닝권고자(tuning advisor)는 offline으로 튜닝을 해준다.

- 튜닝권고자는 실제데이터를 기반으로 통계학습, 질의, 부분적인 조인 테스트를 하여 결과를 알려준다.

- 튜닝권고자가 제시하는 프로파일(profile) 내용은 wri$ 파일을 조회하면 알수있다.

- 튜닝권고자가 제시한 내장힌트(stored hint)는 SQL 문에 포함하여 실행시킬 수 있다.

- (예제) OPT_ESTIMATE 정보

select

  /*+

    OPT_ESTIMATE(TABLE, T2, SCALE_ROWS=200),

        - 테이블 액세스시 통계정보보다 200배 더 반환된다는 정보

    OPT_ESTIMATE(JOIN, (T2, T1), SCALE_ROWS=15)

        - 조인시 통계정보보다 15배 더 반환된다는 정보

  */

    count(t1.v1) ct_v1, count(t2.v1) ct_v2

from      t1, t2

where    t2.n2 = 15 and t2.n1 = 15and t1.n2 = t2.n2 + 0 and t1.n1 = t2.n1

- 아직은 opt_estimate 힌트를 공식적으로 권장하지는 않겠다.

 

이행적 폐쇄 172 /위충환

옵티마이저가 논리적 추론에 의해 더 좋은 방법이라 판단하여 실행계획을 세우는 점에서 문제점 발생 가능

   ☞ 이행적 폐쇄로 알려진 매커니즘 사용으로 몇개의 조건 생성

[해결방안]

    - 사용자가 기술하지 않은 보이지 않는 조건 확인

    - 전체 실행계획을 더 상세히 표시해주는 적절한 툴 사용

• 단일 테이블의 이행적 폐쇄

trans_close_01.sql

(책에는 상수가 100으로 나왔으나 스크립트의 상수는 5로 되어있어 스크립트로 테스트 실행)

테이블 t1 : n1, n2 컬럼 각 1~10까지 값을 100개씩 소유로 총 1000개 로우

(테스트 환경 WinXP, 오라클 10gR2)

         ↓

☞ 몇몇 경우에는 유용하지만, 뜻밖의 부작용 발생 가능

• 2개의 테이블 조인 시 이행적 폐쇄

trans_close_02.sql

(테스트 환경 WinXP, 오라클 10gR2)

[테스트 결과]

_OPTIMIZER_TRANSITIVITY_RETAIN  = true 설정 시

☞ 조인 조건 유지

   - 교재에 나오는 BUFFER SORT 연산 없음

   - COST 변화

• 적당한 방법으로 폐쇄 알고리즘 제어

1. t2.n1 = t1.n1 조건을 중복 기술

[테스트 결과]

_OPTIMIZER_TRANSITIVITY_RETAIN  = true 설정 시

☞ 조인 조건 유지로 알고리즘 제어 의미가 없음

_OPTIMIZER_TRANSITIVITY_RETAIN  = false 설정 시

☞ 실행 계획의 변경으로 카디널리티( Rows)가 크게 낮아짐

2.  명시적 t1.n1 = 5 조건 추가

[테스트 결과]

_OPTIMIZER_TRANSITIVITY_RETAIN  = true / false 설정 모두 같은 결과

   ☞ 실행 계획의 변경으로 카디널리티(Rows)가 크게 낮아짐

교재에서 표현한 적당한 방법으로 폐쇄 알고리즘 제어 방법은 오라클 9,2.0.6 버전으로 작성되어

10gR2에서는 _OPTIMIZER_TRANSITIVITY_RETAIN  = false 설정으로 테스트 해야 비슷한 결과가 나타남

※ 참고 _OPTIMIZER_TRANSITIVITY_RETAIN 히든 파라미터의 기본값은 TRUE (10gR2)

    (10gR2 이전 버전 query_rewrite_enabled 파라미터의 값으로 설정)

3. 오래된 규칙기반(rule-based) 트릭 이용 (바람직하지 않음)

[테스트 결과]

_OPTIMIZER_TRANSITIVITY_RETAIN  = true / false 설정 모두 같은 결과

※ 뜻밖의 실행계획과 카디널리티를 만들어내는 조인을 만날 시

    옵티마이저를 속여서 현명하게 처리하도록 SQL을 수정할 수도 있음

    (주의! 업그레이드에 대비하여 문서화 필요)

옵티마이저의 추정

1. 'n1 operator 상수' and 'n2 = n1'   =>   'n2 operator 상수'

   ( n1 < 10    and   n2 = n1        =>        n2  < 10 )

2. 'col1 > col2'  and 'col2 > {constant K}'   =>   'col1 > {constrantK}'

   ( 'n1   >   10'   and 'n1   <   0'        =>        '0   >   10')

2번째 같은 경우 판단으로 전체 실행계획을 무의미하게 만듦.

이행적 폐쇄의 일관성 결여

실제 제공한 조건절 이행적	폐쇄 후에 오라클이 사용한 조건절
n1 = 5 and n1 = n2  n1 < 5 and n1 = n2  n1 between 4 and 6 and n1 = n2	n1 = 5 and n2 = 5  n1 < 5 and n2 < 5 and n1 = n2  n1 >= 4 and n1 <= 6 and n2 > 4 and n2 <=6 and n1 = n2

 

제약이 만든 조건 176 /위충환

• constraint_02.sql

--------------------------------------------------------------------
| Id | Operation          | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------
| 0  | SELECT STATEMENT   |      |  1   |   444 |   33 |
| 1  | SORT AGGREGATE     |      |  1   |   444 |      |
|* 2 | HASH JOIN          |      | 15   |  6660 |   33 |
|* 3 | TABLE ACCESS FULL  | T1   | 15   |  3330 |   16 |
|* 4 | TABLE ACCESS FULL  | T2   | 15   |  3330 |   16 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."N1"="T2"."N1")
3 - filter("T1"."N2"=15 AND "T1"."N1">=0 AND "T1"."N1"<=199)
4 - filter("T2"."N2"=15)

• 3번 라인의 필터 조건이 범위기반 체크조건 포함.

• t2 테이블의 CHECK 제약에 의한 것으로 t1 테이블에 대해서 만들어짐.

  ∵ t1.n1 =  t2.n1 조건에 의해 t1에서 조인에 성공한 로우는 t2의 CHECK 제약을 따라야한다고 옵티마이저는 처리

• t2 CHECK 제약 내용이 t1의 조건 안으로 이동하여 더정확한 조인 카디널리티 계산 가능

  (t1 테이블에 똑같은 제약 존재 시 위와같은 조건절 생성이 일어나지 않음)

• constraint_03.sql

Execution path 10.1.0.4
---------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |   222 |    12 |
|   1 |  SORT AGGREGATE              |      |     1 |   222 |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1   |    30 |  6660 |    12 |
|*  3 |    INDEX RANGE SCAN          | T_I1 |   301 |       |     2 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."N2">=180)
   3 - access("N1">=180)


Execution path 9.2.0.6

---------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |   222 |    12 |
|   1 |  SORT AGGREGATE              |      |     1 |   222 |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1   |    30 |  6660 |    12 |
|*  3 |    INDEX RANGE SCAN          | T_I1 |   301 |       |     2 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."N2">=180)
   3 - access("T1"."N1">=180)

• CHECK (n2 >= n1) 형태의 테이블 레벨 제약
• 쿼리의 실행계획 :  테이블 스캔  =>  인덱스 액세스

요약 180 /이태경

 

테스트 스크립트 181 /이태경