III. OPT_ESTIMATE Hint

III.1. 개념설명

• OPT_ESTIMATE Hint는 CARDINALITY Hint의 10g버전
• OPT_ESTIMATE Hint는 (CARDINALITY * SCALE_ROWS(사용자 지정값))이 CARDINALITY가 되도록 하는 방법
• OPT_ESTIMATE Hint는 Undocumented Hint로 SQL Profile을 구현하기 위해 추가됨

III.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;
/

III.3. 테스트

EXPLAIN PLAN FOR
SELECT *
FROM   T2,
       (SELECT /*+ OPT_ESTIMATE(TABLE T1 SCALE_ROWS=0.000363636) */
               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")