by-nc-sa     개발자, DBA가 함께 만들어가는 구루비 지식창고!

5. 카디널리티




05 카디널리티

(1) 선택도

선택도(Selectivity)는 전체 대상 레코드 중에서 특정 조건에 의해 선택될 것으로 예상되는 레코드 비율을 말한다.
선택도를 가지고 카디널리티를 구하고, 다시 비용을 구함으로써 인덱스 사용 여부, 조인 순서와 방법 등을 결정하므로 선택도는 최적의 실행계획을 수립하는데 있어 중요한 팩터이다.

선택도 -> 카디널리티 -> 비용 -> 액세스 방식, 조인 순서,. 조인 방법 등 결정

히스토그램이 있으면 그것으로 선택도를 산정한다.
히스토그램이 없거나, 있더라도 조건절에 바인드 변수를 사용하면 옵티마이저는 데이터 분포가 균일하다고 가정한 상태에서 선택도를 구한다.
여기에서는 히스토그램이 없는 경우를 기준으로 설명한다.

히스토그램 없이 등치(=)조건에 대한 선택도를 구하는 공식은 다음과 같다.

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

히스토그램 없이 부등호, between 같은 범위 검색 조건에 대한 선택도를 구하는 공식은 다음과 같다.

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

분자, 분모에 사용된 두개의 '값 범위'는 컬럼 통계로서 수집된 high_value, low_value, num_distinct 등을 이용해 구한다.(dba_tab_col_statistics 참조)

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.5
no between 500 and 1000 (1,000-500) / (1,000-1) + 1/100 + 1/100 = 0.5

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

컬럼 히스토그램이 없다면 옵티마이저는 '조건절에서 요청한 값 범위'에 속한 값들이 '전체 값 범위'에 고르게 푼포돼 있을을 가정하고 선택도를 구한다.

두번째는 1)의 선택도 + 등치조건 선택도(1 / num_distinct)를 더한 값이다.

(2) 카디널리티

키다널리티(Cardinality)는 특정 액세스 단계를 거치고 나서 출력될 것으로 예상되는 결과 건수를 말하며, 아래와 같이 총 로우 수에 선택도를 곱해서 구한다.

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

컬럼 히스토드램이 없은 경우 등치 조건(=)에 대한 선택도
카디널리티 = 총 로우 수 X 선택도 = num_rows X (1 / num_distinct)

num_rows는 테이블 통계에서, num_distinct는 컬럼 통계에서 확인

  • 테이블 통계 : dba_tables, dba_tab_statistics
  • 컬럼 통계 : dba_tab_columns, dba_tab_col_statistics

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

SQL> CREATE TABLE t_emp AS
  2  SELECT b.no ,
  3         a.*
  4  FROM   (
  5          SELECT *
  6          FROM   scott.emp
  7          WHERE  ROWNUM <= 10
  8         ) a ,
  9         (
 10          SELECT ROWNUM no
 11          FROM   dual
 12          CONNECT BY LEVEL <= 100
 13         ) b ;

테이블이 생성되었습니다.

SQL> -- 통계정보 수집(size를 1로 설정했으므로 히스토그램은 생성하지 않음)
SQL> begin
  2      dbms_stats.gather_table_stats(user, 't_emp', method_opt => 'for all columns size 1');
  3  end;
  4  /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SELECT job ,
  2         COUNT( * )
  3  FROM   t_emp
  4  GROUP  BY job
  5* ORDER  BY job
SQL> /

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

히스토그램이 없는 경우 옵티마이저가 CLERK, SALESMAN 두 값의 카디널리티를 어떻게 추정하는지 확인

SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT  *
  4  FROM    t_emp
  5  WHERE   job = 'CLERK' ;

해석되었습니다.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092

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

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("JOB"='CLERK')

13 개의 행이 선택되었습니다.

SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT  *
  4  FROM    t_emp
  5  WHERE   job = 'SALESMAN' ;

해석되었습니다.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092

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

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("JOB"='SALESMAN')

13 개의 행이 선택되었습니다.

히스토그램이 없는 경우 동일한 선택도를 가진다는 가정하에 두 값 모두 200으로 추정하였다.

