아래 OTN Forum에 안타까운 사연이 있습니다. :)

http://forums.oracle.com/forums/message.jspa?messageID=3661603

1. Standard Edition을 쓰고 있구요.
2. Standard edition은 1) bitmap index 와 그 형제라 할 수 있는 2) index combination 을 지원하지 않습니다.
3. Fucntion-based Index를 사용하고 있고
4. 불행하게도 "OR" predicate를 사용하고 있습니다.
5. 무엇보다 큰 문제는 Query Text를 수정할 수 없다는 겁니다. Software에 내장된 Query라서...

제가 왜 이것을 안타까운 사연이라고 부르는지 아래 데모를 보시면 이해하실 겁니다.

1. Object를 만듭니다.

drop table t1 purge;

create table t1(c1 int, c2 int, c3 int);

insert into t1 
select level, level, level
from dual
connect by level <= 100000;

create index t1_n1 on t1(c1+1);  -- function-based index
create index t1_n2 on t1(c2+1);  -- function-based index
create index t1_n3 on t1(c1);  -- normal index
create index t1_n4 on t1(c2);  -- normal index

exec dbms_stats.gather_table_stats(user, 't1');

2.Index Combination을 사용가능한 경우에는 다음과 같이 아주 효율적인 실행 계획을 만듭니다.

alter session set "_b_tree_bitmap_plans" = true;

explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;

-------------------------------------------------------------------------------
| Id  | Operation                        | Name  | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |       |     2 |    48 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID     | T1    |     2 |    48 |     2   (0)|
|   2 |   BITMAP CONVERSION TO ROWIDS    |       |       |       |            |
|   3 |    BITMAP OR                     |       |       |       |            |
|   4 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |            |
|*  5 |      INDEX RANGE SCAN            | T1_N1 |       |       |     1   (0)|
|   6 |     BITMAP CONVERSION FROM ROWIDS|       |       |       |            |
|*  7 |      INDEX RANGE SCAN            | T1_N2 |       |       |     1   (0)|
-------------------------------------------------------------------------------
                                                                               
Predicate Information (identified by operation id):                            
---------------------------------------------------                            
                                                                               
   5 - access("C1"+1=1)                                                        
   7 - access("C2"+1=1)                                                        

3. 하지만 Index Combination이 비활성화되면?

alter session set "_b_tree_bitmap_plans" = false; -- In standard edition, this would be fixed behavior.

explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    48 |    99   (6)|
|*  1 |  TABLE ACCESS FULL| T1   |     2 |    48 |    99   (6)|
---------------------------------------------------------------
                                                               
Predicate Information (identified by operation id):            
---------------------------------------------------            
                                                               
   1 - filter("C1"+1=1 OR "C2"+1=1)                            

놀랍게도 Full Table Scan을 선택해버립니다.

4. Function-based Index가 아닌 일반 Index인 경우에는 차선책으로 Or-Expansion을 사용합니다. 이 정도만 해도 충분히 효율적입니다.

explain plan for
select *
from t1
where c1 = 1 or c2 = 1
;

---------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    30 |     4   (0)|
|   1 |  CONCATENATION               |       |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  3 |    INDEX RANGE SCAN          | T1_N4 |     1 |       |     1   (0)|
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  5 |    INDEX RANGE SCAN          | T1_N3 |     1 |       |     1   (0)|
---------------------------------------------------------------------------
                                                                           
Predicate Information (identified by operation id):                        
---------------------------------------------------                        
                                                                           
   3 - access("C2"=1)                                                      
   4 - filter(LNNVL("C2"=1))                                               
   5 - access("C1"=1)                                                      

5. 그렇다면 다음과 같이 힌트를 통해서 완벽하게 제어하면 어떨까요?(이 방법은 이 포스트에 설명되어 있습니다)

explain plan for
select 
		/*+
				INDEX_RS_ASC(@"SEL$1_2" "T1"@"SEL$1_2" "T1_N1")
				INDEX_RS_ASC(@"SEL$1_1" "T1"@"SEL$1" "T1_N2")
				USE_CONCAT(@"SEL$1" 8) */
		*
from t1
where c1+1 = 1 or c2+1 = 1
;

----------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    48 |   195   (4)|
|   1 |  CONCATENATION     |      |       |       |            |
|*  2 |   TABLE ACCESS FULL| T1   |     1 |    24 |    98   (5)|
|*  3 |   TABLE ACCESS FULL| T1   |     1 |    24 |    98   (5)|
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C2"+1=1)
   3 - filter("C1"+1=1 AND LNNVL("C2"+1=1))

역시 안됩니다.

왜 이런 현상이 발생할까요? 아래 문서에 잘 설명되어 있습니다.
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10795/adfns_in.htm#1006464

불행하게도 Oracle은 Or-Expansion과 Funtion-based Index를 같이 사용하지 못합니다. 따라서 이 경우에는 Full Table Scan이 가장 효율적인 실행 계획이 되어 버린 셈입니다.

6. 한가지 더 시도할 수 있는 방법이 있습니다. Stored Outline을 수동으로 생성하는 것이지요. 이 방법은 이 포스트에 설명되어 있습니다. 하지만 이 경우에는 그나마도 불가능합니다. Hint로 제어할 수 있는 영역을 벗어났기 때문입니다.

7. 마지막 트릭이 있습니다. Oracle 10g에서 제공되는 Advacned Query Rewriting 기능을 이용해 SQL Text 자체를 바꿔버리는 겁니다. 단, 이 기능은 아래와 같은 제한이 있습니다.

• 역시 Enterprise Edition에서만 사용 가능하고
• Select 문장만 가능하며
• Bind 변수가 지원되지 않습니다.

이 포스트의 간단한 데모의 경우에는 다행스럽게도 이 기능으로 해결이 가능합니다.

begin
  sys.dbms_advanced_rewrite.declare_rewrite_equivalence (
     name           => 'rewrite1',
     source_stmt =>
'select *
from t1
where c1+1 = 1 or c2+1 = 1',
    destination_stmt =>
'select *
from t1
where c1 = 0 or c2 = 0',
     validate       => false,
     rewrite_mode   => 'text_match');
end;
/

alter session set query_rewrite_integrity = trusted;

explain plan for
select *
from t1
where c1+1 = 1 or c2+1 = 1
;
        
---------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |    30 |     4   (0)|
|   1 |  CONCATENATION               |       |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  3 |    INDEX RANGE SCAN          | T1_N4 |     1 |       |     1   (0)|
|*  4 |   TABLE ACCESS BY INDEX ROWID| T1    |     1 |    15 |     2   (0)|
|*  5 |    INDEX RANGE SCAN          | T1_N3 |     1 |       |     1   (0)|
---------------------------------------------------------------------------
                                                                           
Predicate Information (identified by operation id):                        
---------------------------------------------------                        
                                                                           
   3 - access("C2"=0)                                                      
   4 - filter(LNNVL("C2"=0))                                               
   5 - access("C1"=0)                                                      

하지만, 실제 운영 환경에서는 사용하기에는 너무 제약이 많죠?

OTN Forum에 이 질문을 올린 분께는 미안하지만, 참으로 안타까운 상황에 처하고 말았네요.