use_nl,use_hash,use_merge的HINT用法。

最新推荐文章于 2021-04-03 08:35:42 发布
最新推荐文章于 2021-04-03 08:35:42 发布
阅读量330 收藏
点赞数

SQL> select count(*) from a,b where a.object_id=b.object_id;
已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------                                                                                              
Plan hash value: 2064530317                                                                                                                             
                                                                                                                                                        
----------------------------------------------------------------------------------------------                                                          
| Id  | Operation              | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                          
----------------------------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT       |             |     1 |     8 |       |   498  (10)| 00:00:06 |                                                          
|   1 |  SORT AGGREGATE        |             |     1 |     8 |       |            |          |                                                          
|*  2 |   HASH JOIN            |             |  1844K|    14M|  2024K|   498  (10)| 00:00:06 |                                                          
|   3 |    INDEX FAST FULL SCAN| A_OBJECT_ID |   129K|   504K|       |    77   (4)| 00:00:01 |                                                          
|   4 |    INDEX FAST FULL SCAN| B_OBJECT_ID |   210K|   823K|       |   123   (5)| 00:00:02 |                                                          
----------------------------------------------------------------------------------------------                                                          
                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                     
---------------------------------------------------                                                                                                     
                                                                                                                                                        
   2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")                                                                                                          

SQL> select /*+ use_nl(a,b) */count(*) from a,b where a.object_id=b.object_id;
已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------                                                                                              
Plan hash value: 89762486                                                                                                                               
                                                                                                                                                        
--------------------------------------------------------------------------------------                                                                  
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                                  
--------------------------------------------------------------------------------------                                                                  
|   0 | SELECT STATEMENT       |             |     1 |     8 |   129K  (1)| 00:25:54 |                                                                  
|   1 |  SORT AGGREGATE        |             |     1 |     8 |            |          |                                                                  
|   2 |   NESTED LOOPS         |             |  1844K|    14M|   129K  (1)| 00:25:54 |                                                                  
|   3 |    INDEX FAST FULL SCAN| A_OBJECT_ID |   129K|   504K|    77   (4)| 00:00:01 |                                                                  
|*  4 |    INDEX RANGE SCAN    | B_OBJECT_ID |    14 |    56 |     1   (0)| 00:00:01 |                                                                  
--------------------------------------------------------------------------------------                                                                  
                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                     
---------------------------------------------------                                                                                                     
                                                                                                                                                        
   4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")                                                                                                          

SQL> select /*+ ordered use_nl(a,b) */count(*) from a,b where a.object_id=b.object_id;
已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------                                                                                              
Plan hash value: 89762486                                                                                                                               
                                                                                                                                                        
--------------------------------------------------------------------------------------                                                                  
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                                  
--------------------------------------------------------------------------------------                                                                  
|   0 | SELECT STATEMENT       |             |     1 |     8 |   129K  (1)| 00:25:54 |                                                                  
|   1 |  SORT AGGREGATE        |             |     1 |     8 |            |          |                                                                  
|   2 |   NESTED LOOPS         |             |  1844K|    14M|   129K  (1)| 00:25:54 |                                                                  
|   3 |    INDEX FAST FULL SCAN| A_OBJECT_ID |   129K|   504K|    77   (4)| 00:00:01 |                                                                  
|*  4 |    INDEX RANGE SCAN    | B_OBJECT_ID |    14 |    56 |     1   (0)| 00:00:01 |                                                                  
--------------------------------------------------------------------------------------                                                                  
                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                     
---------------------------------------------------                                                                                                     
                                                                                                                                                        
   4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")                                                                                                          

SQL> select /*+ ordered use_nl(b,a) */count(*) from b,a where a.object_id=b.object_id;
已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------                                                                                              
Plan hash value: 461217448                                                                                                                              
                                                                                                                                                        
