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

精选原创

崖山数据库系统YashanDB 2024-07-24 15:08:51 博主文章分类：知识库 ©著作权

文章标签 SYS SQL sed YashanDB 崖山数据库 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者崖山数据库系统YashanDB的原创作品，请联系作者获取转载授权，否则将追究法律责任

问题现象

当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
                                                                 
+----+--------------------------------+----------------------+------------+----------+-------------+--------------------------------+
| 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