no_unnest,push_subq用法小试

最新推荐文章于 2024-08-06 18:12:03 发布
转载 最新推荐文章于 2024-08-06 18:12:03 发布 · 248 阅读 · 0

本文探讨了Oracle数据库中不同提示对SQL执行计划的影响，包括no_unnest和push_subq提示的作用，展示了如何通过这些提示调整执行计划以提高查询效率。

create table t1 as select * from dba_objects;

Table created.

create table t2 as select * from dba_objects;

Table created.

create table t3 as select * from dba_objects;

Table created.

create index w_1 on t1(object_id);

Index created.

create index w_2 on t2(object_id);

Index created.

create index w_3 on t3(object_id);

Index created.

exec dbms_stats.gather_table_stats(user,'T1');

PL/SQL procedure successfully completed.

exec dbms_stats.gather_table_stats(user,'t2');

PL/SQL procedure successfully completed.

exec dbms_stats.gather_table_stats(user,'t3');

PL/SQL procedure successfully completed.

explain plan for
  2  select t1.object_id,t1.object_name ,t2.object_type
  3   from t1 ,t2 where t1.object_id=t2.object_id and t2.object_type='TABLE'
  4   and exists ( select 1 from t3 where t3.object_name='WXH' AND t3.object_id=t1.object_id);

Explained.

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3087771463

---------------------------------------------------------------------------------------
| Id  | Operation                      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |     1 |    61 |    79   (2)| 00:00:01 |
|   1 |  NESTED LOOPS                  |      |       |       |            |          |
|   2 |   NESTED LOOPS                 |      |     1 |    61 |    79   (2)| 00:00:01 |
|   3 |    NESTED LOOPS                |      |     1 |    48 |    77   (2)| 00:00:01 |
|   4 |     SORT UNIQUE                |      |     1 |    24 |    74   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL         | T3   |     1 |    24 |    74   (0)| 00:00:01 |
|   6 |     TABLE ACCESS BY INDEX ROWID| T1   |     1 |    24 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | W_1  |     1 |       |     1   (0)| 00:00:01 |
|*  8 |    INDEX RANGE SCAN            | W_2  |     1 |       |     1   (0)| 00:00:01 |
|*  9 |   TABLE ACCESS BY INDEX ROWID  | T2   |     1 |    13 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter("T3"."OBJECT_NAME"='WXH')
   7 - access("T3"."OBJECT_ID"="T1"."OBJECT_ID")
   8 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   9 - filter("T2"."OBJECT_TYPE"='TABLE')

看到ORACLE把子查询展开和t3进行了join。看看使用no_unnest提示的情况。

explain plan for
  2  select t1.object_id,t1.object_name ,t2.object_type
  3   from t1 ,t2 where t1.object_id=t2.object_id and t2.object_type='TABLE'
  4   and exists ( select /*+ no_unnest */1 from t3 where t3.object_name='WXH' AND t3.object_id=t1.object_id);

Explained.

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2811354197

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |    37 |  3609   (1)| 00:00:44 |
|*  1 |  FILTER                      |      |       |       |            |          |
|*  2 |   HASH JOIN                  |      |  3458 |   124K|   149   (1)| 00:00:02 |
|*  3 |    TABLE ACCESS FULL         | T2   |  3474 | 45162 |    74   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL         | T1   | 19046 |   446K|    74   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T3   |     1 |    24 |     2   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN          | W_3  |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T3" "T3" WHERE
              "T3"."OBJECT_ID"=:B1 AND "T3"."OBJECT_NAME"='WXH'))
   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   3 - filter("T2"."OBJECT_TYPE"='TABLE')
   5 - filter("T3"."OBJECT_NAME"='WXH')
   6 - access("T3"."OBJECT_ID"=:B1)

使用no_unnest提示后，子查询将不被展开，T1.T2完成join后，再通过filter操作进行过滤。

再看看push_subq来将子查询强制先进行join-filter。