--------------------------------------------------------------------------------------                                                                  
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                                  
--------------------------------------------------------------------------------------                                                                  
|   0 | SELECT STATEMENT       |             |     1 |     8 |   211K  (1)| 00:42:16 |                                                                  
|   1 |  SORT AGGREGATE        |             |     1 |     8 |            |          |                                                                  
|   2 |   NESTED LOOPS         |             |  1844K|    14M|   211K  (1)| 00:42:16 |                                                                  
|   3 |    INDEX FAST FULL SCAN| B_OBJECT_ID |   210K|   823K|   123   (5)| 00:00:02 |                                                                  
|*  4 |    INDEX RANGE SCAN    | A_OBJECT_ID |     9 |    36 |     1   (0)| 00:00:01 |                                                                  
--------------------------------------------------------------------------------------                                                                  
                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                     
---------------------------------------------------                                                                                                     
                                                                                                                                                        
   4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")                                                                                                          

SQL> select /*+  use_nl(b,a) */count(*) from b,a where a.object_id=b.object_id;
已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------                                                                                              
Plan hash value: 89762486                                                                                                                               
                                                                                                                                                        
--------------------------------------------------------------------------------------                                                                  
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |                                                                  
--------------------------------------------------------------------------------------                                                                  
|   0 | SELECT STATEMENT       |             |     1 |     8 |   129K  (1)| 00:25:54 |                                                                  
|   1 |  SORT AGGREGATE        |             |     1 |     8 |            |          |                                                                  
|   2 |   NESTED LOOPS         |             |  1844K|    14M|   129K  (1)| 00:25:54 |                                                                  
|   3 |    INDEX FAST FULL SCAN| A_OBJECT_ID |   129K|   504K|    77   (4)| 00:00:01 |                                                                  
|*  4 |    INDEX RANGE SCAN    | B_OBJECT_ID |    14 |    56 |     1   (0)| 00:00:01 |                                                                  
--------------------------------------------------------------------------------------                                                                  
                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                     
---------------------------------------------------                                                                                                     
                                                                                                                                                        
   4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")                                                                                                          

SQL> select /*+ ordered use_hash(b,a) */count(*) from b,a where a.object_id=b.object_id;
已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------                                                                                              
Plan hash value: 291674056                                                                                                                              
                                                                                                                                                        
----------------------------------------------------------------------------------------------                                                          
| Id  | Operation              | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                          
----------------------------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT       |             |     1 |     8 |       |   498  (10)| 00:00:06 |                                                          
|   1 |  SORT AGGREGATE        |             |     1 |     8 |       |            |          |                                                          
|*  2 |   HASH JOIN            |             |  1844K|    14M|  3296K|   498  (10)| 00:00:06 |                                                          
|   3 |    INDEX FAST FULL SCAN| B_OBJECT_ID |   210K|   823K|       |   123   (5)| 00:00:02 |                                                          
|   4 |    INDEX FAST FULL SCAN| A_OBJECT_ID |   129K|   504K|       |    77   (4)| 00:00:01 |                                                          
----------------------------------------------------------------------------------------------                                                          
                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                     
---------------------------------------------------                                                                                                     
                                                                                                                                                        
   2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")                                                                                                          

SQL> select /*+ ordered use_hash(a,b) */count(*) from a,b where a.object_id=b.object_id;
已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------                                                                                              
Plan hash value: 2064530317                                                                                                                             
                                                                                                                                                        
----------------------------------------------------------------------------------------------                                                          
| Id  | Operation              | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                          
----------------------------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT       |             |     1 |     8 |       |   498  (10)| 00:00:06 |                                                          
|   1 |  SORT AGGREGATE        |             |     1 |     8 |       |            |          |                                                          
|*  2 |   HASH JOIN            |             |  1844K|    14M|  2024K|   498  (10)| 00:00:06 |                                                          
|   3 |    INDEX FAST FULL SCAN| A_OBJECT_ID |   129K|   504K|       |    77   (4)| 00:00:01 |                                                          
|   4 |    INDEX FAST FULL SCAN| B_OBJECT_ID |   210K|   823K|       |   123   (5)| 00:00:02 |                                                          
----------------------------------------------------------------------------------------------                                                          
                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                     
---------------------------------------------------                                                                                                     
                                                                                                                                                        
   2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")                                                                                                          

SQL> select /*+ use_hash(b,a) */count(*) from b,a where a.object_id=b.object_id;
已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------                                                                                              
Plan hash value: 2064530317                                                                                                                             
                                                                                                                                                        