SQL> SELECT t.num_rows ,
  2         c.num_nulls ,
  3         c.num_distinct ,
  4         1/c.num_distinct AS selectivity ,
  5         num_rows/c.num_distinct AS cardinality
  6  FROM   user_tables t ,
  7         user_tab_columns c
  8  WHERE  t.table_name = 'T_EMP'
  9  AND    c.table_name = t.table_name
 10  AND    c.column_name = 'JOB' ;

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

히스토그램이 있는 경우를 확인해 보자.

SQL> begin
  2      dbms_stats.gather_table_stats(user, 't_emp', method_opt => 'for all columns size 5'); -- 도수분포 히스토그램
  3  end;
  4  /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT  *
  4  FROM    t_emp
  5  WHERE   job = 'CLERK' ;

해석되었습니다.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   100 |  4100 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_EMP |   100 |  4100 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("JOB"='CLERK')

13 개의 행이 선택되었습니다.

SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT  *
  4  FROM    t_emp
  5  WHERE   job = 'SALESMAN' ;

해석되었습니다.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |   400 | 16400 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_EMP |   400 | 16400 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("JOB"='SALESMAN')

13 개의 행이 선택되었습니다.

각각 100과 400으로 예측하였고, 실제값과 정확히 일치 한다.

히스토그램이 있고 바인드 변수를 사용한 경우를 보자

SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT  *
  4  FROM    t_emp
  5  WHERE   job = :job ;

해석되었습니다.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092

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

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("JOB"=:JOB)

13 개의 행이 선택되었습니다.

(3) NULL 값을 포함할 때

조건절 컬럼이 Null값을 포함할 때는 카디널리티를 어떻게 구할지를 테스트해 보자.
no <= 50인 500개 레코드의 job을 NULL로 치환한다.

SQL> UPDATE t_emp
  2  SET    job = NULL
  3  WHERE  no <= 50 ;

500 행이 갱신되었습니다.

SQL> COMMIT ;

커밋이 완료되었습니다.

통계정보를 다시 생성하고 job 컬럼의 통계치를 확이해 보면, 아래와 같이 num_nulls가 0에서 500으로 바뀌었지만 num_distinct를 그대로 5dlek.
오라클에서 NULL은 값의 종류로 간주하지 않음을 알 수 있고, 따라서 NULL 값이 아닌 로우 비중을 곱해 주기만 하면 된다.

SQL> begin
  2      dbms_stats.gather_table_stats(user, 't_emp', method_opt => 'for all columns size 1'); -- 히스토그램 제거
  3  end;
  4  /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> SELECT num_nulls ,
  2         num_distinct
  3  FROM   user_tab_columns
  4  WHERE  table_name = 'T_EMP'
  5  AND    column_name = 'JOB' ;

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

Null 값을 고려해 조정된 선택도 공식은 다음과 같다.

                    1             Null 값을 제외한 로우 수
선택도 = --------------------- X --------------------------
          Distinct Value 개수            총 로우 수
                1          (num_rows - num_nulls)
       = -------------- X ------------------------
          num_distinct           num_rows
          1 - (num_nulls / num_rows)
       = ----------------------------
                 num_distinct

t_emp 테이블과 job 컬럼에 대해 수집된 통계 정보를 공식에 대입해 보면 아래와 같다.(등치인 경우의 선택도와 카디널리티)

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

카디널리티 = 1000 X 0.1 = 100

옵티마이저가 구한 카디널리타와 일차함을 아래와 같이 확인할 수 있다.

SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT  *
  4  FROM    t_emp
  5  WHERE   job = :job ;

해석되었습니다.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092

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

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("JOB"=:JOB)

13 개의 행이 선택되었습니다.

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

조건줄이 두 개 이상일 경우 카디널리티를 구하는 공식은 각 컬럼의 선택도와 전체 로우수를 곱이다.

SQL> SELECT c.column_name ,
  2         t.num_rows ,
  3         c.num_nulls ,
  4         c.num_distinct ,
  5         ( 1 - c.num_nulls /t.num_rows ) /c.num_distinct AS selectivity ,
  6         num_rows/c.num_distinct AS cardinality
  7  FROM   user_tables t ,
  8         user_tab_columns c
  9  WHERE  t.table_name = 'T_EMP'
 10  AND    c.table_name = t.table_name
 11  AND    c.column_name IN ( 'DEPTNO' , 'JOB' ) ;

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

SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT  *
  4  FROM    t_emp
  5  WHERE   job = :job
  6  AND     deptno = :deptno ;

