안녕하세요! 늘 많은 정보 잘 얻어가고 있습니다!
오늘은 정말 풀리지 않는 문제가 있어 질문을 남깁니다.
계층구조의 테이블이 하나 있습니다.
최하위 데이터를 그 상위로 업데이트를 하고 또한번 상위로 업데이트를 하여 최상위에 최종금액이 집계되는 구조입니다.
예를 들어
create table apt_amount ( code varchar2(5), parent varchar2(5), name varchar2(100), amount number )
의 테이블이 존재 할 때.
code | parent | name | amount |
00001 | 00000 | 대한민국 | |
00002 | 00001 | 서울특별시 | |
00003 | 00001 | 경기도 | |
00004 | 00002 | 서초구 | |
00005 | 00002 | 용산구 | |
00006 | 00003 | 고양시 | |
00007 | 00003 | 성남시 | |
00008 | 00004 | 방배동 | |
00009 | 00008 | 방배1동 | |
00010 | 00008 | 방배2동 | |
00011 | 00009 | e편한세상 | 100,000,000 |
00012 | 00009 | 푸르지오 | 110,000,000 |
...... |
위의 표와 같은 자료가 존재합니다.
아파트별 가격을 등록하고, 해당 아파트는 시군구 / 읍면동 구조의 계층구조로 집계되어 업데이트 되어지기를 원합니다.
code | parent | name | amount |
00001 | 00000 | 대한민국 | 3,910,000,000 |
00002 | 00001 | 서울특별시 | 1,610,000,000 |
00003 | 00001 | 경기도 | 2,300,000,000 |
00004 | 00002 | 서초구 | 810,000,000 |
00005 | 00002 | 용산구 | 800,000,000 |
00006 | 00003 | 고양시 | 1,000,000,000 |
00007 | 00003 | 성남시 | 1,300,000,000 |
00008 | 00004 | 방배동 | 410,000,000 |
00009 | 00008 | 방배1동 | 210,000,000 |
00010 | 00008 | 방배2동 | 200,000,000 |
00011 | 00009 | e편한세상 | 100,000,000 |
00012 | 00009 | 푸르지오 | 110,000,000 |
...... |
위에 기재한 자료를 기준으로 해본다면 위와같은 자료가 만들어 지게 됩니다.
이를 업데이트로 구현하기 위하여
UPDATE apt_amount a SET a.amount = ( SELECT sum(b.amount) FROM apt_amount b WHERE connect_by_isleaf = 1 CONNECT BY PRIOR b.code = b.parent START WITH b.code = a.code )
라는 업데이트 쿼리를 작성하였습니다.
PK는 당연히 amount이고, parent컬럼에 인덱스를 부여하였습니다.
문제는 해당 테이블의 자료가 6-7천건이 존재하는데
속도가 아주 떨어집니다.
해당 업데이트 쿼리를 실행하는데 5분정도가 소요됩니다.
테이블의 데이터가 더욱 늘어날 것인데 그러면 업데이트 쿼리가 더욱더 속도 저하가 예상됩니다.
이 속도문제를 해결 할 수 있는 방안을 여쭈어보고 싶습니다.
깊은 지식을 가지고 계신분의 도움이 간절히 필요합니다
1. 꼭 업데이트를 해야 하나요?
- 이러면 금액 변경될때마다 업데이트 해야 할텐데요?
- 업데이트 없이 그냥 필요시 조회하는 방향으로 가면 안되나요?
2. 쿼리 개선
- 변경전 : 특정 행 시작기준 서브쿼리 방식
- 변경후 : 모든 행 시작기준 계층쿼리 후 connect_by_root 로 집계하는 방식
- http://gurubee.net/lecture/2250
3. 조건 개선
- 변경전 : 모든 행 업데이트
- 변경후 : 변경된 행에 해당하는 상위행만 찾아 업데이트