메뉴 건너뛰기

bysql.net

5. 카디널리티

2011.04.26 23:23

오라클잭 조회 수:13001

05.카디널리티

 

(1)선택도(selectivity)

 

-전체 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율

 

선택도  -> 카디널리티 -> 비용 -> 액세스 방식,조인순서,조인 방법등 결정

 

  • 히스토그램이 있는 경우 - 히스토그램으로 선택도 산정
  • 히스토그램이 없는 경우,바인드 변수 사용시 - 데이터 분포가 균일하다고 가정

 

 *히스토그램 없이 등치(=)조건에 대한 선택도 공식

                                 1                                  1

선택도 =------------------------------=      -------------------------

                  Distinct Value 개수             num_distinct

 

 *히스토그램 없이 부등호,between 같은 범위검색 조건에 대한 선택도 공식

                   조건절에서 요청한 값  범위                     

선택도 =-------------------------------------

                      전체 값 범위            

 

<Example>

num_rows

num_distinct

low_value

high_value

1,000,000

100

1

1,000

 

 

 ?조건절

선택도

no > 500

(1,000-500) / (1,000-1)=0.5

no >=500

(1,000-500) / (1,000-1) +1/100=0.51

no between 500 and 1000

(1,000-500) / (1,000-1) +1/100+1/100=0.52

 1) no > 500

 

                 high_value - 비교값                  1,000 - 500

선택도=---------------------------------- =   ----------------------------- =0.5

                 high_value - low_value              1,000 -1

 

 

2) no < 500

                 비교값 - low_value                    500 - 1

선택도=---------------------------------- =   ----------------------------- =0.499

                 high_value - low_value              1,000 -1

 

 

(2)카디널리티(Cardinality)

 

 -특정 액세스 단계를 거치고 나서 출력될 것으로 예상되는 결과 건수

 

카디널리티=총 로우 수 * 선택도=num_rows / num_distinct

 

<선택도 및 카디널리티 계산식 테스트>

 

*t_emp 테이블 생성

 

SQL> select job, count(*) from t_emp group by job order by job;

 

JOB               COUNT(*)  
---------              -----------
ANALYST             100
CLERK                   100
MANAGER           300
PRESIDENT          100
SALESMAN          400

 

1)컬럼 히스토그램을 생성하지 않은 상태의 카디널리티

 

 explain plan for
 select * from t_emp where job = 'CLERK';

 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

----------------------------------------------------------------
| Id  | Operation                         | Name       | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT  |                |   200 |  8200 |     4   (0)|
|*  1 |  TABLE ACCESS FULL| T_EMP  |   200 |  8200 |     4   (0)|
----------------------------------------------------------------

 

explain plan for
select * from t_emp where job = 'SALESMAN' ;

 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

----------------------------------------------------------------
| Id  | Operation                         | Name      | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |   200 |  8200 |     4   (0)|
|*  1 |  TABLE ACCESS FULL| T_EMP |   200 |  8200 |     4   (0)|
----------------------------------------------------------------

 

*히스토그램이 없으므로 평균적인 컬럼 분포을 가정함

 

NUM_ROWS    NUM_NULLS   NUM_DISTINC SELECTIVITY CARDINALITY
-----------                -----------                 -----------             -----------              -----------
       1000                      0                              5                        0.2                       200

2)컬럼 히스토그램을 생성한 상태의 카디널리티

 

 explain plan for
 select * from t_emp where job = 'CLERK';

 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

----------------------------------------------------------------
| Id  | Operation                          | Name   | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |   100 |  4100 |     4   (0)|
|*  1 |  TABLE ACCESS FULL| T_EMP |   100 |  4100 |     4   (0)|
----------------------------------------------------------------

explain plan for
 select * from t_emp where job = 'SALESMAN' ;

 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

----------------------------------------------------------------
| Id  | Operation                          | Name   | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |   400 | 16400 |     4   (0)|
|*  1 |  TABLE ACCESS FULL| T_EMP |   400 | 16400 |     4   (0)|
----------------------------------------------------------------

 *공식에 의한 값이 아닌 히스토그램을 이용한 결과 출력

 *히스토그램을 생성햇더라도 바인드 변수를 사용하면 평균적인 분포를 가정해 카디널리티를 구한다

 

(3)NULL 값을 포함할 때

  

NUM_ROWS    NUM_NULLS   NUM_DISTINC SELECTIVITY CARDINALITY
-----------                   -----------               -----------             -----------         -----------
       1000                        500                         5                        0.2                 200

 

 

    

                                1                     null 값을 제외한 로우수

선택도=--------------------------- * ----------------------------------

                 Distinct Value 개수                     총 로우수

 

 

                                    1                     (num_rows - num_nulls)

선택도=--------------------------- * ----------------------------------

                   num_distinct                        num_rows

 

 

 

 (4)조건절이 두 개 이상일 때

 

