메뉴 건너뛰기

bysql.net

10. 조인 카디널리티

2009.12.28 10:19

휘휘 조회 수:8757

CHAPTER 10. 조인 카디널리티




조인 카디널리티 기본 326 / 남송휘





조인 카디널리티를 구하는 공식
: 메타링크 문서 68992.1




조인 선택도 

 ((num_rows(t1) - num_nulls(t1.cs)) / num_rows(t1)) *
 ((num_rows(t2) -  num_nulls(g2.c2)) / num_rows(t2)) /
    greater (num_distinct(t1.c1), num_distinct(t2.c2))

조인 카디널리티 =
    조인 선택도 *
    필터된 카디널리티 (t1) * 필터된 카니널리티 (t2)





join_card_01.sql

TABLE_NAME             NUM_ROWS
-------------------- ----------
T1                        10000
T2                        10000


TABLE_NAME           COLUMN_NAME           NUM_NULLS NUM_DISTINCT
-------------------- -------------------- ---------- ------------
T1                   FILTER                        0           25
T1                   JOIN1                         0           30
T1                   V1                            0        10000
T1                   PADDING                       0            1
T2                   FILTER                        0           50
T2                   JOIN1                         0           40
T2                   V1                            0        10000
T2                   PADDING                       0            1

select t1.v1, t2.v1
from
t1,
t2
where
t1.filter = 1
and t2.join1 = t1.join1
and t2.filter = 1

조인선택도 =
    (10,000 - 0) / 10,000) *
    (10,000 - 0) / 10,000) /

    greater(30,40) = 1/40
 
조인카디널리티 = 1/40 * (10000/25) * (10000/50)
= 1/40 * (400 * 200)
=2000


-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2000 | 68000 |    38 |
|*  1 |  HASH JOIN         |      |  2000 | 68000 |    38 |
|*  2 |   TABLE ACCESS FULL| T2   |   200 |  3400 |    18 |
|*  3 |   TABLE ACCESS FULL| T1   |   400 |  6800 |    18 |
-----------------------------------------------------------







NULL 을 포함할때

TABLE_NAME           COLUMN_NAME           NUM_NULLS NUM_DISTINCT
-------------------- -------------------- ---------- ------------
T1                   FILTER                        0           25
T1                   JOIN1                       500           30
T1                   V1                            0        10000
T1                   PADDING                       0            1
T2                   FILTER                        0           50
T2                   JOIN1                       333           40
T2                   V1                            0        10000
T2                   PADDING                       0            1


select t1.v1, t2.v1
from
t1,
t2
where
t2.join1 = t1.join1
and t1.filter = 1
and t2.filter = 1



조인선택도
 (10000-500) / 10000 *
 (10000-333) / 10000 /
    greater(30,40) =
    0.022959125

조인 카디널리티 = 400 * 200 * 0.022959125 = 1,836.73

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1837 | 62458 |    38 |
|*  1 |  HASH JOIN         |      |  1837 | 62458 |    38 |
|*  2 |   TABLE ACCESS FULL| T2   |   200 |  3400 |    18 |
|*  3 |   TABLE ACCESS FULL| T1   |   400 |  6800 |    18 |
-----------------------------------------------------------

다른 레코드도 null 값으로 치환

TABLE_NAME           COLUMN_NAME           NUM_NULLS NUM_DISTINCT
-------------------- -------------------- ---------- ------------
T1                   FILTER                      200           25
T1                   JOIN1                       500           30
T1                   V1                            0        10000
T1                   PADDING                       0            1
T2                   FILTER                      100           50
T2                   JOIN1                       333           40
T2                   V1                            0        10000
T2                   PADDING                       0            1


조인선택도 = 0.22959125
카디널리티 = 기본선택도 * (num_rows-num_nulls)
t1 의 카디널리티 = 1/25 * (10000 - 200) = 392
t2 의 카디널리티 = 1/50 * (10000 - 100) = 198

조인카디널리티 = 392*198 * 0.022959125 = 1781.995




-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1782 | 60588 |    38 |
|*  1 |  HASH JOIN         |      |  1782 | 60588 |    38 |
|*  2 |   TABLE ACCESS FULL| T2   |   198 |  3366 |    18 |
|*  3 |   TABLE ACCESS FULL| T1   |   392 |  6664 |    18 |
-----------------------------------------------------------






기본공식의 한계점과 의문점
. 조인 컬림이 두개 이상이라면 어떻게 계산해야 할까?
. 조인절에 범위 검색 조건(range scan)을 포함한다면 어떻게 해야 할까?
. 3개 이상의 테이블을 조인한다면 어떻게 해야 할까?
. 공식을 보면, 양쪽 조인 컬럼 값을 일부가 일부만 겹치는, 즉 일부 값들만 조인에 성공하는 경우에 대해서는 고려하고 있지 않은 것 같은데, 그 이유는 무엇일까?
. 히스토그램 정보가 있다면 조인 카디널리티 계산에 어떤영향을 미칠까?







한 쪽에만 필터조건을 적용한 경우 331 /남송휘




select t1.v1, t2.v1
from
t1,
t2
where
t2.join1 = t1.join1
and t2.filter = 1

TABLE_NAME             NUM_ROWS
-------------------- ----------
T1                         1000
T2                         1000



TABLE_NAME           COLUMN_NAME           NUM_NULLS NUM_DISTINCT
-------------------- -------------------- ---------- ------------
T1                   FILTER                        0          100
T1                   JOIN1                         0           30
T1                   V1                            0         1000
T1                   PADDING                       0            1
T2                   FILTER                        0          100
T2                   JOIN1                         0           40
T2                   V1                            0         1000
T2                   PADDING                       0            1

* 교제에서 10000개가 아니라 1000개

기본공식 대입
조인선택도 - 
(1000/1000) * (1000/1000) /greater(30,40) =
1/40 =0.025

조인카디널리티 =
    조인선택도 *
    필터된 카디널리티 (t1) * 필터된 카디널리티 (t2)
    0.025*10*1000 = 250


t2 필터 적용
-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   333 | 10323 |     7 |
|*  1 |  HASH JOIN         |      |   333 | 10323 |     7 |
|*  2 |   TABLE ACCESS FULL| T2   |    10 |   170 |     3 |
|   3 |   TABLE ACCESS FULL| T1   |  1000 | 14000 |     3 |
-----------------------------------------------------------