explain plan for
  2  select /*+ push_subq(@tmp)  */t1.object_id,t1.object_name ,t2.object_type
  3   from t1 ,t2 where t1.object_id=t2.object_id and t2.object_type='TABLE'
  4   and exists ( select /*+ qb_name(tmp) no_unnest */1 from t3 where t3.object_name='WXH' AND t3.object_id=t1.object_id);

Explained.

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2880557960

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |   948 | 35076 |   151   (1)| 00:00:02 |
|*  1 |  HASH JOIN                    |      |   948 | 35076 |   149   (1)| 00:00:02 |
|*  2 |   TABLE ACCESS FULL           | T1   |   952 | 22848 |    74   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T3   |     1 |    24 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | W_3  |     1 |       |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL           | T2   |  3474 | 45162 |    74   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
   2 - filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST QB_NAME ("TMP") */ 0
              FROM "T3" "T3" WHERE "T3"."OBJECT_ID"=:B1 AND "T3"."OBJECT_NAME"='WXH'))
   3 - filter("T3"."OBJECT_NAME"='WXH')
   4 - access("T3"."OBJECT_ID"=:B1)
   5 - filter("T2"."OBJECT_TYPE"='TABLE')

这里的执行计划显示的不够专业。可能是ORACLE怕格式不好看，FILTER操作被隐藏了。

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |   948 | 35076 |   151   (1)| 00:00:02 |
|*  1 | HASH JOIN                     |      |   948 | 35076 |   149   (1)| 00:00:02 |
|     |  FILTER                       |      |       |       |    00      | 00:00:01 |
|*  2 |   TABLE ACCESS FULL           | T1   |   952 | 22848 |    74   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T3   |     1 |    24 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | W_3  |     1 |       |     1   (0)| 00:00:01 |
|*  5 |  TABLE ACCESS FULL            | T2   |  3474 | 45162 |    74   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-689861/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-689861/

