【YashanDB知识库】filter or改写问题
问题现象
当 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
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | 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=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
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+ | 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: 3573759094 Optimizer: 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=5 union all 3 select * from test_tab1 where col1<>5 and col2=5;
PLAN_DESCRIPTION ---------------------------------------------------------------- SQL hash value: 2073754267 Optimizer: 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=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 或者阻碍了走索引计划。
经验总结
执行计划并不是建了索引就可以选上索引的，需要将条件改为可以走索引才行。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/70028812/viewspace-3023258/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- 10亿美元收购Neon背后的技术风口：数据分支爆红，YashanDB正式发布数据沙箱
- 为AI Coding设计！YashanDB发布极简应用开发Skill套件
- 行业发布！企业级龙虾管控方案YashanClaw来了
- 图灵奖得主断言“AI Agent最后全是数据库问题”，YashanDB如何破解 AI落地困局
- YashanDB CREATE DATABASE LINK语句
- YashanDB COMMIT语句
- YashanDB COMMENT语句
- YashanDB BUILD DATABASE语句
- YashanDB BACKUP DATABASE函数
- YashanDB BACKUP ARCHIVELOG语句
