【YashanDB 知识库】filter or 改写问题
作者：YashanDB
- 2024-07-24 广东
本文字数：4163 字
阅读完需：约 14 分钟
问题现象
当 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); beginfor 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=col4union allselect * 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=5union allselect * 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: 1783492608Optimizer: ADOPT_C +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 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=col4union all 3 select * from test_tab1, test_tab2 where col1<>col4 and col2=col5; PLAN_DESCRIPTION----------------------------------------------------------------SQL hash value: 2495885085Optimizer: ADOPT_C +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 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: 3573759094Optimizer: ADOPT_C +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 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=5union all 3 select * from test_tab1 where col1<>5 and col2=5; PLAN_DESCRIPTION----------------------------------------------------------------SQL hash value: 2073754267Optimizer: ADOPT_C +----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| Id | Operation type | Name | Owner | Rows | Cost(%CPU) | Partition info |+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+| 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=col4union 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=5union 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复制代码
问题的风险及影响
性能会大幅下降。
问题影响的版本
截止 2024 年 4 月份，最新版本依然有这个问题。
问题发生原因
问题单：优化器支持 or 改为了集合操作（CONCATENATION）需求没有实现。
解决方法以及规避方法
理论上有两种解决方案，一种是使用 or 索引，另一种是改写为集合操作，改写为集合操作的覆盖面会更大一些。
问题分析以及处理过程
1、可以通过观察 oracle 的执行计划，是否出现 CONCATENATION 这个算子，如果出现说明计划被改写为集合操作；
2、观察 filter 中是否有 or 导致了无法走 hash join 或者阻碍了走索引计划。
经验总结
执行计划并不是建了索引就可以选上索引的，需要将条件改为可以走索引才行。
划线
评论
复制
发布于: 刚刚阅读数: 5
YashanDB
关注
全自研国产新型大数据管理系统 2022-02-15 加入
还未添加个人简介
评论