t1 필터 적용
-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   250 |  7750 |     7 |
|*  1 |  HASH JOIN         |      |   250 |  7750 |     7 |
|*  2 |   TABLE ACCESS FULL| T1   |    10 |   170 |     3 |
|   3 |   TABLE ACCESS FULL| T2   |  1000 | 14000 |     3 |
-----------------------------------------------------------


t1의 경우 계산 결과와 동일
이유
-> 두개의 num_distinct중 큰값이 아니라 필터 값이 없는 쪽 테이블의 num_distinct 값을 사용

t1= 1/40
t2= 1/30







실환경에서의 조인 카디널리티 334 /남송휘





join_card_04.sql 

TABLE_NAME             NUM_ROWS
-------------------- ----------
T1                        10000
T2                        10000

2 rows selected.

Elapsed: 00:00:00.01
SQL>
TABLE_NAME           COLUMN_NAME          NUM_DISTINCT  NUM_NULLS
-------------------- -------------------- ------------ ----------
T1                   JOIN1                          30          0
T1                   JOIN2                          50          0
T1                   V1                          10000          0
T1                   PADDING                         1          0
T2                   JOIN1                          40          0
T2                   JOIN2                          40          0
T2                   V1                          10000          0
T2                   PADDING                         1          0


select t1.v1, t2.v1
from
t1,
t2
where
t2.join1 = t1.join1
and t2.join2 = t1.join2




선택도 구하는 공식을 각 조건절마다 차례로 한번씩 적용하고 
그값들을 곱하면 최종 조인선택도가 구해짐

t1.join1, t2.join1

(10000-0)/10000 *
(10000-0)/10000 /
greater(t1.join1,t2.join1) =
1*1/40 =1/40

t1.join2, t2.join2

(10000-0)/10000 *
(10000-0) /10000 /
greater(t1.join2,t2.join2)=
1*1/50

1/40*1/50 = 1/2000

카디널리티 :
필터된값이 없으므로 10000*10000=100000000

최종 카디널리티
100000000*1/20000= 50000


9.2.0.4

----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=45 Card=50000 Bytes=1700000)
   1    0   HASH JOIN (Cost=45 Card=50000 Bytes=1700000)
   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=18 Card=10000 Bytes=170000)
   3    1     TABLE ACCESS (FULL) OF 'T2' (Cost=18 Card=10000 Bytes=170000)




but 10.2.0.1

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 62500 |  2075K|    46 |
|*  1 |  HASH JOIN         |      | 62500 |  2075K|    46 |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |   166K|    18 |
|   3 |   TABLE ACCESS FULL| T2   | 10000 |   166K|    18 |
-----------------------------------------------------------

* 9i 와 10g 의 결과가 다르다. 

62,500=100,000,000 * 조인선택도 =
조인선택도 = 62,500 / 100,000,000 = 1/1,600
로 계산했음을 알수있음





set autotrace traceonly explain;

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
alter session set tracefile_identifier='10053';



select t1.v1, t2.v1
from
t1,
t2
where
t2.join1 = t1.join1
and t2.join2 = t1.join2
;


alter session set events '10053 trace name context off';

set autotrace off;

spool off;


