对于不能合并的视图,有两种连接方式,一种是不下推连接谓词,与视图的结果集进行连接;一种是下推连接谓词,对每个驱动行都获取对应的视图结果集(nestloop)。
no_push_pred hint 在oracle 中用于不下推连接谓词到不能合并的视图中。
在LightDB 中不支持下推连接谓词, 因此使用no_push_pred hint总是起效。
下面介绍使用示例:
示例
初始化
create table test_no_push_pred1 (key1 int primary key, key2 int);
create table test_no_push_pred2 (key1 int primary key, key2 int);
create table test_no_push_pred3 (key1 int primary key, key2 int);
set enable_hashjoin to off;
set enable_mergejoin to off;
案例
lightdb@test_o=# EXPLAIN (COSTS false)
lightdb@test_o-# select /*+leading(a)*/* from test_no_push_pred1 a join (select * from test_no_push_pred2 order by key1 limit 1) b on a.key1 =b.key2;
QUERY PLAN
----------------------------------------------------------------------------
------
Nested Loop
Join Filter: (a.key1 = test_no_push_pred2.key2)
-> Seq Scan on test_no_push_pred1 a @"lt#1"
-> Materialize
-> Limit
-> Index Scan using test_no_push_pred2_pkey on test_no_push_pred2
(6 rows)
lightdb@test_o=# EXPLAIN (COSTS false)
lightdb@test_o-# select /*+leading(a) no_push_pred(b)*/* from test_no_push_pred1 a join (select * from test_no_push_pred2 order by key1 limit 1) b on a.key1 =b.key2;
QUERY PLAN
----------------------------------------------------------------------------
------
Nested Loop
Join Filter: (a.key1 = test_no_push_pred2.key2)
-> Seq Scan on test_no_push_pred1 a @"lt#1"
-> Materialize
-> Limit
-> Index Scan using test_no_push_pred2_pkey on test_no_push_pred2
(6 rows)
lightdb@test_o=# EXPLAIN (COSTS false)
lightdb@test_o-# select /*+leading(a) no_push_pred(@qb)*/* from test_no_push_pred1 a join (select/*+ qb_name(qb)*/ * from test_no_push_pred2 order by key1 limit 1) b on a.key1 =b.key2;
QUERY PLAN
----------------------------------------------------------------------------
----------
Nested Loop
Join Filter: (a.key1 = test_no_push_pred2.key2)
-> Seq Scan on test_no_push_pred1 a @"lt#0"
-> Materialize
-> Limit
-> Index Scan using test_no_push_pred2_pkey on test_no_push_pred2 @qb
(6 rows)
lightdb@test_o=# EXPLAIN (COSTS false)
lightdb@test_o-# select /*+leading(a) */* from test_no_push_pred1 a join (select/*+ no_push_pred*/ * from test_no_push_pred2 order by key1 limit 1) b on a.key1 =b.key2;
QUERY PLAN
----------------------------------------------------------------------------
--------------
Nested Loop
Join Filter: (a.key1 = test_no_push_pred2.key2)
-> Seq Scan on test_no_push_pred1 a @"lt#1"
-> Materialize
-> Limit
-> Index Scan using test_no_push_pred2_pkey on test_no_push_pred2 @"lt#0"
(6 rows)