DP-ORA 쿼리 최적화 가이드 쿼리 최적화 방법 2014년 7월.

Presentation on theme: "DP-ORA 쿼리 최적화 가이드 쿼리 최적화 방법 2014년 7월."— Presentation transcript:

1 DP-ORA 쿼리 최적화 가이드 쿼리 최적화 방법 2014년 7월

2 목차 개요 암호화 동작원리 VTI 모드 쿼리 최적화 방법 API 모드 쿼리 최적화 방법 최적화된 쿼리 검증 방법 주의사항
쿼리 최적화란? 암호화 적용 시 쿼리 최적화 대상 암호화 동작원리 VTI 모드 동작원리 암호화 적용 이후 객체 변화 API 모드 VTI 모드 쿼리 최적화 방법 API 모드 쿼리 최적화 방법 최적화된 쿼리 검증 방법 주의사항

3 개요 쿼리 최적화란? 암호화 적용 시 쿼리 최적화 대상

4 I. 개요 1. 쿼리 최적화란? 개요 - 쿼리 최적화란? D’Amo는 암호화 후에도 기존 쿼리를 동일하게 사용할 수 있는 기능을 제공하고 있습니다. 일부 쿼리의 경우 암호화 후 불필요한 암복호화로 인해 성능이 저하되거나, Oracle 특성상 기존의 쿼리를 사용하기 어려운 경우들이 있습니다. 이러한 경우 쿼리를 적절하게 변경함으로써, 문제를 해결할 수 있으며, 이러한 과정을 “쿼리 최적화”라고 부릅니다. 암호화 후 변경 내역 view/trigger process 추가 일부 경우 execution plan 변경 쿼리 최적화 암호화 쿼리 최적화 필요성 성능 개선! 서버 부하 감소! View로 인한 쿼리 Syntax 제약 해결! DP-ORA 쿼리 최적화 가이드

5 개요 - 암호화 적용 시 쿼리 최적화 대상 (1/2) 불필요한 암복호화 방지 View 제약
2. 암호화 적용 시 쿼리 최적화 대상 개요 - 암호화 적용 시 쿼리 최적화 대상 (1/2) 불필요한 암복호화 방지 실제 암호화 값에 대한 복호화 값이 query를 실행하는 도중 일시적으로만 사용되거나, 단순 비교만을 위해 사용되는 경 우에는 암호화 값을 직접 사용하여 복호화 과정을 줄이거나 생략할 수 있습니다. 대표적인 사례로는 다음과 같습니다. Sub Query에서 암호화 컬럼에 대한 참조가 대량으로 참조하는 경우 암호화 컬럼 간 조인 암호화 데이터 간의 복제 암호화 컬럼이 group/having 절에 있는 쿼리 View 제약 View는 일종의 가상의 테이블로 실제 테이블에 적용 가능한 Query의 경우 동일하게 사용할 수 있지만 아래와 같은 일 부 쿼리의 경우 Oracle의 특성상 view에 적용하는 것이 불가능합니다. Syntax 에러 발생 Return rowid, merge into Partition을 명시한 query rowid를 이용한 쿼리 LOB 컬럼의 처리 DP-ORA 쿼리 최적화 가이드

6 개요 - 암호화 적용 시 쿼리 최적화 대상 (2/2) Plan 변경 기존 Table에 적용되어 있던 Trigger DML
2. 암호화 적용 시 쿼리 최적화 대상 개요 - 암호화 적용 시 쿼리 최적화 대상 (2/2) Plan 변경 암호화 적용 후에는 기존 query들은 view를 이용하게 되고, 암호화 컬럼에 대한 인덱스가 변경되므로 execution plan이 변경될 수 있습니다. plan이 비효율적으로 변경되는 경우, view대신에 table을 직접 이용하도록 변경함으로써 plan을 개 선할 수 있습니다. 다음의 경우에는 암호화 후에 실행 계획이 비효율적으로 개선되는 사례들입니다. 암호화 컬럼을 이용한 조인 (outer 조인 포함) 복잡한 쿼리 (sub query+order+group가 포함된 경우) 전방위 검색 (%plain%) Hint 가 들어있는 쿼리 DB link와 연관 쿼리 기존 Table에 적용되어 있던 Trigger 기존 table에 적용되어 있던 trigger의 구문은 새로 추가된 view의 trigger의 영향으로 오동작하거나, 결과가 달라질 수 있습니다. 따라서 view 대신에 암호화 테이블을 참조하도록 수정하는 것이 바람직합니다. DML Insert/update 구문의 경우 암호화 후에는 view의 trigger를 이용하게 됩니다. 따라서 실행 시에 암호화 부하에 더불어 trigger사용에 따른 부하가 추가됩니다. 그러므로 trigger를 이용하지 않도록 수정함으로써 성능을 향상시킬 수 있다. DP-ORA 쿼리 최적화 가이드

7 암호화 동작원리 VTI 모드 동작원리 암호화 적용 이후 객체 변화 API 모드

8 암호화 동작원리 – VTI 모드 DP-ORA VTI 모드의 동작 원리 TRIGGER 암호화 전 암호화 후
II. 암호화 동작원리 1. VTI 모드 암호화 동작원리 – VTI 모드 DP-ORA VTI 모드의 동작 원리 암호화 후 원본 테이블이 변경되기 때문에, 암호화 전 테이블과 동일한 형식을 가진 VIEW를 통해 사용자에게 정보를 보 여줍니다. 이때 VIEW를 통해 정보를 보여주기 위해 데이터 입력 조회 시 원본테이블과 VIEW사이에서 데이터 암복호화 가 이루어집니다. MEMBER (TABLE) MEMBER_DAMO (TABLE) (MODIFY) A B C JUMIN_NO RENAME A B C SEC_JUMIN_NO D2FCD121E35 복 호 화 암 호 화 기 존 접 근 경 로 A B C JUMIN_NO 암호화 후 접근 경로 MEMBER (VIEW) 사용자 TRIGGER 암호화 전 암호화 후 sIMEMBER, sUMEMBER, sDMEMBER (TRIGGER) DP-ORA 쿼리 최적화 가이드

