10. 조인 카디널리티

조회 수 3441 추천 수 0 2010.03.03 21:44:33
휘휘 *.35.73.164

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 order1:  T1T1#0  T2T2#1
***************
Now joining: T2T2#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 /남송휘