NATIVE_FULL_OUTER_JOIN

最新推荐文章于 2022-11-01 17:30:45 发布
最新推荐文章于 2022-11-01 17:30:45 发布
阅读量2k 收藏 1
点赞数
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jgmydsai/article/details/13774253
版权

一网友问一sql的优化，语句就不贴了，噼里啪啦一大堆，主要结构是select * from (里面一大堆) a full join ()。看plan就可以了

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                          |    30M|    26G|       |  8761K  (2)| 03:28:58 |       |       |
|   1 |  VIEW                                   |                          |    30M|    26G|       |  8761K  (2)| 03:28:58 |       |       |
|   2 |   UNION-ALL                             |                          |       |       |       |            |          |       |       |
|*  3 |    HASH JOIN OUTER                      |                          |   109K|   106M|    52M|  4491K  (2)| 01:47:08 |       |       |
|   4 |     MERGE JOIN                          |                          |   109K|    50M|       |   285K  (2)| 00:06:50 |       |       |
|   5 |      SORT JOIN                          |                          |  4084K|  1546M|       |   285K  (2)| 00:06:50 |       |       |
|   6 |       VIEW                              |                          |  4084K|  1546M|       |   285K  (2)| 00:06:50 |       |       |
|   7 |        HASH GROUP BY                    |                          |  4084K|   494M|   602M|   285K  (2)| 00:06:50 |       |       |
|*  8 |         HASH JOIN                       |                          |  4084K|   494M|       |   168K  (2)| 00:04:01 |       |       |
|*  9 |          TABLE ACCESS FULL              | T_xxxx_OUT               |  9815 |   335K|       |   226   (2)| 00:00:01 |       |       |
|* 10 |          TABLE ACCESS FULL              | T_xxxx_OUT_LINE          |  4084K|   358M|       |   167K  (2)| 00:04:01 |       |       |
|* 11 |      SORT JOIN                          |                          |    10 |   920 |       |     8  (13)| 00:00:01 |       |       |
|  12 |       VIEW                              |                          |    10 |   920 |       |     7   (0)| 00:00:01 |       |       |
|* 13 |        FILTER                           |                          |       |       |       |            |          |       |       |
|* 14 |         CONNECT BY WITH FILTERING       |                          |       |       |       |            |          |       |       |
|* 15 |          TABLE ACCESS FULL              | T_*****_ORGA             |    36 |   972 |       |     8   (0)| 00:00:01 |       |       |
|  16 |          NESTED LOOPS                   |                          |       |       |       |            |          |       |       |
|  17 |           CONNECT BY PUMP               |                          |       |       |       |            |          |       |       |
|  18 |           TABLE ACCESS BY INDEX ROWID   | T_*****_ORGA             |    10 |   180 |       |     7   (0)| 00:00:01 |       |       |
|* 19 |            INDEX RANGE SCAN             | IDX_*****_ORGA_PARENT_ID |    10 |       |       |     1   (0)| 00:00:01 |       |       |
|  20 |     VIEW                                |                          |    30M|    14G|       |  3425K  (2)| 01:21:43 |       |       |
|  21 |      HASH GROUP BY                      |                          |    30M|  5094M|  5452M|  3425K  (2)| 01:21:43 |       |       |
|* 22 |       HASH JOIN                         |                          |    30M|  5094M|       |  2242K  (2)| 00:53:29 |       |       |
|  23 |        TABLE ACCESS FULL                | T_--------               | 37788 |   332K|       |   860   (3)| 00:00:02 |       |       |
|* 24 |        HASH JOIN                        |                          |    29M|  4787M|   306M|  2240K  (2)| 00:53:27 |       |       |
|* 25 |         TABLE ACCESS FULL               | T_TERMINAL_*****_ORDER   |  8230K|   211M|       |   117K  (2)| 00:02:48 |       |       |
|  26 |         PARTITION RANGE ALL             |                          |    30M|  4173M|       |  1880K  (2)| 00:44:52 |     1 |    58 |
|* 27 |          TABLE ACCESS FULL              | T_---_LINE               |    30M|  4173M|       |  1880K  (2)| 00:44:52 |     1 |    58 |
|  28 |    HASH GROUP BY                        |                          |    29M|  6463M|  6891M|  4269K  (2)| 01:41:50 |       |       |
|* 29 |     HASH JOIN                           |                          |    29M|  6463M|       |  2785K  (2)| 01:06:26 |       |       |
|  30 |      TABLE ACCESS FULL                  | T_--------               | 37788 |   332K|       |   860   (3)| 00:00:02 |       |       |
|* 31 |      HASH JOIN RIGHT ANTI               |                          |    29M|  6143M|  6400K|  2783K  (2)| 01:06:24 |       |       |
|  32 |       VIEW                              | VW_SQ_1                  |   109K|  5119K|       |   285K  (2)| 00:06:50 |       |       |
|  33 |        MERGE JOIN                       |                          |   109K|  9172K|       |   285K  (2)| 00:06:50 |       |       |
|  34 |         SORT JOIN                       |                          |  4084K|   284M|       |   285K  (2)| 00:06:50 |       |       |
|  35 |          VIEW                           |                          |  4084K|   284M|       |   285K  (2)| 00:06:50 |       |       |
|  36 |           HASH GROUP BY                 |                          |  4084K|   494M|   602M|   285K  (2)| 00:06:50 |       |       |
|* 37 |            HASH JOIN                    |                          |  4084K|   494M|       |   168K  (2)| 00:04:01 |       |       |
|* 38 |             TABLE ACCESS FULL           | T_xxxx_OUT               |  9815 |   335K|       |   226   (2)| 00:00:01 |       |       |
|* 39 |             TABLE ACCESS FULL           | T_xxxx_OUT_LINE          |  4084K|   358M|       |   167K  (2)| 00:04:01 |       |       |
|* 40 |         SORT JOIN                       |                          |    10 |   130 |       |     8  (13)| 00:00:01 |       |       |
|  41 |          VIEW                           |                          |    10 |   130 |       |     7   (0)| 00:00:01 |       |       |
|* 42 |           FILTER                        |                          |       |       |       |            |          |       |       |
|* 43 |            CONNECT BY WITH FILTERING    |                          |       |       |       |            |          |       |       |
|* 44 |             TABLE ACCESS FULL           | T_*****_ORGA             |    36 |   972 |       |     8   (0)| 00:00:01 |       |       |
|  45 |             NESTED LOOPS                |                          |       |       |       |            |          |       |       |
|  46 |              CONNECT BY PUMP            |                          |       |       |       |            |          |       |       |
|  47 |              TABLE ACCESS BY INDEX ROWID| T_*****_ORGA             |    10 |   180 |       |     7   (0)| 00:00:01 |       |       |
|* 48 |               INDEX RANGE SCAN          | IDX_*****_ORGA_PARENT_ID |    10 |       |       |     1   (0)| 00:00:01 |       |       |
|* 49 |       HASH JOIN                         |                          |    29M|  4787M|   306M|  2240K  (2)| 00:53:27 |       |       |
|* 50 |        TABLE ACCESS FULL                | T_TERMINAL_*****_ORDER   |  8230K|   211M|       |   117K  (2)| 00:02:48 |       |       |
|  51 |        PARTITION RANGE ALL              |                          |    30M|  4173M|       |  1880K  (2)| 00:44:52 |     1 |    58 |
|* 52 |         TABLE ACCESS FULL               | T_---_LINE               |    30M|  4173M|       |  1880K  (2)| 00:44:52 |     1 |    58 |
--------------------------------------------------------------------------------------------------------------------------------------------

