데이터베이스 질문과 답변
by poodle [Oracle Tuning] 튜닝 hint order by index [2018.08.07 17:48:39]
안녕하세요~
조언을 얻고자 글을 적습니다.
데이터를 order by하여 데이터를 가지고 오는데 33초가 걸리네요
order by구문을 빼면은 1초 안에 나오는데요... 그래서 이것을 힌트나 다른 방법으로 빠르게 나오게 하고자 합니다.
지금 문제가 되는 부분이 ORDER BY s.ymd desc,s.unt,s.seq desc
asc와 desc가 같이 있어서 어찌 해야 할 지 모르겠습니다.
힌트를 asc,desc같이 쓸 수 있는지 모르겠지만 desc 힌트 테스트 하려고 /*+index_desc(s MST_IDX_01)*/ 이렇게 사용을 해 보았는데
m.D_YMD>='20180101' and m.d_ymd<='20180631' 구문이 있어서 제대로 소팅도 안되네요 ;;
그리고 저 join문 안에 또 DB LINK테이블이 있어서 힌트 사용에 영향이 있지 않을지 모르겠어요
질문 1. 한 테이블에 힌트를 여러개 쓸 수 있나요???? ASC와 DESC
질문 2. 힌트를 썼을때 부등호m.D_YMD>='20180101' 이 구문 때문에 제대로 적용이 안되는데 해결 방법이 있을까요? between을써도 그러네요(order by가 적용안됨)
질문 3. 힌트를 여러개 동시에 쓰지 못한다면????? 어떤방법으로 튜닝을 할 수 있을까요? ㅜ_ㅜ
masterTB_IDX_06 => yy,ymd,unt,seq
고수님들의 소중한 답변 부탁 드립니다.
select /*+index_desc(s masterTB_IDX_06 )*/
s.yy,s.seq,TO_CHAR(TO_DATE(ymd,'YYYYMMDD'),'YYYY-MM-DD') AS ymd,amt,s.unt
FROM masterTB s
LEFT OUTER JOIN
(SELECT sum(amt) amt, yy, seq, unt
FROM detailTB
WHERE yy ='2018' AND yn = 'Y' AND ymd >= '20180101' AND ymd <= '20180630'
group by yy, seq, unt) j ON s.unt= j.unt AND s.seq=j.seq and s.yy=s.yy
LEFT OUTER JOIN (
SELECT cd, nm FROM table1
UNION
SELECT cd, nm FROM dblink_tb
) j on j.cd = s.cd
WHERE s.yy = '2018' /**P*/
AND s.ymd >= '20180101' /**P*/
AND s.ymd <= '20180630'
and s.yn='Y'
--ORDER BY s.ymd desc,s.unt asc ,s.seq desc
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 58 | 13 (8)| 00:00:01 | | |
| 1 | NESTED LOOPS OUTER | | 1 | 58 | 13 (8)| 00:00:01 | | |
|* 2 | HASH JOIN OUTER | | 1 | 43 | 13 (8)| 00:00:01 | | |
|* 3 | TABLE ACCESS BY INDEX ROWID | masterTB | 1 | 26 | 6 (0)| 00:00:01 | | |
|* 4 | INDEX RANGE SCAN DESCENDING | masterTB_IDX_06 | 5 | | 3 (0)| 00:00:01 | | |
| 5 | VIEW | | 1 | 17 | 7 (15)| 00:00:01 | | |
| 6 | HASH GROUP BY | | 1 | 28 | 7 (15)| 00:00:01 | | |
|* 7 | TABLE ACCESS BY INDEX ROWID| detailTB | 1 | 28 | 6 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | IDX_detailTB_IDX_02 | 91 | | 1 (0)| 00:00:01 | | |
| 9 | VIEW | | 1 | 15 | 0 (0)| 00:00:01 | | |
| 10 | SORT UNIQUE | | | | | | | |
| 11 | UNION-ALL PARTITION | | | | | | | |
| 12 | TABLE ACCESS BY INDEX ROWID| table1 | 1 | 45 | 2 (0)| 00:00:01 | | |
|* 13 | INDEX UNIQUE SCAN | SYS_C009084 | 1 | | 1 (0)| 00:00:01 | | |
| 14 | REMOTE | dblink_tb | 1 | 90 | 2 (0)| 00:00:01 | L_MATS | R->S |
----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."seq"="J"."seq"(+) AND "S"."unt"="J"."unt"(+))
filter("S"."yy"=CASE WHEN ("J"."unt"(+) IS NOT NULL) THEN "S"."yy" ELSE "S"."yy" END
AND CASE WHEN "J"."unt"(+) IS NOT NULL THEN "S"."yy" ELSE "S"."yy" END ='2018')
3 - filter("S"."yn"='Y')
4 - access("S"."yy"='2018' AND "S"."ymd">='20180101' AND "S"."ymd" IS NOT NULL)
7 - filter("ymd">='20180101' AND "yn"='Y' AND "ymd"<='20180630')
8 - access("yy"='2018')
13 - access("cd"="S"."cd")
Remote SQL Information (identified by operation id):
----------------------------------------------------
14 - SELECT /*+ INDEX ("dblink_tb") */ "cd","nm" FROM "dblink_tb" "dblink_tb" WHERE "cd"=:1
(accessing 'dblink' )
DBLINK 사용하시나요?
INDEX 정보도 올려주세요.
네 db link 사용해요 masterTB_IDX_06 yy,asc ymd asc, unt asc, seq asc
인덱스를 masterTB_IDX_06=> yy, ymd dasc, unt,unt asc, seq desc로 생성하고 /*+index(s masterTB_IDX_06)*/ 로 써보았는데 부등호 때문에 소팅이 엉망이 되더라구요
다행히 한 테이블에 있는 컬럼으로만 정렬을 해서
조인후 정렬->정렬 후 조인 으로 변경해서 해결을 했는데
위에 질문했던 부분에 대해서는 검색능력이 부족한지 의문으로 남아 있어요... 힌트 쓸때 부등호나 between이 있으면 소팅힌트가 제대로 적용이 안되는지 궁금해요~ 답변 부탁드려요~
쿼리에 오타가 있어서 몇군데 수정했고,
아웃터는 결과와 상관 없기 때문에 Inlivew View 로 변경했습니다.
질문 1 : 힌트는 여러개 사용할수 있으나 ASC와 DESC는 한번밖에 안되는 걸로 알고 있습니다.
질문 2 : 적용이 안된다는게 정렬이 안된다는 말씀 이신가요?
질문 3 : 아래 처럼 수정했습니다.
아래 쿼리 결과 어떻게 나오나요?
SELECT /*+ LEADING(S J K) USE_NL(S J K) */
*
FROM (SELECT /*+ INDEX_DESC(S MASTERTB_IDX_06) */
S.YY,
S.SEQ,
TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'YYYY-MM-DD') AS YMD
FROM MASTERTB S
WHERE S.YY = '2018'
AND S.YMD >= '20180101'
AND S.YMD <= '20180130'
AND S.YN = 'Y'
-- ORDER BY S.YMD DESC,S.UNT,S.SEQ DESC
) S,
(SELECT SUM(AMT) AMT,
YY,
SEQ,
UNT
FROM DETAILTB
WHERE YY ='2018'
AND YN = 'Y'
AND YMD >= '20180101'
AND YMD <= '20180630'
GROUP BY YY,
SEQ,
UNT
) J,
(SELECT CD, NM FROM TABLE1
UNION
SELECT CD, NM FROM DBLINK_TB
) K
WHERE S.UNT= J.UNT(+)
AND S.SEQ = J.SEQ(+)
AND S.YY = J.YY(+)
AND S.CD = K.CD(+)
잠만보님..
혹시 /*+ INDEX_DESC(S MASTERTB_IDX_06) */ 빼고
그냥 order by 처리하는게 낫지 않을까요?
이상하게 INDEX_DESC는 쓰기 꺼려지더 라구요..
나중에 인덱스 수정하면 이상하게 탈거 같고..
답변 감사 드려요~~~ 많은 도움 받고 가요
오늘도 좋은 하루 보내세요~
정렬은 힌트 이용보다는 order by 를 이용하는게 좋습니다.
order by 를 이용하더라도 인덱스를 이용해 정렬이 필요없다고 판단된다면 그렇게 동작됩니다.
정렬이 필요하다고 판단된다면 정렬을 할 것입니다.
DB링크 사용과 UNION 사용이 꺼림직해 보입니다. (둘다 성능에 취약한 부분입니다.)
인라인뷰의 알리아스를 동일하게 j 로 준것도 눈에 거슬리네요.
말씀해주시는 정보와 실행계획이 일치하지 않네요?
masterTB_IDX_06 이 (ymd, unt, seq) 인데
실행계획의 4번 라인의 access 조건엔 yy 가 있네요?
정보를 정확하게 표현해 주셔야 합니다.
Select 절에서 조회하는 항목도 조인된 값을 전혀 이용하지 않고 있구요?
이러면 조인할 이유가 없죠.
정보를 정확하게 표현해 주셔야 합니다.
아우터 조인도 성능 취약 부분입니다.
아우터 조인이 꼭 필요한지 다시 한번 검토해 보시구요.
위,아래(master/detail)의 기간 조건이 상이한데 이것도 맞는지 의문이네요?
질문 작성하면서 테스트를 같이 하다보니 섞여 있네요
정확히 yy,ymd,unt,seq 로 인덱스 생성이 되어 있고
실제 쿼리에서 테이블명칭 컬럼명칭을 바꾸고 보기쉽게 줄이다보니 컬럼명에 들어가지 않은 명칭도 있구요
날짜도 속도가 느리다보니 mst만 기간을 줄여놨네요 ;;
DB LINK는 어쩔 수 없이 사용을 해야 되는 부분이라... 어쩔수 없을 것 같고
union이 성능에 취약하다는게 어떤 뜻인지요?
master에 대한 정보가 금액정보가 detail에 1:다로 등록이 되어 있기 때문에
아우터조인을 할 수 밖에 없는 상황입니다.
) 버튼을 클릭하여 작성 하시면 됩니다.