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

来源：这里教程网时间：2026-03-03 20:21:07 作者：

问题现象

当 filte r 中出现 o r 的时候，会导致 filte r 无法走索引或者走 hash joi n ，就需要进行改写，例如：

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

+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

| 0 | SELECT STATEMENT | | | | | |

|* 1 | NESTED LOOPS INNER | | | 15003| 16082( 0)| |

| 2 | TABLE ACCESS FULL | TEST_TAB1 | SYS | 10000| 24( 0)| |

| 3 | TABLE ACCESS FULL | TEST_TAB2 | SYS | 10000| 24( 0)| |

+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

Operation Information (identified by operation id):

---------------------------------------------------

1 - Predicate : filter("TEST_TAB1"."COL2" = "TEST_TAB2"."COL5" OR "TEST_TAB1"."COL1" = "TEST_TAB2"."COL4")

16 rows fetched.

SQL> explain select * from test_tab1, test_tab2 where col1=col4

union all

3 select * from test_tab1, test_tab2 where col1<>col4 and col2=col5;

PLAN_DESCRIPTION

----------------------------------------------------------------

SQL hash value: 2495885085

Optimizer: ADOPT_C

+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

| 0 | SELECT STATEMENT | | | | | |

| 1 | VIEW | | | 20000| 111( 0)| |

| 2 | UNION ALL | | | 20000| 111( 0)| |

|* 3 | HASH JOIN INNER | | | 10000| 54( 0)| |

| 4 | TABLE ACCESS FULL | TEST_TAB1 | SYS | 10000| 24( 0)| |

| 5 | TABLE ACCESS FULL | TEST_TAB2 | SYS | 10000| 24( 0)| |

|* 6 | HASH JOIN INNER | | | 10000| 54( 0)| |

| 7 | TABLE ACCESS FULL | TEST_TAB1 | SYS | 10000| 24( 0)| |

| 8 | TABLE ACCESS FULL | TEST_TAB2 | SYS | 10000| 24( 0)| |

+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

Operation Information (identified by operation id):

---------------------------------------------------

3 - Predicate : access("TEST_TAB1"."COL1" = "TEST_TAB2"."COL4")

6 - Predicate : access("TEST_TAB1"."COL2" = "TEST_TAB2"."COL5")

filter("TEST_TAB1"."COL1" <> "TEST_TAB2"."COL4")

23 rows fetched.

SQL> explain select * from test_tab1 where col1=5 or col2=5;

PLAN_DESCRIPTION

----------------------------------------------------------------

SQL hash value: 3573759094

Optimizer: ADOPT_C

+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

| 0 | SELECT STATEMENT | | | | | |

|* 1 | TABLE ACCESS FULL | TEST_TAB1 | SYS | 2| 25( 0)| |

+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

Operation Information (identified by operation id):

---------------------------------------------------

1 - Predicate : filter("TEST_TAB1"."COL1" = 5 OR "TEST_TAB1"."COL2" = 5)

14 rows fetched.

SQL> explain select * from test_tab1 where col1=5

union all

3 select * from test_tab1 where col1<>5 and col2=5;

PLAN_DESCRIPTION

----------------------------------------------------------------

SQL hash value: 2073754267

Optimizer: ADOPT_C

+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

| 0 | SELECT STATEMENT | | | | | |

| 1 | VIEW | | | 2| 1( 0)| |

| 2 | UNION ALL | | | 2| 1( 0)| |

| 3 | TABLE ACCESS BY INDEX ROWID | TEST_TAB1 | SYS | 1| 1( 0)| |

|* 4 | INDEX UNIQUE SCAN | IDX1 | SYS | 1| 1( 0)| |

|* 5 | TABLE ACCESS BY INDEX ROWID | TEST_TAB1 | SYS | 1| 1( 0)| |

|* 6 | INDEX UNIQUE SCAN | IDX2 | SYS | 1| 1( 0)| |

+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+

Operation Information (identified by operation id):

---------------------------------------------------

4 - Predicate : access("TEST_TAB1"."COL1" = 5)

5 - Predicate : filter("TEST_TAB1"."COL1" <> 5)

6 - Predicate : access("TEST_TAB1"."COL2" = 5)

21 rows fetched.

执行时间：

SQL> select * from test_tab1, test_tab2 where col1=col4 or col2=col5;

...

10000 rows fetched.

Elapsed: 00:00:45.263

SQL> select * from test_tab1, test_tab2 where col1=col4

union all

3 select * from test_tab1, test_tab2 where col1<>col4 and col2=col5;

...

10000 rows fetched.

Elapsed: 00:00:00.447

SQL> select * from test_tab1 where col1=5 or col2=5;

COL1 COL2 COL3

------------ ------------ ------------

4 5 6

5 6 7

2 rows fetched.

Elapsed: 00:00:00.004

SQL> select * from test_tab1 where col1=5

union all

2 3 select * from test_tab1 where col1<>5 and col2=5;

COL1 COL2 COL3

------------ ------------ ------------

5 6 7

4 5 6

2 rows fetched.

Elapsed: 00:00:00.000

问题的风险及影响

性能会大幅下降。

问题影响的版本

截止 202 4 年 4 月份，最新版本依然有这个问题。

问题发生原因

问题单：优化器支持 o r 改为了集合操作（ CONCATENATIO N ）需求没有实现。

解决方法以及规避方法

理论上有两种解决方案，一种是使用 o r 索引，另一种是改写为集合操作，改写为集合操作的覆盖面会更大一些。

问题分析以及处理过程

1 、可以通过观察 oracl e 的执行计划，是否出现 CONCATENATIO N 这个算子，如果出现说明计划被改写为集合操作；

2 、观察 filte r 中是否有 o r 导致了无法走 hash joi n 或者阻碍了走索引计划。

经验总结

执行计划并不是建了索引就可以选上索引的，需要将条件改为可以走索引才行。

编辑推荐：

返回列表

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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推