指定子查询不展开的Hint

功能描述

数据库在对查询进行逻辑优化时通常会将可以提升的子查询提升到上层来避免嵌套执行，但对于某些本身选择率较低且可以使用索引过滤访问页面的子查询，嵌套执行不会导致性能下降过多，而提升之后扩大了查询路径的搜索范围，可能导致性能变差。对于此类情况，可以使用no_expand Hint进行调试。大多数情况下不建议使用此hint。

语法格式

no_expand

示例

1、创建测试表并插入数据。

create table  company (id number,name varchar,age number,address varchar, salary number,join_date date);

insert into company values(1,'Paul',32,'California',20000);
insert into company values(3,'Teddy',23,'Norway',20000);
insert into company values(5,'David',27,'South-Hall',85000);
insert into company values(8,'Paul',24,'Houston',20000);
insert into company values(9,'James',44,'Norway',5000);
insert into company values(10,'James',45,'James',5000);
insert into company values(6,'Kim',22);
insert into company values(7,'James',24);

2、查询数据。

select * from company;

返回结果为：

 id | name  | age |  address   | salary | join_date
----+-------+-----+------------+--------+-----------
  1 | Paul  |  32 | California |  20000 |
  3 | Teddy |  23 | Norway     |  20000 |
  5 | David |  27 | South-Hall |  85000 |
  8 | Paul  |  24 | Houston    |  20000 |
  9 | James |  44 | Norway     |   5000 |
 10 | James |  45 | James      |   5000 |
  6 | Kim   |  22 |            |        |
  7 | James |  24 |            |        |
(8 rows)

3、执行子查询语句。

SELECT * FROM company WHERE ID IN (SELECT ID FROM company  WHERE SALARY > 45000) ;

返回结果为：

 id | name  | age |  address   | salary | join_date
----+-------+-----+------------+--------+-----------
  5 | David |  27 | South-Hall |  85000 |
(1 row)

4、正常的查询执行的查询计划。

explain SELECT * FROM company WHERE ID IN (SELECT ID FROM company  WHERE SALARY > 45000) ;

返回结果为：

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Hash Join  (cost=17.02..33.56 rows=206 width=168)
   Hash Cond: (public.company.id = public.company.id)
   ->  Seq Scan on company  (cost=0.00..14.13 rows=413 width=168)
   ->  Hash  (cost=16.18..16.18 rows=67 width=32)
         ->  HashAggregate  (cost=15.51..16.18 rows=67 width=32)
               Group By Key: public.company.id
               ->  Seq Scan on company  (cost=0.00..15.16 rows=138 width=32)
                     Filter: (salary > 45000::numeric)
(8 rows)

5、加入no_expand选项之后的执行计划。

explain SELECT * FROM company WHERE ID IN (SELECT  /*+ no_expand*/ ID FROM company  WHERE SALARY > 45000) ;

返回结果为：

                            QUERY PLAN
-------------------------------------------------------------------
 Seq Scan on company  (cost=15.51..30.67 rows=206 width=168)
   Filter: (hashed SubPlan 1)
   SubPlan 1
     ->  Seq Scan on company  (cost=0.00..15.16 rows=138 width=32)
           Filter: (salary > 45000::numeric)
(5 rows)