【YashanDB知识库】filter or改写问题

问题现象

当filter中出现or的时候，会导致filter无法走索引或者走hash join，就需要进行改写，例如：

create table test_tab1(col1 int, col2 int, col3 int);
create table test_tab2(col4 int, col5 int, col6 int);
 
begin
for i in 1..10000 loop
  insert into test_tab1 values (i,i+1,i+2);
  insert into test_tab2 values (i,i+1,i+2);
end
 loop;
commit;
end;
/
 
exec DBMS_STATS.GATHER_TABLE_STATS('SYS', 'test_tab1', null, 1, FALSE, 'FOR ALL COLUMNS SIZE AUTO', 4, 'AUTO', TRUE);
exec DBMS_STATS.GATHER_TABLE_STATS('SYS', 'test_tab2', null, 1, FALSE, 'FOR ALL COLUMNS SIZE AUTO', 4, 'AUTO', TRUE);
 
select * from test_tab1, test_tab2 where col1=col4 or col2=col5;
 
select * from test_tab1, test_tab2 where col1=col4
union all
select * from test_tab1, test_tab2 where col1<>col4 and col2=col5;
  
create unique index idx1 on test_tab1(col1);
create unique index idx2 on test_tab1(col2);
 
exec DBMS_STATS.GATHER_TABLE_STATS('SYS', 'test_tab1', null, 1, FALSE, 'FOR ALL COLUMNS SIZE AUTO', 4, 'AUTO', TRUE);
exec DBMS_STATS.GATHER_TABLE_STATS('SYS', 'test_tab2', null, 1, FALSE, 'FOR ALL COLUMNS SIZE AUTO', 4, 'AUTO', TRUE);
 
select * from test_tab1 where col1=5 or col2=5;
 
select * from test_tab1 where col1=5
union all
select * from test_tab1 where col1<>5 and col2=5;

执行计划：

SQL> explain select * from test_tab1, test_tab2 where col1=col4 or col2=col5;
 
PLAN_DESCRIPTION
----------------------------------------------------------------
SQL hash value: 1783492608
Optimizer: ADOPT_C
                                                                 
+----+--------------------------------+----------------------+