(1)선택도

- 전체 대상 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율
- 선택도 → 카디널리티 → 비용 → 액세스 방식, 조인 순서, 조인 방법 등 결정

 

* 히스토그램이 있는 경우

: 히스토그램으로 선택도 산정, 단일 컬럼에 대해서는 정확도도 비교적 높음

 

* 히스토그램이 없거나, 있더라도 조건절에서 바인드 변수를 사용할 경우

: 옵티마이저는 데이터 분포가 균일하다고 가정한 상태에서 선택도 구함 

 

- 선택도 구하는 공식

 히스토그램이 없는 경우??

등치(=) 조건에 대한 선택도 공식

 

 

선택도=	조건절에서 요청한 값 범위 ------------------
	전체 값 범위

 

 

 

 

 부등호, between 같은 범위검색 조건에 대한 선택도 공식

 

 

선택도=	1 ------------------	=	1 ------------------
	Distinct Value 개수		num_distinct

 

 

 

 

 

 - 분자, 분모에 사용된 두 개의 '값 범위'는 컬럼 통계로서 수집된 high_value, low_value, num_disticnt 을 이용해 구한다. 

 - (예) 부등호 조건에 대한 선택도  

컬럼 통계

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        선택도 = (hight value - 비교값) / (high_value - low_value)
no >= 500	(1,000-500) / (1,000-1) + 1/100 = 0.51        첫번째 조건 선택도 + '='조건 선택도(1/num_distinct)
no between 500 and 1000	(1,000-500) / (1,000-1) + 1/100 + 1/100 = 0.52        between이므로 '=' 조건이 두 개여서 '=' 조건 선택도를 두 번 더함

 

 -  히스토그램이 없을 경우 옵티마이저는 '조건절에서 요청한 값 범위'에 속한 값들이 '전체 값 범위'에 고르게

분포돼 있음을 가정하고 선택도를 구한다.

 

 

 (2)카디널리티

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

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

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

 

create table t_emp
as
select b.no, a.*
from  (select * from scott.emp where rownum <= 10) a
     ,(select rownum no from dual connect by level <= 100) b ;

begin
  dbms_stats.gather_table_stats( user, 't_emp'
        , method_opt => 'for all columns size 1');
end;
/

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

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

 ▶ 히스토그램 없는 경우

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

 

SQL> @?/rdbms/admin/utlxpls

 

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

 

 

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

 

SQL> @?/rdbms/admin/utlxpls

 

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

 

select t.num_rows, c.num_nulls, c.num_distinct
     , 1/c.num_distinct selectivity
     , num_rows/c.num_distinct cardinality
from   user_tables t, user_tab_columns c
where  t.table_name = 'T_EMP'
and    c.table_name = t.table_name
and    c.column_name = 'JOB' ;

 

  NUM_ROWS  NUM_NULLS NUM_DISTINCT SELECTIVITY CARDINALITY
---------- ---------- ------------ ----------- -----------
      1000          0            5          .2         200

 

 

테이블 통계 num_rows와 컬럼 통계 num_distinct 를 조회해서 공식대로 계산해 보면, 옵티마이저가 예상한 것과 같은 값 확인.

  ▶ 히스토그램 있는 경우

 begin
  dbms_stats.gather_table_stats( user, 't_emp'
        , method_opt => 'for all columns size 5');
end;
/

 

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

 

SQL> @?/rdbms/admin/utlxpls

 

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

 

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

 

SQL> @?/rdbms/admin/utlxpls

 

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

 ▶ 히스토그램 있지만 바인드 변수를 사용한 경우

SQL> explain plan for
  2  select * from t_emp where job = :job ;

 

SQL> @?/rdbms/admin/utlxpls

 

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

 

 (3)NULL 값을 포함할 때

- no <= 50 인 500개 레코드의 job을 null로 치환

- '=' 조건에 대한 선택도를 구할 때, null인 레코드는 결과집합에서 제외되는 특성을 반영하려면 기존 공식에

null값이 아닌 로우 비중을 곱하고 분모인 Distinct Value  개수에서 null값을 제외 시킨다.

 

 SQL> begin
  2    dbms_stats.gather_table_stats( user, 't_emp'
  3          , method_opt => 'for all columns size 1');
  4  end;
  5  /

 

SQL> select num_nulls, num_distinct from user_tab_columns
  2  where table_name = 'T_EMP' and column_name = 'JOB';

 

 NUM_NULLS NUM_DISTINCT
