데이터베이스 질문과 답변

셀렉트시 서브쿼리에서 카운트 성능 저하 문제입니다. 0 11 7,070

by 콩이 성능 테이블구조 [2012.11.07 17:05:42]

실행계획.JPG (37,657Bytes)
안녕하세요. 정말 많은 도움을 얻고 있어 감사드립니다.
이번에 처한 제 문제는 스스로 해결을 해보아도 잘 안풀려서 또 올려봅니다.

테이블 구조는 아래와 같습니다.
'그룹' 테이블
번호 | 그룹명 | 등록자ID | 날짜
'그룹상세' 테이블
번호 | 부모번호 | 성명 | 핸드폰 | 이메일 | 팩스 | 등록자ID | 날짜

'그룹' 테이블은 T_ADDR_GROUP
'그룹상세' 테이블은 T_ADDR_DETAIL 입니다.

인덱스 정보는 아래와 같습니다.
T_ADDR_GROUP (NO) *PK
T_ADDR_GROUP (NAME, NO)
T_ADDR_DETAIL (NO) *PK
T_ADDR_DETAIL (USER_ID, NO, PARENT_NO)

사용한 쿼리는 아래와 같습니다.
   
SELECT * FROM (
SELECT ROWNUM AS RNUM, A.*, (SELECT COUNT(B.NO) FROM T_ADDR_DETAIL B WHERE B.PARENT_NO = A.NO) AS GROUP_NAME FROM (
SELECT * FROM T_ADDR_GROUP A WHERE USER_ID = 'lee9982'
ORDER BY A.NAME ASC
) A WHERE ROWNUM <=10
) WHERE RNUM >= 1

그룹 상세 테이블에 데이터는 약 500만건 입니다. 운영시 예상되는 데이터는 2000~3000만건 이구요..
문제는 그룹 리스트에서 그룹에 속한 그룹수를 보여주어야 하는데, 서브 쿼리에서 카운트를 해보았는데
카운트를 하면 5~6초 이상이 걸립니다. 카운트를 빼면 뭐 데이터가 별로 없어 1초도 안걸리지만요..

상세 정보를 입력시/삭제시 마다 그룹 테이블에 카운트를 UP/DOWN 시키는게 좋을까요? 아니면
다른 좋은 방법이 있을까요?

실행계획은 아래와 같습니다.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|Operation                                 |        Object Name        | Rows  | Bytes | Cost | Object Node | In/Out | PStart | PStop | Access Predicates |  Filter Predicates  |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT Optimizer Mode=ALL_ROWS |                           |     9 |   2 K |    4 |             |        |        |       |                   |                     |
|  SORT AGGREGATE                          |                           |     1 |     3 |      |             |        |        |       |                   |                     |
|   INDEX FAST FULL SCAN                  IN_T_ADDR_DETAIL01  | 438 K |   1 M | 5210 |             |        |        |       |                   | "B"."PARENT_NO"=:B1 |
|  VIEW                                    |                           |     9 |   2 K |    4 |             |        |        |       |                   |           "RNUM">=1 |
|   COUNT STOPKEY                          |                           |       |       |      |             |        |        |       |                   |          ROWNUM<=10 |
|    VIEW                                  |                           |     9 |   1 K |    4 |             |        |        |       |                   |                     |
|     SORT ORDER BY STOPKEY                |                           |     9 |   360 |    4 |             |        |        |       |                   |          ROWNUM<=10 |
|      TABLE ACCESS FULL                  T_ADDR_GROUP        |     9 |   360 |    3 |             |        |        |       |                   | "USER_ID"='lee9982' |
by 마농 [2012.11.07 17:19:16]

쿼리가 뭔가 이상하네요. 지금까지의 설명과 다른데요?
user_id 는 상세 테이블에 있는줄로 알았는데... 그룹테이블 조회조건으로 들어가 있네요?
카운트 쿼리에서 인덱스 효과를 얻으려면 user_id 조건이 있어야 하는데??? 없네요.
b.parent_no 만으로 된 인덱스는 없으니 풀스캔 하느라 오래 걸리지요.
user_id 가 양쪽에 모두 있는 건가요?
양쪽에 모두 있다면 user_id 로 조인해야 하는게 아닌지요?

by 콩이 [2012.11.07 17:29:58]
설명 된것처럼

