no_pullup_subquery

用在子查询中，表示不提升子查询。

举例:

--  清理环境
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;

--  创建表
CREATE TABLE a(a1 INT, a2 INT);
CREATE TABLE b(b1 INT, b2 INT);
CREATE TABLE c(c1 INT, c2 INT);


explain select * from a,b,c where a.a1=b.b1 and b.b1 in (
    select /*+ no_pullup_subquery */ c1 from c where c.c2>6);
QUERY PLAN(text)      
----------------------
Nested Loop(Inner Join)  (cost=1097.15..27491493.72 rows=1374389535 width=24)
  ->  Merge Join(Inner Join)  (cost=999.04..3536.10 rows=167772 width=16)
        Merge Cond: ("outer".B1 = "inner".A1)
        ->  Sort  (cost=376.64..386.88 rows=4096 width=8)
              Sort Key: B1
              ->  Seq Scan on B  (cost=0.00..130.88 rows=4096 width=8)
                    Filter: (hashed subplan)
                    SubPlan
                      ->  Seq Scan on C  (cost=0.00..110.40 rows=2731 width=4)
                            Scan Key: (C2 > 6)
        ->  Sort  (cost=622.40..642.88 rows=8192 width=8)
              Sort Key: A1
              ->  Seq Scan on A  (cost=0.00..89.92 rows=8192 width=8)
  ->  Materialize  (cost=98.11..180.03 rows=8192 width=8)
        ->  Seq Scan on C  (cost=0.00..89.92 rows=8192 width=8)
总数目：15

--  删除表
DROP TABLE a CASCADE;
DROP TABLE b CASCADE;
DROP TABLE c CASCADE;