10053 트레이스 파일 내용

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: T2  Alias: T2
    #Rows: 10000  #Blks:  173  AvgRowLen:  117.00
  Column (#1): JOIN1(NUMBER)
    AvgLen: 3.00 NDV: 40 Nulls: 0 Density: 0.025 Min: 0 Max: 39
  Column (#2): JOIN2(NUMBER)
    AvgLen: 3.00 NDV: 40 Nulls: 0 Density: 0.025 Min: 0 Max: 39
***********************
Table Stats::
  Table: T1  Alias: T1
    #Rows: 10000  #Blks:  173  AvgRowLen:  117.00
  Column (#1): JOIN1(NUMBER)
    AvgLen: 3.00 NDV: 30 Nulls: 0 Density: 0.033333 Min: 0 Max: 29
  Column (#2): JOIN2(NUMBER)
    AvgLen: 3.00 NDV: 50 Nulls: 0 Density: 0.02 Min: 0 Max: 49
***************************************
SINGLE TABLE ACCESS PATH
  Table: T1  Alias: T1
    Card: Original: 10000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00
  Access Path: TableScan
    Cost:  40.47  Resp: 40.47  Degree: 0
      Cost_io: 40.00  Cost_cpu: 3132009
      Resp_io: 40.00  Resp_cpu: 3132009
  Best:: AccessPath: TableScan
         Cost: 40.47  Degree: 1  Resp: 40.47  Card: 10000.00  Bytes: 0
***************************************
SINGLE TABLE ACCESS PATH
  Table: T2  Alias: T2
    Card: Original: 10000  Rounded: 10000  Computed: 10000.00  Non Adjusted: 10000.00
  Access Path: TableScan
    Cost:  40.47  Resp: 40.47  Degree: 0
      Cost_io: 40.00  Cost_cpu: 3132009
      Resp_io: 40.00  Resp_cpu: 3132009
  Best:: AccessPath: TableScan
         Cost: 40.47  Degree: 1  Resp: 40.47  Card: 10000.00  Bytes: 0
Multi-column join key card: 1500  #cols: 2  table: T1
Multi-column join key card: 1600  #cols: 2  table: T2
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
***********************
Join order[1]:  T1[T1]#0  T2[T2]#1
***************
Now joining: T2[T2]#1
***************
NL Join
  Outer table: Card: 10000.00  Cost: 40.47  Resp: 40.47  Degree: 1  Bytes: 17
  Inner table: T2  Alias: T2
  Access Path: TableScan
    NL Join:  Cost: 383206.76  Resp: 383206.76  Degree: 0
      Cost_io: 378479.00  Cost_cpu: 31323223209
      Resp_io: 378479.00  Resp_cpu: 31323223209
  Best NL cost: 383206.76
          resc: 383206.76 resc_io: 378479.00 resc_cpu: 31323223209
          resp: 383206.76 resp_io: 378479.00 resp_cpu: 31323223209
Using multi-column join key sanity check for table T2
Revised join sel:6.2500e-04 = 5.0000e-04 * (1/1600.00) * (1/5.0000e-04)
Join Card:  62500.00 = outer (10000.00) * inner (10000.00) * sel (6.2500e-04)
Join Card - Rounded: 62500 Computed: 62500.00


t2 의 선택도 인 1/40 을 이용하여 두개의 선택도를 취한후최종 카디널리티 계산


10g의 계산방법
 양쪽 조인 컬럼 중 num_distinct가 큰값을 선택하는 작업을 조인 조건절마다 차례로 수행하여 최악의 선택도 계산
 각 테이블 조인 컬럼들의 num_distinct값을 조회해서 테이블별 선택도를 따로 구한후 그중 작은값사용
 (t1=1/1500, t2=1/1600) 
 _optimizer_join_sel_sanity_check : 행동제어

테스트 환경
  _optimizer_join_sel_sanity_check    = true







확장과 예외 338



범위조건에 의한 조인 338 /위충환


옵티마이저는 조인 조건절의 선택도로서 미리 정해 둔 고정된 상수 이용

ex. 단일조인 조건 t2.join1 = t1.join1 을 t2.join1 > t1.join1로 변경하여 실행


  • Join condition: t2.join1 = t1.join1


select t1.v1, t2.v1
from
       t1,
       t2
where
       t1.filter = 1
   and t2.join1 = t1.join1
   and t2.filter = 1;
-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2000 | 68000 |    38 |
|*  1 |  HASH JOIN         |      |  2000 | 68000 |    38 |
|*  2 |   TABLE ACCESS FULL| T2   |   200 |  3400 |    18 |
|*  3 |   TABLE ACCESS FULL| T1   |   400 |  6800 |    18 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T2"."JOIN1"="T1"."JOIN1")
   2 - filter("T2"."FILTER"=1)
   3 - filter("T1"."FILTER"=1)



  • Join condition: t2.join1 > t1.join1


select t1.v1, t2.v1
from
       t1,
       t2
where
       t1.filter = 1
   and t2.join1 > t1.join1
   and t2.filter = 1;
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  4000 |   132K|    44 |
|   1 |  MERGE JOIN         |      |  4000 |   132K|    44 |
|   2 |   SORT JOIN         |      |   200 |  3400 |    21 |
|*  3 |    TABLE ACCESS FULL| T2   |   200 |  3400 |    18 |
|*  4 |   SORT JOIN         |      |   400 |  6800 |    22 |
|*  5 |    TABLE ACCESS FULL| T1   |   400 |  6800 |    18 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T2"."FILTER"=1)
   4 - access(INTERNAL_FUNCTION("T2"."JOIN1")>INTERNAL_FUNCTION("T1"."JOIN1"))
       filter(INTERNAL_FUNCTION("T2"."JOIN1")>INTERNAL_FUNCTION("T1"."JOIN1"))
   5 - filter("T1"."FILTER"=1)


AUTOTRACE 결과

  • 카디널리티 2,000 → 4,000
  • 선택도 1/40은 5%(1/20)에서 대체된 데서 온 결과
  • 해시 조인 → 머지 조인

  ∵ 해시조인은 조인조건이 등호(=)일때만 작동하여 범위 기반 조인일 경우 사용 불가



  • Join condition: t2.join1 between t1.join1 - 1 and t1.join1 + 1


select t1.v1, t2.v1
from
       t1,
       t2
where
       t1.filter = 1
   and t2.join1 between t1.join1 - 1 and t1.join1 + 1
   and t2.filter = 1;
-------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost  |
-------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   200 |  6800 |    44 |
|   1 |  MERGE JOIN          |      |   200 |  6800 |    44 |
|   2 |   SORT JOIN          |      |   200 |  3400 |    21 |
|*  3 |    TABLE ACCESS FULL | T2   |   200 |  3400 |    18 |
|*  4 |   FILTER             |      |       |       |       |
|*  5 |    SORT JOIN         |      |   400 |  6800 |    22 |
|*  6 |     TABLE ACCESS FULL| T1   |   400 |  6800 |    18 |
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T2"."FILTER"=1)
   4 - filter("T2"."JOIN1"<="T1"."JOIN1"+1)
   5 - access(INTERNAL_FUNCTION("T2"."JOIN1")>="T1"."JOIN1"-1)
       filter(INTERNAL_FUNCTION("T2"."JOIN1")>="T1"."JOIN1"-1)
   6 - filter("T1"."FILTER"=1)


AUTOTRACE 결과

  • 조인 카디널리티 2,000 → 200
  • join1 between :bind1 and  :bind2 범위조건을 두 개의 독립적 조건절을, join1 >= :bind1 and  :join1 <= bind2 로 처리

  • 선택도 1/400(0.025%)은 5%(1/20)과 5%(1/20) 곱

    ☞ 1/40의 조인 선택도가 1/400으로 변경, 두 테이블 카디널리티 200과 400을 곱해서 조인 카디널리티가 200으로 계산

     ☞ (t2.join1 = t1.join1) ⊂ (t2.join1 between t1.join1 - 1 and t1.join1 + 1) 이지만

계산된 카디널리티의 결과는 각 2,000 과 200 으로 일관성 문제 발생


교재 테스트 결과 9i에서는 라인 4의 Fiter 오퍼레이션 아래의 sort(join) 라인이 그 처리단계에서의 비용을 표시하지 못하는 사소한 에러를 발견할 수 있으나 10g2r 환경에서는 라인 4의 Fiter 오퍼레이션 아래의 sort(join) 라인까진 비용을 표시



부등호 조인 340 /위충환


부등호 조인이란 (ex.t1.join != t2.join1)


  • The original join

 select               
      t1.v1, t2.v1        
 from                 
      t1,                 
      t2                  
 where                
      t2.join1 = t1.join1 
   or t2.join2 = t1.join2;
Execution Plan
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  2125K|    68M|   528 |
|   1 |  CONCATENATION      |      |       |       |       |
|*  2 |   HASH JOIN         |      |  2000K|    64M|   239 |
|   3 |    TABLE ACCESS FULL| T1   | 10000 |   166K|    18 |
|   4 |    TABLE ACCESS FULL| T2   | 10000 |   166K|    18 |
|*  5 |   HASH JOIN         |      |   125K|  4150K|   289 |
|   6 |    TABLE ACCESS FULL| T1   | 10000 |   166K|    18 |
|   7 |    TABLE ACCESS FULL| T2   | 10000 |   166K|    18 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."JOIN2"="T1"."JOIN2")
   5 - access("T2"."JOIN1"="T1"."JOIN1")
       filter(LNNVL("T2"."JOIN2"="T1"."JOIN2"))



☞ 8i와 9i에서는 라인2 의 결과가 125,000 카디널리티를 나타내지만 라인0의 최종 카디널리티는 250,000(125,000 + 125,000)

이여야지만 2,125,000으로 표현됨 (참고 10g에서는 라인 2결과가 2,000,000으로 최종 카디널리티는 2,125,000(2,000,000+125,000)로 표현


☞ 1번째 해시조인 카디널리티(라인 2)는 1/50의 조인 선택도를 적요해서 구한 값.

    2번째 해시조인 카디널리티(라인 5)는 1/40의 조인 선택도를 적용하고 나서

    앞에서 이미 처리된 jon2 조건에 해당 로우들을 제거하기 위해

    터무니 없는 계수 5%(바인드 변수 사용 시 선택도 계수)를 적용해서 만들어진 수치


■ 올바른 카디널리티 값을 구하기 위해 사용 할 수 있는 방법


1번째 (아래와 같은 형태로 쿼리의 재작성)


  • How Oracle treats it (almost)

 select t1.v1, t2.v1    
 from                   
        t1,                   
        t2                    
 where                  
        t2.join2 = t1.join2   
 union all              
 select t1.v1, t2.v1    
 from                   
        t1,                   
        t2                    
 where                  
        t2.join1 = t1.join1   
    and t2.join2 != t1.join2;
Execution Plan
------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost  |
------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  4450K|   132M|   528 |
|   1 |  UNION-ALL          |      |       |       |       |
|*  2 |   HASH JOIN         |      |  2000K|    53M|   239 |
|   3 |    TABLE ACCESS FULL| T1   | 10000 |   136K|    18 |
|   4 |    TABLE ACCESS FULL| T2   | 10000 |   136K|    18 |
|*  5 |   HASH JOIN         |      |  2450K|    79M|   289 |
|   6 |    TABLE ACCESS FULL| T1   | 10000 |   166K|    18 |
|   7 |    TABLE ACCESS FULL| T2   | 10000 |   166K|    18 |
------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T2"."JOIN2"="T1"."JOIN2")
   5 - access("T2"."JOIN1"="T1"."JOIN1")
       filter("T2"."JOIN2"<>"T1"."JOIN2")


■ 앞의 결과 2,215,000 카디널리티 값은 틀린 값, 4,450,000이 맞는 것임을 증명 (3장의 조건절들을 조합하는 공식 적용)

The selectivity of (predicate1 OR predidcate2) =

      selectivity of (predicate1) +

      selectivity of (predicate2) -

      selectivity of (predicate1 AND predidcate2)    (집합의 중복 제거)


  • 기존의 결과를 통해 알고 있는 개별 선택도 값

t1.join1 = t2.join1                                    1/40

t1.join2 = t2.join2                                    1/50

t1.join1 = t2.join1  and  t1.join2 = t2.join2          1/2000


∴ t1.join1 = t2.join1  or  t1.join2 = t2.join2  의 선택도

     ☞ 1 / 40 + 1 / 50 - 1 / 2000 = 89 / 2000 = 0.045



2번째 (힌트 사용)

  • If we block Concatenation

 select               
  /*+ no_expand */    
      t1.v1, t2.v1        
 from                 
      t1,                 
      t2                  
 where                
      t2.join1 = t1.join1 
   or t2.join2 = t1.join2;
Execution Plan
----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  4450K|   144M|   166K|
|   1 |  NESTED LOOPS      |      |  4450K|   144M|   166K|
|   2 |   TABLE ACCESS FULL| T1   | 10000 |   166K|    18 |
|*  3 |   TABLE ACCESS FULL| T2   |   445 |  7565 |    17 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T2"."JOIN1"="T1"."JOIN1" OR "T2"."JOIN2"="T1"."JOIN2")


☞  no expand 힌트 추가 시 오라클은 조인을 한 번만 수행하여 실행계획은 바른 카디널리티를 보여줄 것임.

     but, 조인을 위해 nested loop 오퍼레이션 수행


☞ 조인 선택도와 조인 카디널리를 구하기 위한 처리 로직들이 스스로 일관성을 잃은 사례들이 있음. → 방안 마련 필요



조인하는 두 집합이 완전히 겹치지 않는 경우 343 /위충환



지금까지의 자료 : 잘 정제된 데이터만을 사용하여 계속 테스트 시행, 지금부터 하나의 중요한 측면에 변경 시도

               값의 범위(가장 낮은 값에서부터 가장 높은 값자리) 비슷

              즉, 두 테이블 집합 간 서로 중복 값이 많아서 거의 대부분 조인 성공한다는 가정


두집합이 서로 중복되는 비율이 낮아질때의  조인 카디널리티의 변화를 살펴봄


▼ join_card_06.sql (각 10,000 개의 로우를 가진 두 개의 테이블 생성)

  • 테이블  t1, t2 조회

select
   min(join1), max(join1)
from t1;

MIN(JOIN1) MAX(JOIN1)
---------- ----------
         0         99


select
   min(join1), max(join1)
from t2;

MIN(JOIN1) MAX(JOIN1)
---------- ----------
         0         99


  • 테이블  t1, t2 조인 계산 결과
SQL> set autotrace traceonly explain
SQL> alter session set events '10053 trace name context forever';

select t1.v1, t2.v1
from
       t1,
       t2
where
       t2.join1 = t1.join1;

Execution Plan
----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000K|    26M|   139 |
|*  1 |  HASH JOIN         |      |  1000K|    26M|   139 |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |   136K|    18 |
|   3 |   TABLE ACCESS FULL| T2   | 10000 |   136K|    18 |
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T2"."JOIN1"="T1"."JOIN1")


  • 테이블  t1, t2 조인 실제 로우 수
SQL> alter session set events '10053 trace name context off';
SQL> set autotrace off


select count(*)
from
       t1,
       t2
where
       t2.join1 = t1.join1;

  COUNT(*)
----------
    999920



☞ 조인 시 두 테이블 범위가 같을 경우 계산된 카디널리티 = 1,000,000, 실제 로우 수 = 999,920




MIN(JOIN1) MAX(JOIN1)
---------- ----------
      -100         -1

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=43 Card=1 Bytes=29)
   1    0   HASH JOIN (Cost=43 Card=1 Bytes=29)
   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=18 Card=10000 Bytes=140000)
   3    1     TABLE ACCESS (FULL) OF 'T1' (Cost=18 Card=10000 Bytes=150000)




  COUNT(*)
----------
         0

TABLE_NAME           REQUESTED_BUCKETS STORED_BUCKETS
-------------------- ----------------- --------------
T1                                   1              1
T2                                   1              1






▼ 표 10-1 조인 카디널리티에서 발생하는 문제

t1.join 최소값

t1.join 최대값

계산된 카디널리티

실제 로우 수

 -100

-1

1

 0

 -50

 49

 1,000,000

 486,318

 -25

 74

 1,000,000

 737,270

 0

 99

  1,000,000

 999,920

 25

 124

  1,000,000

 758,631

 50

 149

  1,000,000

 513,404

 75

 174

  1,000,000

 262,170

 99

 198

  1,000,000

 10.560

 100

 199

 1

0



* 두테이블을 조인할때 한쪽 테이블에 있는 로우와 매칭되는 로우가 다른족에 항상 있다는것은 보장할수없음

   하지만 옵티마이져가 사용하는 카디널리티 계산공식은 그 상황을 전혀 반영하지 않음



히스토그램 345 /박우창


? 히스토그램의 버킷 크기가 카디널리티 예측에 미치는 영향을 실험한다.

? 예제 - (join_card_06.sql)

- t1(join1, v2, padding) : 10,000개 튜플

Column

type

값

비고

join1

number

x..(x+100)

v1

number

1..10000

padding

varchar2(100)

x

- t2(join1, v2, padding) : 10,000개 튜플

Column

type

값

비고

join1

number

0..99

v1

number

1..10000

padding

varchar2(100)

x

- 질의문

  select t1.v1, t2.v1

  from t1, t2

  where t2.join1 = t1.join1;

- 값에 따른 조인 카디널리티 결과 - 히스토그램을 사용해서 그나마 정확함

t1.join1

Low Value

t1.join1

High Value

Computed

Cardinality

Actual

Rows

-100

-1

1,000,000

0

-50

49

1,000,000

486,318

-25

74

1,000,000

737,270

-22

79

622,025

767,437

0

99

1,007,560

999,920

25

124

767,036

758,631

50

149

538,364

513,404

75

174

286,825

262,170

99

198

1,000,000(10,615)

10,560

100

199

1,000,000

0

? 히스토그램 버킷 크기의 중요성 - 히스토그램 버킷의 최대크기는 254이나
실제 데이터와 비슷한 숫자값의 버킷수를 사용할 때 중첩되지 않는 조건에 대하여 비교적 정확히 카디널리티를 예측한다.





Enter value for m_buckets: 254
Enter value for m_offset: 99

MIN(JOIN1) MAX(JOIN1)
---------- ----------
        99        198

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=43 Card=10615 Bytes=307835)
   1    0   HASH JOIN (Cost=43 Card=10615 Bytes=307835)
   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=18 Card=10000 Bytes=140000)
   3    1     TABLE ACCESS (FULL) OF 'T1' (Cost=18 Card=10000 Bytes=150000)




  COUNT(*)