9 암호화 동작원리 – VTI 모드 암호화 적용 이후 객체 변화
II. 암호화 동작원리 1. VTI 모드 암호화 동작원리 – VTI 모드 암호화 적용 이후 객체 변화 DP-ORA VTI 모드 암호화 적용 이후 객체 변화 MEMBER_DAMO sIMEMBER sUMEMBER sDMEMBER MEMBER MEMBER_ROOT View Table Triggers JUMIN_NO DECRYPT(SEC_JUMIN_NO) ENCRYPT(JUMIN_NO) SEC_JUMIN_NO libdamo.so 암복호화 라이브러리 Oracle JVM O/S 영역 DB 영역 DP-ORA 쿼리 최적화 가이드

10 암호화 동작원리 – API 모드 DP-ORA API 모드의 동작 원리 암호화 전 암호화 후 A B C JUMIN_NO
II. 암호화 동작원리 2. API 모드 암호화 동작원리 – API 모드 DP-ORA API 모드의 동작 원리 암호화 후 원본 테이블이 변경되지만, 별도의 VIEW/TRIGGER는 생성하지 않고, 암/복호화 쿼리를 삽입하여 암호화 테이 블에서 직접 암호화 데이터를 조회/수정 합니다. MEMBER (TABLE) MEMBER (TABLE) (MODIFY) A B C JUMIN_NO A B C JUMIN_NO D2FCD121E35 기 존 접 근 경 로 SELECT A,B,C,JUMIN_NO FROM MEMBER SELECT A,B,C,DECRYPT_VAR(‘AES’,JUMIN_NO,’’) JUMIN_NO FROM MEMBER 암호화 후 접근 경로 사용자 암호화 전 암호화 후 DP-ORA 쿼리 최적화 가이드

11 VTI 모드 쿼리 최적화 방법

12 III. VTI 모드 쿼리 최적화 방법 1. 쿼리 최적화 방법 VTI모드 쿼리 최적화 방법 쿼리 최적화하는 방법은 매우 다양한 경우가 존재하지만 기본적으로 다음의 방식 중 하나 또는 복수의 방 법들을 사용합니다. 복호화 된 값 대신에 암호화 값을 직접 이용하도록 변경 view 대신에 암호화 테이블을 직접 참조하도록 대상 테이블 수정 쿼리에서 직접 암복호화 함수를 호출 인덱스의 효율을 높이기 위해 암호화 인덱스에 사용되는 인덱스 함수를 호출 DP-ORA 쿼리 최적화 가이드

13 VTI모드 쿼리 최적화 방법 – 암복호화 함수 (1/2)
III. VTI 모드 쿼리 최적화 방법 2. 암복호화 함수 VTI모드 쿼리 최적화 방법 – 암복호화 함수 (1/2) 암호화 함수 암호화 함수명(‘소유자’, ’테이블’, ’컬럼’, 암호화 하고자 하는 값) 컬럼 type에 따라 각기 다른 암호화 함수를 사용. 함수 인자는 모두 동일. ENCODE TYPE에 따라 사용 해야 하는 함수 명이 각각 다름에 주의 해야 한다. NUMBER 타입의 경우 부분암호화 지원 안함(부분암호화의 경우 CHAR, VARCHAR2 타입만 지원) 암호화 함수 정보는 암호화 테이블의 SIT_**** Trigger Script에서 얻을 수 있음 DAMO.ENC_VARCHAR(‘SM’, ‘MEMBER’, ‘JUMIN_NO’,’ ’) -- “JUMIN_NO” 컬럼이 varchar2 type 인 경우 VARCHAR2 CHAR NUMBER DATE RAW CLOB/BLOB ENC_VARCHAR ENC_CHAR ENC_NUMBER ENC_DATE ENC_RAW ENC_CLOB, ENC_BLOB HEX ENC_VARCHAR_HEX ENC_CHAR_HEX ENC_NUMBER_HEX B64 ENC_VARCHAR_B64 ENC_CHAR_B64 ENC_NUMBER_B64 부분암호화 ENC_VARCHAR_PE ENC_VARCHAR_PE_B64 ENC_CHAR_PE ENC_CHAR_PE_B64 DP-ORA 쿼리 최적화 가이드

14 VTI모드 쿼리 최적화 방법 – 암복호화 함수 (2/2)
III. VTI 모드 쿼리 최적화 방법 2. 암복호화 함수 VTI모드 쿼리 최적화 방법 – 암복호화 함수 (2/2) 복호화 함수 복호화 함수명(‘소유자’, ’테이블’, ’컬럼’, 일반값) 컬럼 type에 따라 각기 다른 복호화 함수를 사용. 함수 인자는 모두 동일. ENCODE TYPE에 따라 사용 해야 하는 함수 명이 각각 다름에 주의 해야 한다. 복호화 함수 정보는 암호화 테이블의 ****_ROOT View Script에서 얻을 수 있음 Domain Index가 걸린 컬럼의 경우 ****_ROOT에서는 Operator만을 얻을 수 있고 관련 있는 복호화 함수는 얻을 수 없 는 경우가 있음. DAMO.DEC_VARCHAR(‘SM’, ‘MEMBER’, ‘JUMIN_NO’,SEC_JUMIN_NO) -- “JUMIN_NO” 컬럼이 varchar2 type 인 경우 VARCHAR2 CHAR NUMBER DATE RAW CLOB/BLOB DEC_VARCHAR DEC_CHAR DEC_NUMBER DEC_DATE DEC_RAW DEC_CLOB, DEC_BLOB HEX DEC_VARCHAR_HEX DEC_CHAR_HEX DEC_NUMBER_HEX B64 DEC_VARCHAR_B64 DEC_CHAR_B64 DEC_NUMBER_B64 부분암호화 DEC_VARCHAR_PE DEC_VARCHAR_PE_B64 DEC_CHAR_PE DEC_CHAR_PE_B64 DP-ORA 쿼리 최적화 가이드