----------------------------------------------------------------------------------------------                                                          
| Id  | Operation              | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                          
----------------------------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT       |             |     1 |     8 |       |   498  (10)| 00:00:06 |                                                          
|   1 |  SORT AGGREGATE        |             |     1 |     8 |       |            |          |                                                          
|*  2 |   HASH JOIN            |             |  1844K|    14M|  2024K|   498  (10)| 00:00:06 |                                                          
|   3 |    INDEX FAST FULL SCAN| A_OBJECT_ID |   129K|   504K|       |    77   (4)| 00:00:01 |                                                          
|   4 |    INDEX FAST FULL SCAN| B_OBJECT_ID |   210K|   823K|       |   123   (5)| 00:00:02 |                                                          
----------------------------------------------------------------------------------------------                                                          
                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                     
---------------------------------------------------                                                                                                     
                                                                                                                                                        
   2 - access("A"."OBJECT_ID"="B"."OBJECT_ID")                                                                                                          

SQL> select /*+ use_merge(a,b) */count(*) from a,b where a.object_id=b.object_id;
已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------                                                                                              
Plan hash value: 3197909081                                                                                                                             
                                                                                                                                                        
-----------------------------------------------------------------------------------------------                                                         
| Id  | Operation               | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                         
-----------------------------------------------------------------------------------------------                                                         
|   0 | SELECT STATEMENT        |             |     1 |     8 |       |   966   (7)| 00:00:12 |                                                         
|   1 |  SORT AGGREGATE         |             |     1 |     8 |       |            |          |                                                         
|   2 |   MERGE JOIN            |             |  1844K|    14M|       |   966   (7)| 00:00:12 |                                                         
|   3 |    INDEX FULL SCAN      | B_OBJECT_ID |   210K|   823K|       |   474   (2)| 00:00:06 |                                                         
|*  4 |    SORT JOIN            |             |   129K|   504K|  3048K|   464   (5)| 00:00:06 |                                                         
|   5 |     INDEX FAST FULL SCAN| A_OBJECT_ID |   129K|   504K|       |    77   (4)| 00:00:01 |                                                         
-----------------------------------------------------------------------------------------------                                                         
                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                     
---------------------------------------------------                                                                                                     
                                                                                                                                                        
   4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")                                                                                                          
       filter("A"."OBJECT_ID"="B"."OBJECT_ID")                                                                                                          

SQL> select /*+ ordered use_merge(b,a) */count(*) from b,a where a.object_id=b.object_id;
已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------                                                                                              
Plan hash value: 3197909081                                                                                                                             
                                                                                                                                                        
-----------------------------------------------------------------------------------------------                                                         
| Id  | Operation               | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                         
-----------------------------------------------------------------------------------------------                                                         
|   0 | SELECT STATEMENT        |             |     1 |     8 |       |   966   (7)| 00:00:12 |                                                         
|   1 |  SORT AGGREGATE         |             |     1 |     8 |       |            |          |                                                         
|   2 |   MERGE JOIN            |             |  1844K|    14M|       |   966   (7)| 00:00:12 |                                                         
|   3 |    INDEX FULL SCAN      | B_OBJECT_ID |   210K|   823K|       |   474   (2)| 00:00:06 |                                                         
|*  4 |    SORT JOIN            |             |   129K|   504K|  3048K|   464   (5)| 00:00:06 |                                                         
|   5 |     INDEX FAST FULL SCAN| A_OBJECT_ID |   129K|   504K|       |    77   (4)| 00:00:01 |                                                         
-----------------------------------------------------------------------------------------------                                                         
                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                     
---------------------------------------------------                                                                                                     
                                                                                                                                                        
   4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")                                                                                                          
       filter("A"."OBJECT_ID"="B"."OBJECT_ID")                                                                                                          

SQL> select /*+ ordered use_merge(a,b) */count(*) from a,b where a.object_id=b.object_id;
已用时间:  00: 00: 00.01

执行计划
----------------------------------------------------------                                                                                              
Plan hash value: 3063902891                                                                                                                             
                                                                                                                                                        