----------
     10560

TABLE_NAME           REQUESTED_BUCKETS STORED_BUCKETS
-------------------- ----------------- --------------
T1                               10000             99
T2                               10000             99





Enter value for m_buckets: 85
Enter value for m_offset: 99

MIN(JOIN1) MAX(JOIN1)
---------- ----------
        99        198

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=43 Card=1000000 Bytes=29000000)
   1    0   HASH JOIN (Cost=43 Card=1000000 Bytes=29000000)
   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=18 Card=10000 Bytes=140000)
   3    1     TABLE ACCESS (FULL) OF 'T1' (Cost=18 Card=10000 Bytes=150000)




  COUNT(*)
----------
     10560

TABLE_NAME           REQUESTED_BUCKETS STORED_BUCKETS
-------------------- ----------------- --------------
T1                                  85             85
T2                                  85             84





 

이행적 폐쇄 349 /박우창


? 이행적 폐쇄 조건이 있는 경우 카디널리티 예측에 미치는 영향을 실험한다.

? 예제 - (join_card_07.sql)

- t1(filter, join1, v2, padding) : 10,000개 튜플

Column

type

값

비고

join1

number

1..30

join2

number

1..50

v1

number

1..10000

padding

varchar2(60)

x

- t2(filter, join1, v2, padding) : 10,000개 튜플