cbo把full join 转为了union all。30M 的行数，两次，坑啊。哥找了好大一会儿，找到了hint NATIVE_FULL_OUTER_JOIN，报着试试看的心情让网友加上 /*+ NATIVE_FULL_OUTER_JOIN */ 

新的plan如下:

-----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                          |    30M|    26G|       |  4397K  (2)| 01:44:54 |       |       |
|   1 |  VIEW                                | VW_FOJ_0                 |    30M|    26G|       |  4397K  (2)| 01:44:54 |       |       |
|*  2 |   HASH JOIN FULL OUTER               |                          |    30M|    26G|    50M|  4397K  (2)| 01:44:54 |       |       |
|   3 |    VIEW                              |                          |   109K|    49M|       |   285K  (2)| 00:06:50 |       |       |
|   4 |     MERGE JOIN                       |                          |   109K|    50M|       |   285K  (2)| 00:06:50 |       |       |
|   5 |      SORT JOIN                       |                          |  4084K|  1546M|       |   285K  (2)| 00:06:50 |       |       |
|   6 |       VIEW                           |                          |  4084K|  1546M|       |   285K  (2)| 00:06:50 |       |       |
|   7 |        HASH GROUP BY                 |                          |  4084K|   494M|   602M|   285K  (2)| 00:06:50 |       |       |
|*  8 |         HASH JOIN                    |                          |  4084K|   494M|       |   168K  (2)| 00:04:01 |       |       |
|*  9 |          TABLE ACCESS FULL           | T_xxxx_OUT               |  9815 |   335K|       |   226   (2)| 00:00:01 |       |       |
|* 10 |          TABLE ACCESS FULL           | T_xxxx_OUT_LINE          |  4084K|   358M|       |   167K  (2)| 00:04:01 |       |       |
|* 11 |      SORT JOIN                       |                          |    10 |   920 |       |     8  (13)| 00:00:01 |       |       |
|  12 |       VIEW                           |                          |    10 |   920 |       |     7   (0)| 00:00:01 |       |       |
|* 13 |        FILTER                        |                          |       |       |       |            |          |       |       |
|* 14 |         CONNECT BY WITH FILTERING    |                          |       |       |       |            |          |       |       |
|* 15 |          TABLE ACCESS FULL           | T_*****_ORGA             |    36 |   972 |       |     8   (0)| 00:00:01 |       |       |
|  16 |          NESTED LOOPS                |                          |       |       |       |            |          |       |       |
|  17 |           CONNECT BY PUMP            |                          |       |       |       |            |          |       |       |
|  18 |           TABLE ACCESS BY INDEX ROWID| T_*****_ORGA             |    10 |   180 |       |     7   (0)| 00:00:01 |       |       |
|* 19 |            INDEX RANGE SCAN          | IDX_*****_ORGA_PARENT_ID |    10 |       |       |     1   (0)| 00:00:01 |       |       |
|  20 |    VIEW                              |                          |    30M|    13G|       |  3425K  (2)| 01:21:43 |       |       |
|  21 |     HASH GROUP BY                    |                          |    30M|  5094M|  5452M|  3425K  (2)| 01:21:43 |       |       |
|* 22 |      HASH JOIN                       |                          |    30M|  5094M|       |  2242K  (2)| 00:53:29 |       |       |
|  23 |       TABLE ACCESS FULL              | T_--------               | 37788 |   332K|       |   860   (3)| 00:00:02 |       |       |
|* 24 |       HASH JOIN                      |                          |    29M|  4787M|   306M|  2240K  (2)| 00:53:27 |       |       |
|* 25 |        TABLE ACCESS FULL             | T_TERMINAL_*****_ORDER   |  8230K|   211M|       |   117K  (2)| 00:02:48 |       |       |
|  26 |        PARTITION RANGE ALL           |                          |    30M|  4173M|       |  1880K  (2)| 00:44:52 |     1 |    58 |
|* 27 |         TABLE ACCESS FULL            | T_---_LINE               |    30M|  4173M|       |  1880K  (2)| 00:44:52 |     1 |    58 |
-----------------------------------------------------------------------------------------------------------------------------------------

