[SQLP필기풀이]3장 인덱스 튜닝(4)-인덱스설계

✍️ 53번 : 가장 효과적인 컬럼 구성안

▶ 두 SQL의 수행 빈도는 동일
< SQL 1>
SELECT *
FROM 거래
WHERE 상품코드 = :prd_cd
AND 거래일자 BETWEEN :trd_dt1 AND :trd_dt2;

< SQL 2>
SELECT *
FROM 거래
WHERE 상품코드 = :prd_cd
AND 공급업체코드 LIKE :supl_co_cd || '%'
AND 거래일자 BETWEEN :trd_dt1 AND :trd_dt2;

가장 효과적인 컬럼 구성안

1. 상품코드 + 거래일자 + 공급업체코드 👉 ⭕️

🍋 기출 포인트

1. 두 SQL에 공통적으로 사용된 조건절은 상품코드와 거래일자다. 둘 중 '=' 조건인 상품코드를 선두에 두고 BETWEEN 조건인 거래일자를 뒤쪽에 두어야 인덱스 스캔 효율이 좋다.
2. 두 SQL의 수행 빈도가 동일하고 단 하나의 인덱스만 생성해야 하므로..SQL 2의 스캔 과정에 약간의 비효율이 있더라도 상품코드 + 거래일자 + 공급업체코드」 순으로 구성하는 것이
   좋다.

🍒 문제 해설

1. 만약 거래일자 BETWEEN 조건보다 공급업체코드 LIKE 조건의 선택도(Selectivity)가 일반적으로 더 낮다면, SQL 2를 위한 최적 인덱스는 「상품코드 + 공급업체코드 + 거래일자」다.하지만, 인덱스를 이렇게 구성할 경우 SQL 1의 인덱스 스캔 비효율이 매우 안 좋아진다.

✍️ 54번 : 가장 안 좋은 컬럼 구성

가장 안 좋은 컬럼 구성 고르기

SELECT 고객번호, 고객명, 등록일자, 전화번호, 고객등급
FROM 고객
WHERE 등록일자 BETWEEN :DT1 AND :DT2
AND 등록지점 = "마포’
AND 성별 = '남자' ;

1. ① 성별 + 등록지점 + 등록일자 👉 좋음
2. ② 등록지점 + 성별 + 등록일자 👉 좋음
3. ③ 등록지점 + 등록일자 + 성별 👉 쏘쏘
4. ④ 성별 + 등록일자 + 등록지점 👉 안좋음 (필터 대상 데이터 많음)

🍋 기출 포인트

1. 등록지점보다 성별의 선택도(Selectivity)가 더 높으므로 ③번보다 ④번의
   인덱스 스캔량이 더 많다.

✍️ 55번 : 단 하나의 인덱스를 설계할 때, 최적 컬럼 구성

▶ 총 고객수 = 100만 명
▶ 월 등록고객수 = 2만 명
▶ 전화번호가 '02'로 시작하는 고객수 = 50만 명

SELECT 고객번호, 고객명, 등록쇼핑몰, 등록일자, 전화번호, 고객등급
FROM 고객
WHERE 등록일자 >= ADD_MONTHS(SYSDATE, -1)
AND 등록쇼핑몰 IN ('ACT', 'CPN' )
AND 전화번호 LIKE '02%' ;

단 하나의 인덱스를 설계할 때, 최적 컬럼 구성

1. ③ 등록쇼핑몰 + 등록일자 + 전화번호 👉 ⭕️

🍋 기출 포인트

1. 등록일자가 선두이면 2만개가 필터대상
2. 전화번호가 선두이면 50만개가 필터대상
3. 등록쇼핑몰이 선두이면 in-list 를 쓴다는 가정하에 등록 쇼핑몰 선택도에 따라 효율성이 달라질듯
4. 인덱스를 설계할 때 '=' 조건을 앞쪽에 두어야 하는데 현재로선 '=' 조건이 없다. 다행히 IN 조건은 IN-List Iterator 방식으로 풀면 '=' 조건으로 처리할 수 있다.

SELECT 고객번호, 고객명, 등록쇼핑몰, 등록일자, 전화번호, 고객등급
FROM 고객
WHERE 등록일자 >= ADD_MONTHS (SYSDATE, -1)
AND 등록쇼핑몰 = 'ACT'
AND 전화번호 LIKE '02%'
UNION ALL
SELECT 고객번호, 고객명, 등록쇼핑몰, 등록일자, 전화번호, 고객등급
FROM 고객
WHERE 등록일자 >= ADD_MONTHS(SYSDATE, -1)
AND 등록쇼핑몰 = 'CPN'
AND 전화번호 LIKE '02' ; 

