데이터베이스 질문과 답변
기존에 올린 구문만 없어도 성능이 잘 되어서 해당 구문만 올렸는데 그렇게 작성하면
전반적인걸 알 수 없는것 같아서 전체 쿼리를 추가하였습니다.
왜이렇게 성능이 떨어지는지 ㅠㅠ 아시는 분은 조언 부탁 드립니다.
SELECT B.PA_NO
, B.PA_NM
, A.SITE_CD
, (SELECT AA.DEPT_CD FROM TABLE3 AA WHERE AA.DEPT_CD = A.SITE_CD AND AA.WON_YN = 'Y') WON
, B.BIRTH || '/' || DECODE(SEX, 'M','남','F','여',SEX) AGESEX
, (SELECT PB_NO
FROM TABLE1 BB
WHERE PB_NO LIKE 'S%'
AND REPLACE(SUBSTR(MAIN_CON,1,11), '-', '') = 'B 000000001'
AND ROWNUM = 1) ADD_PB_NO
FROM TABLE1 A, TABLE2 B
WHERE A.PB_NO = REPLACE('B 000000001','-','')
AND A.PA_NO = B.PA_NO
안녕하세요
쿼리 튜닝 중에 SELECT 문에서 사용하는 서브쿼리에서
replace(substr(:item, 1,5), 'A', '') = 'B01010' 이라는 WHERE절 조건이 있는데요
이거 때문에 쿼리 전체가 얼마 되지 않는데도 1초 이상으로 성능이 저하되요
저 구문 말고 다르게 사용할 수 있는 구문은 없을까요 ㅠㅠ
쿼리의 극히 일부분만 가지고 튜닝할 수 있을까요?
튜닝을 위한 정보가 너무 부족합니다.
해당 조건은 테이블 컬럼과 연관된 조건도 아니고, 그냥 입력 조건값에 대한 체크일 뿐이구요.
또한 절대로 참이 될 수 없는 조건이네요.
5자리를 잘라서 Replace 까지 하면 5자리보다 더 작아질 수 있는데...그걸 6자리와 비교하네요.
네ㅠㅠ 죄송합니다 너무 서두없이 질문을해서...
다시 올렸습니다 ㅠㅠ
해당 조건을 만족하는 자료를 1건 찾는데 시간이 오래 걸리는 것입니다.
만족하는 건이 적을 수록 찾는데 시간이 오래 걸립니다.
만족하는 건이 없다면? 전체스캔을 하게 되는거구요.
해당 조건이 없다면? 조건과 관계 없이 1건을 금방 찾는거죠.
관건은 main_con 에 인덱스가 존재하고 이를 이용하여 바로 탐색이 가능해야 합니다.
해당 조건은 main_con 을 가공하였으므로 인덱스 검색이 안되구요.
11자리를 잘라서 Replace 를 하면 '-' 포함자료는 어짜피 11자리가 안되어 참일 수 없네요.
--> Replace 불필요
Substr 을 제거하고 LIKE 검색 활용 가능합니다.
--> AND main_con LIKE 'B 000000001' || '%'
아 감사합니다
한가지 더 여쭤봐도 될까요?
계속 저 서브쿼리 쪽에서 count stopkey가 잡히고, table access full 된다고 plan에 표시되는데
이게 속도 저하에 관련이 있나요?
네.
SELECT 절의 서브쿼리는 메인쿼리 결과수만큼 반복 수행되는데...
위의 경우에는 풀스캔하면서 조건을 만족하는 1건을 찾았을 때 스캔을 멈춤니다.
달리 말하면 1건을 찾을때까지 계속 스캔하는거죠.
조건이 적을수록(조건을 만족하는 자료가 많을수록) 1 건을 금방 찾아 끝나는데...
조건이 들어가면서(조건을 만족하는 자료가 적어져서) 1건 찾는데 오래 걸리는거구요.
최악의 경우엔 테이블 전체를 스캔해야 합니다.(만족하는 자료가 없는 경우)
해결방안은 풀스캔이 아닌 인덱스 스캔을 유도해야 합니다.
인덱스 스캔을 이용해 스캔 범위를 줄여주는 거죠.
조건 항목에 인덱스가 있어야 하구요.
현재 아래 서브쿼리에 인덱스는 PB_NO로 걸려있는데요
인덱스 컬럼이 like로 걸려있으면 비효율적이라는 얘기도있고요..
뭐가맞는지 헷갈려요 ㅠ
pb_no 가 'S' 로 시작하는 자료는 엄첨 많을 것으로 예상 됩니다.
반면에 main_con 이 'B 000000001' 으로 시작하는 자료는 적을 것입니다.
== 1번 시나리오 ==
pb_no 의 인덱스를 이용해 'S' 로 시작하는 자료를 검색한 뒤
인덱스에 있는 ROWID 를 이용해 테이블에 접근하고(이를 랜덤엑세스라고 합니다)
테이블에서 가져온 main_con 이 'B 000000001' 으로 시작하는지를 체크합니다.
하지만 체크는 번번히 실패로 끝나고 위의 과정을 성공할때까지 계속 반복합니다.
지나친게 많은 랜덤엑세스는 풀스캔보다 성능이 떨어집니다.
== 2번 시나리오 ==
랜덤엑세스를 줄이기 위해 인덱스를 포기하고 테이블 풀스캔을 합니다.
풀스캔을 하면서 조건을 만족하는 자료를 1건 찾을때까지 계속 스캔합니다.
하지만 큰 이득은 없었던 것으로 나타나네요.
== 3번 시나리오 ==
렌덤엑세스를 줄이기 위한 다른 방안으로
pb_no 의 인덱스에 main_con 을 추가하여 복합인덱스로 구성합니다.
pb_no 의 인덱스를 이용해 'S' 로 시작하는 자료의 스캔 범위는 크지만
인덱스 안에 main_con 이 존재하므로 테이블 랜덤엑세스 없이도 조건 체크가 가능합니다.
테이블 랜덤엑세스를 줄임으로써 성과가 있을 것으로 생각됩니다.
== 4번 시나리오 ==
main_con 이 'B 000000001' 으로 시작하는 자료는 적을 것으로 예상 됩니다.
main_con 에 인덱스를 만들고 LIKE 검색을 한다면?
좋은 성과가 있을 듯 합니다.
인덱스 컬럼이 like로 걸려있으면 비효율적이라는 얘기에 대해서
효율과 비효율은 상대적인 것입니다.
비교 대상이 없이 막연하게 표현한 위의 문구를 그대로 받아들이면 안됩니다.
) 버튼을 클릭하여 작성 하시면 됩니다.