대용량 테이블 조인 후 rank 함수? 0 11 1,746

by 박군two [SQL Query] [2017.11.01 13:45:59]


안녕하세요

대용량 테이블 조회결과에 rank 함수를 사용할 경우, 시스템에 문제가 있을지 감이 안와서 조언을 구해보고자 합니다.

 

테이블A

  - 컬럼수 : 118 개 (VARCHAR2 1~1000 까지 여러개, NUMBER, DATE 형등의 컬럼)

  - 총 ROW 수 : 540만건

  - 키 컬럼 : 4개 (4개를 묶은 인덱스 존재)

 

테이블 B

  - 컬럼수 : 19 개 (VARCHAR2 1~1000 까지 여러개, NUMBER, DATE 형등의 컬럼)

  - 총 ROW 수 : 197만건

  - 키 컬럼 : 6개 ( 테이블 A의 키컬럼 4개를 외부키로 사용하고 자체 2개의 키컬럼 존재)

 

테이블 C, D, ...

  - 테이블 B의 주키 2개를 키컬럼으로 동일하게 사용하고, 부가적인 정보를 저장

 

A, B를 동등 조인하고 특정조건에 따라 필터링 한 후 C, D에서 B의 부가적인 정보를 추출 했을 때 

14만 2천여건 조회됨

 

이때,

B의 2개 키 컬럼 + C의 1개 일반 VHARCHAR2 컬럼 을 PARTITION으로

C의 DATE 컬럼을 DESC 정렬하여 상위 1개만 추출하고자 함

 

엑셀로 확인해 보니, 추출한 결과는 38700여개 ROW가 최종 조회되어야 함.

최종 출력 형태 : A.1,   A.2,   A.3,   A.4,   B.1,   B.2,   C.1,   C.2(DATE),   D.1,   D.2

 

반드시 필요한 정보인데 실제 운영중인 DB라서 SQL 실행시 문제가 발생하면 안되는 상황이라 막연하지만 문의 드려 봅니다.

서버 성능에 따라 달라지긴 하겠지만, 작은 프로젝트는 아니라서 서버 성능은 딸리지 않는다(?) 고 생각되네요.

 

WITH LANG_MAP AS (
    SELECT B.COUNTRY_CODE, B.COUNTRY_NAME, A.BUYER_CODE, C.LANGUAGE_CODE
    FROM   CS_BUYER_LOCALE_MAP A,
           CS_PREFERENCE_M B,
           CS_COUNTRY_LANGUAGE_M C
    WHERE  A.USE_FLAG = 'Y'
    AND    B.USE_FLAG = 'Y'
    AND    C.USE_FLAG = 'Y'
    AND    A.LOCALE_CODE = B.LOCALE_CODE
    AND    B.COUNTRY_CODE = C.COUNTRY_CODE
    GROUP BY B.COUNTRY_CODE, B.COUNTRY_NAME, A.BUYER_CODE, C.LANGUAGE_CODE
    ORDER BY B.COUNTRY_CODE, B.COUNTRY_NAME, A.BUYER_CODE, C.LANGUAGE_CODE
),
BUYER_MODEL AS (
    SELECT M.HQ_ACCOUNTING_UNIT_CODE,
           M.AFFILIATE_CODE, 
           M.MODEL_CODE,
           M.SUFFIX_CODE, 
           M.PRODUCT_LEVEL3_CODE,
           G.BUYER_CODE,
           G.BUYER_MODEL
    FROM   테이블A M,
           테이블B G
    WHERE  M.HQ_ACCOUNTING_UNIT_CODE = G.OBU_CODE
    AND    M.AFFILIATE_CODE          = G.AFFILIATE_BRANCH_CODE
    AND    M.MODEL_CODE              = G.LG_MODEL
    AND    M.SUFFIX_CODE             = G.SUFFIX
    AND    M.PRODUCT_LEVEL1_CODE IN ('CD', 'CS', 'HT') -- 1레벨 이 'CD', 'CS', 'HT' 인것만
    AND    M.TENTATIVE_MODEL_FLAG = 'N'
    AND    M.ENABLED_FLAG = 'Y'
    AND    G.USE_FLAG = 'Y'
    AND    EXISTS (SELECT 1 FROM LANG_MAP A WHERE A.BUYER_CODE = G.BUYER_CODE) -- 시스템에 맵핑된 BUYER_CODE 만 조회
)
SELECT BUYER_MODEL.*,
       LANG_MAP.COUNTRY_CODE,
       MM.DOC_ID,
       MM.LANGUAGE_CODE,
       MM.ISSUE_DATE,
       MMF.FILE_NAME,
       MMF.ORIGINAL_NAME,
       MMF.FILE_SIZE,
       MMF.MANUAL_NAME,
       MMF.MANUAL_SIZE,
       MMF.CONVERT_FLAG
FROM   BUYER_MODEL,
       테이블C MM,
       테이블D MMF,
       LANG_MAP
WHERE  BUYER_MODEL.HQ_ACCOUNTING_UNIT_CODE = MM.OBU_CODE
AND    BUYER_MODEL.AFFILIATE_CODE          = MM.AFFILIATE_BRANCH_CODE
AND    BUYER_MODEL.MODEL_CODE              = MM.LG_MODEL
AND    BUYER_MODEL.SUFFIX_CODE             = MM.SUFFIX
AND    MM.DOC_ID = MMF.DOC_ID
AND    MMF.USE_FLAG = 'Y'
AND    BUYER_MODEL.BUYER_CODE = LANG_MAP.BUYER_CODE
AND    MM.LANGUAGE_CODE = LANG_MAP.LANGUAGE_CODE
AND    NOT EXISTS (SELECT 1 
                   FROM   CS_GCSC_DELETE_PDF D
                   WHERE  MMF.DOC_ID = D.DOC_ID
                   AND    D.DELETE_FLAG = 'Y'
                   AND    D.USE_FLAG = 'Y');

