5. 카디널리티

조회 수 10508 추천 수 0 2011.04.27 21:38:48
오라클잭 *.176.29.44

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