-----------------------------------------------------------------------------------------------                                                         
| Id  | Operation               | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                         
-----------------------------------------------------------------------------------------------                                                         
|   0 | SELECT STATEMENT        |             |     1 |     8 |       |  1076   (7)| 00:00:13 |                                                         
|   1 |  SORT AGGREGATE         |             |     1 |     8 |       |            |          |                                                         
|   2 |   MERGE JOIN            |             |  1844K|    14M|       |  1076   (7)| 00:00:13 |                                                         
|   3 |    INDEX FULL SCAN      | A_OBJECT_ID |   129K|   504K|       |   294   (2)| 00:00:04 |                                                         
|*  4 |    SORT JOIN            |             |   210K|   823K|  4984K|   754   (5)| 00:00:10 |                                                         
|   5 |     INDEX FAST FULL SCAN| B_OBJECT_ID |   210K|   823K|       |   123   (5)| 00:00:02 |                                                         
-----------------------------------------------------------------------------------------------                                                         
                                                                                                                                                        
Predicate Information (identified by operation id):                                                                                                     
---------------------------------------------------                                                                                                     
                                                                                                                                                        
   4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")                                                                                                          
       filter("A"."OBJECT_ID"="B"."OBJECT_ID")                                                                                                          

SQL> spool off

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

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

