SQLP CBT 기출문제 예상문제 446번

다음 문제를 풀어보세요.

다음 SQL 쿼리와 예상되는 성능 문제점을 고려하여, 가장 효과적으로 성능을 개선할 수 있는 SQL로 고치시오.

CREATE TABLE EMPLOYEES (
    EMP_ID      NUMBER PRIMARY KEY,
    EMP_NAME    VARCHAR2(100),
    DEPT_ID     NUMBER,
    SALARY      NUMBER
);

CREATE TABLE DEPARTMENTS (
    DEPT_ID     NUMBER PRIMARY KEY,
    DEPT_NAME   VARCHAR2(100),
    LOCATION    VARCHAR2(100)
);

[원본 SQL]
SELECT
    E.EMP_NAME,
    E.SALARY
FROM
    EMPLOYEES E
WHERE
    E.DEPT_ID IN (SELECT D.DEPT_ID FROM DEPARTMENTS D WHERE D.LOCATION = 'SEOUL')
AND
    E.SALARY > (SELECT AVG(E2.SALARY) FROM EMPLOYEES E2 WHERE E2.DEPT_ID = E.DEPT_ID);

SELECT
    E.EMP_NAME,
    E.SALARY
FROM
    EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
WHERE
    D.LOCATION = 'SEOUL'
AND
    E.SALARY > (SELECT AVG(E2.SALARY) FROM EMPLOYEES E2 WHERE E2.DEPT_ID = E.DEPT_ID);

SELECT
    E.EMP_NAME,
    E.SALARY
FROM
    EMPLOYEES E JOIN DEPARTMENTS D ON E.DEPT_ID = D.DEPT_ID
    JOIN (SELECT DEPT_ID, AVG(SALARY) AS AVG_SALARY FROM EMPLOYEES GROUP BY DEPT_ID) DEPT_AVG ON E.DEPT_ID = DEPT_AVG.DEPT_ID
WHERE
    D.LOCATION = 'SEOUL'
AND
    E.SALARY > DEPT_AVG.AVG_SALARY;

SELECT
    E.EMP_NAME,
    E.SALARY
FROM
    EMPLOYEES E
WHERE
    E.DEPT_ID IN (SELECT D.DEPT_ID FROM DEPARTMENTS D WHERE D.LOCATION = 'BUSAN')
AND
    E.SALARY > (SELECT AVG(E2.SALARY) FROM EMPLOYEES E2 WHERE E2.DEPT_ID = E.DEPT_ID);

SELECT /*+ NO_MERGE */
    E.EMP_NAME,
    E.SALARY
FROM
    EMPLOYEES E
WHERE
    E.DEPT_ID IN (SELECT D.DEPT_ID FROM DEPARTMENTS D WHERE D.LOCATION = 'SEOUL')
AND
    E.SALARY > (SELECT AVG(E2.SALARY) FROM EMPLOYEES E2 WHERE E2.DEPT_ID = E.DEPT_ID);