메뉴 건너뛰기

bysql.net

5. 카디널리티

2011.04.27 08:23

오라클잭 조회 수:12919

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 7. 인덱스 스캔 효율 file 휘휘 2011.03.06 8449
34 7. OR-Expansion AskZZang 2011.06.01 8328
33 1. 소트 수행 원리 file balto 2011.06.12 8013
32 2. 소트 머지 조인 오예스 2011.03.21 7912
31 4. 테이블 Random 액세스 부하 file 휘휘 2011.02.26 6942
30 1장. 인덱스 원리와 활용 휘휘 2011.02.21 6796
29 3. 옵티마이저의 한계 휘휘 2011.04.19 6698
28 3. 해시 조인 휘휘 2011.03.21 6487
27 8. 고급 조인 테크닉-1 file 휘휘 2011.04.05 6461
26 1. 쿼리 변환이란? 운영자 2011.05.16 6258
25 2. 옵티마이저 행동에 영향을 미치는 요소 balto 2011.04.18 6161
24 3. 뷰 Merging 오라클잭 2011.05.17 6082
23 1. 옵티마이저 file 휘휘 2011.04.18 6064
22 7. Sort Area 크기 조정 오예스 2011.06.16 5973
21 8. 통계정보 Ⅱ AskZZang 2011.05.04 5913
20 8. 인덱스 설계 AskZZang 2011.03.09 5726
19 5. 조건절 이행 file balto 2011.05.30 5465
18 6. 조인 제거 AskZZang 2011.06.01 5441
17 4. 조인 순서의 중요성 AskZZang 2011.03.30 5328
16 9. 비트맵 인덱스 휘휘 2011.03.07 5014
쓰기
태그