메뉴 건너뛰기

bysql.net

6. 선택도 이슈

2009.12.28 10:17

ZoneWorker2 조회 수:7520

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)
  1. 컬럼에서 32바이트 추출
  2. 15바이트 추출.(15바이트 이하일 경우 뒤에 0을 붙인다.
  3. 16진수를 10진수로 변환한 후 , 앞쪽의 15자리만 남기고 반올림.

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 TypeCardinality(8.1.7.4)Cardinality(9.2.0.4/10.1.0.2)Cardinality(9.2.0.6 /10.1.0.4)
Date988
Numeric397396396
Character457457396

컬럼 타입에 따른 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)
해결책
  • 히스토그램을 생성하여 오차를 줄 일 수 있다.

버킷 개수를 120으로 지정하여 히스토그램을 생성한 후의 결과
Column TypeOriginal8i9i/10g
Numeric397/3981615
Character457/3971615

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 특이한 패턴을 가진 범위기반 조건


Low
High
Between low and high
조건의 card(변경분)
Low
High
'> low and < high'
조건의 card(변경분)
-2
0
1,000



-1
1
1,000
-2
2
 1,000
0
2
1,133 ( +133)
-1
3
 1,000
1
3
1,265 (+132)
0
4
 1,000
2
4
1,398 (+133)
1
5
1
3
5
1,531 (+133)
2
6
867 (-133 from 1000)
4
6
1,663 (+132)
3
7
735 (-132)
5
7
1,796 (+133)
4
8
602 (-133)
6
8
1,929 (+133)
5
9
531 (-71)
7
9
2,061 (+132)
6
10
531
8
10
2,194 (+133)



9
11
2,265 (+71)









89
91
2,265



90
92
2,194 (-71)



91
93
2,061 (-133)
90
94
 531
92
94
1,929 (-132)
91
95
531(+71)
93
95
1,796 (-133)
92
96
602(+133)
94
96
1,663 (-133)
93
97
735(+133)
95
97
1,531 (-133)
94
98
867(+132)
96
98
1,398 (-133)
95
99
1
97
99
1,265 (-133)
96

1,000 (+133 from 867)
98
100
1,133 (-132)
97

1,000
99
101
1,000 (-133)



100
102
1,000





  • 표분석결과
- 컬럼의 최소값과 최대값 사이의 많은 부분에서 표준 공식과 일치한다
- 조건절이 컬럼 값 범위의 바깥으로 나가면, 카디널리티가 num_rows/num_distinct로 떨어짐
- <> 에서 1,5 와 95,99는 버그에 의한것
- 공식에 의한 표준값 2,265와 경계 값 1,000 사이에서 선형적으로 증감
- 단계별 카디널리티 증감분 133은 num_rows/(column high - column low_ = 13,000/98 과 동일

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



10g 업데이트 159 /남송휘


10.1.0.4 버전의 변화

Low
High
 Between Low and High
Low
High
>Low and < High
96
98
1,398
95
99
1
97
99
1,265
96
100
1,000
98
100
1,133
97
101
1,000
99
101
1,000
98
102
1,000
100
102
990
99
103
1,000
101
103
980
100
104
990
102
104
969
101
105
980

  • 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)

explain plan for
select   small_vc
from     t1
where    n1 = 5
;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |   700 |     3 |
|*  1 |  TABLE ACCESS FULL| T1   |   100 |   700 |     3 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1"=5)


explain plan for
select   small_vc
from     t1
where    n1 = 5
     and n2 = n1
;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |   100 |     3 |
|*  1 |  TABLE ACCESS FULL| T1   |    10 |   100 |     3 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("N1"=5 AND "N2"=5)


     n2 = 5   -  옵티마이저는 상수 조건 생성

        

     n1 = 5   -  상수가 없는 조건 제거, 최종 where 절

and  n2 = 5


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


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

trans_close_02.sql

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

explain plan for
select count(*)
from   t1, t2
where  t1.n1 = 5
   and t2.n1 = t1.n1
;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT (
set "_OPTIMIZER_TRANSITIVITY_RETAIN" = false)

--------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost  |
--------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |     6 |   168 |
|   1 |  SORT AGGREGATE       |      |     1 |     6 |       |
|   2 |   MERGE JOIN CARTESIAN|      | 10000 | 60000 |   168 |
|*  3 |    TABLE ACCESS FULL  | T1   |   100 |   300 |     3 |
|   4 |    BUFFER SORT        |      |   100 |   300 |   165 |
|*  5 |     TABLE ACCESS FULL | T2   |   100 |   300 |     2 |
--------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T1"."N1"=5)
   5 - filter("T2"."N1"=5)


PLAN_TABLE_OUTPUT(set "_OPTIMIZER_TRANSITIVITY_RETAIN" = true)
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |     8 |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |       |
|*  2 |   HASH JOIN         |      | 10000 | 60000 |     8 |
|*  3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3 |
|*  4 |    TABLE ACCESS FULL| T2   |   100 |   300 |     3 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."N1"="T1"."N1")
   3 - filter("T1"."N1"=5)
   4 - filter("T2"."N1"=5)


[테스트 결과]

_OPTIMIZER_TRANSITIVITY_RETAIN  = true 설정 시

☞ 조인 조건 유지

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

   - COST 변화


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

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

