no_bind_values

用在查询语句中，控制Values表（IN (expression_list)优化而来）的行为，使其参与连接顺序交换，不受values绑定功能影响。

举例:

--  清理环境
drop table t1 cascade;
drop table t2 cascade;

--  开启values绑定功能
set ENABLE_BIND_VALUES = TRUE;
--  创建表
create table t1(a int, b int);
create table t2(a int, b int);
insert into t1 select generate_series(1, 10)， generate_series(1, 10);
insert into t2 select generate_series(1, 1000)， generate_series(1, 1000);
analyze t1;
analyze t2;

-- 确保in可以成为可以提升的values
set ENABLE_EXPAND_AEXPR_IN=FALSE;
set FORCE_EXPAND_AEXPR_IN_NUM = 0;
set ENABLE_SCALARARRAYOPEXPR_TO_VALUES=TRUE;


-- 通过hint指定当前查询树不进行Values绑定
explain select /*+ no_bind_values */ * from t1, t2 where t1.a = t2.a 
and t2.b in (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, 30,
			   31, 32, 33, 34, 35, 36, 37, 38, 39, 40,
			   41, 42, 43, 44, 45, 46, 47, 48, 49, 50);
QUERY PLAN(text)      
----------------------
Hash Join(Inner Join)  (cost=29.95..30.15 rows=1 width=16)
  Hash Cond: ("outer"."column1" = "inner".B)
  ->  HashAggregate  (cost=0.75..0.75 rows=50 width=4)
        Group Key: "column1"
        ->  Values Scan on *VALUES*  (cost=0.00..0.63 rows=50 width=4)
  ->  Hash  (cost=29.08..29.08 rows=10 width=16)
        ->  Hash Join(Inner Join)  (cost=7.22..29.08 rows=10 width=16)
              Hash Cond: ("outer".A = "inner".A)
              ->  Seq Scan on T2  (cost=0.00..18.00 rows=1000 width=8)
              ->  Hash  (cost=7.10..7.10 rows=10 width=8)
                    ->  Seq Scan on T1  (cost=0.00..7.10 rows=10 width=8)
总数目：11

			   

-- 不使用no_bind_values hint, values表会和关联表t1进行绑定
explain select * from t1, t2 where t1.a = t2.a 
and t2.b in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10,
               11, 12, 13, 14, 15, 16, 17, 18, 19, 20,
QUERY PLAN(text)      
----------------------
Hash Join(Inner Join)  (cost=8.47..30.92 rows=1 width=16)
  Hash Cond: ("outer".A = "inner".A)
  ->  Hash Join(Left Semi Join)  (cost=1.25..23.50 rows=50 width=8)
        Hash Cond: ("outer".B = "inner"."column1")
        ->  Seq Scan on T2  (cost=0.00..18.00 rows=1000 width=8)
        ->  Hash  (cost=0.63..0.63 rows=50 width=4)
              ->  Values Scan on *VALUES*  (cost=0.00..0.63 rows=50 width=4)
  ->  Hash  (cost=7.10..7.10 rows=10 width=8)
        ->  Seq Scan on T1  (cost=0.00..7.10 rows=10 width=8)
总数目：9
			   21, 22, 23, 24, 25, 26, 27, 28, 29, 30,
			   31, 32, 33, 34, 35, 36, 37, 38, 39, 40,
			   41, 42, 43, 44, 45, 46, 47, 48, 49, 50);
			   
--  清理环境
reset all;
drop table t1 cascade;
drop table t2 cascade;