PLAN 결과

 

Id Operation Name Rows Bytes Cost Time
0 SELECT STATEMENT   84 24024 97702 00:19:33
1 . TEMP TABLE TRANSFORMATION          
2 .. LOAD AS SELECT SYS_TEMP_9FD9E3EF0_19645455        
3 ... SORT GROUP BY   366 15738 23 00:00:01
* 4 .... HASH JOIN   366 15738 22 00:00:01
* 5 ..... HASH JOIN   132 3564 16 00:00:01
* 6 ...... TABLE ACCESS FULL CS_COUNTRY_LANGUAGE_M 135 1080 6 00:00:01
* 7 ...... TABLE ACCESS FULL CS_PREFERENCE_M 161 3059 10 00:00:01
* 8 ..... TABLE ACCESS FULL CS_BUYER_LOCALE_MAP 478 7648 6 00:00:01
9 .. NESTED LOOPS   4 1144 97510 00:19:31
10 ... NESTED LOOPS   84 1144 97510 00:19:31
* 11 .... HASH JOIN   84 16632 97342 00:19:29
* 12 ..... HASH JOIN   22801 3283344 89164 00:17:50
13 ...... VIEW   366 10614 3 00:00:01
14 ....... TABLE ACCESS FULL SYS_TEMP_9FD9E3EF0_19645455 366 8784 3 00:00:01
* 15 ...... HASH JOIN   29628 3407220 89161 00:17:50
* 16 ....... TABLE ACCESS FULL 테이블A 186754 10644978 77839 00:15:35
* 17 ....... HASH JOIN RIGHT SEMI   308526 17894508 9688 00:01:57
18 ........ VIEW   366 3294 3 00:00:01
19 ......... TABLE ACCESS FULL SYS_TEMP_9FD9E3EF0_19645455 366 8784 3 00:00:01
* 20 ........ TABLE ACCESS FULL 테이블B 1944723 95291427 9680 00:01:57
21 ..... TABLE ACCESS FULL 테이블C 1118080 60376320 4515 00:00:55
* 22 .... INDEX UNIQUE SCAN PK_CS_GSCS_OWNER_MANUAL_FILE 1   1 00:00:01
* 23 ..... TABLE ACCESS BY INDEX ROWID CS_GCSC_DELETE_PDF 1 19 4 00:00:01
* 24 ...... INDEX RANGE SCAN PK_CS_GSCS_DELETE_PDF 1   3 00:00:01
* 25 ... TABLE ACCESS BY INDEX ROWID 테이블D 1 88 2 00:00:01

 

by 우리집아찌 [2017.11.01 14:13:38]

먼저 SQL를 좀 보여주시는게 어떠실지요?


by 박군two [2017.11.01 14:32:42]

SQL과 PLAN 결과 추가 했습니다.


by 우리집아찌 [2017.11.01 15:22:13]

인덱스 구성 컬럼은요?


by 박군two [2017.11.01 15:42:24]

테이블 A  : M.HQ_ACCOUNTING_UNIT_CODE, M.AFFILIATE_CODE, M.MODEL_CODE, M.SUFFIX_CODE 

테이블 B : G.OBU_CODE, G.AFFILIATE_BRANCH_CODE, LG_MODEL, G.SUFFIX

테이블 B : G.BUYER_CODE, G.BUYER_MODEL

테이블 C : DOC_ID, LANGUAGE_CODE, AFFILIATE_BRANCH_CODE, LG_MODEL, SUFFIX, OBU_CODE

위와 같이 구성되어 있습니다.


by 우리집아찌 [2017.11.01 15:51:43]

테이블 A에 BUYER_CODE는 INDEX가 안걸려있나보네요.

못 봤네요.. 테이블 B 였군요... B도 BUYER_CODE 가 인덱스가 없네요..


by 박군two [2017.11.01 15:58:43]

테이블 A에는 BUYER_CODE  컬럼이 없어요..

테이블 B에는 몇개의 인덱스가 있는데 그중 두개를 위에 기술했습니다.

두번째 기술한 인덱스에 BUYER_CODE 포함되어 있구요


by 우리집아찌 [2017.11.01 15:49:11]

테이블 A랑 B랑 먼저 조인되고 LANG_MAP랑 나중에 조인되어 풀리는거 같은데

B랑 LANG_MAP이랑 먼저 조인되게 풀면 집합이 줄어들어서 좀 낫지 않을까요?


by 박군two [2017.11.01 15:59:33]

해보겠습니다.

생각해보니, A와 LANG_MAP은 연결고리가 없네요

LANG_MAP은 B와 C정보를 필터링하기 위한 정보입니다.


by 우리집아찌 [2017.11.01 16:13:13]

B테이블랑 JOIN 해보세요.. 

인라인뷰로 싸보시는게 좋을것같아요.


by 마농 [2017.11.01 15:51:20]

buyer_model 과 lang_map 이 결국 마지막에 한번 더 조인이 되므로
  - buyer_model 안의 Exists(lang_map) 구문은 불필요해 보입니다.
  - Exists 제거해 보세요.


by 박군two [2017.11.01 16:01:54]

마농님.
제거하니, 플랜에서 cost가 더 증가 되네요

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