explain plan for
select count(*)
from   t1, t2
where  t1.n1 = 5
   and t2.n1 = t1.n1
   and t2.n1 = t1.n1
;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
(set "_OPTIMIZER_TRANSITIVITY_RETAIN" = false)
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |     8 |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |       |
|*  2 |   HASH JOIN         |      |  10006000 |     8 |
|*  3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3 |
|*  4 |    TABLE ACCESS FULL| T2   |   100 |   300 |     3 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."N1"="T1"."N1")
   3 - filter("T1"."N1"=5)
   4 - filter("T2"."N1"=5)


PLAN_TABLE_OUTPUT(set "_OPTIMIZER_TRANSITIVITY_RETAIN" = true)
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |     8 |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |       |
|*  2 |   HASH JOIN         |      | 10000 | 60000 |     8 |
|*  3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3 |
|*  4 |    TABLE ACCESS FULL| T2   |   100 |   300 |     3 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."N1"="T1"."N1")
   3 - filter("T1"."N1"=5)
   4 - filter("T2"."N1"=5)


[테스트 결과]

_OPTIMIZER_TRANSITIVITY_RETAIN  = true 설정 시

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

_OPTIMIZER_TRANSITIVITY_RETAIN  = false 설정 시

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


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

explain plan for
select count(*)
from   t1, t2
where  t1.n1 = 5
   and t2.n1 = t1.n1
   and t2.n1 = 5
;
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
(set "_OPTIMIZER_TRANSITIVITY_RETAIN" = false)
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |     8 |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |       |
|*  2 |   HASH JOIN         |      |  10006000 |     8 |
|*  3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3 |
|*  4 |    TABLE ACCESS FULL| T2   |   100 |   300 |     3 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."N1"="T1"."N1")
   3 - filter("T1"."N1"=5)
   4 - filter("T2"."N1"=5)


PLAN_TABLE_OUTPUT (set "_OPTIMIZER_TRANSITIVITY_RETAIN" = true)
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |     8 |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |       |
|*  2 |   HASH JOIN         |      |  10006000 |     8 |
|*  3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3 |
|*  4 |    TABLE ACCESS FULL| T2   |   100 |   300 |     3 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."N1"="T1"."N1")
   3 - filter("T1"."N1"=5)
   4 - filter("T2"."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) 트릭 이용 (바람직하지 않음)

explain plan for
select count(*)
from   t1, t2
where  t1.n1 = 5

   and t2.n1 = t1.n1 + 0
;

select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
(set "_OPTIMIZER_TRANSITIVITY_RETAIN" = false)
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |     8 |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |       |
|*  2 |   HASH JOIN         |      | 10000 | 60000 |     8 |
|*  3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3 |
|   4 |    TABLE ACCESS FULL| T2   |  1000 3000 |     3 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."N1"="T1"."N1"+0)
   3 - filter("T1"."N1"=5)


PLAN_TABLE_OUTPUT (set "_OPTIMIZER_TRANSITIVITY_RETAIN" = true)
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |     6 |     8 |
|   1 |  SORT AGGREGATE     |      |     1 |     6 |       |
|*  2 |   HASH JOIN         |      | 10000 | 60000 |     8 |
|*  3 |    TABLE ACCESS FULL| T1   |   100 |   300 |     3 |
|   4 |    TABLE ACCESS FULL| T2   |  10003000 |     3 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."N1"="T1"."N1"+0)
   3 - filter("T1"."N1"=5)


[테스트 결과]

_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

create table t1
as
select
    trunc((rownum-1)/15)    n1,
    trunc((rownum-1)/15)    n2,
    rpad(rownum,215)    v1
from all_objects
where rownum <= 3000
;
create table t2
as
select
    mod(rownum,200)        n1,
    mod(rownum,200)        n2,
    rpad(rownum,215)    v1
from all_objects
where rownum <= 3000
;
create index t_i1 on t1(n1);
create index t_i2 on t2(n1);

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


-- dbms_stats를 사용하여 통계정보 수집
begin
    dbms_stats.gather_table_stats(
        user,
        't1',
        cascade => true,
        estimate_percent => null,
        method_opt => 'for all columns size 1'
    );
end;
/
begin
    dbms_stats.gather_table_stats(
        user,
        't2',
        cascade => true,
        estimate_percent => null,
        method_opt => 'for all columns size 1'
    );
end;
/

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
;
select * from table(dbms_xplan.display);

--------------------------------------------------------------------
| 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

create table t1
as
select
    trunc((rownum-1)/15)    n1,
    trunc((rownum-1)/15)    n2,
    rpad(rownum,215)    v1
from all_objects
where rownum <= 3000
;
create index t_i1 on t1(n1);

alter table t1 modify n1 not null;
alter table t1 modify n2 not null;

alter table t1 add constraint t1_ck check (n1 >= n2);

-- dbms_stats를 사용하여 통계정보 수집
begin
    dbms_stats.gather_table_stats(
        user,
        't1',
        cascade => true,
        estimate_percent => null,
        method_opt => 'for all columns size 1'
    );
end;
/

explain plan for

select
    count(t1.v1)    ct_v1
from     t1
where    t1.n2 >= 180
;
select * from table(dbms_xplan.display);


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 /이태경


 

번호 제목 글쓴이 날짜 조회 수

등록된 글이 없습니다.