데이터베이스 질문과 답변
by djwoo [PL/SQL] union union all multi table [2017.03.31 11:31:39]
key 값과 type을 가진 A 테이블
| id | type | search_id |
| 1 | AAA | 3 |
| 1 | BBB | 2 |
| 3 | AAA | 1 |
| 4 | CCC | 10 |
| 4 | DDD | 2 |
이런식의 테이블 데이터가 들어가 있구요. A 테이블의 타입에 따라서 각각 조회할 테이블이 다릅니다. 거의 20개 정도 테이블이구요.
처음에는
select id, type, search_id , col1,col2 ...
from A ,
( select search_id, 'AAA' as type , col1, col2, col3 ... from A1
uion all
select search_id, 'BBB' as type ,col1, col2, col3 ... from A2
union all
select search_id, 'CCC' as type, clo1,col2, col3 ... from A3
union all
....
) B
where A.type = B.type
and A.search_id = B.search_id
and id = 1;
이런식으로 누군가 구현을 해놨던데.. 점점 타입에 따른 테이블과 테이블 안에 데이터가 많아 질수록 .... 느려지고 감당하기 힘들어져서 변경요청이 왔는데
저도 쿼리공부한지 1년밖에 안된 초보자인지라 도움을 구해요. postgresql 문법으로 작성을 해오라고해서 더 멘붕이네요. ㅠ ㅠ
해당 여러개의 테이블을 하나로 통합하세요.
통합된 테이블의 형태는 union all 한 B 집합의 형태가 되구요.
통합된 테이블의 키는 type + search_id 가 되겠네요.
테이블 구조를 변경해보라는 말씀이신가요? ㅠ .. 그건 힘들것같아요. 을의 입장이라.. 그냥 가져다가 잘 쓰라고만 하시네요. 휴
테이블 구조 변경이 어렵다면
select *
from
(
select id, type, serchid, col1, col2
from a, a1 b
where a.type ='AAA'
and a.search_id = b.search_id
union all
select id, type, serchid, col1, col2
from a, a2 b
where a.type ='BBB'
and a.search_id = b.search_id
union all
select id, type, serchid, col1, col2
from a, a3 b
where a.type ='CCC'
and a.search_id = b.search_id
)
where id = 1
이런 형태도 적용해보시고요.
저도 이런방법으로 하긴 했는데 윗분 눈엔 마음에 안차신다고.. 후.. with 구문 사용해보라고 하시는데...
이렇게 하니까 성능이 나아졌나요?
성능이 나아졌는데도 with문으로 바꾸라고 한다면 이유가 무엇인지 여쭤보시죠.
with문을 사용했을 때 장점은
동일한 패턴의 sql을 줄여줌으로써 가독성이 올라가는 점(inline으로 풀릴 때)
반복되는 데이터에 대해 한번만 읽어줌으로써 성능을 올려주는 점(materialize로 풀릴 때)
인데 union all로 묶인 테이블들이 각각 a1, a2, a3으로 각각 다르다면 굳이 with문을 쓸 이유가 없을 것 같은데요.
혹시 a1, a2, a3이 동일한 테이블이고 where 조건만 다른 건가요?
아니요. 테이블은 모두 다른 테이블이에요.!! 그냥 더 성능을 높이는 방법을 찾아보라고만 하세요. 답은 없으신듯해요..
알면 니가 하라고 하세요ㅎㅎ
각 테이블 정보를 좀더 자세히 알려 주세요.
- type 수 : 20
- 컬럼수 : col1, col2, col3... 몇개?
- 각 테이블별 레코드 건수?
- 인덱스는 적절하게 다 있는지?
- 메인 테이블의 건수?
- 조건(id=1)은 항상 들어오는지? id 별 건수는?
일단 type은 현재 10개 정도구요!! (추후 늘어날 여지가 잇습니다.)
컬럼은 각각 테이블마다 다 달라요. 기본적으로 모두 10개는 넘는 컬럼들을 가지고 잇어요.
인덱스는 테이블마다 있구요. search_id가 키값이자 인덱스처럼 사용되고 있어요.
메인테이블의 역할은 has 테이블이라고해서 중간다리하는 역할을 하는데
id =1 이면 100개 정도의 데이터가 들어가있어용. id는 계속 시퀀스가 증가되면서 생성되고 잇구 현재는 1부터 50까지 증가한 상태에요.
테이블마다 컬럼명이나 갯수가 다른데 유니온을 하나요?
- 왜 이렇게 해야 하죠?
테이블별 데이터 건수는요?
- 건수가 많지 않다면 성능상 크게 문제 없을 듯 한데요?
현재는 DB를 싹밀어서 괜찮지만 예전엔 테이블마다 기본 select 만 하는데도 10~ 20초정도 기다리고 잇어야햇어요. 그리고 현재 union 하는 각각 테이블마다 성격이 분명한 예를들어 A 업체 테이블, B업체 테이블, C업체 테이블이라고 생각하시면되세요. 전 그걸 전체 가져다가 사용자가 어떤업체든 선택하게 만드는 창구 역할을 하고잇어요. 그러니 테이블을 직접 건드릴순없고 그냥저냥 필요한 부분만 가져오고 싶은데... 구분할 컬럼이 생각보다 없어서요. 기존 했던 사람이 전체 union 했더라구요..
예전에 테이블마다 select만 해도 느렸다면 인덱스 구성이 완전 잘못된 구조였던 것이고
지금은 DB를 밀어서 데이터가 작아져버렸으니 괜찮은 것처럼 보이는 것 뿐이죠.
시간이 지나서 데이터가 많아지면 다시 느려질 겁니다.
결론적으로 테이블마다 조회 조건 구성을 잘 파악하고 적절한 인덱스 구성만 해준다면
성능이 나아진다는 것이죠.
애초에 with문으로 쿼리를 바꾼다고 성능 문제를 해결할 수도 없는 구조구요.
항상 id 검색조건이 들어온다면 각 테이블마다 id컬럼으로 인덱스가 있는지..
조인 컬럼인 search_id 컬럼으로 인덱스가 있는지 확인해보세요.
그래서 테이블 건수가 얼만지 계속 묻고 있네요...
- 다시 한번 더 묻습니다. 건수가 얼마나 되나요?
- 건수가 많지 않다면? 성능상 문제가 없을 듯 하고
테이블 마다 특성이 다르다면?
- Union 이 아닌 Join 을 해야 하는게 아닌가 하는 생각도 드네요.
) 버튼을 클릭하여 작성 하시면 됩니다.