해석되었습니다.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 205437092

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |    33 |  1221 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T_EMP |    33 |  1221 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("JOB"=:JOB AND "DEPTNO"=TO_NUMBER(:DEPTNO))

13 개의 행이 선택되었습니다.

(5) 범위검색 조건일 때

범위검색 조건일 때의 선택도는 아래와 같다.

          조건절에서 요청한 값 범위
선택도 = ---------------------------
              전체 값 범위
SQL> CREATE TABLE t AS
  2  SELECT ROWNUM no1 ,
  3         CASE
  4             WHEN ROWNUM <= 1000
  5             OR   ROWNUM > 9000
  6             THEN ROWNUM
  7             ELSE 5000
  8         END no2
  9  FROM   dual
 10  CONNECT BY LEVEL <= 10000 ;

테이블이 생성되었습니다.

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

PL/SQL 처리가 정상적으로 완료되었습니다.

no1, no2 모두 low_value와 high_value는 각각 1과 10,000이다.
no1은 고르게 분포되어 있지만 no2는 그렇지 않다.
하지만 히스토 그램이 없기 때문에 아래 두 쿼리의 키디널리티는 항상 똑같이 계산된다.

SELECT *
FROM
WHERE  no1 >( 상수 ) ;

SELECT *
FROM
WHERE  no2 >( 상수 ) ;

아래와 같이 no2에 없는 값을 조회하는 경우도 1,010으로 계산되는 것을 볼 수 있다.

SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT  *
  4  FROM    t
  5  WHERE   no2 BETWEEN 3000
  6  AND     4000 ;

해석되었습니다.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

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

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("NO2"<=4000 AND "NO2">=3000)

13 개의 행이 선택되었습니다.

아래와 같이 히스토그램을 생성하고 나면 거의 근사치(Card=5)로 카디널리티를 계산해 내는 것을 확인할 수 있다.

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

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL>
SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT  *
  4  FROM    t
  5  WHERE   no2 BETWEEN 3000
  6  AND     4000 ;

해석되었습니다.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |    35 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     5 |    35 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - filter("NO2"<=4000 AND "NO2">=3000)

13 개의 행이 선택되었습니다.

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

옵티마이저가 계산한 카디널리티가 부정확할 때 힌트를 이용하여 사용자가 직접 카디널리티의 정보를 제공할 수 있다.
아래 쿼리는 dept의 카디널리티가 4이고, emp의 카디널리티가 14이므로 Build Input으로 dept 테이블을 선택된다.

SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT /*+ use_hash(d e) */
  4         *
  5  FROM   dept d ,
  6         emp e
  7  WHERE  d.deptno = e.deptno;

해석되었습니다.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   728 |     9  (12)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   728 |     9  (12)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     4   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   448 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("D"."DEPTNO"="E"."DEPTNO")

15 개의 행이 선택되었습니다.

하지만 dept의 실제 카디널리타가 16이고 우리는 emp 테이블을 Build Input으로 삼고자 한다면 아래와 같이 cardinality나 opt_estimate 힌트를 이용할 수 있다.
opt_estimate 힌트는 옵티마이저가 예상한 카디너리티에 4를 곱하는 뜻이다.

SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT /*+ use_hash(d e) cardinality(d 16) */
  4         *
  5  FROM   dept d ,
  6         emp e
  7  WHERE  d.deptno = e.deptno;

해석되었습니다.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1123238657

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("D"."DEPTNO"="E"."DEPTNO")

15 개의 행이 선택되었습니다.

SQL> EXPLAIN PLAN
  2  FOR
  3  SELECT /*+ use_hash(d e) opt_estimate(table, d, scale_rows=4) */
  4          *
  5  FROM    dept d ,
  6          emp e
  7  WHERE   d.deptno = e.deptno ;

해석되었습니다.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1123238657

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


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("D"."DEPTNO"="E"."DEPTNO")

15 개의 행이 선택되었습니다.

문서에 대하여

  • 최초작성자 : [황종필]
  • 최초작성일 : 2010년 04월 30일
  • 이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.
  • 이 문서의 내용은 (주)비투엔컬설팅에서 출간한 '오라클 성능 고도화 원리와 해법II'를 참고하였습니다.

문서정보

Enter labels to add to this page:
Please wait 
Looking for a label? Just start typing.