SQLD 기출문제 예상문제 197번

다음 문제를 풀어보세요.

다음 중 아래 SQL 쿼리의 응답 시간 개선을 위해 적용할 수 있는 가장 적절한 인덱스 전략을 고르시오.

SELECT O.ORDER_ID, C.CUSTOMER_NAME, SUM(OD.QUANTITY * P.PRICE) AS TOTAL_AMOUNT
FROM ORDERS O
JOIN CUSTOMERS C ON O.CUSTOMER_ID = C.CUSTOMER_ID
JOIN ORDER_DETAILS OD ON O.ORDER_ID = OD.ORDER_ID
JOIN PRODUCTS P ON OD.PRODUCT_ID = P.PRODUCT_ID
WHERE C.CITY = 'Seoul'
AND O.ORDER_DATE BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY O.ORDER_ID, C.CUSTOMER_NAME
HAVING SUM(OD.QUANTITY * P.PRICE) > 100000;
ORDER BY TOTAL_AMOUNT DESC;

다음과 같은 테이블 구조를 가정합니다:

CREATE TABLE CUSTOMERS (
    CUSTOMER_ID INT PRIMARY KEY,
    CUSTOMER_NAME VARCHAR(255),
    CITY VARCHAR(255)
);

CREATE TABLE ORDERS (
    ORDER_ID INT PRIMARY KEY,
    CUSTOMER_ID INT,
    ORDER_DATE DATE,
    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(CUSTOMER_ID)
);

CREATE TABLE ORDER_DETAILS (
    ORDER_ID INT,
    PRODUCT_ID INT,
    QUANTITY INT,
    PRIMARY KEY (ORDER_ID, PRODUCT_ID),
    FOREIGN KEY (ORDER_ID) REFERENCES ORDERS(ORDER_ID),
    FOREIGN KEY (PRODUCT_ID) REFERENCES PRODUCTS(PRODUCT_ID)
);

CREATE TABLE PRODUCTS (
    PRODUCT_ID INT PRIMARY KEY,
    PRICE DECIMAL(10, 2)
);