1. 둘 중 선택도(Selectivity)가 낮은(=변별력이 좋은) 컬럼을 선택해야 한다.
   전화번호가 '02'로 시작하는 고객의 선택도는 50%이고 최근 한달 이내 등록 고객의 선택도
   는 2%이므로 「등록쇼핑몰 =, 전화번호 LIKE」 조건보다 「등록쇼핑몰 =, 등록일자 」 조
   건의 선택도가 더 낮다.

🍒 문제 해설

1. 등록일자와 전화번호는 둘 다 범위검색 조건이므로 둘 중 하나를 선택하는 순간, 그 컬럼까지는 인덱스 액세스 조건으로 사용되고 나머지는 필터 조건으로 사용된다.

✍️ 56번 : 최적의 인덱스 구성방안

< SQL 1 >
SELECT *
FROM 거래
WHERE 고객번호 = cust_no
AND 거래일자 BETWEEN ADD_MONTHS(SYSDATE, -3) AND SYSDATE ;

< SQL 2 >
SELECT *
FROM 거래
WHERE 고객번호 = :cust_no
AND 상품번호 = :prd_no
AND 거래일자 BETWEEN ADD_MONTHS(SYSDATE, -3) AND SYSDATE ;

< SQL 3 >
SELECT *
FROM 거래
WHERE 거래일자 BETWEEN ADD_MONTHS(SYSDATE, -3) AND SYSDATE ;

세 개 SQL 모두 인덱스를 효과적으로 사용하도록 설계하고자 할 때 최적의 인덱스 구성방안

(테이블은 파티션하지 않은 상태이며, 인덱스 스캔 효율이나 테이블 랜덤 액세스 발생량
에 큰 차이가 없다면, 인덱스 개수를 최소화해야 함)

1. ⭕️

   ③ 거래_X1 : 고객번호 + 거래일자 + 상품번호
   거래_X2 : 거래일자

2. ❌

     ② 거래_X1 : 고객번호 + 상품번호 + 거래일자
     거래_X2 : 거래일자

3. ❌

   ① 거래_X1 : 고객번호 + 거래일자
   거래_X2 : 고객번호 + 상품번호 + 거래일자
   거래_X3 : 거래일자

4. ❌

   ④ 거래_X1 : 거래일자 + 고객번호 + 상품번호

🍋 기출 포인트

1. ③ SQL 1을 위해서는 거래_X1 인덱스가 최적이고, SQL3 을 위해서는 거래_X2 인덱스가 최적이다. SQL 2를 위해서 거래_X1 인덱스를 사용하면 상품번호가 필터 조건이므로 스캔 비효율이 존재한다.하지만 지난 3개월 간 사용자가매일 한 번씩 주문하는 단골 고객이라고 해도 3개월이면 90여 건에 불과하고, 이는 인덱스 리프 블록 하나에 담을 수 있는 양이므로 인덱스를 고객번호 + 상품번호 + 거래일자 」로 따로 구성할 때와 비교해 큰 성능 차이는 없다.

🍒 문제 해설

1. ① SQL 별로 최적 인덱스를 설계하면 조회 성능은 좋지만, 저장 성능이 나빠진다.
2. ② SQL 2와 SQL 3은 최적이지만, SQL 1에 비효율이 발생한다. 특정 고객의 3개월 치 거래만 조회하고 싶은데, 인덱스에서 특정 고객의 과거 모든 거래를 스캔하면서 거래일자를 필터링하기 때문이다.
3. ④ 선두컬럼인 거래일자만 인덱스 액세스 조건이고, 고객번호, 상품번호는 항상 필터 조건이므로 SQL 1과 SQL 2에 대해 스캔 비효율이 발생한다.

✍️ 57번 : 올바른 인덱스 후보군

올바른 인덱스 후보군

1. ② 조직별판매집계_X1, 조직별판매집계_X2 👉 ⭕️

🍋 기출 포인트

1. 조건절 컬럼을 인덱스가 모두 포함한다면 INDEX RANGE SCAN 후에 얻은 결과 건수(100)와 테이블 액세스 후에 얻은 결과 건수(50)가 다를 수 없다.
2. SQL에 사용된 컬럼을 인덱스가 모두 포함한다면 실행계획 세 번째 라인의 TABLE ACCESS 오퍼레이션이 생략된다.

✍️58번 : 올바른 인덱스 후보군

올바른 인덱스 후보군

1. EMP_X2, EMP_X5 👉 ⭕️