Column

type

값

비고

join1

number

1..40

join2

number

1..40

v1

number

1..10000

padding

varchar2(60)

x

 

? 질의문 실험1

  select t1.v1, t2.v1

  from t1, t2

  where t1.join1=20 and t2.join1 = t1.join1 and t2.join2=t1.join2;

- 실행계획

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    52 |  1768 |    39 |
|*  1 |  HASH JOIN         |      |    52 |  1768 |    39 |
|*  2 |   TABLE ACCESS FULL| T2   |   250 |  4250 |    19 |
|*  3 |   TABLE ACCESS FULL| T1   |   333 |  5661 |    19 |
-----------------------------------------------------------

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

   1 - access("T2"."JOIN1"="T1"."JOIN1" AND "T2"."JOIN2"="T1"."JOIN2")
   2 - filter("T2"."JOIN1"=20)
   3 - filter("T1"."JOIN1"=20)



9.2.0.4


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=37 Card=1667 Bytes=56678)
   1    0   HASH JOIN (Cost=37 Card=1667 Bytes=56678)
   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=18 Card=250 Bytes=4250)
   3    1     TABLE ACCESS (FULL) OF 'T1' (Cost=18 Card=333 Bytes=5661)





- 이행적 추론의 조건

   IF colB=colA and colA=x THEN colB=x;


- 예제에서의 카디널리티 계산

   조인선택도 = (10,000/10,000)*(10,000/10,000)/max(40,50)=1/50

   조인카디널리티=(1/50)*(10,000/30)*(10,000/40)=1,666.66


? 질의문 실험2

  select t1.v1, t2.v1

  from t1, t2

  where t1.join1=20 and t2.join1 = t1.join1 and t2.join2=t1.join2  and t2.join1=20;

- 실행계획

-----------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost  |
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    52 |  1768 |    39 |
|*  1 |  HASH JOIN         |      |    52 |  1768 |    39 |
|*  2 |   TABLE ACCESS FULL| T2   |   250 |  4250 |    19 |
|*  3 |   TABLE ACCESS FULL| T1   |   333 |  5661 |    19 |
-----------------------------------------------------------

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

   1 - access("T2"."JOIN1"="T1"."JOIN1" AND "T2"."JOIN2"="T1"."JOIN2")
   2 - filter("T2"."JOIN1"=20)
   3 - filter("T1"."JOIN1"=20)

