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
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
55 | Front Page | 운영자 | 2011.02.16 | 114630 |
54 | 1. 테이블 파티셔닝 | 오라클잭 | 2011.06.21 | 28274 |
53 | 5. 병렬 처리에 관한 기타 상식 [1] | balto | 2011.06.26 | 28162 |
52 | 1. Nested Loops 조인 | 오라클잭 | 2011.03.23 | 23031 |
51 | 6. 스칼라 서브쿼리를 이용한 조인 | balto | 2011.03.27 | 18959 |
50 | 2. 인덱스 기본 원리 | balto | 2011.02.18 | 18848 |
49 | 5. Outer 조인 | 휘휘 | 2011.03.28 | 17670 |
48 | 4. 조건절 Pushing | 오예스 | 2011.05.31 | 17496 |
47 | 6. 히스토그램 | 오예스 | 2011.04.25 | 17418 |
46 | 4. 통계정보 Ⅰ | 토시리 | 2011.04.25 | 16017 |
45 | 1. 인덱스 구조 | 운영자 | 2011.03.30 | 15954 |
44 | 4. PQ_DISTRIBUTE 힌트 | 휘휘 | 2011.06.27 | 15620 |
43 | 6. IOT, 클러스터 테이블 활용 | 오예스 | 2011.02.26 | 15604 |
» | 5. 카디널리티 | 오라클잭 | 2011.04.27 | 12922 |
41 | 8. 고급 조인 테크닉-2 | 오라클잭 | 2011.04.05 | 12564 |
40 | 2. 파티션 Pruning | 휘휘 | 2011.06.19 | 11868 |
39 | 2. 병렬 Order By와 Group By | 휘휘 | 2011.06.30 | 10127 |
38 | 7. 조인을 내포한 DML 튜닝 | 오예스 | 2011.04.04 | 9955 |
37 | 10. 실체화 뷰 쿼리로 재작성 | 오라클잭 | 2011.06.08 | 9953 |
36 | 5. 테이블 Random 액세스 최소화 튜닝 | 휘휘 | 2011.02.26 | 9507 |