II. CARDINALITY Hint

II.1. 개념설명

• CARDINALITY Hint를 사용하여 Cardinality를 직접 제어할 수 있다.

II.2. 테스트 Object 생성

DROP TABLE T1 PURGE;
DROP TABLE T2 PURGE;

CREATE TABLE T1(C1 INT, 
                C2 VARCHAR2(10),
                C3 VARCHAR2(10));

CREATE TABLE T2(C1 INT, 
                C2 INT);

CREATE INDEX T1_N1 ON T1(C1);
CREATE INDEX T1_N2 ON T1(C2);
CREATE INDEX T2_N1 ON T2(C1);

INSERT INTO T1
SELECT LEVEL,
       'A',
       'a'
FROM   DUAL
CONNECT BY LEVEL <= 10000
;
 
INSERT INTO T1
SELECT LEVEL + 10000,
       'B',
       'b'
FROM   DUAL
CONNECT BY LEVEL <= 1000
;

INSERT INTO T2
SELECT LEVEL,
       LEVEL
FROM   DUAL
CONNECT BY LEVEL <= 11000
;

COMMIT;

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,
                                  'T1',
                                  CASCADE          => TRUE,
                                  ESTIMATE_PERCENT => 100,
                                  METHOD_OPT       => 'FOR ALL COLUMNS SIZE 1',
                                  NO_INVALIDATE    => FALSE
                                  );
END;
/

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(USER,
                                  'T2',
                                  CASCADE          => TRUE,
                                  ESTIMATE_PERCENT => 100,
                                  METHOD_OPT       => 'FOR ALL COLUMNS SIZE 1',
                                  NO_INVALIDATE    => FALSE
                                  );
END;
/

II.3. 테스트

EXPLAIN PLAN FOR
SELECT *
FROM   T2,
       (SELECT C1, C2
        FROM   T1
        WHERE  T1.C2 = 'A'
        AND    T1.C3 = 'b') V
WHERE  T2.C1 = V.C1
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
               
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  2750 | 44000 |    19   (6)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  2750 | 44000 |    19   (6)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   |  2750 | 22000 |     9   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   | 11000 | 88000 |     9   (0)| 00:00:01 |
---------------------------------------------------------------------------
                                                                           
Predicate Information (identified by operation id):                        
---------------------------------------------------                        
                                                                           
   1 - access("T2"."C1"="C1")                                              
   2 - filter("T1"."C2"='A' AND "T1"."C3"='b')                             
;

• T1.C2 = 'A' AND T1.C3 = 'b' 인 로우는 0건이지만 예상로우는 2750이 나왔다.
  이렇게 나온 이유는 앞에서 살펴본것처럼 Correlated Columns에 대한 통계정보가 없기 때문

• 이를 해결하기 위해서 CARDINALITY Hint를 사용함.

II.4. CARDINALITY Hint 사용

EXPLAIN PLAN FOR
SELECT *
FROM   T2,
       (SELECT /*+ CARDINALITY(T1 1) */
               C1, C2
        FROM   T1
        WHERE  T1.C2 = 'A'
        AND    T1.C3 = 'b') V
WHERE  T2.C1 = V.C1
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
         
-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    16 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2    |     1 |     8 |     2   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |       |     1 |    16 |    11   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL        | T1    |     1 |     8 |     9   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | T2_N1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
                                                                                     
Predicate Information (identified by operation id):                                  
---------------------------------------------------                                  
                                                                                     
   3 - filter("T1"."C2"='A' AND "T1"."C3"='b')                                       
   4 - access("T2"."C1"="C1")                                                        

이 hint는 CBO의 특성을 이용하는 것이기 때문에 INDEX, USE_HASH와 같은 실행계획을 고정시키는 힌트 보다 유연한 실행계획 수립이 가능하다.