- 실험2에서 (10g) 교재와 달리 조건을 추가한다고 실행계획이 달라지지 않음.

 

query_rewrite_enable=true 로 하면 9i도 10g와 같은 결과를 보여줌




세 개 이상 테이블을 조인할 경우 356 / 이태경

  • 세개 이상의 테이블을 조인한 쿼리
SQL> select     t1.v1, t2.v1, t3.v1
  2  from
  3     t1,
  4     t2,
  5     t3
  6  where
  7     t2.join1 = t1.join1
  8  and        t2.join2 = t1.join2
  9  --
 10  and        t3.join2 = t2.join2 --(비교 대상)
 11  and        t3.join3 = t2.join3
 12  --
 13  and        t3.join4 = t1.join4
 14  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1573120526

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  9551 |   559K|   124   (3)| 00:00:02 |
|*  1 |  HASH JOIN          |      |  9551 |   559K|   124   (3)| 00:00:02 |
|   2 |   TABLE ACCESS FULL | T3   | 10000 |   195K|    40   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |      | 62500 |  2441K|    82   (2)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1   | 10000 |   195K|    41   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| T2   | 10000 |   195K|    40   (0)| 00:00:01 |
----------------------------------------------------------------------------

쿼리의 조인순서 t1 ? t2 ? t3


t1과 t2를 조인할 때의 선택도(중간집합)
= 1/greater(36,40) * 1/greater(38,40) = 1/1600 --null값은 포함되지 않았다.

조인 카디널러티(중간집합)
= 1/1600 * 10000 * 10000 = 62500

중간집합(t1조인t2)과 t3의 조인 선택도
= 1/greater(37,38) * 1/greater(39,42) * 1/greater(41,40) = 1/65436

최종 카디널러티 = 1/65436 * 62500 * 10000 = 9,551




  • 첫번째 쿼리의 조건을 달리한 쿼리

SQL> select     t1.v1, t2.v1, t3.v1
  2  from
  3     t1,
  4     t2,
  5     t3
  6  where
  7     t2.join1 = t1.join1
  8  and        t2.join2 = t1.join2
  9  --
 10  and        t3.join3 = t2.join3
 11  --
 12  and        t3.join2 = t1.join2 --첫번째 쿼리와 달라진 조건
 13  and        t3.join4 = t1.join4
 14  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1573120526

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  9301 |   544K|   124   (3)| 00:00:02 |
|*  1 |  HASH JOIN          |      |  9301 |   544K|   124   (3)| 00:00:02 |
|   2 |   TABLE ACCESS FULL | T3   | 10000 |   195K|    40   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |      | 62500 |  2441K|    82   (2)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T1   | 10000 |   195K|    41   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL| T2   | 10000 |   195K|    40   (0)| 00:00:01 |
----------------------------------------------------------------------------

*첫번째 쿼리와 결과집합은 동일하다. 그러나 최종 카디널러티 9551 --> 9074 로 떨어졌다.
원인은 중간집합과 t3테이블을 조인할 때 사용하는 테이블 선택도(t1 테이블의 join2 컬럼의 선택도와 t2 테이블의 join2 컬럼의 선택도)의 차이에 있다.

중간집합(t1조인t2)과 t3의 조인 선택도
= 1/greater(37,40) * 1/greater(39,42) * 1/greater(41,40) = 1/67200

최종 카디널러티 = 1/67200 * 62500 * 10000 = 9,301


*의문점
10g에서의 두번째 쿼리 결과는 조인 선택도의 계산에 의해 나온게 맞습니다.(위 계산식 그대로)
그런데 책에서는 9i에서의 두번째 쿼리 계산을 '1/greater(37,40) * 1/greater(39,42) * 1/greater(41,40)' 이런식으로 하고는
결과는 9074 라고 하는데 결과값이 이해가 안됩니다.


 

조인 컬럼에 Null 값을 갖는 경우 360 /이태경




TABLE_NAME             NUM_ROWS
-------------------- ----------
T1                          100
T2                          120
T3                          150

3 rows selected.

SQL> SQL>

TABLE_NAME           COLUMN_NAME             DENSITY  NUM_NULLS NUM_DISTINCT
-------------------- -------------------- ---------- ---------- ------------
T1                   N1                   .111111111         10            9
T1                   N2                   .111111111         10            9
T1                   V1                          .01          0          100
T1                   PADDING                       1          0            1
T2                   N1                   .090909091         10           11
T2                   N2                   .090909091         10           11
T2                   V1                   .008333333          0          120
T2                   PADDING                       1          0            1
T3                   N1                   .071428571         10           14
T3                   N2                   .071428571         10           14
T3                   V1                   .006666667          0          150
T3                   PADDING                       1          0            1





SQL> select
  2     t1.v1, t2.v1, t3.v1
  3  from
  4     t1,
  5     t2,
  6     t3
  7  where
  8     t2.n1 = t1.n1
  9  and        t3.n1 = t2.n1
 10  ;

Execution Plan (8.1.7.4 autotrace)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=8250 Bytes=346500)
1 0 HASH JOIN (Cost=5 Card=8250 Bytes=346500)
2 1 TABLE ACCESS (FULL) OF 'T3' (Cost=1 Card=150 Bytes=2100)
3 1 HASH JOIN (Cost=3 Card=900 Bytes=25200)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=1 Card=100 Bytes=1400)
5 3 TABLE ACCESS (FULL) OF 'T2' (Cost=1 Card=120 Bytes=1680)

Execution Plan (9.2.0.6 autotrace)
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=9000 Bytes=378000)
1 0 HASH JOIN (Cost=8 Card=9000 Bytes=378000)
2 1 TABLE ACCESS (FULL) OF 'T3' (Cost=2 Card=140 Bytes=1960)
3 1 HASH JOIN (Cost=5 Card=900 Bytes=25200)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=90 Bytes=1260)
5 3 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=110 Bytes=1540)

