지난 주 자료를 작성하면서.. Plan이 조금 이상하게 나온다고 생각이 되었는데..

불행히도 불참을 하게되어 ㅜㅜ 죄송한 맘을 어찌할 바를 모르겠지만..

어제 모임에서 다행인지 불행인지 누군가에 의해서 질문이 다시 올라와서 몇자 적어봅니다.

예제에 있던 SQL입니다.

 

select /*+ leading(d) use_nl(e) index(e yhn_emp_idx)  */
       count(e.empno), count(d.dname)
  from yhn_emp e, dept d
 where d.deptno = e.deptno
   and e.sal <= 2900;

 

-- 11g 

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |     1 |    52 |  3236   (1)| 00:00:23 |
|   1 |  SORT AGGREGATE               |             |     1 |    52 |            |          |
|   2 |   NESTED LOOPS                |             |       |       |            |          |
|   3 |    NESTED LOOPS               |             |     1 |    52 |  3236   (1)| 00:00:23 |
|   4 |     TABLE ACCESS FULL         | DEPT        |     4 |    52 |     4   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | YHN_EMP_IDX | 10063 |       |    26   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| YHN_EMP     |     1 |    39 |   808   (1)| 00:00:06 |
---------------------------------------------------------------------------------------------

 

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      NLJ_BATCHING(@"SEL$1" "E"@"SEL$1")
      USE_NL(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      INDEX(@"SEL$1" "E"@"SEL$1" ("YHN_EMP"."SAL"))
      FULL(@"SEL$1" "D"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

 

Why? NL 조인이 두번 나오게 되는가...?

 

-- 10g

--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    52 |  3236   (1)| 00:00:23 |
|   1 |  SORT AGGREGATE              |             |     1 |    52 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| YHN_EMP     |     1 |    39 |   808   (1)| 00:00:06 |
|   3 |    NESTED LOOPS              |             |     1 |    52 |  3236   (1)| 00:00:23 |
|   4 |     TABLE ACCESS FULL        | DEPT        |     4 |    52 |     4   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN         | YHN_EMP_IDX | 10063 |       |    26   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

 

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      NLJ_PREFETCH(@"SEL$1" "E"@"SEL$1")
      USE_NL(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      INDEX(@"SEL$1" "E"@"SEL$1" ("YHN_EMP"."SAL"))
      FULL(@"SEL$1" "D"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

 

-- 9i

----------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     1 |    52 |    46 |
|   1 |  SORT AGGREGATE              |             |     1 |    52 |       |
|*  2 |   TABLE ACCESS BY INDEX ROWID| YHN_EMP     |   102 |  3978 |    11 |
|   3 |    NESTED LOOPS              |             |   408 | 21216 |    46 |
|   4 |     TABLE ACCESS FULL        | DEPT        |     4 |    52 |     2 |
|*  5 |     INDEX RANGE SCAN         | YHN_EMP_IDX |    74 |       |     1 |
----------------------------------------------------------------------------
 
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      NLJ_PREFETCH(@"SEL$1" "E"@"SEL$1")
      USE_NL(@"SEL$1" "E"@"SEL$1")
      LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
      INDEX(@"SEL$1" "E"@"SEL$1" ("YHN_EMP"."SAL"))
      FULL(@"SEL$1" "D"@"SEL$1")
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('9.2.0.8')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

 

차이는 NLJ_PREFETCH와 NLJ_BATCHING에 있었습니다.

사족을 좀 달아보고 싶지만.. 정리가 너무 잘되어 있는 자료를 알려드리는게 더욱 효과적이고

지금 1시가 다되어 저도 꿈나라로 가야할 듯 합니다.

이와 관련된 자료를 Link합니다.

 

http://ukja.tistory.com/166

http://scidb.tistory.com/entry/Nested-Loop-Join-성능향상과-관련된-2가지-원리

 

정말 슬픈 사실은... 위의 글중 아래의 오동규씨 내용은 예전에 읽었다는 것입니다..ㅜㅜ

머리가 나쁘다는 걸 예전부터 알고 있었지만.. 오늘 다시 한번 확인을 하니..

정말로 많이 슬프군요..

이 슬픔 조만간 누군가가.. 촉촉한 소맥으로 달래주지 않을까 싶네요.. ㅜㅜ