15 VTI모드 쿼리 최적화 방법 – 인덱스 함수 암호화 인덱스 함수
III. VTI 모드 쿼리 최적화 방법 3. 인덱스 함수 VTI모드 쿼리 최적화 방법 – 인덱스 함수 암호화 인덱스 함수 암호화 인덱스 함수(‘소유자’,’테이블’,’컬럼’,값) : 암호문 값을 인덱스용 암호화 값으로 변환함. 부분 암호화의 경우 암호화 방식에 따라 각기 다른 암호화 인덱스 함수를 사용함. 암호화 인덱스 함수 정보는 암호화 테이블 도메인 인덱스 Script에서 얻을 수 있음 ENCODE TYPE에 따라 사용 해야 하는 함수 명이 각각 다름에 주의 해야 한다. DAMO.PRED_META_VARCHAR2(‘SM’, ‘MEMBER’, ‘JUMIN_NO’, SEC_JUMIN_NO) VARCHAR2 CHAR NUMBER RAW PRED_META_VARCHAR2 PRED_META_CHAR PRED_META_NUMBER HEX PRED_META_VARCHAR2_HEX PRED_META_CHAR_HEX PRED_META_NUMBER_HEX B64 PRED_META_VARCHAR2_B64 PRED_META_CHAR_B64 PRED_META_NUMBER_B64 부분암호화 PRED_META_VARCHAR2_PE PRED_META_VARCHAR2_PE_B64 PRED_META_CHAR_PE PRED_META_CHAR_PE_B64 지원 안함(부분암호화의 경우 CHAR, VARCHAR2 타입만 지원) DP-ORA 쿼리 최적화 가이드

16 PRED_META_PLAIN_LIKE
III. VTI 모드 쿼리 최적화 방법 3. 인덱스 함수 VTI모드 쿼리 최적화 방법 – 인덱스 함수 평문값 인덱스 함수 평문암호화 인덱스 함수(‘소유자’,’테이블’,’컬럼’,값) : 평문값을 인덱스용 암호화 값으로 변환함. 심플 암호화 인덱스 함수 정보는 암호화 테이블 도메인 인덱스 Script에서 얻을 수 있음 DAMO.PRED_META_PLAIN_V(‘ ’) DAMO.PRED_META_PLAIN_LIKE(‘ %’) VARCHAR CHAR NUMBER LIKE PRED_META_PLAIN_V PRED_META_PLAIN_C PRED_META_PLAIN_N PRED_META_PLAIN_LIKE DP-ORA 쿼리 최적화 가이드

17 VTI모드 쿼리 최적화 방법 – 단방향 함수 단방향(HASH) 함수 DAMO.HASH_STR_DATA(‘1234567890’)
III. VTI 모드 쿼리 최적화 방법 4. 단방향 함수 VTI모드 쿼리 최적화 방법 – 단방향 함수 단방향(HASH) 함수 단방향 함수(값) : 평문을 단방향(HASH) 알고리즘으로 암호화 변환함. 단방향 함수의 경우 알고리즘 선택을 단방향 함수 스크립트(SCRIPT) 내 숫자 선택 수정으로 설정함. 아래와 같이 단방향 함수 스크립트 내 2가지 파라미터 변경으로 알고리즘 선택 알고리즘 파라미터 변경 후 HASH_STR_DATA 함수 재 생성 해야 적용됨 1 :HAS160, 2: SHA1, 3: SHA256, 4: SHA512 ****기본값 : 3: SHA256 DAMO.HASH_STR_DATA(‘ ’) IF INIT_CHECK.V_JAVA = FALSE THEN RET := DAMO.EXT_HASH_STR_DATA( OUTTMP, INDATA, '3' ); IF RET < 0 THEN RAISE_APPLICATION_ERROR(-20902, 'ERROR ! HASH VALUE IS NOT VALID' ); RETURN NULL; END IF; ELSE OUTRAW := DAMO.JAVA_HASH_STR_DATA( UTL_RAW.CAST_TO_RAW(INDATA), TO_NUMBER('3') ); -- 4:SHA512, 3:SHA256, 2:SHA1, 1:HAS160 DP-ORA 쿼리 최적화 가이드

18 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (1/11)
III. VTI 모드 쿼리 최적화 방법 5. SELECT문 변경 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (1/11) 대량의 데이터를 처리하는 Inline Sub-Query 의 변경 불필요한 대량의 복호화 작업을 우회하여 암호화 값 자체로 비교하여 처리 Inline Sub-Query 결과 : 10만 건, 최종 결과값 : 20건  10만 건의 모든 데이터를 복호화 하는 것은 매우 큰 성능저하를 불러옴  10만 건의 모든 데이터를 복호화 하는 것 대신 사용자에게 보여줄 최종 결과인 20건에 대한 복호화만 하도록 쿼리 수 정을 통하여 성능 개선 DAMO.HASH_STR_DATA(‘ ’) SELECT A.A, DAMO.DEC_VARCHAR(‘SM’,’MEMBER’,’JUMIN_NO’,A.SEC_JUMIN_NO) JUMIN_NO, B.B FROM (SELECT CS_PK, SEC_JUMIN_NO FROM MEMBER_HIS_DAMO) A, MEMBER_DAMO B WHERE A.A = B.A; DP-ORA 쿼리 최적화 가이드

19 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (2/11)
III. VTI 모드 쿼리 최적화 방법 5. SELECT문 변경 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (2/11) 암호화 컬럼으로 일치 검색(=) 최적화 방안 (암호화 값 자체 인덱스로 비교) 암호화 적용 후 암호화 대상 컬럼 (SEC_JUMIN_NO)에 일반 인덱스(Normal INDEX)를 적용 암호화 적용 시 반드시 IV모드는 Fixed IV로 설정 인덱스 구성 컬럼 변경 : (CS_PK, JUMIN_NO, B) -> (CS_PK, SEC_JUMIN_NO, B) SELECT CS_PK, JUMIN_NO FROM MEMBER WHERE CS_SECD = '1' AND DEL_YN = '0' AND JUMIN_NO = ' ' SELECT CS_PK, DAMO.DEC_VARCHAR(‘SM’,’MEMBER’,’JUMIN_NO’,SEC_JUMIN_NO) JUMIN_NO FROM MEMBER_DAMO WHERE CS_SECD = '1' AND DEL_YN = '0' AND SEC_JUMIN_NO = DAMO.ENC_VARCHAR(‘SM’,’MEMBER’,’JUMIN_NO’,’ ‘) DP-ORA 쿼리 최적화 가이드