ok，让网友试运行了下，时间从25分钟降到了15分钟。

在群里提起时，棉花糖大师动作那叫一个快，马上给出一段

When you ask Oracle to use native full join implementation either with parameter _optimizer_native_full_outer_join equals to force (which is mentioned here as an enhancement of 10.2.0.4) or a hint NATIVE_FULL_OUTER_JOIN,

后面再优化，应该就是调pga了，那是后话不提


jgmydsai
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
ORACLE11G优化器对full Outer join 的加强
道行尚浅（老道）的博客
08-21 1844
SQL> alter system set optimizer_features_enable        ='10.2.0.2' scope=memory ; System altered. SQL> set autotrace trace  SQL> set pagesize 200 SQL> set linesize 200 SQL> select *   2
三分钟学会数据库， FULL OUTER JOIN 连接
qq_42064309的博客
09-09 881
建表1： CREATE TABLE student( Sno char(15) NOT NULL, Sname char(20) NULL, Ssex char(2) NULL, Sage smallint NULL, Sdept char(20) NULL ) INSERT student (Sno, Sname, Ssex, Sage, Sdept) ...
Native Full Outer Join
小麦苗DBA宝典
05-18 322
Native Full Outer Join是11g的特性，采用此特性，FULL JOIN时只需要对基表做一次扫描。而在Oracle 10g中，缺省FULL JOIN（等同于：...
[20120223]full outer join.txt
02-23 90
full outer join 也就是包括左连接以及右连接，然后去除重复的记录。11g改进了算法，演示如下:SQL> select * from v$version;BANNER-----------------...
值得反复研读的表连接之HASH JOIN方式
u011868279的博客
10-21 1049
值得反复研读的表连接之NL(嵌套循环)方式
ORACLE 12c 支持的hint 命令
ugghhj的博客
11-15 598
ORACLE 12c 支持的hint 命令
ORACLE 10046 10053 EVENT
xielong8866的博客
11-01 630
ORACLE 10046 10053 EVENT
optimizer_features_enable
田攀的日志
11-14 3776
if you upgrade your database from release 10.1 to release 11.1, but you want to keep the release 10.2.0.4 optimizer behavior, you can do so by setting this parameter to 10.2.0.4. At a later time, you
双流join(SparkStreaming)
格子衫
11-13 1048
一、概述 1.1 需求分析 Spark Streaming实现两个流的join操作，如：一个流是订单数据，另一个流是订单详情数据，现需要将两个流按照某个公共字段连接进行join操作，同时订单数据和订单详情数据理论上是同时产生的，但考虑到实际情况即：延迟，结合Spark Streaming的批次处理实时数据的理念，这两个流的数据不一定是同时到达的，意思就是订单一的数据已经过来了，可能订单详情一的一系列数据早到或者迟到，这就导致直接做join时会join不上，且数据会丢失。 1.2 需求实现 为了保证早
10g full join 优化
weixin_30298497的博客
05-05 222
今天一个女生咨询我报名学优化。聊着聊着就让我优化一个sql 因为怕泄密，所以删除了 sql 。不好意思 该sql是 olap 的， 在oracle10g 上面跑。跑一次要33秒钟，一般olap报表，最好的客户体验要在5秒以内。 大家注意看，sql里面有full join，在oracle10g里面，full join默认会扫描2次，一次left join一次right...
Oracle SQL 优化与调优技术详解-附录：SQL提示.pdf
07-03
Oracle SQL 优化与调优技术详解-附录：SQL提示
数据库多表连接方式介绍－HASH-JOIN
weixin_30544657的博客
11-12 1223
1.概述 　　hash join是一种数据库在进行多表连接时的处理算法，对于多表连接还有两种比较常用的方式：sort merge-join 和 nested loop。 为了比较清楚的介绍hash join的使用场景以及为何要引入这样一种连接算法，这里也会顺带简单介绍一下上面提到的两种join方式。 　　连接方式是一个什么样的概念，或者说我们为何要有而且有好几种，对于不太了解数据库的人来讲可能...
哈希连接(hash join) 原理
OOM
03-04 4639
访问次数：驱动表和被驱动表都只会访问0次或1次。 驱动表是否有顺序：有。 是否要排序：否。 应用场景： 1. 一个大表，一个小表的关联； 2. 表上没有索引； 3. 返回结果集比较大。 原理我们说的简单一点，先把驱动表的关联字段hash到PGA中（当然rowid也在PGA中），然后扫描被驱动表，取第一条数据，将关联的字段hash 一下探测PGA中的小表，如果匹配则关联，再取第二
带你了解数据库中JOIN的用法
weixin_33859231的博客
04-07 564
前言 欢迎关注公众号：Coder编程 获取最新原创技术文章和相关免费学习资料，随时随地学习技术知识！ 本章主要介绍数据库中Join的的用法，也是我们在使用数据库时非常基础的一个知识点。本次会介绍数据库中的inner join、left join、right join 的用法以及它们之间的区别。 文章如有错误还请大家及时指出~ 以下都是采用mysql数据库 Join 相信大家在学习数据库的使...
NATIVE SQL
盈于心胸，充塞天地
03-02 648
oracle 日期处理 execsql. opencfor selectTO_CHAR(zxbrq,'YYYYMMDD:HH24:MI:SS')zxbrq, zxbbh,zxbmc,zydrs,zqqrs,zjb,ztb,zxzrs,zxzqq,zxzjb,zxztb,zxzhj,zxbgs,orgeh,orgtx fromEDH...
视图合并、hash join连接列数据分布不均匀引发的惨案
FaN()
08-26 2464
表大小SQL> select count(*) from agent.TB_AGENT_INFO; COUNT(*) ---------- 1751SQL> select count(*) from TB_CHANNEL_INFO ; COUNT(*) ---------- 1807SQL> select count(*) from TB_USER_CHANNEL; C
使用FULL OUTER JOIN拼接多表数据
Kevin's Blog SQL讨论群21426959 Dynamics AX 438084988
10-18 5735
今天帮用户修改一个报表，里边设计到出库和入库的操作，要将每个产品每天的出入口信息列出来。   可能存在这样的情况：   1.   产品在出库表中存在数据但是入库表中没有数据 2.   产品入库表中存在数据但是出库表中没有数据   所以这个Report直接使用INNER JOIN或者Left join/Right Join连接两张表都得不到想要的数据。当时Report的开发者的思路
native_bayes_sentiment_analyzer
最新发布
12-24
native_bayes_sentiment_analyzer是一种基于贝叶斯定理的情感分析器，用于分析文本中的情感倾向。它基于贝叶斯定理，通过计算文本中每个词语在特定情感类别下的概率来确定整个文本的情感倾向。该分析器通常用于对...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值