Execution Plan(10.2.0.3.0 autotrace)
----------------------------------------------------------
Plan hash value: 1573120526

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |  9000 |   369K|     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |  9000 |   369K|     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL | T3   |   140 |  1960 |     2   (0)| 00:00:01 |
|*  3 |   HASH JOIN         |      |   900 | 25200 |     5  (20)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| T1   |    90 |  1260 |     2   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL| T2   |   110 |  1540 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - access("T3"."N1"="T2"."N1")
   2 - filter("T3"."N1" IS NOT NULL)
   3 - access("T2"."N1"="T1"."N1")
   4 - filter("T1"."N1" IS NOT NULL)
   5 - filter("T2"."N1" IS NOT NULL)


8i 와 달리 9i,10g에서는 선택도에 Null 효과를 반영하지 않는다. 또한 조인 카디널리티 계산시 필터된(Null을 제외한) 카디널리티를 인자로 사용한다.(아래 표 참조.)

  • 버젼별 선택도/Card 계산

    		t1과 t2(8i)
    		t1과 t2(9i/10g)
    		중간집합과 t3 조인(8i)
    		중간집합과 t3 조인(9i/10g)
    조인 선택도 계산
    		((100-10)/100) * ((120-10)/120)  / greater(9,11)  = 0.075
    		1/greater(9,11) = 0.09090909
    		((120-10)/120) * ((150-10)/150) / greater(11,14) = 0.0611111
    		1 / greater(11,14) = 0.0714285
    Cardinality 계산
    		0.075 * 100 * 120 = 900
    		0.09090909 * 90 * 110 = 900
    		0.061111 * 900 * 150 = 8250
    		0.0714285 * 900 * 140 = 9000



 

구현 이슈 364 /이창헌

 




모든 코드성 참조 데이터를 타입(TYPE) 컬럼을 가진 단일 테이블에 모두 밀어 넣는 것인데, 잘못하면 옵티마이저의 판단을 흐리게 해 비극적인 결과에 이를 수 있다.




---------------------------------------------

 select

       t1.small_vc,

       type1.description

from

       t1, type1

where

       t1.id between 1000 and 1999

and    type1.id = t1.class1_code

and    type1.type = 'CLASS1'

and    type1.description = lpad(0,10,'0')

;

---------------------------------------------

 

코드명을 필터조건으로 사용해서 일부데이터를 제거하는 것이 전부이다.

 

T1테이블에서 1,000개 로우를 읽게 됨,

T1테이블에서 1개 로우를 읽게됨

 

조인 선택도 = 1/greater( 20, 20 ) = 1/20

조인 카디널리티 = 1/20 * (20*1/20) * (500,000 * 1001/500000) ) = 50

 

--------------------------------------------- 

select

       t1.small_vc,

       type2.description

from

       t1, type2

where

       t1.id between 1000 and 1999

and    type2.id = t1.class1_code

and    type2.type = 'CLASS1'

and    type2.description = lpad(0,10,'0')

--------------------------------------------- 

 

type2테이블 설명  : 45개의 로우 중 description : 중복이 없다. ID와 TYPE컬럼에는 중복이 있고 각각 25개와 2개의 값을 갖는다.  사람의 기각으로 볼 때는 조인 타입(TYPE)에 속하는 로우가 20개임을 정확히 구별할 수 있고 그 결과집합이 어떻게 될지 미리 짐작할 수 있지만 옵티마이저는 공식대로만 처리함.

  

그러나 위해서는 예외 적으로 처리 합니다.

 

 조인선택도 = 1 /greater( 25, 20 ) = 1/20 ? 예외 처리 부분 공식적으로는 25를 선택

조인 카디널리티 = 1/20 * ( 45/90*(500,000 * 1001/500000 ) ) = 25

 

=>TYPE컬럼처럼 이상한 데이터 분포 때문에 문제가 발생한 것이므로 히스토그램을 생성하는 것을 고려

히스토그램을 사용 결과 DESCRIPTION컬럼 필터 조건에 의해 선택된 로우들이 ‘CLASS1’에 속하므로 조인 선택도를 구할 때 45개 중 하나가 아니라 20개 중 하나로 처리함..

 

해결 방범 : TYPE컬럼을 파티션 키로 두고 LIST파티션 테이블로 재생성하는 것

 

실습 소스 : TYPE_DEMO.SQL







 

골치 아픈 사항들 370 /이창헌

 

대개 아래 세 조건에 해당하면 조인 카디널리티를 비정상적인 방법으로 계산 됩니다.

1.       어떤 테이블 조인 컬럼의 distinct값 개수가 다른 테이블에 상응하는 컬럼의 distinct값 개수와 크게 다르다.

2.       두 컬럼이 갖는 값의 범위가 서로 현저하게 다르다.

3.       조인 선택도 공식에서 사용된 개별 선택도들을 곱한 값이 해당 테이블들의 로우 수보다 더 크다.

 




select

        t1.v1, t2.v1

from

        t1, t2

where

        t2.join1 = t1.join1

and     t2.join2 = t1.join2

and     t1.filter = 10

-- and  t2.filter = 10

;

--------------------------------------------- 

select

        t1.v1, t2.v1

from

        t1, t2

where

        t2.join1 = t1.join1

and     t2.join2 = t1.join2

-- and  t1.filter = 10

and     t2.filter = 10

;

--------------------------------------------- 

 

9I : 카디널리티가 다릅니다.

Execution Plan (9.2.0.6 autotrace. Filter on t1)

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=7 Bytes=266)

   1    0   HASH JOIN (Cost=57 Card=7 Bytes=266)

   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=28 Card=100 Bytes=2000)

   3    1     TABLE ACCESS (FULL) OF 'T2' (Cost=28 Card=10000 Bytes=180000)

 

 

Execution Plan (9.2.0.6 autotrace. Filter on t2)

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=57 Card=500 Bytes=19000)

   1    0   HASH JOIN (Cost=57 Card=500 Bytes=19000)

   2    1     TABLE ACCESS (FULL) OF 'T2' (Cost=28 Card=100 Bytes=2100)

   3    1     TABLE ACCESS (FULL) OF 'T1' (Cost=28 Card=10000 Bytes=170000)

 

 

10G : 카디널리티가 같습니다.

다중컬럼 sanity check효과 때문에 필터조건이 t1에 사용되든 t2에 사용되든 계산된 카디널리티는 같다.

 

 

SELECT STATEMENT Optimizer Mode=ALL_ROWS ( Card  =  100 Cost =  41  )