-컬럼의 선택도와 전체 로우수를 곱해 주면 됨

 
COLUMN_NAME                      NUM_ROWS  NUM_NULLS NUM_DISTINCT SELECTIVITY
------------------------------                   ----------                   ----------             ------------            -----------
JOB                                                         1000                        500                       5                       .1
DEPTNO                                                 1000                           0                        3                 .333333333

 

*job 과 deptno 컬럼의 선택도가 0.1 과 0.33 이므로 카디널리티는

 1000*0.1*0.33=33

 

explain plan for
select * from t_emp where job = :job and deptno = :deptno;

 

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

----------------------------------------------------------------
| Id  | Operation                        | Name  | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |    33 |  1221 |     4   (0)|
|*  1 |  TABLE ACCESS FULL| T_EMP |    33 |  1221 |     4   (0)|
----------------------------------------------------------------

 

 (5)범위검색 조건일 때

                   조건절에서 요청한 값  범위                     

선택도 =-------------------------------------

                      전체 값 범위            

 

-no1 컬럼: 1~10,000 값이 고르게 분포

-no2 컬럼:8,000개의 값은 모두 5000

 

COLUMN_NAME                      NUM_ROWS  NUM_NULLS NUM_DISTINCT SELECTIVITY
------------------------------                   ---------- -               ---------                ------------          -----------
NO1                                                     10000                         0                       10000                 .0001
NO2                                                     10000                          0                          2001          .00049975

 

*히스토그램이 없을 때 

 -두 컬럼 값을 다르게 해서 실행시켜도 두 쿼리의 커디널리티는 똑 같이 계산됨

select * from t where no2 between 3000 and 4000;

Execution Plan
----------------------------------------------------------

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1010 |  7070 |     7  (15)|
|*  1 |  TABLE ACCESS FULL| T    |  1010 |  7070 |     7  (15)|
---------------------------------------------------------------

 

*히스토그램이 있을경우

-거의 근사치로 카디널리티를 계산 함

 

(6)cardinality 힌트를 이용한 실행계획과 제어

 

-사용자가 직접 카디널리티 정보를 제공 

 

<Example>

-dept 카디널리티:4

-emp 카디널리티:14

-카디널리티의 정보를 제공함으로써 해시조인의 build input table을 조정한다

-leading 이나 swap_join_inputs 힌트와 결과는 같다


----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   840 |     7  (15)|
|*  1 |  HASH JOIN         |      |    14 |   840 |     7  (15)|
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    84 |     3   (0)|
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   546 |     3   (0)|
----------------------------------------------------------------

 

select /*+ use_hash(d e) cardinality(d 16) */ * from dept d,emp e 
 where d.deptno=e.deptno;

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    56 |  3360 |     7  (15)|
|*  1 |  HASH JOIN         |      |    56 |  3360 |     7  (15)|
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   546 |     3   (0)|
|   3 |   TABLE ACCESS FULL| DEPT |    16 |   336 |     3   (0)|
----------------------------------------------------------------

 

select /*+ use_hash(d e) opt_estimate(table,d,scale_rows=4) */ * from dept d,emp e
where d.deptno=e.deptno;  2 

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    56 |  3360 |     7  (15)|
|*  1 |  HASH JOIN         |      |    56 |  3360 |     7  (15)|
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   546 |     3   (0)|
|   3 |   TABLE ACCESS FULL| DEPT |    16 |   336 |     3   (0)|
----------------------------------------------------------------

 

 

이 게시물을
목록
목록
번호 제목 글쓴이 날짜 조회 수
35 1. 옵티마이저 file 휘휘 2011.04.17 6189
34 3. 옵티마이저의 한계 - P 휘휘 2011.04.17 3735
33 2. 옵티마이저 행동에 영향을 미치는 요소 balto 2011.04.17 6197
32 3. 옵티마이저의 한계 휘휘 2011.04.19 6735
31 4. 통계정보 Ⅰ file 토시리 2011.04.24 16114
30 6. 히스토그램 오예스 2011.04.25 17672
» 5. 카디널리티 오라클잭 2011.04.26 13001
28 7. 비용 file balto 2011.05.01 5120
27 8. 통계정보 Ⅱ AskZZang 2011.05.04 5946
26 1. 쿼리 변환이란? 운영자 2011.05.16 6296
25 3. 뷰 Merging 오라클잭 2011.05.17 6114
24 2. 서브쿼리 Unnesting 토시리 2011.05.17 2116
23 5. 조건절 이행 file balto 2011.05.29 5500
22 4. 조건절 Pushing 오예스 2011.05.31 17747
21 6. 조인 제거 AskZZang 2011.06.01 5484
20 7. OR-Expansion AskZZang 2011.06.01 8468
19 12. 기타 쿼리 변환 휘휘 2011.06.05 3185
18 10. 실체화 뷰 쿼리로 재작성 오라클잭 2011.06.07 9996
17 11. 집합 연산을 조인으로 변환 오라클잭 2011.06.07 4993
16 2. 소트를 발생시키는 오퍼레이션 file balto 2011.06.11 4890
쓰기
태그