251027 TIL
2025. 10. 27. 19:35ㆍCourses/아이티윌 오라클 DBA 과정
문제
--[문제37] 년도,분기별 급여의 총액을 구하세요.
/*
년도 1분기 2분기 3분기 4분기
-------- ---------- ---------- ---------- ----------
2001 17000
2002 36808 21008 11000
2003 35000 8000 3500
2004 40700 14300 17000 14000
2005 86900 16800 60800 33400
2006 69400 20400 14200 17100
2007 36600 20200 2500 35600
2008 46900 12300
*/
SELECT
to_char(hire_date, 'yyyy') year,
sum(case to_char(hire_date, 'q') when '1' then salary end) "1분기",
sum(case to_char(hire_date, 'q') when '2' then salary end) "2분기",
sum(case to_char(hire_date, 'q') when '3' then salary end) "3분기",
sum(case to_char(hire_date, 'q') when '4' then salary end) "4분기"
FROM hr.employees
GROUP BY to_char(hire_date, 'yyyy')
ORDER BY year;
SELECT *
FROM (SELECT to_char(hire_date, 'yyyy') year, to_char(hire_date, 'q') q, salary
FROM hr.employees)
PIVOT (sum(salary) for q in ('1' "1분기", '2' "2분기", '3' "3분기", '4' "4분기"))
ORDER BY 1;
SELECT *
FROM (SELECT to_char(hire_date, 'yyyy') year, to_char(hire_date, 'q') q, sum(salary) 총액
FROM hr.employees
GROUP BY to_char(hire_date, 'yyyy'), to_char(hire_date, 'q'))
PIVOT (max(총액) for q in ('1' "1분기", '2' "2분기", '3' "3분기", '4' "4분기"))
ORDER BY 1;
SELECT
year,
max(case q when '1' then 총액 end) "1분기",
max(case q when '2' then 총액 end) "2분기",
max(case q when '3' then 총액 end) "3분기",
max(case q when '4' then 총액 end) "4분기"
FROM (SELECT to_char(hire_date, 'yyyy') year, to_char(hire_date, 'q') q, sum(salary) 총액
FROM hr.employees
GROUP BY to_char(hire_date, 'yyyy'), to_char(hire_date, 'q'))
GROUP BY year
ORDER BY 1;서브쿼리
다중열 서브쿼리
-- 쌍비교
SELECT *
FROM hr.employees
WHERE (manager_id, department_id) IN (SELECT manager_id, department_id
FROM hr.employees
WHERE first_name = 'John');
-- 비쌍비교
SELECT *
FROM hr.employees
WHERE manager_id IN (SELECT manager_id
FROM hr.employees
WHERE first_name = 'John')
AND department_id IN (SELECT department_id
FROM hr.employees
WHERE first_name = 'John');- 쌍비교와 비쌍비교의 결과 값은 다름
문제
-- [문제38] commission_pct null이 아닌 사원들의 department_id, salary 일치하는 사원들의 정보를 출력해주세요.
-- 1) 쌍비교
SELECT *
FROM hr.employees
WHERE (nvl(department_id,0), salary) IN (SELECT nvl(department_id,0), salary
FROM hr.employees
WHERE commission_pct is not null);
-- 2) 비쌍비교
SELECT *
FROM hr.employees
WHERE nvl(department_id, 0) IN (SELECT nvl(department_id, 0)
FROM hr.employees
WHERE commission_pct is not null)
AND salary IN (SELECT salary
FROM hr.employees
WHERE commission_pct is not null); - NULL 값도 포함해야 하는 경우 NVL로 변환 작업 수행
-- [문제39] location_id가 1700 위치에 있는 사원들의 salary, commission_pct가 일치하는 사원들의 정보를 출력해주세요.
-- 1) 쌍비교
SELECT *
FROM hr.employees
WHERE (salary, nvl(commission_pct, 0)) IN (SELECT e.salary, nvl(e.commission_pct, 0)
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
AND d.location_id = 1700);
SELECT *
FROM hr.employees
WHERE (salary, nvl(commission_pct, 0)) IN (SELECT salary, nvl(commission_pct, 0)
FROM hr.employees
WHERE department_id IN (SELECT department_id
FROM hr.departments
WHERE location_id = 1700));
-- 2) 비쌍비교
SELECT *
FROM hr.employees
WHERE salary IN (SELECT e.salary
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
AND d.location_id = 1700)
AND nvl(commission_pct, 0) IN (SELECT nvl(e.commission_pct, 0)
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
AND d.location_id = 1700); Scalar subquery(스칼라 서브쿼리)
- 한 행에서 정확히 하나의 열 값만 반환하는 쿼리(단일 컬럼, 단일 값만 리턴해야 함)
- 동일한 입력 값이 들어오면 수행 횟수를 최소화할 수 있는 로직을 구현
- query execution cache 기능 수행
- I/O를 최소화 할 수 있음
- 키 값이 없는 데이터가 입력되면 또는 NULL이 입력되면 NULL 값으로 리턴(outer join처럼 출력됨)
SELECT employee_id, department_id, (SELECT department_name
FROM hr.departments
WHERE department_id = e.department_id)
FROM hr.employees e
ORDER BY 2;
SELECT e.employee_id, e.department_id, d.department_id, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id(+)
ORDER BY 2;문제
-- [문제40] 부서이름별 급여의 총액, 평균을 구하세요.
-- 1) 일반적인 형식
SELECT d.department_name, sum(salary), avg(salary)
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name
ORDER BY 1;
-- 2) inline view 이용
SELECT d.department_name, sum, avg
FROM hr.departments d, (SELECT e.department_id, sum(salary) sum, avg(salary) avg
FROM hr.employees e
GROUP BY e.department_id) e
WHERE d.department_id = e.department_id
ORDER BY 1;
-- 3) scalar subquery
SELECT department_name, substr(sum_avg, 1, 10) sum, substr(sum_avg, 11) avg
FROM (SELECT department_name, (SELECT lpad(sum(salary), 10)||lpad(round(avg(salary)), 10) FROM hr.employees WHERE department_id = d.department_id) sum_avg
FROM hr.departments d)
WHERE sum_avg is not null;- 1번 방식은 사원 수만큼 조인 결과 만든 후(107건) 부서 이름 별 sum/avg 계산
- 2번 방식은 부서 이름 별 sum/avg 먼저 구한 후( 12건) 조인 수행
SELECT
department_name,
(SELECT sum(salary)
FROM hr.employees
WHERE department_id = d.department_id) sal,
(SELECT avg(salary)
FROM hr.employees
WHERE department_id = d.department_id) avg
FROM hr.departments d;- 3번 방식을 위 쿼리처럼 작성하면 동일 테이블을 두 번씩 접근하는 문제 발생
SELECT department_name, substr(sum_avg, 1, 10) sum, substr(sum_avg, 11) avg
FROM (SELECT department_name, (SELECT lpad(sum(salary), 10)||lpad(round(avg(salary)), 10) FROM hr.employees WHERE department_id = d.department_id) sum_avg
FROM hr.departments d)
WHERE sum_avg is not null;- 스칼라 서브쿼리는 단일 컬럼만 반환가능
- 여러 개의 값이 필요한 경우 연결 연산자를 사용한 다음 substr로 잘라서 사용
- lpad를 사용해서 길이 제한
-- [문제41] 사원들의 employee_id, last_name을 출력하세요.
-- 단 department_name을 기준으로 오름차순 정렬해주세요.
-- 1) join
SELECT employee_id, last_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id(+)
ORDER BY department_name;
-- 2) scalar subquery
SELECT employee_id, last_name
FROM hr.employees e
ORDER BY (SELECT department_name
FROM hr.departments
WHERE department_id = e.department_id);집합 연산자
- 집합 연산자는 둘 이상의 쿼리 결과를 하나의 결과로 조합하는 연산자
주의
- 두 쿼리의 컬럼 개수와 타입이 일치해야 함
- 첫 번째 쿼리가 기준
- union, intersect, minus 연산자는 중복을 제거하기 위해 정렬이 수행됨
- order by는 맨 마지막에 기술해야 함
- order by 절에는 첫 번째 SELECT 절의 컬럼 이름, 별칭, 위치 표기법을 사용
SELECT employee_id, job_id, salary
FROM hr.employees
UNION ALL
SELECT employee_id, job_id, NULL
FROM hr.job_history
ORDER BY 1; UNION
- 중복행이 제거된 두 쿼리 행을 합침
- 합집합
SELECT employee_id, job_id, salary
FROM hr.employees
UNION
SELECT employee_id, job_id, NULL
FROM hr.job_history;
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 117 |00:00:00.01 | 12 | | | |
| 1 | SORT UNIQUE | | 1 | 117 | 117 |00:00:00.01 | 12 | 9216 | 9216 | 8192 (0)|
| 2 | UNION-ALL | | 1 | | 117 |00:00:00.01 | 12 | | | |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
| 4 | VIEW | index$_join$_002 | 1 | 10 | 10 |00:00:00.01 | 6 | | | |
|* 5 | HASH JOIN | | 1 | | 10 |00:00:00.01 | 6 | 1096K| 1096K| 1034K (0)|
| 6 | INDEX FAST FULL SCAN| JHIST_EMPLOYEE_IX | 1 | 10 | 10 |00:00:00.01 | 3 | | | |
| 7 | INDEX FAST FULL SCAN| JHIST_JOB_IX | 1 | 10 | 10 |00:00:00.01 | 3 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(ROWID=ROWID)- 정렬 제거를 위한 쿼리 변환(not exists와 union all 활용)
SELECT employee_id, job_id
FROM hr.employees
UNION
SELECT employee_id, job_id
FROM hr.job_history;
SELECT employee_id, job_id
FROM hr.employees e
WHERE not exists (SELECT 'x'
FROM hr.job_history
WHERE employee_id = e.employee_id
and job_id = e.job_id)
UNION ALL
SELECT employee_id, job_id
FROM hr.job_history;
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 115 |00:00:00.01 | 18 | | | |
| 1 | UNION-ALL | | 1 | | 115 |00:00:00.01 | 18 | | | |
|* 2 | HASH JOIN ANTI | | 1 | 97 | 105 |00:00:00.01 | 12 | 1134K| 1134K| 1233K (0)|
| 3 | VIEW | index$_join$_001 | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
|* 4 | HASH JOIN | | 1 | | 107 |00:00:00.01 | 6 | 1096K| 1096K| 1525K (0)|
| 5 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 1 | 107 | 107 |00:00:00.01 | 3 | | | |
| 6 | INDEX FAST FULL SCAN| EMP_JOB_IX | 1 | 107 | 107 |00:00:00.01 | 3 | | | |
| 7 | VIEW | index$_join$_002 | 1 | 10 | 10 |00:00:00.01 | 6 | | | |
|* 8 | HASH JOIN | | 1 | | 10 |00:00:00.01 | 6 | 1096K| 1096K| 1261K (0)|
| 9 | INDEX FAST FULL SCAN| JHIST_EMPLOYEE_IX | 1 | 10 | 10 |00:00:00.01 | 3 | | | |
| 10 | INDEX FAST FULL SCAN| JHIST_JOB_IX | 1 | 10 | 10 |00:00:00.01 | 3 | | | |
| 11 | VIEW | index$_join$_003 | 1 | 10 | 10 |00:00:00.01 | 6 | | | |
|* 12 | HASH JOIN | | 1 | | 10 |00:00:00.01 | 6 | 1096K| 1096K| 1195K (0)|
| 13 | INDEX FAST FULL SCAN | JHIST_EMPLOYEE_IX | 1 | 10 | 10 |00:00:00.01 | 3 | | | |
| 14 | INDEX FAST FULL SCAN | JHIST_JOB_IX | 1 | 10 | 10 |00:00:00.01 | 3 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPLOYEE_ID"="E"."EMPLOYEE_ID" AND "JOB_ID"="E"."JOB_ID")
4 - access(ROWID=ROWID)
8 - access(ROWID=ROWID)UNION ALL
- 중복행이 포함된 두 쿼리 행을 합침
- 합집합
SELECT employee_id, job_id, salary
FROM hr.employees
UNION ALL
SELECT employee_id, job_id, NULL
FROM hr.job_history;
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 117 |00:00:00.01 | 14 | | | |
| 1 | UNION-ALL | | 1 | | 117 |00:00:00.01 | 14 | | | |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 8 | | | |
| 3 | VIEW | index$_join$_002 | 1 | 10 | 10 |00:00:00.01 | 6 | | | |
|* 4 | HASH JOIN | | 1 | | 10 |00:00:00.01 | 6 | 1096K| 1096K| 987K (0)|
| 5 | INDEX FAST FULL SCAN| JHIST_EMPLOYEE_IX | 1 | 10 | 10 |00:00:00.01 | 3 | | | |
| 6 | INDEX FAST FULL SCAN| JHIST_JOB_IX | 1 | 10 | 10 |00:00:00.01 | 3 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(ROWID=ROWID)INTERSECT
- 두 쿼리의 공통적인 행만 추출
- 교집합
SELECT employee_id
FROM hr.employees
INTERSECT
SELECT employee_id
FROM hr.job_history;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 7 |00:00:00.01 | 2 |
| 1 | INTERSECTION | | 1 | | 7 |00:00:00.01 | 2 |
| 2 | SORT UNIQUE NOSORT| | 1 | 107 | 107 |00:00:00.01 | 1 |
| 3 | INDEX FULL SCAN | EMP_EMP_ID_PK | 1 | 107 | 107 |00:00:00.01 | 1 |
| 4 | SORT UNIQUE NOSORT| | 1 | 10 | 7 |00:00:00.01 | 1 |
| 5 | INDEX FULL SCAN | JHIST_EMPLOYEE_IX | 1 | 10 | 10 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------------- 정렬 제거를 위한 쿼리 변환(exists 활용)
SELECT employee_id, job_id
FROM hr.employees e
WHERE exists (SELECT 'x'
FROM hr.job_history
WHERE employee_id = e.employee_id
and job_id = e.job_id);
-- unnest
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 12 | | | |
|* 1 | HASH JOIN SEMI | | 1 | 10 | 2 |00:00:00.01 | 12 | 1134K| 1134K| 1225K (0)|
| 2 | VIEW | index$_join$_001 | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
|* 3 | HASH JOIN | | 1 | | 107 |00:00:00.01 | 6 | 1096K| 1096K| 1246K (0)|
| 4 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 1 | 107 | 107 |00:00:00.01 | 3 | | | |
| 5 | INDEX FAST FULL SCAN| EMP_JOB_IX | 1 | 107 | 107 |00:00:00.01 | 3 | | | |
| 6 | VIEW | index$_join$_002 | 1 | 10 | 10 |00:00:00.01 | 6 | | | |
|* 7 | HASH JOIN | | 1 | | 10 |00:00:00.01 | 6 | 1096K| 1096K| 1005K (0)|
| 8 | INDEX FAST FULL SCAN| JHIST_EMPLOYEE_IX | 1 | 10 | 10 |00:00:00.01 | 3 | | | |
| 9 | INDEX FAST FULL SCAN| JHIST_JOB_IX | 1 | 10 | 10 |00:00:00.01 | 3 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPLOYEE_ID"="E"."EMPLOYEE_ID" AND "JOB_ID"="E"."JOB_ID")
3 - access(ROWID=ROWID)
7 - access(ROWID=ROWID)
-- no_unnest
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 177 | | | |
|* 1 | FILTER | | 1 | | 2 |00:00:00.01 | 177 | | | |
| 2 | VIEW | index$_join$_001 | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
|* 3 | HASH JOIN | | 1 | | 107 |00:00:00.01 | 6 | 1096K| 1096K| 1401K (0)|
| 4 | INDEX FAST FULL SCAN | EMP_EMP_ID_PK | 1 | 107 | 107 |00:00:00.01 | 3 | | | |
| 5 | INDEX FAST FULL SCAN | EMP_JOB_IX | 1 | 107 | 107 |00:00:00.01 | 3 | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| JOB_HISTORY | 107 | 1 | 2 |00:00:00.01 | 171 | | | |
|* 7 | INDEX RANGE SCAN | JHIST_JOB_IX | 107 | 1 | 85 |00:00:00.01 | 107 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NOT NULL)
3 - access(ROWID=ROWID)
6 - filter("EMPLOYEE_ID"=:B1)
7 - access("JOB_ID"=:B1)MINUS
- 첫 번째 쿼리에 있는 행 중 두 번째 쿼리에 없는 행만 추출
- 차집합
SELECT employee_id
FROM hr.employees
MINUS
SELECT employee_id
FROM hr.job_history;
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 50 |00:00:00.01 | 2 |
| 1 | MINUS | | 1 | | 50 |00:00:00.01 | 2 |
| 2 | SORT UNIQUE NOSORT| | 1 | 107 | 54 |00:00:00.01 | 1 |
| 3 | INDEX FULL SCAN | EMP_EMP_ID_PK | 1 | 107 | 55 |00:00:00.01 | 1 |
| 4 | SORT UNIQUE NOSORT| | 1 | 10 | 5 |00:00:00.01 | 1 |
| 5 | INDEX FULL SCAN | JHIST_EMPLOYEE_IX | 1 | 10 | 8 |00:00:00.01 | 1 |
---------------------------------------------------------------------------------------------------- 정렬 제거를 위한 쿼리 변환(not exists 활용)
SELECT employee_id, job_id
FROM hr.employees e
WHERE not exists (SELECT 'x'
FROM hr.job_history
WHERE employee_id = e.employee_id
and job_id = e.job_id);
-- unnest
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 105 |00:00:00.01 | 12 | | | |
|* 1 | HASH JOIN ANTI | | 1 | 97 | 105 |00:00:00.01 | 12 | 1134K| 1134K| 1235K (0)|
| 2 | VIEW | index$_join$_001 | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
|* 3 | HASH JOIN | | 1 | | 107 |00:00:00.01 | 6 | 1096K| 1096K| 1536K (0)|
| 4 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 1 | 107 | 107 |00:00:00.01 | 3 | | | |
| 5 | INDEX FAST FULL SCAN| EMP_JOB_IX | 1 | 107 | 107 |00:00:00.01 | 3 | | | |
| 6 | VIEW | index$_join$_002 | 1 | 10 | 10 |00:00:00.01 | 6 | | | |
|* 7 | HASH JOIN | | 1 | | 10 |00:00:00.01 | 6 | 1096K| 1096K| 1272K (0)|
| 8 | INDEX FAST FULL SCAN| JHIST_EMPLOYEE_IX | 1 | 10 | 10 |00:00:00.01 | 3 | | | |
| 9 | INDEX FAST FULL SCAN| JHIST_JOB_IX | 1 | 10 | 10 |00:00:00.01 | 3 | | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPLOYEE_ID"="E"."EMPLOYEE_ID" AND "JOB_ID"="E"."JOB_ID")
3 - access(ROWID=ROWID)
7 - access(ROWID=ROWID)
-- no_unnest
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 105 |00:00:00.01 | 179 | | | |
|* 1 | FILTER | | 1 | | 105 |00:00:00.01 | 179 | | | |
| 2 | VIEW | index$_join$_001 | 1 | 107 | 107 |00:00:00.01 | 8 | | | |
|* 3 | HASH JOIN | | 1 | | 107 |00:00:00.01 | 8 | 1096K| 1096K| 1461K (0)|
| 4 | INDEX FAST FULL SCAN | EMP_EMP_ID_PK | 1 | 107 | 107 |00:00:00.01 | 3 | | | |
| 5 | INDEX FAST FULL SCAN | EMP_JOB_IX | 1 | 107 | 107 |00:00:00.01 | 5 | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| JOB_HISTORY | 107 | 1 | 2 |00:00:00.01 | 171 | | | |
|* 7 | INDEX RANGE SCAN | JHIST_JOB_IX | 107 | 1 | 85 |00:00:00.01 | 107 | | | |
--------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter( IS NULL)
3 - access(ROWID=ROWID)
6 - filter("EMPLOYEE_ID"=:B1)
7 - access("JOB_ID"=:B1)FULL OUTER JOIN
SELECT employee_id, e.last_name, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id(+)
UNION
SELECT employee_id, e.last_name, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id(+) = d.department_id;
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 123 |00:00:00.01 | 20 | | | |
| 1 | SORT UNIQUE | | 1 | 213 | 123 |00:00:00.01 | 20 | 11264 | 11264 |10240 (0)|
| 2 | UNION-ALL | | 1 | | 229 |00:00:00.01 | 20 | | | |
|* 3 | HASH JOIN OUTER | | 1 | 107 | 107 |00:00:00.01 | 12 | 1079K| 1079K| 901K (0)|
| 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
| 5 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 6 | | | |
| 6 | MERGE JOIN OUTER | | 1 | 106 | 122 |00:00:00.01 | 8 | | | |
| 7 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 2 | | | |
| 8 | INDEX FULL SCAN | DEPT_ID_PK | 1 | 27 | 27 |00:00:00.01 | 1 | | | |
|* 9 | SORT JOIN | | 27 | 107 | 106 |00:00:00.01 | 6 | 15360 | 15360 |14336 (0)|
| 10 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 6 | | | |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
9 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
SELECT employee_id, e.last_name, d.department_name
FROM hr.employees e FULL OUTER JOIN hr.departments d
ON e.department_id = d.department_id;
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 123 |00:00:00.01 | 14 | | | |
| 1 | VIEW | VW_FOJ_0 | 1 | 122 | 123 |00:00:00.01 | 14 | | | |
|* 2 | HASH JOIN FULL OUTER| | 1 | 122 | 123 |00:00:00.01 | 14 | 1079K| 1079K| 1222K (0)|
| 3 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 27 | 27 |00:00:00.01 | 6 | | | |
| 4 | TABLE ACCESS FULL | EMPLOYEES | 1 | 107 | 107 |00:00:00.01 | 8 | | | |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")- 오라클 조인 방식으로 full outer join 하려면 1번 쿼리처럼 작성해야 함
- 같은 테이블을 2번 접근 → 비효율
- ANSI FULL OUTER JOIN을 사용하는 것이 좋음
SELECT employee_id, e.last_name, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id(+)
UNION ALL
SELECT null, null, department_name
FROM hr.departments d
WHERE not exists (SELECT 'x'
FROM hr.employees
WHERE department_id = d.department_id);- UNION은 정렬을 수행하기 때문에 비효율 발생
- 정렬이 필요 없는 경우 not exists와 union all 을 활용한 쿼리로 변환하여 사용하는 것이 좋음
'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글
| 251029 TIL (0) | 2025.10.29 |
|---|---|
| 251028 TIL (1) | 2025.10.28 |
| 251024 TIL (0) | 2025.10.24 |
| 251023 TIL (0) | 2025.10.23 |
| 251022 TIL (0) | 2025.10.22 |