'그룹' 테이블
번호 | 그룹명 | 등록자ID | 날짜
'그룹상세' 테이블
번호 | 부모번호 | 성명 | 핸드폰 | 이메일 | 팩스 | 등록자ID | 날짜

와 같은 구조구요 등록자ID가 USER_ID 입니다.
by 콩이 [2012.11.07 17:33:06]
아.. 참고로 위에 쿼리는 별도로 그룹리스트에서 보여지는 부분입니다.
기존까지 문의드린 부분은 개인별 상세리스트였습니다.^^
by 마농 [2012.11.07 17:34:47]

user_id 가 조인 조건으로 들어가야 하는지? 아닌지? 에 따라 또 다르겠지요.
user_id 가 조인 조건으로 들어가야 한다면... 지금까지 조안했던거 다시 생각해 봐야 할듯 하네요.
이전에 user_id 조건이 b 에만 주는 조건인줄 알고 답변 드린거구요.
a 에 주는 조건이라면 이야기는 전혀 달라지네요....

by 콩이 [2012.11.07 17:39:25]
아...

일단 그룹테이블에서 USER_ID가 있어서 그룹만 보여주기 위한 테이블이구요
그룹상세 테이블에는 각 연락처가 들어있는데요, 여기에도 USER_ID가 있습니다.
왜냐하면 개별 연락처에서 USER_ID로 잡아서 보여주어야 할 페이지가 있어서 입니다.

그리고 현재 페이지에 질문 드린 부분은 그룹리스트 즉 내가만든 그룹들만 보여주는 페이지입니다.
거기에서 그룹명만 보여주지 않고 그룹에 등록된 상세 개별 연락처의 수(COUNT)도 보여주어야 하는
문제 때문에 느려지고 있습니다.

말씀해주신건 그룹과 상세를 조인해서 카운트 하란 말씀이신가요?
by 마농 [2012.11.07 17:46:20]

조인을 할지 말지를 판단해서 알려달라는 거였구요...
조인을 해야 하는지 저에게 물어볼게 아니구요.^^a... 직접 판단하셔야 할 문제입니다.
제 판단으로는 위 설명을 들어보면 조인을 하면 안될 것 같네요.
T_ADDR_DETAIL (PARENT_NO) 인덱스가 있어야 해결될 문제입니다.

그런데 쿼리 하나 느려질때마다 인덱스 추가 생성한다면...인덱스만 중구난방 관리가 안되겠지요.
사용되는 쿼리(요구사항)들을 모두 정리하고
해당 쿼리에서 요구되는 엑세스 패턴을 분석하고
엑세스 패턴별 최적의 인덱스를 모두 산정한뒤
중요도에 따라 버릴 것과 합칠 것을 선택하셔야 합니다.

by 마농 [2012.11.07 17:48:41]

좀. 어려울지 모르겠지만... 한번 읽어 보세요.
http://wiki.gurubee.net/pages/viewpage.action?pageId=12189750

by 부쉬맨 [2012.11.07 18:02:47]
결국문제는
보아하니깐
detail 건수를 가져오는 부분에서 조인을하는데
실제로보면 건수는 그렇게 적지않은 플랜으로만 보면 9건정도나오네요

하지만 실제 order by 로 인하여 table 풀 스캔 하게되어서

detail도 9건만 조인해서 가져와야되는데
풀테이블을 읽은 상태로 조인이 되어서 문제가 발생되는것으로 보여집니다.

orderby 부분을 일단 밖으로 빼고 스칼라 서브쿼리부분 가운터 를 일단 계산후에
orderby 하시는 방향으로 선회하시는게 좋아보이네요.
by 마농 [2012.11.07 18:29:37]

헉~ 부쉬맨~ 그건 아니지...
페이징 쿼리 후 스칼라서브쿼리 9번만 수행하는걸
스칼라서브쿼리부터 수행하고 페이징 하면 서브쿼리가 전체건수만큼 반복 수행되지...
거꾸로 설명하고 있음...

by 부쉬맨 [2012.11.07 19:14:28]
그러게요
결론은 rownum과 order by 부분을 뺀상태에서
결과값을 받은다음에 작업을처리를...
by 아발란체 [2012.11.08 08:42:52]

대단하시다.. 인터넷 공황장애로 이렇게 긴 내용들은 보기가 힘든데 분석하여 답까지 다시공..

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입