"분석 함수 튜닝"의 두 판 사이의 차이
DB CAFE
(→그룹 분석함수 NO-SORT) |
(→그룹 분석함수 NO-SORT) |
||
(같은 사용자의 중간 판 15개는 보이지 않습니다) | |||
3번째 줄: | 3번째 줄: | ||
=== min/max 함수를 Ranking 함수 변경 === | === min/max 함수를 Ranking 함수 변경 === | ||
− | 최종일자에 해당하는 데이터를 구할 때, MAX(최종일자) 분석함수를 사용하지 말고 Ranking 분석 함수 사용. | + | # 최종일자에 해당하는 데이터를 구할 때, MAX(최종일자) 분석함수를 사용하지 말고 Ranking 분석 함수 사용. |
− | WINDOW BUFFER를 WINDOW NOSORT로 바꾸어 SORTING 부하를 줄여 줌. | + | # WINDOW BUFFER를 WINDOW NOSORT로 바꾸어 SORTING 부하를 줄여 줌. |
+ | <source lang=sql> | ||
+ | SELECT /*+ LEADING(A) USE_NL(B) */ A.*, B.AMOUNT_SOLD | ||
+ | FROM ( SELECT /*+ INDEX_DESC(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, | ||
+ | RANK() OVER(PARTITION BY CUST_ID, CHANNEL_ID ORDER BY TIME_ID DESC) RNK, | ||
+ | ROWID AS RID | ||
+ | FROM SALES_T T | ||
+ | WHERE PROD_ID = 30 ) A, | ||
+ | SALES_T B | ||
+ | WHERE A.RNK = 1 | ||
+ | AND A.RID = B.ROWID; | ||
+ | </source> | ||
− | |||
=== ORDER BY 절에 NULL FIRST나 LAST를 삭제 검토 === | === ORDER BY 절에 NULL FIRST나 LAST를 삭제 검토 === | ||
− | 분석함수의 ORDER BY 절에 NULL FIRST나 LAST를 삭제할 수 있는지 검토. | + | # 분석함수의 ORDER BY 절에 NULL FIRST나 LAST를 삭제할 수 있는지 검토. |
− | 인덱스 사용에 의한 Sort 유지 되도록 하여 추가적인 sort를 방지. | + | #인덱스 사용에 의한 Sort 유지 되도록 하여 추가적인 sort를 방지. |
+ | {{알림 | ||
+ | |배경색 = #f7d4063e | ||
+ | |선굵기 = 3px | ||
+ | |테두리색 = #de5a5a | ||
+ | |사이즈 = 100% | ||
+ | |둥굴기 = 4px | ||
+ | |스타일 = dashed | ||
+ | |내용 = * 주의사항 | ||
+ | * 1) Index ASC로 사용 + 분석함수의 ORDER BY절에 NULL FIRST 는 추가적인 sort발생 | ||
+ | * 2) Index DESC로 사용 + 분석함수의 ORDER BY절에 NULL LAST 는 추가적인 sort발생 | ||
− | + | }} | |
− | |||
− | |||
=== 여러 개의 분석함수를 하나로 통합 === | === 여러 개의 분석함수를 하나로 통합 === | ||
− | 분석함수를 여러 개 사용할 때, 가능하면 OVER절의 Partition By와 Order By절을 일치 하도록 검토. | + | # 분석함수를 여러 개 사용할 때, 가능하면 OVER절의 Partition By와 Order By절을 일치 하도록 검토. |
− | 분석함수를 하나만 실행하는 효과를 얻어서 실행시간을 단축하도록 검토 . | + | # 분석함수를 하나만 실행하는 효과를 얻어서 실행시간을 단축하도록 검토 . 이때 ORDER BY는 완전히 같지 않아도 Operation은 통합 가능 |
− | 이때 ORDER BY는 완전히 같지 않아도 Operation은 통합 가능 | ||
− | |||
− | |||
− | |||
− | |||
− | + | {{알림 | |
+ | |배경색 = #f7d4063e | ||
+ | |선굵기 = 3px | ||
+ | |테두리색 = #de5a5a | ||
+ | |사이즈 = 100% | ||
+ | |둥굴기 = 4px | ||
+ | |스타일 = dashed | ||
+ | |내용 = * 주의사항 | ||
+ | * 1) WINDOW NOSORT + WINDOW SORT => WINDOW SORT | ||
+ | * 2) WINDOW NOSORT + WINDOW BUFFER => WINDOW BUFFER | ||
+ | * 3) WINDOW BUFFER + WINDOW SORT => WINDOW SORT | ||
+ | }} | ||
=== Ranking 분석함수 이용 TOP SQL의 Sort 최소화 === | === Ranking 분석함수 이용 TOP SQL의 Sort 최소화 === | ||
− | Ranking 분석함수를 인라인뷰 외부에서 Rownum 처럼 Filter로 사용 시 불필요한 Partition By 절 삭제 검토 . | + | # Ranking 분석함수를 인라인뷰 외부에서 Rownum 처럼 Filter로 사용 시 불필요한 Partition By 절 삭제 검토 . |
− | + | ## FULL SCAN을 하거나 혹은 OVER절의 NULL FIRST나 LAST등의 원인으로 sort가 발생될 때 . | |
− | + | ### Rownum과는 달리 STOPKEY가 발생되지 않으므로 비효율은 존재. | |
− | + | ### 이 때 발생되는 오퍼레이션 은 WINDOW SORT PUSHED RANK | |
+ | ## 적절한 인덱스가 있어서 sort가 발생되지 않는 경우, Rownum 처럼 STOPKEY를 발생시켜 부분범위 처리의 효과. | ||
+ | ### 분석함수를 Rownum 처럼 사용. WINDOW NOSORT STOPKEY 오퍼레이션 발생. | ||
− | + | {{알림 | |
− | + | |배경색 = #f7d4063e | |
+ | |선굵기 = 3px | ||
+ | |테두리색 = #de5a5a | ||
+ | |사이즈 = 100% | ||
+ | |둥굴기 = 4px | ||
+ | |스타일 = dashed | ||
+ | |내용 = * 주의사항 | ||
+ | * 1) Partition By절 전체를 제거해야 한다는 것. | ||
+ | *: 만약 Partition By절에 컬럼이 하나라도 있으면 Sort가 대량으로 발생. | ||
− | + | }} | |
− | |||
=== 그룹 분석함수 NO-SORT === | === 그룹 분석함수 NO-SORT === | ||
− | Sort를 방지하고, STOPKEY 작동, sum/min/max/avg등의 Group 분석함수를 사용할 경우 명시적으로 WINDOW의 범위를 지정. | + | # Sort를 방지하고, STOPKEY 작동, sum/min/max/avg등의 Group 분석함수를 사용할 경우 명시적으로 WINDOW의 범위를 지정. |
− | WINDOW BUFFER Operation을 WINDOW NOSORT로 바꾸어 불필요한 sort를 방지.(현재 row 까지의 누적집계 시 ) | + | # WINDOW BUFFER Operation을 WINDOW NOSORT로 바꾸어 불필요한 sort를 방지.(현재 row 까지의 누적집계 시 ) |
+ | * 예시) | ||
+ | <source lang=sql> | ||
+ | SUM(AMOUNT_SOLD) OVER(ORDER BY CUST_ID,CHANNEL_ID,TIME_ID | ||
+ | ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | ||
+ | ) | ||
+ | </source> | ||
− | |||
− | + | {{알림 | |
− | + | |배경색 = #f7d4063e | |
+ | |선굵기 = 3px | ||
+ | |테두리색 = #de5a5a | ||
+ | |사이즈 = 100% | ||
+ | |둥굴기 = 4px | ||
+ | |스타일 = dashed | ||
+ | |내용 = * 주의사항 | ||
+ | * 1) Ranking 함수를 사용하여 WINDOW STOPKEY가 발생하는 경우는 SORT가 발생하지 않는 경우(NOSORT)뿐. | ||
+ | *: 그룹분석함수에 의해서 추가적인 SORT를 해야 한다면, 전체범위로 처리됨으로 STOPKEY가 발생하지 않음. | ||
+ | }} | ||
+ | [[category:oracle]] |
2023년 10월 13일 (금) 14:24 기준 최신판
thumb_up 추천메뉴 바로가기
- DBA { Oracle DBA 명령어 > DBA 초급 과정 > DBA 고급 과정 }
- 튜닝 { 오라클 튜닝 목록 }
- 모델링 { 데이터 모델링 가이드 }
목차
1 분석함수의 튜닝 고려사항[편집]
1.1 min/max 함수를 Ranking 함수 변경[편집]
- 최종일자에 해당하는 데이터를 구할 때, MAX(최종일자) 분석함수를 사용하지 말고 Ranking 분석 함수 사용.
- WINDOW BUFFER를 WINDOW NOSORT로 바꾸어 SORTING 부하를 줄여 줌.
SELECT /*+ LEADING(A) USE_NL(B) */ A.*, B.AMOUNT_SOLD
FROM ( SELECT /*+ INDEX_DESC(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID,
RANK() OVER(PARTITION BY CUST_ID, CHANNEL_ID ORDER BY TIME_ID DESC) RNK,
ROWID AS RID
FROM SALES_T T
WHERE PROD_ID = 30 ) A,
SALES_T B
WHERE A.RNK = 1
AND A.RID = B.ROWID;
1.2 ORDER BY 절에 NULL FIRST나 LAST를 삭제 검토[편집]
- 분석함수의 ORDER BY 절에 NULL FIRST나 LAST를 삭제할 수 있는지 검토.
- 인덱스 사용에 의한 Sort 유지 되도록 하여 추가적인 sort를 방지.
assignment * 주의사항
- 1) Index ASC로 사용 + 분석함수의 ORDER BY절에 NULL FIRST 는 추가적인 sort발생
- 2) Index DESC로 사용 + 분석함수의 ORDER BY절에 NULL LAST 는 추가적인 sort발생
1.3 여러 개의 분석함수를 하나로 통합[편집]
- 분석함수를 여러 개 사용할 때, 가능하면 OVER절의 Partition By와 Order By절을 일치 하도록 검토.
- 분석함수를 하나만 실행하는 효과를 얻어서 실행시간을 단축하도록 검토 . 이때 ORDER BY는 완전히 같지 않아도 Operation은 통합 가능
assignment * 주의사항
- 1) WINDOW NOSORT + WINDOW SORT => WINDOW SORT
- 2) WINDOW NOSORT + WINDOW BUFFER => WINDOW BUFFER
- 3) WINDOW BUFFER + WINDOW SORT => WINDOW SORT
1.4 Ranking 분석함수 이용 TOP SQL의 Sort 최소화[편집]
- Ranking 분석함수를 인라인뷰 외부에서 Rownum 처럼 Filter로 사용 시 불필요한 Partition By 절 삭제 검토 .
- FULL SCAN을 하거나 혹은 OVER절의 NULL FIRST나 LAST등의 원인으로 sort가 발생될 때 .
- Rownum과는 달리 STOPKEY가 발생되지 않으므로 비효율은 존재.
- 이 때 발생되는 오퍼레이션 은 WINDOW SORT PUSHED RANK
- 적절한 인덱스가 있어서 sort가 발생되지 않는 경우, Rownum 처럼 STOPKEY를 발생시켜 부분범위 처리의 효과.
- 분석함수를 Rownum 처럼 사용. WINDOW NOSORT STOPKEY 오퍼레이션 발생.
- FULL SCAN을 하거나 혹은 OVER절의 NULL FIRST나 LAST등의 원인으로 sort가 발생될 때 .
assignment * 주의사항
- 1) Partition By절 전체를 제거해야 한다는 것.
- 만약 Partition By절에 컬럼이 하나라도 있으면 Sort가 대량으로 발생.
1.5 그룹 분석함수 NO-SORT[편집]
- Sort를 방지하고, STOPKEY 작동, sum/min/max/avg등의 Group 분석함수를 사용할 경우 명시적으로 WINDOW의 범위를 지정.
- WINDOW BUFFER Operation을 WINDOW NOSORT로 바꾸어 불필요한 sort를 방지.(현재 row 까지의 누적집계 시 )
- 예시)
SUM(AMOUNT_SOLD) OVER(ORDER BY CUST_ID,CHANNEL_ID,TIME_ID
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
assignment * 주의사항
- 1) Ranking 함수를 사용하여 WINDOW STOPKEY가 발생하는 경우는 SORT가 발생하지 않는 경우(NOSORT)뿐.
- 그룹분석함수에 의해서 추가적인 SORT를 해야 한다면, 전체범위로 처리됨으로 STOPKEY가 발생하지 않음.