안녕하세요.
도로명주소 변경분에 대해 Merge into를 사용해서 데이터 갱신을 하려는데요.
특정 테이블에서만 1초 안으로 동작하고 나머지 테이블에서는 1분이 넘도록 완료가 안돼서 중간에 취소했습니다.
예를 들면 충청남도, 충청북도 도로명주소를 업데이트 할 때,
충청남도의 경우, UPDATE 해야할 항목이 3388건이고, 충청북도의 경우는 978건입니다.
하지만 충청남도는 1초만에 업데이트가 완료되고, 충청북도는 1분이 넘도록 완료되지않습니다.
업데이트 타겟이 되는 테이블 A와 업데이트 할 데이터가 들어있는 테이블 B의 OUTER JOIN 의 갯수도
충남이 455880건, 충북이 309700건으로, 충남이 훨씬많은데 충남만 1초안에 업데이트가 됩니다.
어떤 원인들을 예상해볼 수 있을까요?
UPDATE로 했을 때, 속도가 너무 않나서 MERGE INTO로 바꾼건데, 다량을 데이터를 업데이트할 때 어떤 방법들이 있을까요?
스크립트 올려보겠습니다.
MERGE INTO TN_ZIP_CN A
USING ( SELECT DISTINCT Y.* FROM
(SELECT BULD_MANAGE_NO_CODE,MAX(TEMP3) AS TEMP3
FROM TMP_JUSO
WHERE SIDO = '충청남도' AND TYP = '34' GROUP BY BULD_MANAGE_NO_CODE) X
INNER JOIN TMP_JUSO Y
ON X.BULD_MANAGE_NO_CODE = Y.BULD_MANAGE_NO_CODE AND X.TEMP3 = Y.TEMP3) B
ON (A.BULD_MANAGE_NO_CODE = B.BULD_MANAGE_NO_CODE)
WHEN MATCHED THEN UPDATE SET A.컬럼 = B.컬럼
MERGE INTO TN_ZIP_CB A
USING ( SELECT DISTINCT Y.* FROM
(SELECT BULD_MANAGE_NO_CODE,MAX(TEMP3) AS TEMP3
FROM TMP_JUSO
WHERE SIDO = '충청북도' AND TYP = '34' GROUP BY BULD_MANAGE_NO_CODE) X
INNER JOIN TMP_JUSO Y
ON X.BULD_MANAGE_NO_CODE = Y.BULD_MANAGE_NO_CODE AND X.TEMP3 = Y.TEMP3) B
ON (A.BULD_MANAGE_NO_CODE = B.BULD_MANAGE_NO_CODE)
WHEN MATCHED THEN UPDATE SET A.컬럼 = B.컬럼
답변 감사합니다.
실행계획에서는 레코드 갯수의 차이 이외에는 별다른점이 없구요.
각 테이블에 동일한 인덱스가 존재하구요 BULD_MANAGE_NO_CODE에 대한 인덱스없습니다.
기존에 생성되어 있는 인덱스가 이미 3개이고, 운영중인 데이터라 리빌드 시 오류날거같아서 생성하지 않았습니다.
DISTINCT 한 이유는
우체국에서 제공한 변경 데이터에 PK까지 동일한 데이터 행이 몇개 있어서요, 업데이트 할 때 2개이상의 행이 존재로 인한 에러때문에 사용했습니다.
제가 초보라 잘 몰라서요ㅜ 다시 몇가지 질문 더 드려도 될까요?
1. DISTINCT말고 다른 방법이 있을까요?
2. 인덱스 생성해보고 싶은데 인덱스 생성해도 별 다른 이상 없을까요?
3. 임시테이블을 만들어서 DISTINCT한 테이블 넣어놓고 MERGE INTO말고 UPDATE문만 써서 실행해보려고 합니다.
어떤방식으로 하는게 좋을까요.
희안하게 충남만 337ms 밖에 안나와서요 MERGE INTO에 대한 미련이 남네요ㅠㅠ;
임시테이블에 업데이트 하려고 했는데 데이터가 너무 많다고 INSERT가 안돼네요ㅠ
TMP_JUSO 테이블에 우체국에서 제공하는 변경할 데이터들이 있구요 (우체국에서 가져온 데이터를 sql*Loader 컨트롤러를 사용하여 넣었습니다.)
TMP_JUSO 테이블의 BULD_MANAGE_NO_CODE(건물관리주소 / CHAR(25)), TEMP3(연계일시 CHAR(10)) 이 두 필드가 PK입니다.
건물관리주소 별 최근 연계일시 인 데이터들만 업데이트를 하려는게 목적이구요,
건물관리주소와 최근 연계일시가 동일한(PK동일) 데이터 행에 다른 필드에도 동일한 데이터들이 있어서 그 중복을 제거하려고 distinct를 사용했습니다.
저 두 필드는 TN_ZIP_CN 테이블에 업데이트 되지않습니다.
업데이트되는 되는 컬럼들은 아래에 나열하겠습니다. 각 항목들은 주소에 대한 정보들이고 정의된 데이터 타입으로 저장되고 있습니다.
ZIP_CODE CHAR(6 BYTE),
SIDO VARCHAR2(20 BYTE),
SIGNGU VARCHAR2(20 BYTE),
SIGNGU_ENG VARCHAR2(40 BYTE),
EUPMYUN VARCHAR2(20 BYTE),
EUPMYUN_ENG VARCHAR2(40 BYTE),
RN_CODE CHAR(12 BYTE),
RN VARCHAR2(80 BYTE),
RN_ENG VARCHAR2(255 BYTE),
UNDGRND_AT CHAR(1 BYTE),
BDNBR_MNNM NUMBER(10),
BDNBR_SLNO NUMBER(10),
MUCH_DLVR_OFFIC_NM VARCHAR2(40 BYTE),
BULD_NM VARCHAR2(255 BYTE),
DONG_CODE CHAR(10 BYTE),
DONG_NM VARCHAR2(20 BYTE),
LI VARCHAR2(20 BYTE),
HAJENGDONG VARCHAR2(20 BYTE),
SAN_AT VARCHAR2(2 BYTE),
LNM_MNNM NUMBER(10),
ZIP_SN_CODE CHAR(3 BYTE),
LNM_SLNO NUMBER(10)
아뇨, TMP_JUSO 테이블에 PK로 설정해놓지는 않았구요,
도로명주소 변경분에 대해서 갱신하고자할때, 우체국 데이터를 bulk로 담아주고자 임시테이블용으로 사용합니다.
우체국에서 제공해주는 데이터 파일(.txt) 자체에 중복행이 들어있었구요.
제가 관리할 주소 테이블들이 아니라, 운영자(IT 비전공자) 에게 도로명주소 테이블 업데이트 할 수 있는 방법을 메뉴얼로 제공해줘야되는 상황이라서요. sqlplus에다가 보내주는 스크립트대로 복사붙여넣기하고 실행하도록 설명하고자 합니다.
우체국 데이터 자체를 수정해서 다시 TMP_JUSO 파일로 올리는 방법은 가이드가 너무 복잡해질거같아서 최대한 스크립트에서 해결하구 싶습니다.
어제 우체국에 전화해서 왜 같은 행들이 존재 하냐고 물어봤더니, 도로명주소 담당하는 행정기관에 연락해야된다고해서 물어봤더니, 연계일자는 우체국에서 담당하는거라고 우체국에 다시 물어보라길래..;;; 관뒀습니다. 어차피 같은 데이터니까 distinct하면 될거 같아서요ㅠ ㅎㅎ
키로 설정되어 있지 않다고 한다면..
Distinct 한다고 해서 중복제거된다고 볼 수 없습니다.
buld_manage_no_code 를 제외한 다른 컬럼값들 중에서
한 컬럼이라도 다른 값이 존재한다면 중복은 제거되지 않습니다.
buld_manage_no_code 별로 1건만 가져오려면 Row_number 를 쓰는게 좋을 듯 하네요.
업데이트 대상에 대한 조인 키인 buld_manage_no_code 에 대한 인덱스가 없는게 제일 맘에 걸리구요.
MERGE INTO tn_zip_cb a USING ( SELECT * FROM (SELECT b.* , ROW_NUMBER() OVER(PARTITION BY buld_manage_no_code ORDER BY temp3 DESC) rn FROM tmp_juso b WHERE sido = '충청북도' AND typ = '34' ) WHERE rn = 1 ) b ON (a.buld_manage_no_code = b.buld_manage_no_code) WHEN MATCHED THEN UPDATE SET a.컬럼 = b.컬럼 ;