5. 카디널리티
2011.04.27 08:23
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 개수 총 로우수
|
(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)|
----------------------------------------------------------------
댓글 0
| 번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
|---|---|---|---|---|
| 35 | 6. 조인 제거 | AskZZang | 2011.06.01 | 5440 |
| 34 | 4. 조건절 Pushing | 오예스 | 2011.05.31 | 17481 |
| 33 |
5. 조건절 이행
 | balto | 2011.05.30 | 5465 |
| 32 | 2. 서브쿼리 Unnesting | 토시리 | 2011.05.18 | 2079 |
| 31 | 3. 뷰 Merging | 오라클잭 | 2011.05.17 | 6082 |
| 30 | 1. 쿼리 변환이란? | 운영자 | 2011.05.16 | 6258 |
| 29 | 8. 통계정보 Ⅱ | AskZZang | 2011.05.04 | 5913 |
| 28 |
7. 비용
| balto | 2011.05.02 | 4997 |
| » | 5. 카디널리티 | 오라클잭 | 2011.04.27 | 12918 |
| 26 | 6. 히스토그램 | 오예스 | 2011.04.25 | 17382 |
| 25 |
4. 통계정보 Ⅰ
| 토시리 | 2011.04.25 | 16013 |
| 24 | 3. 옵티마이저의 한계 | 휘휘 | 2011.04.19 | 6698 |
| 23 | 2. 옵티마이저 행동에 영향을 미치는 요소 | balto | 2011.04.18 | 6160 |
| 22 | 3. 옵티마이저의 한계 - P | 휘휘 | 2011.04.18 | 3699 |
| 21 |
1. 옵티마이저
| 휘휘 | 2011.04.18 | 6064 |
| 20 |
8. 고급 조인 테크닉-1
| 휘휘 | 2011.04.05 | 6460 |
| 19 | 8. 고급 조인 테크닉-2 | 오라클잭 | 2011.04.05 | 12560 |
| 18 | 7. 조인을 내포한 DML 튜닝 | 오예스 | 2011.04.04 | 9946 |
| 17 | 4. 조인 순서의 중요성 | AskZZang | 2011.03.30 | 5328 |
| 16 |
1. 인덱스 구조
| 운영자 | 2011.03.30 | 15951 |