Oracle谓词推入

创建测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
drop table t1;
drop table t2;
drop table t3;

create table t1(n1 number,n2 varchar2(200));
create table t2(n1 number,n2 varchar2(200));
create table t3(n1 number,n2 varchar2(200));

insert into t1 select object_id,object_name from dba_objects;
insert into t2 select object_id,object_name from dba_objects;
insert into t3 select object_id,object_name from dba_objects;
commit;

create index idx_t1 on t1 (n2);
create index idx_t2 on t2 (n2);
create index idx_t3 on t3 (n2);

exec dbms_stats.gather_table_stats(ownname=>'XB',tabname=>'T1',cascade=>true);
exec dbms_stats.gather_table_stats(ownname=>'XB',tabname=>'T2',cascade=>true);
exec dbms_stats.gather_table_stats(ownname=>'XB',tabname=>'T3',cascade=>true);

对这三张表进行关联join，其中t2和t3表作为子查询关联

1
2
3
4
5
6
7
select /*+ gather_plan_statistics */t1.n1,v1.n2
from t1,(select /*+ no_merge */t2.n1,t2.n2
from t2,t3
where t2.n1=t3.n1
and t2.n2=t3.n2) v1
where t1.n1>1110 and t1.n1<1120
and v1.n2=t1.n2;

查看执行计划

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |        |      1 |        |      9 |00:00:00.01 |     546 |
|   1 |  NESTED LOOPS                           |        |      1 |     11 |      9 |00:00:00.01 |     546 |
|*  2 |   TABLE ACCESS FULL                     | T1     |      1 |      9 |      9 |00:00:00.01 |     500 |
|   3 |   VIEW PUSHED PREDICATE                 |        |      9 |      1 |      9 |00:00:00.01 |      46 |
|   4 |    NESTED LOOPS                         |        |      9 |      1 |      9 |00:00:00.01 |      46 |
|   5 |     NESTED LOOPS                        |        |      9 |      1 |      9 |00:00:00.01 |      44 |
|   6 |      TABLE ACCESS BY INDEX ROWID BATCHED| T2     |      9 |      1 |      9 |00:00:00.01 |      23 |
|*  7 |       INDEX RANGE SCAN                  | IDX_T2 |      9 |      1 |      9 |00:00:00.01 |      21 |
|*  8 |      INDEX RANGE SCAN                   | IDX_T3 |      9 |      1 |      9 |00:00:00.01 |      21 |
|*  9 |     TABLE ACCESS BY INDEX ROWID         | T3     |      9 |      1 |      9 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(("T1"."N1"<1120 AND "T1"."N1">1110))
   7 - access("T2"."N2"="T1"."N2")
   8 - access("T3"."N2"="T1"."N2")
       filter("T2"."N2"="T3"."N2")
   9 - filter("T2"."N1"="T3"."N1")

在id=3的那一行可以发现VIEW PUSHED PREDICATE的谓词推入关键字，通过执行计划我们可以知道SQL的执行顺序，首先是对T1表进行了全表扫描，然后将得到每一行数据依次传入到内联视图做NESTED LOOPS循环，T2和T3表将得到的T1.N2值作为谓词进行索引访问得到最后的结果（通过id=7和id=8的access information）。其中T1全表扫描得到了9行数据，从id=3的starts可以看到视图也循环访问了9次。

这里你会发现我对t2和t3的内联视图加了no_merge的hint，这里是为了实现谓词推入的效果，就要避免视图合并，否则T1表就直接与T2或T3依次进行关联操作了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
select /*+ gather_plan_statistics */t1.n1,v1.n2
from t1,(select t2.n1,t2.n2
from t2,t3
where t2.n1=t3.n1
and t2.n2=t3.n2) v1
where t1.n1>1110 and t1.n1<1120
and v1.n2=t1.n2;

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |        |      1 |        |      9 |00:00:00.01 |     543 |
|   1 |  NESTED LOOPS                          |        |      1 |     11 |      9 |00:00:00.01 |     543 |
|   2 |   NESTED LOOPS                         |        |      1 |     11 |      9 |00:00:00.01 |     541 |
|   3 |    NESTED LOOPS                        |        |      1 |     11 |      9 |00:00:00.01 |     520 |
|*  4 |     TABLE ACCESS FULL                  | T1     |      1 |      9 |      9 |00:00:00.01 |     499 |
|   5 |     TABLE ACCESS BY INDEX ROWID BATCHED| T2     |      9 |      1 |      9 |00:00:00.01 |      21 |
|*  6 |      INDEX RANGE SCAN                  | IDX_T2 |      9 |      1 |      9 |00:00:00.01 |      20 |
|*  7 |    INDEX RANGE SCAN                    | IDX_T3 |      9 |      1 |      9 |00:00:00.01 |      21 |
|*  8 |   TABLE ACCESS BY INDEX ROWID          | T3     |      9 |      1 |      9 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter(("T1"."N1"<1120 AND "T1"."N1">1110))
   6 - access("T2"."N2"="T1"."N2")
   7 - access("T2"."N2"="T3"."N2")
   8 - filter("T2"."N1"="T3"."N1")

id=6中access(“T2”.“N2”=“T1”.“N2”)，而id=7中access(“T2”.“N2”=“T3”.“N2”)，所以T3并不是与T1的N2字段进行关联。

如果强制不允许其使用谓词推入，看会发生什么

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
select /*+ gather_plan_statistics no_push_pred(v1)*/t1.n1,v1.n2
from t1,(select /*+ no_merge */t2.n1,t2.n2
from t2,t3
where t2.n1=t3.n1
and t2.n2=t3.n2) v1
where t1.n1>1110 and t1.n1<1120
and v1.n2=t1.n2;

------------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |        |      9 |00:00:00.07 |    1498 |       |       |          |
|*  1 |  HASH JOIN           |      |      1 |     11 |      9 |00:00:00.07 |    1498 |  1250K|  1250K| 1359K (0)|
|*  2 |   TABLE ACCESS FULL  | T1   |      1 |      9 |      9 |00:00:00.01 |     499 |       |       |          |
|   3 |   VIEW               |      |      1 |  72937 |  72936 |00:00:00.13 |     999 |       |       |          |
|*  4 |    HASH JOIN         |      |      1 |  72937 |  72936 |00:00:00.12 |     999 |  7636K|  3018K| 6736K (0)|
|   5 |     TABLE ACCESS FULL| T2   |      1 |  72937 |  72937 |00:00:00.01 |     499 |       |       |          |
|   6 |     TABLE ACCESS FULL| T3   |      1 |  72937 |  72937 |00:00:00.01 |     500 |       |       |          |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("V1"."N2"="T1"."N2")
   2 - filter(("T1"."N1"<1120 AND "T1"."N1">1110))
   4 - access("T2"."N1"="T3"."N1" AND "T2"."N2"="T3"."N2")

因为加上了no_push_pred，所以T1全表扫描得到的N2值没办法传入到视图内部，则只能采取对T2和T3全表扫描hash join，最后与T1的结果集再进行hash join，通过starts也可以发现这次视图只进行了一次查询就将数据全部返回。

通过两次的对比可以明显发现在这个实验环境下谓词推入特性很好的节约了资源。CBO会使用谓词推入也有一些条件，比如不能做视图合并，内联视图里的表需要有选择性较好的谓词索引等。

Oracle
#sql tuning
Oracle谓词推入
https://www.xbdba.com/2021/04/16/pushed-predicate/
作者
xbdba
发布于
2021年4月16日
许可协议