---------- ------------
       500            5

 

 

선택도 =  (1 / Distinct Value 개수)   X   (null 값을 제외한 로우 수 / 총 로우 수) 

              = (1 / num_distinct)   X   ((num_rows - num_nulls) / num_rows)

              = (1 - (num_nulls / num_rows))   /  num_distinct

선택도  =  (1 - (500/1000))   /   5  = 0.1

카디널리티 = 1000 X 0.1 = 100

 

 

 SQL> explain plan for
  2  select * from t_emp where job = :job ;

 

SQL> @?/rdbms/admin/utlxpls

 

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

 

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

- 각 컬럼의 선택도와 전체 로우 수를 곱해 준다.

 

SQL> select c.column_name, t.num_rows, c.num_nulls, c.num_distinct
  2        ,(1-c.num_nulls/t.num_rows)/c.num_distinct selectivity
  3  from   user_tables t, user_tab_columns c
  4  where  t.table_name = 'T_EMP'
  5  and    c.table_name = t.table_name
  6  and    c.column_name in ('DEPTNO', 'JOB') ;

 

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

 

- 각 컬럼의 선택도가 0.1과 0.33 이므로 옵티마이저가 구한 카디널리티는 33(= 1000 X 0.1 X 0.33)일 것으로 예상

 

↓ 확인

 

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

SQL> @?/rdbms/admin/utlxpls

 

----------------------------------------------------------------
| 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)범위검색 조건일 때

- 조건절에서 요청한 범윙 속한 값들이 전체 값 범위에 고르게 분포돼 있음을 가정

▶ 히스토그램 없는 경우

create table t
as
select rownum no1
     , case when rownum <= 1000 or rownum > 9000 then rownum else 5000 end no2
from   dual
connect by level <= 10000 ;

 

begin
  dbms_stats.gather_table_stats(user, 't', method_opt => 'for all columns size 1');
end;
/

 

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////

>   no1, no2 모두 low_value와 high_value는 각각 1 과 10,000

>   no1은 값이 고르게 분포, no2는 그렇지 않다.

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////

   select * from t where no1 > 상수;

   select * from t where no2 > 상수;

 

- 두 쿼리의 카디널리티가 항상 똑같이 계산 됨

- no2에는 전혀 없는 부분을 조회할 때도 아래와 같이 1010건으로 계산

 

SQL> explain plan for
  2  select * from t where no2 between 3000 and 4000;

 

SQL> @?/rdbms/admin/utlxpls

 

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

▶ 히스토그램 있는 경우 (근사치(Card=5)로 카디널리티를 계산해 냄)

begin
  dbms_stats.gather_table_stats(user, 't'
    , method_opt => 'for all columns size 254');
end;
/

 

SQL> explain plan for
  2  select * from t where no2 between 3000 and 4000;

 

SQL> @?/rdbms/admin/utlxpls

 

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |    70 |     6   (0)|
|*  1 |  TABLE ACCESS FULL| T    |    10 |    70 |     6   (0)|
---------------------------------------------------------------

  

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

- 옵티마이저가 계산한 카디널리티가 부정확할 때 힌트를 이용해 직접 카디널리티 정보 제공

 

 SQL> explain plan for
  2  select /*+ use_hash(d e) */ * from dept d, emp e
  3  where d.deptno = e.deptno;

SQL> @?/rdbms/admin/utlxpls

 

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

 

- dept의 카디널리티 4,   emp의 카디널리티 14

- 해시 조인을 위한 Build Input으로 dept가 선택됨

 ▶ emp테이블을 Build Input으로 하려면

- leading, swap_join_inputs 힌트가 있지만 cardinality 힌트 이용

 

SQL> explain plan for
  2  select /*+ use_hash(d e) cardinality(d 16) */ * from dept d,
  3  where d.deptno = e.deptno;

SQL> @?/rdbms/admin/utlxpls

 

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

▶ opt_estimate 이용 (10g 부터 이용)  : 위 실행계획과 같은 결과 확인

- 옵티마이저가 예상한 카디널리티(=4)에 4를 곱하라는 뜻

 

 SQL> explain plan for
  2  select /*+ use_hash(d e) opt_estimate(table, d, scale_rows=4) */ *
  3  where d.deptno = e.deptno;

SQL> @?/rdbms/admin/utlxpls

 

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

  
 

오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
작성자: 이주영 (suspace)
최초작성일: 2011년 4월 26일
본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^