20 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (3/11)
III. VTI 모드 쿼리 최적화 방법 5. SELECT문 변경 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (3/11) 암호화 컬럼 간에 JOIN 검색인 경우 암호화 컬럼간에 Join 검색인 경우 쿼리 수정을 하지 않는다면 Full Table Scan으로 처리되어 성능 저하 크게 발생 Join 테이블의 두 개의 암호화 대상 컬럼(SEC_컬럼)에 일반 인덱스(Normal INDEX)를 설정 암호화 적용 시 두 개의 Join 대상 암호화 컬럼 모두 Fixed IV와 동일한 Key로 암호화 적용 (필수) SELECT A.C, A.JUMIN_NO FROM MEMBER A, MEMBER_HIS B WHERE CS_SECD = '1' AND DEL_YN = '0' AND A.JUMIN_NO = B.JUMIN_NO SELECT A.C, DAMO.DEC_VARCHAR(‘SM’,’MEMBER’,’JUMIN_NO’,A.SEC_JUMIN_NO) JUMIN_NO FROM MEMBER_DAMO A, MEMBER_HIS_DAMO B WHERE CS_SECD = '1' AND DEL_YN = '0' AND A.SEC_JUMIN_NO = B.SEC_JUMIN_NO DP-ORA 쿼리 최적화 가이드

21 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (4/11)
III. VTI 모드 쿼리 최적화 방법 5. SELECT문 변경 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (4/11) 암호화 컬럼 간에 JOIN 검색과 값에 의한 일치 검색 함께 공존하는 경우 암호화 컬럼간에 Join 검색이 들어간 쿼리의 경우 최적화를 하지 않는다면 Full Table Scan으로 성능 저하 발생 Join 테이블의 두 개의 암호화 대상 컬럼(SEC_컬럼)에 일반 인덱스(Normal INDEX)를 설정 암호화 적용 시 두 개의 Join 대상 암호화 컬럼 모두 Fixed IV와 동일한 Key로 암호화 적용 (필수) SELECT A.A, A.SOC_NO, B.DEPT_NAME FROM CUST A, CUST_DEP B WHERE A.SOC_NO = B.SOC_NO AND A.SOC_NO = ‘ ’; SELECT A.A, DAMO.DEC_VARCHAR (‘DEV’, ‘CUST’, ‘SOC_NO’, A.SEC_SOC_NO) SOC_NO, B.DEPT_NAME FROM CUST_DAMO A, CUST_DEP_DAMO B WHERE A.SEC_SOC_NO = B.SEC_SOC_NO AND A.SEC_SOC_NO = DAMO.ENC_VARCHAR (‘DEV’, ‘CUST’, ‘SOC_NO’, ‘ ’); DP-ORA 쿼리 최적화 가이드

