데이터베이스 질문과 답변
안녕하세요. 정말 많은 도움 받고 있는 개발자입니다. 먼저 감사를 드리구요..
테이블 구조는 아래와 같습니다.
테이블 구조는 아래와 같습니다.
'그룹' 테이블
번호 | 그룹명 | 등록자ID | 날짜
'그룹상세' 테이블(현재 테스트 데이터 600만건, 향후 운영에선 2000~3000만건 예상합니다.)
번호 | 부모번호 | 성명 | 핸드폰 | 이메일 | 팩스 | 등록자ID | 날짜
'그룹' 테이블은 T_ADDR_GROUP
'그룹상세' 테이블은 T_ADDR_DETAIL 입니다.
인덱스 정보는 아래와 같습니다.
T_ADDR_GROUP (NO) *PK
T_ADDR_GROUP01 (NAME, NO)
T_ADDR_DETAIL (NO) *PK
T_ADDR_DETAIL01 (USER_ID, NO, PARENT_NO)
T_ADDR_DETAIL02 (USER_ID, NAME, PARENT_NO)
T_ADDR_DETAIL03 (PARENT_NO)
T_ADDR_DETAIL04 (USER_ID,HP_REJECT_YN, FAX_REJECT_YN)
T_ADDR_DETAIL05 (USER_ID, NO, PARENT_NO, HP_REJECT_YN, FAX_REJECT_YN)
위의 테이블은 문자 발송 시스템의 그룹관리 테이블입니다.
우선 나머지 부분들은 많은 도움을 통해 처리를 하고 있구요(해결중이지만..)
지금 문제에 대해 말씀드리겠습니다.
'수신거부' 기능에 대한 문제입니다.
별도로 수신거부를 등록할 수 있어야 합니다. 그룹상세 테이블에는 핸드폰, 이메일, 팩스 번호를 넣을 수 있는데
이 3가지에 대해서 수신거부가 가능합니다.
그래서 제 생각에는 고객이 엑셀이든 개별 등록이든 핸드폰, 이메일, 팩스를 선택하고 데이터를 올리면 그걸 잡아서
일치하는 데이터(ROW)에 UPDATE 시키는 방안을 고려중입니다.
'그룹상세' 테이블을 다음과 같이 수정하고자 합니다.
'그룹' 테이블은 T_ADDR_GROUP
'그룹상세' 테이블은 T_ADDR_DETAIL 입니다.
인덱스 정보는 아래와 같습니다.
T_ADDR_GROUP (NO) *PK
T_ADDR_GROUP01 (NAME, NO)
T_ADDR_DETAIL (NO) *PK
T_ADDR_DETAIL01 (USER_ID, NO, PARENT_NO)
T_ADDR_DETAIL02 (USER_ID, NAME, PARENT_NO)
T_ADDR_DETAIL03 (PARENT_NO)
T_ADDR_DETAIL04 (USER_ID,HP_REJECT_YN, FAX_REJECT_YN)
T_ADDR_DETAIL05 (USER_ID, NO, PARENT_NO, HP_REJECT_YN, FAX_REJECT_YN)
위의 테이블은 문자 발송 시스템의 그룹관리 테이블입니다.
우선 나머지 부분들은 많은 도움을 통해 처리를 하고 있구요(해결중이지만..)
지금 문제에 대해 말씀드리겠습니다.
'수신거부' 기능에 대한 문제입니다.
별도로 수신거부를 등록할 수 있어야 합니다. 그룹상세 테이블에는 핸드폰, 이메일, 팩스 번호를 넣을 수 있는데
이 3가지에 대해서 수신거부가 가능합니다.
그래서 제 생각에는 고객이 엑셀이든 개별 등록이든 핸드폰, 이메일, 팩스를 선택하고 데이터를 올리면 그걸 잡아서
일치하는 데이터(ROW)에 UPDATE 시키는 방안을 고려중입니다.
'그룹상세' 테이블을 다음과 같이 수정하고자 합니다.
번호 | 부모번호 | 성명 | 핸드폰 | 이메일 | 팩스 | 등록자ID | 날짜 | 핸드폰수신거부 | 이메일수신거부 | 팩스수신거부
추가되는 필드는 아래와 같습니다.
HP_REJECT CHAR(1) DEFAULT 'N'
EMAIL_REJECT CHAR(1) DEFAULT 'N'
FAX_REJECT CHAR(1) DEFAULT 'N'
위처럼 처리를 하고 향후 문자던 팩스던 보낼 시 그룹상세에서 조건을 부모번호와 수신거부를 두고 가져올려고 합니다.
WHERE 부모번호 IN(15,16,22) AND 핸드폰수신거부 = 'N'
처럼요..
위와 같이 했을 때 2000~3000만건의 데이터가 존재할 경우에 셀렉트시 큰 문제가 없을지 조언을 구합니다.
고수님들의 조언을 생각하면 HP_REJECT와 같은 CHAR(1) 필드에 인덱스를 거는게 좋을거 같기도 하구요..
테스트한 결과입니다.
카운트 쿼리(2초 정도 걸립니다.)
추가되는 필드는 아래와 같습니다.
HP_REJECT CHAR(1) DEFAULT 'N'
EMAIL_REJECT CHAR(1) DEFAULT 'N'
FAX_REJECT CHAR(1) DEFAULT 'N'
위처럼 처리를 하고 향후 문자던 팩스던 보낼 시 그룹상세에서 조건을 부모번호와 수신거부를 두고 가져올려고 합니다.
WHERE 부모번호 IN(15,16,22) AND 핸드폰수신거부 = 'N'
처럼요..
위와 같이 했을 때 2000~3000만건의 데이터가 존재할 경우에 셀렉트시 큰 문제가 없을지 조언을 구합니다.
고수님들의 조언을 생각하면 HP_REJECT와 같은 CHAR(1) 필드에 인덱스를 거는게 좋을거 같기도 하구요..
테스트한 결과입니다.
카운트 쿼리(2초 정도 걸립니다.)
SELECT /*+ INDEX_FFS(NO PK_T_ADDR_DETAIL) */ COUNT(NO) AS CNT FROM T_ADDR_DETAIL WHERE USER_ID = 'lee9982' AND HP_REJECT_YN = 'Y' OR FAX_REJECT_YN ='Y'
실행계획
실행계획
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|Operation | Object Name | Rows | Bytes | Cost | Object Node | In/Out | PStart | PStop | Access Predicates | Filter Predicates |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT Optimizer Mode=ALL_ROWS | | 1 | 15 | 5533 | | | | | | |
| SORT AGGREGATE | | 1 | 15 | | | | | | | |
| CONCATENATION | | | | | | | | | | |
| INDEX RANGE SCAN |IN_T_ADDR_DETAIL04 | 40 K | 597 K | 56 | | | | | "USER_ID"='lee9982' AND "HP_REJECT_YN"='Y' | |
| INDEX FAST FULL SCAN |IN_T_ADDR_DETAIL04 | 59 K | 876 K | 5477 | | | | | | "FAX_REJECT_YN"='Y' AND (LNNVL("USER_ID"='lee9982') OR LNNVL("HP_REJECT_YN"='Y')) |
페이징쿼리(30초를 넘어갑니다.)
페이징 쿼리에서 보여져야 할 필드는 그룹상세의 모든 필드와 그룹상세에 있는 부모번호를 통해 그룹 테이블에서 그룹명을
함께 보여주어야 합니다.
페이징쿼리(30초를 넘어갑니다.)
페이징 쿼리에서 보여져야 할 필드는 그룹상세의 모든 필드와 그룹상세에 있는 부모번호를 통해 그룹 테이블에서 그룹명을
함께 보여주어야 합니다.
SELECT a.name AS GROUP_NAME, b.* FROM T_ADDR_GROUP A, (SELECT /*+ INDEX_DESC(b IN_T_ADDR_DETAIL05) */ ROWNUM AS RNUM, b.* FROM T_ADDR_DETAIL B
WHERE b.USER_ID = 'lee9982' AND b.HP_REJECT_YN = 'Y' OR b.FAX_REJECT_YN ='Y' AND ROWNUM <= 10 )b WHERE a.NO = b.PARENT_NO AND RNUM >= 1
실행계획
실행계획
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|Operation | Object Name | Rows | Bytes | Cost | Object Node | In/Out | PStart | PStop | Access Predicates | Filter Predicates |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT Optimizer Mode=ALL_ROWS | | 5 M | 679 M | 73798 | | | | | | |
| HASH JOIN | | 5 M | 679 M | 73798 | | | | | "A"."NO"="B"."PARENT_NO" | |
| INDEX FULL SCAN |IN_T_ADDR_GROUP01 | 20 | 280 | 1 | | | | | | |
| VIEW | | 6 M | 666 M | 73754 | | | | | | "RNUM">=1 |
| COUNT | | | | | | | | | | |
| FILTER | | | | | | | | | | "B"."USER_ID"='lee9982' AND "B"."HP_REJECT_YN"='Y' OR "B"."FAX_REJECT_YN"='Y' AND ROWNUM<=10 |
| TABLE ACCESS BY INDEX ROWID |T_ADDR_DETAIL | 6 M | 281 M | 73754 | | | | | | |
| INDEX FULL SCAN DESCENDING |IN_T_ADDR_DETAIL05 | 6 M | | 27334 | | | | | | |
위와 같습니다.
수신거부 필드들을 IN으로 한번 더 걸러야 할지..고수님들의 조언 부탁드립니다...
위와 같습니다.
수신거부 필드들을 IN으로 한번 더 걸러야 할지..고수님들의 조언 부탁드립니다...
by 마농
[2012.11.20 16:23:10]
튜닝 이전에 SQL 구문 자체가 잘못 되었네요.
WHERE b.user_id = 'lee9982' AND b.hp_reject_yn = 'Y' OR b.fax_reject_yn = 'Y' AND ROWNUM <= 10
위 구문을 설명이 쉽게 간략화 하면 다음과 같습니다.
WHERE aa AND bb OR cc AND dd
AND 와 OR 가 함께 있는 쿼리는 작성시 주의 해야 합니다.
AND 의 수행이 OR 의 수행보다 우선합니다.
즉, 위 구문은 WHERE (aa AND bb) OR (cc AND dd) 와 같이 수행됩니다.
아마도 원하는 결과는 WHERE aa AND (bb OR cc) AND dd 이것일걸로 판단되네요.
by 콩이
[2012.11.20 16:55:40]
아..답변을 해주셨네요..
일단 그부분에 오류가 있는걸 찾아서 말씀하신 것처럼 고치긴 했습니다.
그런데 지금 문제는 쿼리가 너무 늦네요..
쿼리
SELECT * FROM ( SELECT ROWNUM AS RNUM, A.*, (SELECT NAME FROM T_ADDR_GROUP WHERE NO = A.PARENT_NO) AS GROUP_NAME FROM ( SELECT * FROM T_ADDR_DETAIL WHERE USER_ID = ? AND NO IN (SELECT NO FROM T_ADDR_DETAIL WHERE USER_ID = ? AND (HP_REJECT_YN = 'Y' OR FAX_REJECT_YN = 'Y') ORDER BY NO DESC ) A WHERE ROWNUM <= ? ) WHERE RNUM >= ?
실행계획
일단 그부분에 오류가 있는걸 찾아서 말씀하신 것처럼 고치긴 했습니다.
그런데 지금 문제는 쿼리가 너무 늦네요..
쿼리
SELECT * FROM ( SELECT ROWNUM AS RNUM, A.*, (SELECT NAME FROM T_ADDR_GROUP WHERE NO = A.PARENT_NO) AS GROUP_NAME FROM ( SELECT * FROM T_ADDR_DETAIL WHERE USER_ID = ? AND NO IN (SELECT NO FROM T_ADDR_DETAIL WHERE USER_ID = ? AND (HP_REJECT_YN = 'Y' OR FAX_REJECT_YN = 'Y') ORDER BY NO DESC ) A WHERE ROWNUM <= ? ) WHERE RNUM >= ?
실행계획
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|Operation | Object Name | Rows | Bytes | Cost | Object Node | In/Out | PStart | PStop | Access Predicates | Filter Predicates |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| SELECT STATEMENT Optimizer Mode=ALL_ROWS | | 10 | 1 K | 6 | | | | | | |
| TABLE ACCESS BY INDEX ROWID |T_ADDR_GROUP | 1 | 14 | 1 | | | | | | |
| INDEX UNIQUE SCAN |PK_T_ADDR_GROUP | 1 | | 0 | | | | | "NO"=:B1 | |
| VIEW | | 10 | 1 K | 6 | | | | | | "RNUM">=1 |
| COUNT STOPKEY | | | | | | | | | | ROWNUM<=10 |
| VIEW | | 11 | 1 K | 6 | | | | | | |
| TABLE ACCESS BY INDEX ROWID |T_ADDR_DETAIL | 81 K | 3 M | 6 | | | | | | |
| INDEX RANGE SCAN DESCENDING |IN_T_ADDR_DETAIL05 | 11 | | 5 | | | | | "USER_ID"='lee9982' | "HP_REJECT_YN"='Y' OR "FAX_REJECT_YN"='Y' |
위와 같은데요.. 일단 제가 쿼리를 여러 방법으로 고쳐본 결과
한 5초 정도로 단축이 되었습니다.(기존 30초 이상)
그런데 더 단축이 가능할까요?
위와 같은데요.. 일단 제가 쿼리를 여러 방법으로 고쳐본 결과
한 5초 정도로 단축이 되었습니다.(기존 30초 이상)
그런데 더 단축이 가능할까요?
by 마농
[2012.11.20 17:27:27]
다음과 같이 쿼리를 변경해 보세요. 인덱스만으로 페이징처리하세요.
변경전 쿼리 : 1. 인덱스 스캔 > 2. 테이블 스캔 > 3. 정렬 > 4. COUNT STOPKEY
변경후 쿼리 : 1. 인덱스 스캔 > 3. 정렬 > 4. COUNT STOPKEY > 2. 테이블 스캔
) 버튼을 클릭하여 작성 하시면 됩니다.