Title |
[ORACLE] BITMAP CONVERSION에 의한 WRONG RESULT | Product |
Plug-In |
|---|---|---|---|
Phenomenon |
암호화 전 1 row를 return 하던 query가 암호화 후 0 row를 return 함
암호화 전 원본 SQL SQL> select a.사업자등록번호, a.대표자명, a.개인번호, b.개인식별번호, a.대표자이메일주소 2 from 사용_업체대표자상세 a, 사용_개인정보 b 3 where a.사업자등록번호 = '1234567890' 4 and a.개인번호 = b.개인번호 5 and b.식별구분코드 ='1' 6 and b.개인식별번호 like '8%' 7 order by 대표자명 8 ; Execution Plan ---------------------------------------------------------- Plan hash value: 1390702853 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 106 | 0 (0)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 1 | 106 | 0 (0)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID| 사용_업체대표| 4 | 348 | 0 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | 사용_업체대표| 2 | | 0 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | 사용_개인정보| 1 | | 0 (0)| 00:00:01 | |* 6 | TABLE ACCESS BY INDEX ROWID | 사용_개인정보| 1 | 19 | 0 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."사업자등록번호"='1234567890') 5 - access("B"."개인식별번호" LIKE '8%' AND "B"."식별구분코드"='1') filter("B"."식별구분코드"='1' AND "B"."개인식별번호" LIKE '8%') 6 - filter("A"."개인번호"="B"."개인번호") Statistics ---------------------------------------------------------- 446 recursive calls 0 db block gets 85 consistent gets 7 physical reads 0 redo size 909 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed 암호화 후 SQL 수정 없이 수행 SQL> SELECT a.사업자등록번호, a.대표자명, a.개인번호, b.개인식별번호, a.대표자이메일주소 2 from 사용_업체대표자상세 a, 사용_개인정보 b 3 where a.사업자등록번호 = '1234567890' 4 and a.개인번호 = b.개인번호 5 and b.식별구분코드 ='1' 6 and b.개인식별번호 like '8%' 7 order by 대표자명 8 ; no rows selected Execution Plan ---------------------------------------------------------- Plan hash value: 1062875487 ------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 112 | 7 (15)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 1 | 112 | 7 (15)| 00:00:01 | | 3 | TABLE ACCESS BY INDEX ROWID | 사용_업체대 | 4 | 348 | 0 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | 사용_업체대 | 2 | | 0 (0)| 00:00:01 | | 5 | BITMAP CONVERSION TO ROWIDS | | | | | | | 6 | BITMAP AND | | | | | | | 7 | BITMAP CONVERSION FROM ROWIDS| | | | | | |* 8 | INDEX RANGE SCAN | 사용_개인정 | 1 | | 0 (0)| 00:00:01 | | 9 | BITMAP CONVERSION FROM ROWIDS| | | | | | | 10 | SORT ORDER BY | | | | | | |* 11 | DOMAIN INDEX | 개인식554970| 1 | | | | |* 12 | TABLE ACCESS BY INDEX ROWID | 사용_개인정 | 1 | 25 | 7 (15)| 00:00:01 | ------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("A"."사업자등록번호"='1234567890') 8 - access("A"."개인번호"="개인번호") 11 - access("XSERVER"."ODEC_VARCHAR2_SEL"("개인식별번호",10,'AES','사용_개인정보#','개인식별번호') LIKE '8%') filter("XSERVER"."ODEC_VARCHAR2_SEL"("개인식별번호",10,'AES','사용_개인정보#','개인식별번호') LIKE '8%') 12 - filter("식별구분코드"='1') Statistics ---------------------------------------------------------- 6 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 663 bytes sent via SQL*Net to client 513 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 0 rows processed |
||
Cause |
Oracle optimizer가 SQL을 해석할 때 where 절에 여러 조건이 있고 해당 조건 컬럼들에 index가
각각 생성되어 있을 경우 Btree Index를 Bitmap으로 conversion하여 Plan을 수립하여 실행하게 됨. 이럴 경우 성능이 저하되는 경우가 발생 할 수 있으며 where 조건에 domain index가 있을 경우 wrong result가 return되는 현상이 발생할 수 있음. domain index가 있을 경우 wrong result가 return 되는 현상은 현재 오라클본사에 bug로 등록되어 bug fix patch를 만들고 있음. |
||
Solution |
복호화 view가 아닌 암호화 table을 직접 select 하도록 SQL 수정
SQL> select a.사업자등록번호, a.대표자명, a.개인번호, xx1.dec_varchar2_sel(b.개인식별번호,10,'AES'), a.대표자이메일주소 2 from 사용_업체대표자상세 a, 사용_개인정보# b 3 where a.사업자등록번호 = '1234567890' 4 and a.개인번호 = b.개인번호 5 and b.식별구분코드 ='1' 6 and xx1.enc_index_varchar2(b.개인식별번호,'AES') like xx1.enc_index_varchar2_sel('8','AES')||'%' 7 order by 대표자명 8 ; Execution Plan ---------------------------------------------------------- Plan hash value: 813166258 -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 139 | 2 (0)| 00:00:01 | | 1 | NESTED LOOPS | | 1 | 139 | 2 (0)| 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID| 사용_업체대 | 1 | 45 | 1 (0)| 00:00:01 | |* 3 | INDEX UNIQUE SCAN | SYS_C003909 | 1 | | 0 (0)| 00:00:01 | |* 4 | TABLE ACCESS BY INDEX ROWID| 사용_개인정 | 1 | 94 | 1 (0)| 00:00:01 | |* 5 | INDEX UNIQUE SCAN | 사용_개인정 | 1 | | 0 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."사업자등록번호"='1234567890') 4 - filter("XX1"."ENC_INDEX_VARCHAR2"("개인식별번호",'AES') LIKE "XX1"."ENC_INDEX_VARCHAR2_SEL"('8','AES')||'%' AND "B"."식별구분코드"='1') 5 - access("A"."개인번호"="B"."개인번호") Statistics ---------------------------------------------------------- 711 recursive calls 4 db block gets 202 consistent gets 21 physical reads 0 redo size 942 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 16 sorts (memory) 0 sorts (disk) 1 rows processed |
||
메인메뉴