22 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (5/11)
III. VTI 모드 쿼리 최적화 방법 5. SELECT문 변경 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (5/11) 전체 암호화 컬럼으로 LIKE 검색 최적화 방안 전체 암호화 컬럼으로 LIKE 검색인 경우 기본적으로 암호화 인덱스(Domain INDEX)를 사용하여 빠른 결과값을 얻을 수 있지만 옵티마이져(Optimizer)의 판단에 따라 Full Table Scan으로 처리되면 성능 저하 크게 발생 LIKE 검색에 활용되는 암호화 컬럼에 대해서는 암호화 인덱스(Domain INDEX)를 설정 주민번호 컬럼으로 LIKE 검색 예시 SELECT A, JUMIN_NO FROM MEMBER WHERE CS_SECD = '1' AND DEL_YN = '0' AND JUMIN_NO LIKE ' %' SELECT A, DAMO.DEC_VARCHAR(‘SM’,’MEMBER’,’JUMIN_NO’,SEC_JUMIN_NO) JUMIN_NO FROM MEMBER_DAMO WHERE CS_SECD = '1' AND DEL_YN = '0' AND DAMO.PRED_META_VARCHAR2(‘SM’, ‘MEMBER’, ‘JUMIN_NO’, SEC_JUMIN_NO) LIKE DAMO.PRED_META_PLAIN_LIKE(' %‘) DP-ORA 쿼리 최적화 가이드

23 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (6/11)
III. VTI 모드 쿼리 최적화 방법 5. SELECT문 변경 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (6/11) 부분 암호화 컬럼으로 LIKE 검색 최적화 방안 부분 암호화 컬럼으로 LIKE 검색인 경우 쿼리 최적화를 한다면 암호화 전과 같이 빠른 속도를 얻을 수 있음 부분 암호화의 범위 검색의 경우 반드시 쿼리 최적화 수행 범위 검색이 활용되는 암호화 컬럼에 대해서는 일반 인덱스(Normal INDEX) 설정 부분 암호화 주민번호 컬럼으로 LIKE 검색 예시 SELECT A, JUMIN_NO FROM MEMBER WHERE NTLT_PK = '1' AND JUMIN_NO LIKE ‘ %' SELECT A, DAMO.DEC_VARCHAR_PE(‘SM’,’MEMBER’,’JUMIN_NO’,SEC_JUMIN_NO) JUMIN_NO FROM MEMBER_DAMO WHERE NTLT_PK = '1' AND SEC_JUMIN_NO LIKE ‘ %‘ [ 부분암호화 적용 시 최적화 방안 : 주민번호 뒤 8자리부터 암호화 ] DP-ORA 쿼리 최적화 가이드

24 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (7/11)
III. VTI 모드 쿼리 최적화 방법 5. SELECT문 변경 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (7/11) 부분 암호화 컬럼으로 BETWEEN 검색 최적화 방안 부분 암호화 컬럼으로 BETWEEN 검색인 경우 쿼리 최적화를 한다면 암호화 전과 같이 빠른 속도를 얻을 수 있음 BETWEEN 검색의 경우 반드시 쿼리 최적화 수행 BETWEEN 검색이 활용되는 암호화 컬럼에 대해서는 암호화 인덱스(Domain INDEX)를 생성 부분 암호화 적용 시에도 아래 방법으로 쿼리 최적화 수행 SELECT A, JUMIN_NO FROM MEMBER WHERE NTLT_PK = '1' AND JUMIN_NO BETWEEN ‘ ‘ AND ‘ ’ SELECT A, DAMO.DEC_VARCHAR_PE(‘SM’,’MEMBER’,’JUMIN_NO’,SEC_JUMIN_NO) JUMIN_NO FROM MEMBER_DAMO WHERE NTLT_PK = '1' AND DAMO.PRED_META_VARCHAR2(‘SM’, ‘MEMBER’, ‘JUMIN_NO’, SEC_JUMIN_NO) BETWEEN DAMO.PRED_META_PLAIN_V(‘ ‘) AND DAMO.PRED_META_PLAIN_V(‘ ‘) DP-ORA 쿼리 최적화 가이드

25 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (8/11)
III. VTI 모드 쿼리 최적화 방법 5. SELECT문 변경 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (8/11) ORDER BY 최적화 방안 ORDER BY 절에 암호화 컬럼이 포함되는 경우 최적화를 적용 한다면 불필요한 복호화를 줄임으로써 최대한의 성능을 이끌어 낼 수 있음 ORDER BY 절에 암호화 컬럼이 포함되는 경우 반드시 쿼리 최적화 수행 결과절에 ORDER BY 절에 들어가는 암호화 대상 컬럼이 있는 경우에만 성능 개선이 보임 SELECT NTLT_PK, JUMIN_NO FROM MEMBER WHERE NTLT_PK = '1' AND JUMIN_NO = ‘ ‘ ORDER BY JUMIN_NO ASC SELECT NTLT_PK, DAMO.DEC_VARCHAR(‘SM’,’MEMBER’,’JUMIN_NO’,SEC_JUMIN_NO) AS JUMIN_NO FROM MEMBER_DAMO WHERE NTLT_PK = '1' AND SEC_JUMIN_NO = DAMO.ENC_VARCHAR(‘SM’,’MEMBER’,’JUMIN_NO’, ’) ORDER BY 2 ASC DP-ORA 쿼리 최적화 가이드

26 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (9/11)
III. VTI 모드 쿼리 최적화 방법 5. SELECT문 변경 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (9/11) GROUP BY 최적화 방안 GROUP BY 절에 암호화 컬럼이 포함되는 경우 최적화를 적용 한다면 불필요한 복호화를 줄임으로써 최대한의 성능을 이끌어 낼 수 있음 GROUP BY 절에 암호화 컬럼이 포함되는 경우 반드시 쿼리 최적화 수행 결과절에 GROUP BY 절에 들어가는 암호화 대상 컬럼이 있는 경우에만 성능 개선이 보임 SELECT NTLT_PK, JUMIN_NO FROM MEMBER WHERE NTLT_PK = '1' AND JUMIN_NO = ‘ ‘ GROUP BY A, JUMIN_NO SELECT NTLT_PK, DAMO.DEC_VARCHAR(‘SM’,’MEMBER’,’JUMIN_NO’,SEC_JUMIN_NO) AS JUMIN_NO FROM MEMBER_DAMO WHERE NTLT_PK = '1' AND SEC_JUMIN_NO = DAMO.ENC_VARCHAR(‘SM’,’MEMBER’,’JUMIN_NO’, ’) GROUP BY A, 2 DP-ORA 쿼리 최적화 가이드

27 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (10/11)
III. VTI 모드 쿼리 최적화 방법 5. SELECT문 변경 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (10/11) Index_desc Hint를 사용하고자 할 경우 (1/2) 쿼리 최적화를 통해 Index_desc Hint를 사용하기 위해서는 Join에 사용되거나 검색에 사용되는 암호화 컬럼을 인덱스용 함수를 이용하여 수정 Hint의 인덱스명은 암호화용 함수 기반 인덱스명을 사용하고, 기존에 결합인덱스가 있었다면 암호화용 함수기반 인덱 스가 결합인덱스에 포함되어 있어야 함 인덱스 컬럼절 (IDEX_DETAIL(CUST_ID, REAL_ID)) -> ( IDX_DETAIL_DAMO (CUST_ID, DAMO.PRED_META_VARCHAR2(‘JIKER’,‘TUNING_GUIDE’, ‘REAL_ID’, SEC_REAL_ID)) ) 쿼리 최적화 전 SELECT CUST_ID, CUST_NAME, REG_DATE, CUST_TYPE, REAL_ID, REAL_NAME FROM TUNING_GUIDE O WHERE O.REG_DATE BETWEEN TO _DATE(‘ ’, yyyymmdd’) AND TO_DATE(‘ ’, yyyymmdd’) AND O.CUST_TYPE = ‘A’ AND O.REPRESENTATIVE_SEQ = (SELECT /* + INDEX_DESC(I IDEX_DETAIL) */ PAY_SEQ FROM TUNING_GUIDE_PAY_DETAIL I WHERE O.CUST_ID = I.CUST_ID AND O.REAL_ID=I.REAL_ID AND ROWNUM=1) DP-ORA 쿼리 최적화 가이드

28 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (11/11)
III. VTI 모드 쿼리 최적화 방법 5. SELECT문 변경 VTI모드 쿼리 최적화 방법 – SELECT문 변경 (11/11) Index_desc Hint를 사용하고자 할 경우 (2/2) 쿼리 최적화 후 SELECT CUST_ID, CUST_NAME, REG_DATE, CUST_TYPE, DAMO.DEC_VARCHAR (‘JIKER, ‘TUNING_GUIDE’, ‘REAL_ID’, O.SEC_REAL_ID) REAL_ID, REAL_NAME FROM TUNING_GUIDE_DAMO O WHERE O.REG_DATE BETWEEN TO _DATE(‘ ’, yyyymmdd’) AND TO_DATE(‘ ’, yyyymmdd’) AND O.CUST_TYPE = ‘A’ AND O.REPRESENTATIVE_SEQ = (SELECT /*+ INDEX_DESC(I IDEX_DETAIL_DAMO) / PAY_SEQ FROM TUNING_GUIDE_PAY_DETAIL_DAMO I WHERE O.CUST_ID = I.CUST_ID AND DAMO.PRED_META_VARCHAR2('JIKER','TUNING_GUIDE','REAL_ID',O.SEC_REAL_ID) = DAMO.PRED_META_VARCHAR2('JIKER','TUNING_GUIDE_PAY_DETAIL','REAL_ID', I.SEC_REAL_ID) AND ROWNUM=1) DP-ORA 쿼리 최적화 가이드

29 VTI모드 쿼리 최적화 방법 – DML 변경 (1/3)
III. VTI 모드 쿼리 최적화 방법 6. DML 변경 VTI모드 쿼리 최적화 방법 – DML 변경 (1/3) DML 문장의 변경 INSERT INTO MEMBER(A, JUMIN_NO, B, C) VALUES (1, ‘ ’, 1000, ’A’); INSERT INTO MEMBER_DAMO(A, SEC_JUMIN_NO, B, C) VALUES (1, DAMO.ENC_VARCHAR(‘SM’,’MEMBER’,’JUMIN_NO’,‘ ’), 1000, ’A’); UPDATE MEMBER SET JUMIN_NO = ‘ ’ WHERE A = 1; UPDATE MEMBER_DAMO SET SEC_JUMIN_NO = DAMO.ENC_VARCHAR(‘SM’,’MEMBER’,’JUMIN_NO’,‘ ’) WHERE A = 1; [ 전체암호화 적용 시 최적화 방안 ] UPDATE MEMBER SET JUMIN_NO = ‘ ’ WHERE A = 1; UPDATE MEMBER_DAMO SET SEC_JUMIN_NO = DAMO.ENC_VARCHAR_PE(‘SM’,’MEMBER’,’JUMIN_NO’,‘ ’) WHERE A = 1; [ 부분암호화 적용 시 최적화 방안 : 주민번호 뒤 8자리부터 암호화 ] DP-ORA 쿼리 최적화 가이드

30 VTI모드 쿼리 최적화 방법 – DML 변경 (2/3)
III. VTI 모드 쿼리 최적화 방법 6. DML 변경 VTI모드 쿼리 최적화 방법 – DML 변경 (2/3) DML 문장의 변경 (같은 암호화 키, Fixed IV로 암호화되어 있다는 가정) INSERT INTO MEMBER(A, ACTNUM, B, C) SELECT A, ACTNUM, B, C FROM MEMBER_GP WHERE ACTNUM LIKE ‘ %’; INSERT INTO MEMBER_DAMO(A, SEC_ACTNUM, B, C) SELECT A, SEC_ACTNUM, B, C FROM MEMBER_GP_DAMO WHERE DAMO.PRED_META_VARCHAR2(‘SM’, ‘MEMBER_GP’, ‘ACTNUM’, SEC_ACTNUM) LIKE DAMO.PRED_META_PLAIN_LIKE(‘ %’); [ 전체암호화 적용 시 최적화 방안 ] INSERT INTO MEMBER(A, JUMIN_NO, B, C) SELECT A, RRN, B, C FROM MEMBER_HIS WHERE RRN LIKE ‘710101%’; INSERT INTO MEMBER_DAMO(A, SEC_JUMIN, B, C) SELECT A, SEC_RRN, B, C FROM MEMBER_HIS_DAMO WHERE SEC_RRN LIKE ‘710101%’; [ 부분암호화 적용 시 최적화 방안 : 주민번호 뒤 8자리부터 암호화 ] DP-ORA 쿼리 최적화 가이드

31 VTI모드 쿼리 최적화 방법 – DML 변경 (3/3)
III. VTI 모드 쿼리 최적화 방법 6. DML 변경 VTI모드 쿼리 최적화 방법 – DML 변경 (3/3) DML 문장의 변경 암호화된 컬럼에 WHERE ROWNUM + ORDER BY 를 활용하여 INSERT를 시도할 경우, ROWNUM 값이 MIN 또는 MAX 값으로 고정되는 현상이 발생함 쿼리최적화를 통하여 아래와 같이 변경 (또는 물리적 테이블을 직접 참조하도록 변경) INSERT INTO MEMBER SELECT ROWNUM, A, B FROM MEMBER_HIS WHERE ROWNUM <=4 ORDER BY 1; INSERT INTO MEMBER SELECT * FROM (SELECT A, B FROM MEMBER_HIS ORDER BY 1) WHERE ROWNUM <=4; DP-ORA 쿼리 최적화 가이드

32 쿼리 최적화 방법 – 단방향 (HASH) 함수 적용
III. VTI 모드 쿼리 최적화 방법 7. 단방향 (HASH) 함수 적용 쿼리 최적화 방법 – 단방향 (HASH) 함수 적용 단방향 함수 적용 단방향 암호화는 D’Amo Console을 사용하지 않으며, 작업자가 직접 해당 컬럼을 단방향 함수를 사용하여 UPDATE 적 용함. 단방향 암호화 적용 전 반드시 단방향 함수 적용 컬럼의 길이 증가가 필요함. (최소 50byte 이상) HAS160, SHA1, SHA256 : 50byte 이상 길이 증가 SHA512 : 80byte 이상 길이 증가 단방향 암호화 대상인 MEMBER 테이블의 PASSWD 컬럼의 길이를 변경 단방향 알고리즘 확인 후 단방향 함수를 사용하여 MEMBER 테이블의 PASSWD 컬럼을 UPDATE INSERT INTO MEMBER(A, ACTNUM, B, C) SELECT A, ACTNUM, B, C FROM MEMBER_GP WHERE ACTNUM LIKE ‘ %’; INSERT INTO MEMBER(A, JUMIN_NO, B, C) SELECT A, RRN, B, C FROM MEMBER_HIS WHERE RRN LIKE ‘710101%’; DP-ORA 쿼리 최적화 가이드

33 VTI모드 쿼리 최적화 방법 – 단방향(HASH) 함수 쿼리 변경 (1/2)
III. VTI 모드 쿼리 최적화 방법 8. 단방향 (HASH) 함수 변경 VTI모드 쿼리 최적화 방법 – 단방향(HASH) 함수 쿼리 변경 (1/2) SELECT 문장의 변경 INSERT 문장의 변경 SELECT ID, LOGIN_DATA, CHECK_YN FROM MEMBER WHERE ID = :ID AND PASSWD = :PASSWD SELECT ID, LOGIN_DATA, CHECK_YN FROM MEMBER WHERE ID = :ID AND PASSWD = DAMO.HASH_STR_DATA(:PASSWD) INSERT INTO MEMBER(ID, PASSWD, LOGIN_DATA, CHECK_YN) VALUES (:ID, :PASSWD, :LOGIN_DATA, :CHECK_YN) INSERT INTO MEMBER(ID, PASSWD, LOGIN_DATA, CHECK_YN) VALUES (:ID, DAMO.HASH_STR_DATA(:PASSWD), :LOGIN_DATA, :CHECK_YN) DP-ORA 쿼리 최적화 가이드

34 VTI모드 쿼리 최적화 방법 – 단방향(HASH) 함수 쿼리 변경 (2/2)
III. VTI 모드 쿼리 최적화 방법 8. 단방향 (HASH) 함수 변경 VTI모드 쿼리 최적화 방법 – 단방향(HASH) 함수 쿼리 변경 (2/2) UPDATE 문장의 변경 DELETE 문장의 변경 UPDATE MEMBER SET PASSWD = :NEW_PASSWD WHERE ID = :ID UPDATE MEMBER SET PASSWD = DAMO.HASH_STR_DATA(:NEW_PASSWD) WHERE ID = :ID DELETE FROM MEMBER WHERE ID = :ID AND PASSWD = :PASSWD DELETE FROM MEMBER WHERE ID = :ID AND PASSWD = DAMO.HASH_STR_DATA(:PASSWD) DP-ORA 쿼리 최적화 가이드

35 API 모드 쿼리 최적화 방법

36 IV. API 모드 쿼리 최적화 방법 1. 쿼리 최적화 방법 API 모드 쿼리 최적화 방법 쿼리 최적화하는 방법은 매우 다양한 경우가 존재하지만 기본적으로 다음의 방식 중 하나 또는 복수의 방 법들을 사용합니다. 복호화 된 값 대신에 암호화 값을 직접 이용하도록 변경 쿼리, 프로시져, 패키지에서 직접 암복호화 함수를 호출 LIKE, BETWEEN 검색을 위한 암호화 인덱스 생성 및 암호화 인덱스 함수를 호출 비밀번호 암호화를 위한 단방향 암호화 함수를 호출 DP-ORA 쿼리 최적화 가이드

37 API 모드 쿼리 최적화 방법 – 암복호화 함수 (1/2)
IV. API 모드 쿼리 최적화 방법 2. 암복호화 함수 API 모드 쿼리 최적화 방법 – 암복호화 함수 (1/2) 암호화 함수 암호화 함수명(‘정책명’, ‘암호화 하고자 하는 값’, ‘로그 정보’) – 로그정보는 생략 가능 함. DAMO.ENCRYPT_VAR(‘AES_128’, ’ ’, ‘’) DAMO.ENCRYPT_VAR(‘AES_128’, ’ ’, ‘OWNER||TABLE||COLUMN’) VARCHAR2 CHAR NUMBER DATE RAW CLOB/BLOB ENCRYPT_VAR ENCRYPT_CHR ENCRYPT_NUM ENCRYPT_DATE ENCRYPT_RAW ENCRYPT_CLOB, ENCRYPT_BLOB HEX ENCRYPT_VAR_HEX ENCRYPT_CHR_HEX ENCRYPT_NUM_HEX B64 ENCRYPT_VAR_B64 ENCRYPT_CHR_B64 ENCRYPT_NUM_B64 부분암호화 ENCRYPT_VAR_PE ENCRYPT_VAR_PE_B64 ENCRYPT_CHR_PE DP-ORA 쿼리 최적화 가이드

38 API 모드 쿼리 최적화 방법 – 암복호화 함수 (2/2)
IV. API 모드 쿼리 최적화 방법 2. 암복호화 함수 API 모드 쿼리 최적화 방법 – 암복호화 함수 (2/2) 복호화 함수 복호화 함수명(‘정책명’, ‘암호화 하고자 하는 값’, ‘로그 정보’) - 로그정보는 생략 가능 함. DAMO.DECRYPT_VAR(‘AES_128’, ’ ’, ‘’) DAMO.DECRYPT_VAR(‘AES_128’, ’ ’, ‘OWNER||TABLE||COLUMN’) VARCHAR2 CHAR NUMBER DATE RAW CLOB/BLOB DECRYPT_VAR DECRYPT_CHR DECRYPT_NUM DECRYPT_DATE DECRYPT_RAW DECRYPT_CLOB, DECRYPT_BLOB HEX DECRYPT_VAR_HEX DECRYPT_CHR_HEX DECRYPT_NUM_HEX B64 DECRYPT_VAR_B64 DECRYPT_CHR_B64 DECRYPT_NUM_B64 부분암호화 DECRYPT_VAR_PE DECRYPT_VAR_PE_B64 DECRYPT_CHR_PE DP-ORA 쿼리 최적화 가이드

39 API 모드 쿼리 최적화 방법 – 인덱스 함수 (1/2)
IV. API 모드 쿼리 최적화 방법 3. 인덱스 함수 API 모드 쿼리 최적화 방법 – 인덱스 함수 (1/2) 인덱스 암호화 함수 인덱스 함수명(‘정책명’, ‘암호화 하고자 하는 값’) DAMO.INDEX_VAR(‘AES_128’, ’ ’) VARCHAR2 CHAR NUMBER RAW INDEX_VAR INDEX_CHR INDEX_NUM HEX INDEX_VAR_HEX INDEX_CHR_HEX INDEX_NUM_HEX B64 INDEX_VAR_B64 INDEX_CHR_B64 INDEX_NUM_B64 부분암호화 INDEX_VAR_PE INDEX_VAR_PE_B64 INDEX_CHR_PE INDEX_CHR_PE_B64 DP-ORA 쿼리 최적화 가이드

40 API 모드 쿼리 최적화 방법 – 인덱스 함수 (2/2)
IV. API 모드 쿼리 최적화 방법 3. 인덱스 함수 API 모드 쿼리 최적화 방법 – 인덱스 함수 (2/2) 평문값 인덱스 함수 인덱스 함수명(‘암호화 하고자 하는 값’) DAMO.PRED_META_PLAIN_V(’ ’) DAMO.PRED_META_PLAIN_LIKE(’81%’) VARCHAR2 CHAR NUMBER LIKE 인덱스 함수명 PRED_META_PLAIN_V PRED_META_PLAIN_C PRED_META_PLAIN_N PRED_META_PLAIN_LIKE DP-ORA 쿼리 최적화 가이드

41 [인덱스 암호화 함수를 이용하여 FBI 생성 후 최적화 수행]
IV. API 모드 쿼리 최적화 방법 4. 인덱스 함수 사용 방법 API 모드 인덱스 함수 사용 방법 인덱스 암호화 함수를 사용하여 FBI 생성 API의 경우 인덱스 암호화 함수를 사용하여 암호화 대상 컬럼에 FBI를 생성 한 후 최적화를 수행 암호화 TABLE : IDX_TEST 암호화 COLUMN : PNO2 CREATE INDEX IDX_TEST_DAMO2 ON IDX_TEST (DAMO.INDEX_VAR('ALL',PNO3)); SELECT DAMO.DECRYPT_VAR('ALL',PNO2,'') FROM IDX_TEST WHERE DAMO.INDEX_VAR('ALL',PNO2) LIKE DAMO.PRED_META_PLAIN_LIKE('48%'); SELECT DAMO.DECRYPT_VAR('ALL',PNO3,'') WHERE DAMO.INDEX_VAR('ALL',PNO3) BETWEEN DAMO.PRED_META_PLAIN_LIKE('97%') AND DAMO.PRED_META_PLAIN_LIKE('98%') [인덱스 암호화 함수를 이용하여 FBI 생성 후 최적화 수행] DP-ORA 쿼리 최적화 가이드

42 최적화된 쿼리 검증 방법

43 최적화된 쿼리 검증 방법 최적화된 쿼리 수행 결과 정합성 최적화된 쿼리 수행 전후 동일한 결과 보장
V. 최적화된 쿼리 검증 방법 최적화된 쿼리 검증 방법 최적화된 쿼리 수행 결과 정합성 최적화된 쿼리 수행 전후 동일한 결과 보장 쿼리를 최적화하기 이전의 쿼리 수행 결과와 쿼리를 최적화한 후의 쿼리 수행 결과는 동일한지 확인해야 합니다. 수행 결과 Rows 수행 결과 List 최적화된 쿼리에 대한 실행 계획 (Execution Plan) 점검 최적화된 쿼리의 실행 계획을 점검하여 의도한 바와 같이 실행 계획이 수립되었는지 확인해야 합니다. 암호화 작업을 반영한 실행 계획의 COST 가 절절한 수준인지 점검하는 것이 바람직합니다. DP-ORA 쿼리 최적화 가이드

44 최적화된 쿼리 검증 방법 실행 계획 COST 확인 SQL Plus에서 실행 계획 확인하기
V. 최적화된 쿼리 검증 방법 최적화된 쿼리 검증 방법 실행 계획 COST 확인 SQL Plus에서 실행 계획 확인하기 SQLPLUS에 접속하여 autotrace를 On 으로 설정합니다. 수행하고자 하는 SQL을 실행한 후 COST를 확인합니다. DP-ORA 쿼리 최적화 가이드

45 최적화된 쿼리 검증 방법 실행 계획 COST 확인 방법 COST 기반 Optimizer 설정 Autotrace 설정하기
V. 최적화된 쿼리 검증 방법 최적화된 쿼리 검증 방법 실행 계획 COST 확인 방법 Autotrace 설정하기 SQL 수행 DB 계정에 plustrce role 부여 $> Connect /as sysdba SQL> Grant plustrce to scott; Plustrce role이 존재하지 않는 경우 해당 role 설정하기 $> cd $ORACLE_HOME/sqlplus/admin $> sqlplus “sys/password as sysdba” SQL>start plustrce.sql SQL 수행 계정에 plan Table 생성하기 $> cd $ORACLE_HOME/rdbms/admin $> sqlplus scott/tiger SQL>start utlxplan.sql COST 기반 Optimizer 설정 실행 계획의 COST는 비용 기반 옵티마이저에서만 보임 SQL>alter session set optimizer_mode=FIRST_ROWS(ALL_ROWS); DP-ORA 쿼리 최적화 가이드

46 주의사항

47 주의사항 최적화의 한계 최적화가 적합하지 않은 쿼리 유형 암호화 이전에 Tuning 된 쿼리의 최적화 기존 쿼리 로직 변경
VI. 주의사항 주의사항 최적화의 한계 최적화가 적합하지 않은 쿼리 유형 Sum, Avg 등과 같은 집합 함수는 쿼리를 수정해도 최적화 되지 않는 경우가 있습니다. 암호화 이전에 Tuning 된 쿼리의 최적화 많은 조합의 Hint로 이미 Tuning이 된 쿼리는 암호화 이후 최적화 과정에서 Tuning 효과를 상쇄시키는 경우가 발생할 수 있습 니다. 기존 쿼리 로직 변경 기존 쿼리의 로직 Format을 유지한 체, 쿼리를 최적화하는 경우 성능이 보장 되지 않는 경우가 있다. 이런 경우는 쿼리 수행 로 직 변경을 시도해 볼 수 있습니다. 인덱스의 변경 요구 결합 인덱스 구성 컬럼 중 일부만이 암호화 되어, 인덱스 효율 자체가 변경됨으로써 수행 속도 저하가 발생될 수 있다. 이런 경 우 암호화에 맞는 인덱스 생성 전략을 구사해 볼 수 있습니다. 추가 암호화 요구 복수개 테이블 컬럼의 JOIN 시 한쪽만 암호화되어 있고 다른 쪽이 안되어 있는 경우 최적화를 해도 성능 저하를 피할 수 없는 경우가 발생될 수 있습니다. 이런 경우 JOIN 되는 컬럼을 동일 키로 암호화 한 후 인덱스 전략과 병행하여 성능 향상을 시도해 볼 수 있습니다. DP-ORA 쿼리 최적화 가이드

48