안녕하세요

막히는 부분이 있어 고수님들께 도움을 받아보고자 글을 올리게 됐습니다.

select * from test.emp where deptno in ( select deptno from test.dept_n) ; -- query 1

select count(*) from test.emp where deptno in ( select deptno from test.dept_n); -- query 2 

select * from test.emp where deptno in ( select deptno from test.dept_n) order by 1; -- query 3

-- query 1
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
select * from test.emp where deptno in ( select deptno from test.dept_n)

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |      1 |   |  14000 |00:00:00.02 |    1029 |   |   |      |
|*  1 |  HASH JOIN RIGHT SEMI|         |      1 |  14000 |  14000 |00:00:00.02 |    1029 |  2440K|  2440K| 1158K (0)|
|   2 |   TABLE ACCESS FULL  | DEPT_N |      1 |      4 |      4 |00:00:00.01 |       2 |   |   |      |
|   3 |   TABLE ACCESS FULL  | EMP |      1 |  14000 |  14000 |00:00:00.01 |    1027 |   |   |      |
--------------------------------------------------------------------------------------------------------------------

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

   1 - access("DEPTNO"="DEPTNO")

-- query 2
PLAN_TABLE_OUTPUT
-------------------------------------
select count(*) from test.emp where deptno in ( select deptno from test.dept_n)

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |      1 |    |   1 |00:00:00.01 |      96 |    |    |       |
|   1 |  SORT AGGREGATE       |        |      1 |      1 |   1 |00:00:00.01 |      96 |    |    |       |
|*  2 |   HASH JOIN RIGHT SEMI|        |      1 |  14000 |  14000 |00:00:00.01 |      96 |  2440K|  2440K| 1008K (0)|
|   3 |    TABLE ACCESS FULL  | DEPT_N |      1 |      4 |   4 |00:00:00.01 |       2 |    |    |       |
|   4 |    TABLE ACCESS FULL  | EMP |      1 |  14000 |  14000 |00:00:00.01 |      94 |    |    |       |
---------------------------------------------------------------------------------------------------------------------

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

   2 - access("DEPTNO"="DEPTNO")

-- query 3
PLAN_TABLE_OUTPUT
-------------------------------------
select * from test.emp where deptno in ( select deptno from test.dept_n) order by 1

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |      1 |    |  14000 |00:00:00.03 |      96 |    |    |       |
|   1 |  SORT ORDER BY         |        |      1 |  14000 |  14000 |00:00:00.03 |      96 |  1116K|   557K|  991K (0)|
|*  2 |   HASH JOIN RIGHT SEMI|        |      1 |  14000 |  14000 |00:00:00.01 |      96 |  2440K|  2440K|  994K (0)|
|   3 |    TABLE ACCESS FULL  | DEPT_N |      1 |      4 |   4 |00:00:00.01 |       2 |    |    |       |
|   4 |    TABLE ACCESS FULL  | EMP |      1 |  14000 |  14000 |00:00:00.01 |      94 |    |    |       |
---------------------------------------------------------------------------------------------------------------------

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

   2 - access("DEPTNO"="DEPTNO")

3개 쿼리 전부 DEPT_N TABLE을 선행으로 해시 테이블 생성

 이후 , EMP를 후행 테이블로 조인을 하는건 이해를 하였습니다.

 궁극적으로 궁금한것은 노란색을 칠한  Memory 부분과 Buffer 부분입니다.

참고로 , 해당 테이블들은 인덱스가 없습니다.

1) 1번과 2번 쿼리는 emp table에 대해 select * , select count(*) 으로

컬럼의 개수가 달라 가져오는 버퍼수와 hash join right semi의 메모리 사용량이 다른게 맞을까요?

2) 1번이 맞다면, 

왜 , 1번과 3번 쿼리는 전부 같으면서 마지막에만 order by를 하냐 안하냐 차이인데

실행 계획을보면 order by 하기 전 1번은 buffer - 1027 3번은 - 94 입니다. 

또한 , hash join 때 사용되는 메모리도 3번이 훨씬 적구요 . 

왜 이런 차이가 일어나는지 궁금합니다.

글이 두서 없이 작성하여 보기 불편하실거 알지만 양해 부탁 드리며

고수님들의 의견 많이 주셨으면 좋겠습니다.

감사합니다.