cotchte0421
关注
什么时候使用NO_UNNEST
zhaoyangjian724的专栏
05-06 1047
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; C
阻止子查询进行 Subquery Unnesting
zhaoyangjian724的专栏
07-03 213
hint NO_UNNEST 可以禁止CBO 进行 Subquery Unnesting SQL> set linesize 200 SQL> set pagesize 200 SQL> explain plan for select * from test1 where owner='SCOTT' and object_id in(select objec...
CBO hint:no_unnest,push_subq,push_pred的用法
conghuan4367的博客
05-29 355
常常有人把这三个hint搞混，主要是因为对三种重写原理不清楚。特总结如下。（实验环境为10204） 1. no_unnest, unnest unnest我们称为对子查询展开，顾名思义，就是别让子查询孤单地嵌套(nest)在...
push_subq
ciliting2867的博客
07-23 213
如果说no_unnest是为了让子查询不展开，独立的完成，那么push_subq就是为了让子查询最先进行join。所以，这个hint其实是控制的join的顺序。 SQL> create table t1 tablespa...
lightdb-no_push_subq/push_subq
紫无之紫的博客
11-16 148
LightDB 从 23.4 版本开始支持no_push_subq/push_subq hint，在 LightDB 中下推过滤条件是RBO，会尽量下推过滤条件以过滤尽可能多的数据。通过使用这个 no_push_subq hint 可以强制不下推带子链接的过滤条件（带子链接的过滤条件可能效率较差）。使用push_subq hint 可以强制下推带子链接的过滤条件（LIghtDB的默认操作），一般不需使用。需要注意的是，对于被pull up 后的子链接不起效（因为已经不是过滤条件了）。
push_subq－控制子查询比外部连接先运行
congtudi4111的博客
07-08 185
http://bitirainy.itpub.net/post/330/1886 先子查询再做表连接比较有利，也就是说子查询本身代价不大，并且过滤掉很多记录，产生一个很小的结果集再做表连接，比较有利。 oracle ...
push_subq提示
bianxq
09-06 302
PUSH_SUBQ 可以用来控制子查询的执行 这个是PUSH_SUBQ 的本意 我那个例子的意思是说: PUSH_SUBQ 本质上是个CBO的hints(当然RBO也提不上hints) 由于PUSH_SUBQ 的引入就是为了来解决unnesting的某些不足 所以在不同的版本上,这个hints发挥的作用也有所不同了.   8i上这个提示的作用更接近本原:   $ sqlplus "...
oracle三组难缠的hint no_unnest/unnest,push_subq,push_pred
Colin Lau Oracle
11-30 6201
最近在看一个哥们优化sql里面,使用到了几个特殊的hint，但我总是对此不解，所以针对此问题自己做了一些测试(参考了一些资料)  1. no_unnest, unnest  unnest我们称为对子查询展开，顾名思义，就是别让子查询孤单地嵌套(nest)在里面。  所以un_unnest双重否定代表肯定，即让子查询不展开，让它嵌套(nest)在里面。  如果一个查询中的where 中出现
从Oracle 11.2.0.4 BUG到Oracle子查询展开分析
你好我是李白
04-18 442
本文测试用语句来自下面大佬文章《ORACLE CBO 的 SQL 自动转换（Cost Based Transformations）之五》 本文主要介绍由于11.2.0.4.190115中由于bug导致子查询不展开的情况。 https://blog.youkuaiyun.com/weixin_50513167/article/details/115694814 1. 基础知识之子查询展开 ​ 子查询展开（Subquery Unnesting）是优化器处理带子查询的目标SQL的一种优化手段，它是指优化器不再.
SELECT DISTINCT unnest(string_to_array(company_code,',')) FROM "tb_push_data" WHERE push_type='0' and deleted='0'
最新发布
10-25
然而，我们注意到原始查询已经使用了WHERE条件（push_type='0' and deleted='0'）来限制数据。因此，我们首先应该确保这两个字段有索引。 建议的优化步骤： 步骤1：创建索引 如果还没有索引，在push_type和...
oracle push_subq简析
u011165335的博客
08-19 1737
此hint是针对自查询的hint,其含义是让cbo尽早的把未展开的子查询进行执行; 一般情况下,默认未no_push_subq,即未展开的子查询肯定是在最后一步执行的; 但是如果此子查询能够过滤大量数据,这个时候让cbo提前执行这个未展开的子查询,从而有效的筛选数据哈; 结合no_unnest使用; create table dept_temp as select * from dept w...
oracle hint_no_push_pred_no_push_subq
12-21 913
no_push_pred 1,不会把连接谓词推入视图或子查询内部 2,oracle优化器默认会把连接谓词推入子查询内部SQL> explain plan for select * from emp,...
Oracle三组难缠的hint no_unnest/unnest，push_subq，push_pred
jarry_gao的专栏
05-30 3031
Oracle三组难缠的hint no_unnest/unnest，push_subq，push_pred 2012-02-24   友情使欢欣倍增，使疾苦减半。经常有人把这三个hint搞混，主如果因为对三种重写道理不清楚。特总结如下。（实验景象为10204） 1. no_unnest， unnest unnest我们称为对子查询展开，顾名思义，就是别让子查询孤单地嵌套（ne
用push_subq优化批处理统计SQL
cmbj68996的博客
01-06 166
用户反映有一条统计SQL好几个小时跑不完 SQL原文 (部分表名字段名被替换掉了) (t1和t2都是100多G的大表) select * from t1 a where 1 = 1 and not exis...
Postgresql 使用 unnest 函数将 array＜string＞ 类型 转换为新行
2301_79215352的博客
08-06 596
我们有一个表为 mylikefruit，其有两列，一列为人名name，一列为该人喜欢的水果组成的数组friuits_array。第一步：使用 unnest 函数将列表每个元素解析为新行。第二步：根据水果分组，去重统计人数即可。求：统计每类水果各有多少人喜欢。
no_unnest,push_subq,push_pred的用法
cqwcw86668的专栏
12-31 266
no_unnest,push_subq,push_pred的用法 =========================================================== 常常有人把这三个hint搞混，主要是因...
postgresql中unnest使用说明与示例
热门推荐
qq_51167391的博客
11-03 1万+
postgresql中unnest使用说明与示例函数说明使用场景示例一: 单个unnest示例二: unnest与其他字段一起查询(常用场景)示例三: 多个unnest与其他字段一起查询 函数说明 语法:unnest(anyarray) 返回值:setof anyelement(可以理解为一个(临时)表) 说明:unnest函数将输入的数组转换成一个表,这个表的每一列都代表相应的一个数组中的元素。 如果unnest与其他字段一起出现在select中，就相当于其他字段进行了一次join。 使用场景 完成行转列
Oracle SQL调优系列之no_unnest和unnest用法简介
Nicky's blog
01-11 7819
Oracle调优之子查询嵌套 本博客介绍Oracle SQL调优的一种常用也是很实用的方法，也即/*+no_unnest */和/+ unnest/，介绍Oracle 的/*+unnest /、/+ no_unnest */ `之前，先介绍一下Hint。 Hint对于开发人员来说，可能不是很熟悉，但是对于DBA来说，Hint可是一种调优的利器，Hint 是Oracle 提供的一种SQL语法，是or...
string_to_array和unnest用于什么字句中
05-20
### PostgreSQL 中 `string_to_array` 和 `unnest` 的使用场景及 SQL 子句位置 #### 1. `string_to_array` 函数 `string_to_array` 是 PostgreSQL 提供的一个函数，用于将字符串转换为数组。该函数接受两个参数：第一个是要转换的字符串，第二个是分隔符。它通常出现在 **SELECT** 子句或 **FROM** 子句中作为数据源的一部分。 例如： ```sql SELECT string_to_array('apple,banana,cherry', ',') AS fruits; ``` 在这个例子中，`string_to_array` 将字符串 `'apple,banana,cherry'` 转换成了一个数组 `{apple,banana,cherry}`[^1]。 #### 2. `unnest` 函数 `unnest` 函数的作用是从数组中提取元素并将其展平为多行的结果集。它可以与 `string_to_array` 结合使用，从而实现从单个字符串到多行记录的转化过程。`unnest` 主要应用于 **FROM** 子句中，以便生成新的虚拟表结构。 示例代码如下所示： ```sql SELECT unnest(string_to_array('apple,banana,cherry', ',')) AS fruit; ``` 此处，`unnest` 接收由 `string_to_array` 返回的数组，并将其分解成三行独立的数据项。 #### 3. 组合使用场景 当需要查找某些特定条件的产品或者标签时，这两个函数可以联合起来发挥作用。假设有一张存储商品及其对应标签信息的表格，每件商品可能关联多个标签（以逗号分隔的形式保存在一个字段里）。此时就可以利用 `string_to_array` 把这些标签转化为数组形式，接着再通过 `unnest` 进一步拆解开来逐一比较分析。 更进一步地，在 Postgres 9.4 及以上版本中引入了 `WITH ORDINALITY` 功能扩展支持，允许我们在调用诸如 `unnest()` 此类集合返回型函数的同时附加额外的一列序号指示当前所在位置编号[^3]。 下面给出一段综合运用上述知识点的实际案例演示： ```sql SELECT t.id, a.elem, a.nr FROM tbl AS t LEFT JOIN LATERAL unnest(string_to_array(t.elements, ',')) WITH ORDINALITY AS a(elem, nr) ON TRUE; ``` 在此查询片段当中，`string_to_array` 配合 `unnest` 达到了把原先压缩在一起的属性列表分离出来的目的；而新增加的 `nr` 字段则标明了各项原本位于原串中的次序关系[^3]。 --- ### 总结说明 综上所述，`string_to_array` 多数情况下会出现在 **SELECT** 或者 **FROM** 子句内部参与计算流程设计之中；至于 `unnest` 则几乎总是固定搭配于 **FROM** 子句之下负责构造临时视图框架供给后续操作环节调用之便。
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符  | 博主筛选后可见
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值