什么时候使用NO_UNNEST

最新推荐文章于 2024-05-08 16:35:43 发布

scan724

最新推荐文章于 2024-05-08 16:35:43 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： sql 查询优化

本文链接：https://blog.youkuaiyun.com/zhaoyangjian724/article/details/25114919

sql 查询优化专栏收录该内容

204 篇文章 ¥39.90 ¥99.00

订阅专栏

本文通过实例展示了在Oracle查询中，当子查询被展开导致性能下降时，如何使用NO_UNNEST提示来优化查询。通过对比使用NO_UNNEST前后的执行计划和性能，说明了NO_UNNEST在阻止子查询展开、提高查询效率上的作用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

select * from test a where object_id in (select   department_id
  from hr.dept_1 dept
 where department_id IN (select   department_id from hr.employees_1 emp)); 


SQL> select count(*) from test;

  COUNT(*)
----------
   2319328

SQL> select count(*) from dept_1;

  COUNT(*)
----------
   4194304

SQL> select count(*) from employees_1;

  COUNT(*)
----------
   3506176

SQL> (select   department_id
  from hr.dept_1 dept
 where department_id IN (select   department_id from hr.employees_1 emp))  2    3  
  4  ;

DEPARTMENT_ID
-------------
	   10
	   20


SQL> set linesize 200
SQL> set pagesize 200
SQL> explain plan for select * from test a where object_id in (select   department_id
  from hr.dept_1 dept
 where department_id IN (select

了解本专栏