HASH JOIN (Card  = 100      Bytes = 3 K  Cost =  41  )                                                    

    TABLE ACCESS FULL      SCOTT.T1( Card  = 100  Bytes =  1 K   Cost =20 )

 TABLE ACCESS FULL     SCOTT.T2( Card  = 10 K  Bytes =  175 K   Cost =20  )

 

------------------------------------------------------------

 

SELECT STATEMENT Optimizer Mode=ALL_ROWS ( Card  =  100 Cost =  41  )

HASH JOIN (Card  = 100      Bytes = 3 K  Cost =  41  )                                                    

    TABLE ACCESS FULL      SCOTT.T2( Card  = 100  Bytes =  2 K   Cost =20 )

 TABLE ACCESS FULL         SCOTT.T1( Card  = 10 K  Bytes =  166 K   Cost =20  )

 








어이없는 버그 374 /이창헌

역순으로 정렬된 인덱스의 내부적인 처리방식 때문에 발생하는 버그가 있다.

9I에서 카디널리티를 계산하는데 있어 에러를 유발하게 된다.(10G에서 해결됨)

 

 




select

       t1.n1, t2.n2, t1.v1

from

       t1,t2

where

       t1.n2 = 45

and    t2.n1 = t1.n1

 

Execution Plan (9.2.0.6 - with ordinary index)

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=33 Card=225 Bytes=51750)

   1    0   HASH JOIN (Cost=33 Card=225 Bytes=51750)

   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=16 Card=15 Bytes=3330)

   3    1     TABLE ACCESS (FULL) OF 'T2' (Cost=16 Card=3000 Bytes=24000)

 

 

 

Execution Plan (9.2.0.6 - with descending index)

----------------------------------------------------------

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=33 Card=1 Bytes=230)

   1    0   HASH JOIN (Cost=33 Card=1 Bytes=230)

   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=16 Card=15 Bytes=3330)

   3    1     TABLE ACCESS (FULL) OF 'T2' (Cost=16 Card=3000 Bytes=24000)

 

 

10g 

---------------------------------------------------------------------------

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |      |   225 | 51750 |    60  (24)| 00:00:02 |

|*  1 |  HASH JOIN         |      |   225 | 51750 |    60  (24)| 00:00:02 |

|*  2 |   TABLE ACCESS FULL| T1   |    15 |  3330 |    30  (24)| 00:00:01 |

|   3 |   TABLE ACCESS FULL| T2   |  3000 | 24000 |    28  (18)| 00:00:01 |

---------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - access("T2"."N1"="T1"."N1" AND

              SYS_OP_DESCEND("T2"."N1")=SYS_OP_DESCEND("T1"."N1"))

   2 - filter("T1"."N2"=45)

 

 

여기서 발생한 문제는 역순으로 정렬된 인덱스가 존재한다는 사실 때문에 옵티마이저가 추가적인 조건절을 내부적으로 생성하게 된 데에 기인한다. AUTOTRACE가 만든 실행계획 대신 dbms_xplan패키지를 통해 만들어진 실행계획을 확인해 보면 그런 사실을 확인할 수 있다.

 

9i에서 옵티마이저는 선택도 계산 시 sys_op_descend()조 건을 포함한다. 10g에서 옵티마이저는 sys_op_descend()조 건절이 t2.n1 = t1.n1조 건절과 같다는 사실을 인지 한다.






 


 

다른 관점에서의 접근 376 /남송휘


 


지금까지의 계산 방식이 익숙해지지 않을경우
머리속에 이미지를 그려보면 좀더 쉽계 이해할수 있듬

join_card_01.sql 의 예를 들면



TABLE_NAME           COLUMN_NAME          NUM_DISTINCT    DENSITY  NUM_NULLS
-------------------- -------------------- ------------ ---------- ----------
T1                   FILTER                         25        .04          0
T1                   JOIN1                          30 .033333333          0
T1                   V1                          10000      .0001          0
T1                   PADDING                         1          1          0
T2                   FILTER                         50        .02          0
T2                   JOIN1                          40       .025          0
T2                   V1                          10000      .0001          0
T2                   PADDING                         1          1          0




select t1.v1, t2.v1
from
t1,
t2
where
t1.filter = 1                   -- 25 values
and t2.join1 = t1.join1         -- 50/30 values
and t2.filter = 1               -- 50 values

;


총 row수가 10,000개이므로
t1.filter 에의해서 10000/25 = 400
t2.filter 에의해서 10000/50 = 200

둘의 카티젼곱이 이루어지면 400 * 200 = 80000 로우

이제 남은 t2.join1= t1.join1 조건을 보면

t2.join1 = :unknown_value
또는
:unknown_value=t1.join1 이 된다

이둘중 변별력이 높은것을 하나 선택한다

t2.join1= :unknown_value  은 0.025의 density를 가지므로

80000 * 0.025 = 2000 의 값을 가질수 있다

다중 조건절의 경우는 조건절들을 조합하는 단순한 규칙을 적용하면 된다.

요약 378 /남송휘


 

테스트 스크립트 379 /남송휘

이 게시물을
목록
목록
번호 제목 글쓴이 날짜 조회 수
17 Front Page file 운영자 2009.12.21 130110
16 1. 비용(COST)이란? ZoneWorker2 2009.12.28 17925
15 12. 해시 조인 file 헌쓰 2009.12.28 16886
14 13. 정렬과 머지 조인 실천하자 2009.12.28 15942
13 9. 쿼리 변환 헌쓰 2009.12.28 14962
12 5. 클러스터링 팩터 balto 2009.12.28 14694
11 2. 테이블 스캔 balto 2009.12.28 13119
10 11. NL(Nested Loops) 조인 휘휘 2009.12.28 11783
9 14. 10053 트레이스 파일 file 휘휘 2009.12.28 10198
8 7. 히스토그램 ZoneWorker2 2009.12.28 9999
7 4. 단순 B-tree 액세스 file 휘휘 2009.12.28 8800
» 10. 조인 카디널리티 휘휘 2009.12.28 8757
5 3. 단일 테이블 선택도 ZoneWorker2 2009.12.28 8297
4 8. 비트맵 인덱스 ZoneWorker2 2009.12.28 7952
3 진행기록 운영자 2010.04.11 7622
2 6. 선택도 이슈 ZoneWorker2 2009.12.28 7523
1 비용기반의 오라클 원리 첫 모임 [4] 운영자 2010.01.04 5747
쓰기
태그