🍋 기출 포인트

1. 실행계획에 SORT가 없다.
2. EMPX1 인덱스를 사용하면 소트 연산을 생략할 수 있지만, 인덱스 선두 컬럼이 조건절에 없으므로 _Index Full Scan 하는 실행계획이 나타났을 것이다.
3. EMP_X2와 EMP_X5 인덱스는 '=' 조건인 DEPTNO가 인덱스 선두 컬럼이고 ORDER BY 컬럼인 JOB과 ENAME 컬럼이 바로 뒤에 위치하므로 Index Range Scan 가능하고 소트 생략도 가능하다.

✍️ 59번 : 부분범위 처리 가능하면서도 I/O를 최소화하도록 인덱스 구성

SELECT 계약ID, 청약일자, 입력자ID, 계약상태코드, 보험시작일자, 보험종료일자
FROM 계약
WHERE 취급지점ID = :trt_brch_id
AND 청약일자 >= TRUNC(SYSDATE - 3)
AND 입력일자 >= TRUNC(SYSDATE - 7)
AND 계약상태코드 IN ( :ctr_stat_cd1, :ctr_stat_cd2, :ctr_stat_cd3 )
ORDER BY 청약일자, 입력자ID

부분범위 처리 가능하면서도 I/O를 최소화하도록 인덱스 구성

1. 취급지점ID + 청약일자 + 입력자ID + 계약상태코드 👉 ⭕️

🍋 기출 포인트.

1. '=' 조건은 인덱스 선두컬럼으로 뺀다 => 취급지점ID
2. 부분범위 처리를 위해 선두컬럼 뒤에 차례로 해당 컬럼을 인덱스에 추가한다. => 청약일자, 입력자ID
3. I/O를 최소화하기 위해 계약상태코드 조건도 인덱스에서 필터링하도록 추가한다.

🍒 문제 해설

1. 청약일자 조건을 만족하는 데이터는 대부분 입력일자 조건을 만족할 것이므로 굳이
   인덱스에 추가하지 않아도 된다.

✍️ 60번 : 삭제하더라도 성능에 나쁜 영향을 주지 않는 인덱스

삭제하더라도 성능에 나쁜 영향을 주지 않는 인덱스

1. X4 : 거래일자 + 계좌번호 👉 ⭕️

🍋 기출 포인트

1. X4 인덱스는 「거래일자 BETWEEN」 단일 조건으로 검색하거나, 「거래일자 BETWEEN, 계좌번호 = 조건으로 검색할 때 사용될 것이다. 이 때 전자는 PK 또는 X3 인덱스를 사용하면 되고, 후자는 X1 인덱스를 사용한 것이 훨씬 효과적이다.

🍒 문제 해설

1. PK 인덱스는 삭제할 수 없다.
2. X1 인덱스는 「계좌번호 =」 단일 조건으로 검색하거나, 「계좌번호 =, 거래일자 BETWEEN」 조건으로 검색할 때 사용될 것이다.
3. X2 인덱스는 결제일자 단일 조건으로 검색하거나, 결제일자와 관리지점번호로 검색할 때 사용될 것이다.
4. X3 인덱스는 거래일자 단일 조건으로 검색하거나, 거래일자와 종목코드로 검색할 때 사용될것이다.

✍️ 61번 : 최적 인덱스 설계

- 고객은 10만 명
- 연락처구분으로는 '휴대전화' '집전화', '회사전화', '팩스', '이메일', '기타' 여섯 개 값이 존재
(PK 인덱스 컬럼 순서는 ERD 식별자 순서와 다를 수 있으니, ERD 식별자는 무시
하고 인덱스를 구성할 것)

최적 인덱스 설계

1. 고객 ID + 종료일자 + 시작일자 + 연락처구분 👉 ⭕️

🍋 기출 포인트

1. 결론적으로, 종료일자 + 시작일자 순으로 두 컬럼 모두 인덱스에 포함하는 것이 좋다.

🍒 문제 해설

1. 종료일자 >= :BASE_DT 이 조건절로 최근 시점 이력을 조회할 때는 종료일자 조건을 이용하는 것이 유리하다. 종료일자 >= :BASE_DT 조건을 만족하는 데이터가 소량이기 때문이다.
2. 반대로, 오래된 과거 이력을 조회할 때는 시작일자 조건을 이용하는 것이 유리하다.시작일자 <= :BASE_DT 조건을 만족하는 데이터가 소량이기 때문이다.
3. 과거 데이터 조회할 때를 고려하면 시작일자도 인덱스에 포함해야 한다.