일부 스크립트에 오타가 있어 수정했음을 알립니다.(2009.11.19)

거의 모든 시스템에 버그가 존재해 
아주 유명한 FBI 인덱스 관련 버그를 소개한다. 이 버그는 Wrong result 버그로 분류되며 특징은 조회가 되지 않는다는 것이다. 최근까지 Patch 가 나오지 않아 악명이 높았다. 필자의 경험으로는 거의 모든 시스템에서 이 버그가 재연되었다.

버그 재연방법 
CHAR 컬럼의 길이가 2 BYTE 이상이고 이 컬럼에 SUBSTR 함수로 FBI인덱스 생성할 때.

이제 아래의 스크립트를 사용하여 버그를 재연해보자.

환경 : Oracle 11.1.0.6

drop table fbi_test;

create table fbi_test(col1 char(14) );

insert into fbi_test values('01234567890123');
commit;

create index fbi_idx on fbi_test(substr(col1,1,10));

analyze table fbi_test compute statistics;
analyze index fbi_idx compute statistics;

이제 모든 준비가 끝났으므로 데이터를 조회해보자.

select /*+ gather_plan_statistics index(a  fbi_idx ) */  *
 from fbi_test a
where col1 = '01234567890123' ;
no rows selected.

한 건도 나오지 않는다. 이것은 분명 버그이다.
  
--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| FBI_TEST |      1 |      1 |      0 |00:00:00.01 |       1 |
|*  2 |   INDEX RANGE SCAN          | FBI_IDX  |      1 |      1 |      0 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1"='01234567890123')
   2 - access("FBI_TEST"."SYS_NC00002$"='0123456789')
 
위의 Plan을 보면 Virtual Column을 사용하였다. 오라클은 FBI를 만들면 Virtual Column을 생성 한다.
실행계획상에서 Virtual Column을 사용하는 경우는 위와 같이 조회 되지 않는 버그가 발생한다.
이런 상황에서 버그를 피하려면 해당 인덱스를 사용하지 않거나(Full Scan 이용) 아니면 아래와 같이 명시적으로 SQL을 수정해야 한다.

select /*+ gather_plan_statistics index(a  fbi_idx ) */  *
 from fbi_test a
where col1 = '01234567890123'
  and substr(col1,1,10) = '0123456789' ;

COL1         
--------------
01234567890123

1 row selected.

정상적으로 1건이 나왔다.

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS BY INDEX ROWID| FBI_TEST |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  2 |   INDEX RANGE SCAN          | FBI_IDX  |      1 |      1 |      1 |00:00:00.01 |       1 |
--------------------------------------------------------------------------------------------------

 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("COL1"='01234567890123')
   2 - access("FBI_TEST"."SYS_NC00002$"='0123456789')
 
편법은 안돼... 패치가 정답이다
하지만 이런 편법으로 문제를 해결하는 것은 임시방편 밖에 되지 못한다. 또한 모든 개발자들이 substr 함수를 추가하여 개발한다고 보장 할 수 없다. 이 편법을 사용 하더라도 조건절에 > 혹은 < 사용시에는 여전히 조회가 되지 않는다.

select /*+ gather_plan_statistics index(a  fbi_idx ) */  *
 from fbi_test a
where col1 > '0123456789012'
 and substr(col1,1,10) > '012345678' ;

no rows selected.

한가지 다행스러운 점은 최근에 BUG Patch 2개가 새로 나왔다는 것이다. 또한 아쉬운 점은 HP 시스템용 Patch는 아직 없다는 것이다. 각자의 플랫폼에 맞는 Patch가 있는지 확인해보라.

문서번호 : 6131467
패치 ID : 6131467

Posted by extremedb
,