cotchte0421
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle use_merge,oracle hint use_merge提示的hint优化select insert性能
weixin_39771614的博客
04-03 479
如下sql运行非常慢insert /*+APPEND*/ into deal_order_user_choose_tmp nologging(ORDER_ID,--订单ID varchar2(100) VARCHAR2(80)ORDER_DETAILID,-- 订单明细ID varchar2(800) VARCHAR2(80)CHOOSE_TYPE,-- 选择项类型 CHAR(40)...
Oracle Hint：USE_NL、USE_MERGE、UESE_HASH
abc123098098的博客
09-10 301
下面内容取自http://yangtingkun.itpub.net/post/468/26696 一、USE_NL(嵌套循环连接) 在嵌套循环连接中,Oracle从第一个行源中读取第一行,然后和第二个行源中的数据进行对比。所有匹配的记录放在结果集中,然后Oracle将读取第一个行源中的下一行。按这种方式直至第一个数据源中的所在行都经过处理。第一个记录源通常称为外部表,或...
oracle hint use_merge提示的hint优化select insert性能
11-29 117
如下sql运行非常慢insert /*+APPEND*/ into deal_order_user_choose_tmp nologging ( ORDER_ID,--订单ID varc...
排序合并连接的使用原则和分析（use_merge）
cpfengyo322322的博客
05-05 777
========================================================================================================...
USE_NL
落落的专栏 专注SQL调优 性能调优
12-01 7365
use_nl (table1,table2....) 提示指示指定表作为inner table,如果指定的表已经作为了outer table(驱动表)，那么优化器会忽略use_nl 提示，如果非要强制它作为inner table ，可以与ordered 提示一起使用。下面以SCOTT测试用户作为试验 SQL> select table_name,last_analyzed from
oracle的hint函数
01-17
介绍了oracle中的hint，常用的 ordered、use_nl、use_hash、index、full 五种, 给出使用实例和适用场景
ip_vs_sh.rar_ip hash
09-23
描述中提到，“The sh algorithm is to server by the hash key of source IP.” 这意味着，当客户端发送请求时，IPVS会使用源IP地址作为哈希键，通过对这个键进行哈希运算，得到一个唯一的哈希值。这个哈希值与后端...
pwwHash.zip_Big!_hash map
09-19
在IT领域，哈希表（Hash Map）是一种高效的数据结构，尤其在处理大数据时，它的作用至关重要。"pwwHash.zip_Big!_hash map"这个文件标题暗示了这是一个关于大规模数据处理和哈希映射的专题，可能包含了某种特定的...
md5555.rar_hash_hash matlab
07-14
标题中的“md5555.rar_hash_hash matlab”暗示了我们正在讨论的是使用MATLAB进行MD5哈希计算。MD5（Message-Digest Algorithm 5）是一种广泛使用的哈希函数，设计用于产生一个128位（16字节）的数字指纹，通常表示为...
ip_set_hash_ipportip.rar_ip hash
09-20
《IP Set Hash:ip,port,ip模块详解》 在Linux内核中，IP Set是一种强大的工具，用于管理网络流量过滤和路由策略。它允许系统管理员动态地创建和维护一组IP地址、端口或其他网络标识符的集合，以便进行高效的网络...
oracle hint use_nl,了解如何利用ordered,use_nl(),leading(),index()进行调优 ...
weixin_35828255的博客
04-03 1350
首先我们来分别介绍一下这四个hint的概念：1. ORDERED hint:ordered hint 指示oracle按照from关键字后的表顺序来进行连接。如果没有及时收集统计信息，查询优化器没有得到足够信息，此时你可以自行选择适当的inner及outer表进行连接。ORACLE推荐使用LEADING hint,它比ordered hint有更多的用途。2. LEADING hint:leadi...
use_nl
maoweiting19910402的专栏
10-17 986
之前对use_nl的理解一直很模糊，看下面的案例。 [coolcode] SQL> select * 2 from table_detail t, 3 table(cast(str2varlist(’123′) as vartabletype)) t2 4 where t.id = t2.column_value; Execution Plan —————————————————
Oracle Hint:USE_NL、USE_MERGE、USE_HASH
junmail的专栏
12-28 7699
Oracle Hint:USE_NL、USE_MERGE、USE_HASH备查
use_nl,use_hash,use_merge的3种连接方式驱动与被驱动关系
N
05-31 5129
SQL> select * from v$version; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Oracle之三大连接的使用限制，nl、hash、merge
qq_33879355的博客
10-25 2472
的萨达萨达多撒大所
ORDERED 和 USE_NL() hint (zt)的用法
jovitang的专栏
08-15 8884
 前两天,在给一只report写一个Validation Set.发现把这三个table PO_AGENTS PA, PER_PEOPLE_F  PAPF,HR_LOCATIONS_ALL_TL HL 串起来后语句执行的像蜗牛一样慢啊，用户每次跑个报表是都会抱怨说在挑选参数时太慢。不得不优化语句啊。后来想用用oralce 的优化器rule(基于规则)来优化语句。但是依然很慢。无意间看到了Use_N
Oracle表连接方式总结
weixiaohuai的博客
06-20 1220
一、简介 Oracle中主要有下面四种表连接方式： SORT MERGE JOIN（排序-合并连接）； NESTED LOOPS（嵌套循环）； HASH JOIN（哈希连接）； CARTESIAN PRODUCT（笛卡尔积）； Oracle中，通过JOIN关键字进行表连接操作，一次只能连接两张表，JOIN 操作的各步骤一般是串行的（在读取做连接的两张表的数据时可以并行读取）。 需要说明两个重要的概念：驱动表与匹配表。注意这些概念只存在于NESTED LOOPS（嵌套循环）与 HASH JOIN（
深入理解Oracle表(1)：ORDERED和USE_NL
数据库
01-26 346
ORDERED好理解，就是表示根据 from 后面表的顺序join，从左到右，左边的表做驱动表 use_nl（t1,t2）：表示对表t1、t2关联时采用嵌套循环连接,其并不能让优化器确定谁是驱动表或谁是被驱动的表 USE_NL()，先看看oracle doc怎么说： In this statement, the USE_NL hint explicitly chooses a n...
use_hash,use_merge,use_nl
djz19890117的专栏
12-30 2189
use_hash(table1 table2)指定table1与table2连接以hash方式连接，优化器选择驱动表，我们没法控制。use_merge(table1 table2)指定table1与table2连接以merge方式连接，合并连接没有驱动表的概念。use_nl(table1 table2)指定table1与table2连接以nested loop方式连接，优化器选择驱动表，我们没法控
oracle hash leading,oracle 使用leading, use_nl, rownum调优(引用)
最新发布
05-16
Use_nl是一个提示，用于告诉Oracle在查询过程中使用嵌套循环连接（Nested Loop Join）操作。这种连接操作适用于其中一个表比较小的情况下。Rownum则是一个函数，用于限制查询结果的数量。例如，可以使用Rownum来只...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值