swapping join input

最新推荐文章于 2021-04-04 04:45:30 发布
转载 最新推荐文章于 2021-04-04 04:45:30 发布 · 168 阅读 · 0

本文探讨了在Oracle数据库中使用SQL查询优化，特别关注了如何通过调整Join操作的顺序来提高查询效率。通过使用特定的HINTs，如`swap_join_inputs`和`leading`，可以改变Join的执行顺序，从而在不同表大小的情况下获得更优的性能。文章还详细解释了如何在查询中使用这些技巧，以实现针对特定场景的最佳查询执行计划。

select * from wxh_tbd1 a ,wxh_tbd2 b where a.id=b.id(+);
对于如上查询，在10G以前，执行计划的结果只可能为(我们只考虑hash join)：
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   128 |     9  (12)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |          |     1 |   128 |     9  (12)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| WXH_TBD1 |     1 |    13 |     4   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| WXH_TBD2 |     1 |   115 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------
这样有一个问题，如果wxh_tbd1非常大，而wxh_tbd2非常小，那么我们可能想wxh_tbd2作为build table,这样效率会更高
ORACLE10G后，会根据表大小来自动的完成这种切换
----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |   128 |     9  (12)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER|          |     1 |   128 |     9  (12)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | WXH_TBD2 |     1 |   115 |     4   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | WXH_TBD1 |     1 |    13 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------
执行计划显示了一种新的join方式 HASH JOIN RIGHT OUTER

如果我们想改变这种join的顺序，可以通过hint swap_join_inputs来达到目的
select /*+ swap_join_inputs(b) */* from wxh_tbd1 a ,wxh_tbd2 b where a.id=b.id(+);
----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |   128 |     9  (12)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER|          |     1 |   128 |     9  (12)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | WXH_TBD2 |     1 |   115 |     4   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | WXH_TBD1 |     1 |    13 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------
 select * from wxh_tbd1 a  where a.object_id in ( select object_id from wxh_tbd2 b);
 ---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   398K|    37M|  3640   (1)| 00:00:44 |
|*  1 |  HASH JOIN RIGHT SEMI|          |   398K|    37M|  3640   (1)| 00:00:44 |
|   2 |   TABLE ACCESS FULL  | WXH_TBD2 | 49838 |   243K|   414   (1)| 00:00:05 |
|   3 |   TABLE ACCESS FULL  | WXH_TBD1 |   398K|    35M|  3222   (1)| 00:00:39 |
---------------------------------------------------------------------------------

select /*+ leading(a) */* from wxh_tbd1 a  where a.object_id in ( select object_id from wxh_tbd2 b);
---------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |   398K|    37M|       |  5663   (1)| 00:01:08 |
|*  1 |  HASH JOIN SEMI    |          |   398K|    37M|    39M|  5663   (1)| 00:01:08 |
|   2 |   TABLE ACCESS FULL| WXH_TBD1 |   398K|    35M|       |  3222   (1)| 00:00:39 |
|   3 |   TABLE ACCESS FULL| WXH_TBD2 | 49838 |   243K|       |   414   (1)| 00:00:05 |
---------------------------------------------------------------------------------------
 select * from wxh_tbd2 a where a.object_id in ( select object_id from wxh_tbd1 b);
 ---------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          | 49663 |  4752K|       |  4208   (1)| 00:00:51 |
|*  1 |  HASH JOIN SEMI    |          | 49663 |  4752K|  5112K|  4208   (1)| 00:00:51 |
|   2 |   TABLE ACCESS FULL| WXH_TBD2 | 49838 |  4526K|       |   415   (1)| 00:00:05 |
|   3 |   TABLE ACCESS FULL| WXH_TBD1 |   398K|  1946K|       |  3215   (1)| 00:00:39 |
---------------------------------------------------------------------------------------

select * from wxh_tbd2 a where a.object_id in ( select /*+ swap_join_inputs(b) */ object_id from wxh_tbd1 b);
-----------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          | 49663 |  4752K|       |  4207   (1)| 00:00:51 |
|*  1 |  HASH JOIN RIGHT SEMI|          | 49663 |  4752K|  6624K|  4207   (1)| 00:00:51 |
|   2 |   TABLE ACCESS FULL  | WXH_TBD1 |   398K|  1946K|       |  3215   (1)| 00:00:39 |
|   3 |   TABLE ACCESS FULL  | WXH_TBD2 | 49838 |  4526K|       |   415   (1)| 00:00:05 |
-----------------------------------------------------------------------------------------

swap_join_inputs还可以控制hash join的顺序，t1作为build表和T2做hash_join，然后t3作为build表和t1,t2的结果集作hash_join,在把t3,t1,t2的结果集作build表和t4做hash_join
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
create table t3 as select * from dba_objects;
create table t4 as select * from dba_objects;
exec dbms_stats.gather_table_stats(user,'t1');
exec dbms_stats.gather_table_stats(user,'t2');
exec dbms_stats.gather_table_stats(user,'t3');
exec dbms_stats.gather_table_stats(user,'t4');  

select
/*+                            
 ordered                        
 use_hash(t2)                   
 use_hash(t3)                   
 swap_join_inputs(t3)           
 use_hash(t4)                   
 no_swap_join_inputs(t4)        
 */
 *
  from t1, t2, t3, t4
 where t1.object_id = t2.object_id
   and t2.object_name = t3.object_name
   and t3.owner = t4.owner
   and t4.owner = 'MYDB' ;

-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     9 |  3348 |       |   431   (1)| 00:00:06 |
|*  1 |  HASH JOIN           |      |     9 |  3348 |       |   431   (1)| 00:00:06 |
|*  2 |   HASH JOIN          |      |     6 |  1674 |       |   373   (1)| 00:00:05 |
|*  3 |    TABLE ACCESS FULL | T3   |     2 |   186 |       |    57   (0)| 00:00:01 |
|*  4 |    HASH JOIN         |      | 19854 |  3606K|  2048K|   315   (1)| 00:00:04 |
|   5 |     TABLE ACCESS FULL| T1   | 19948 |  1811K|       |    58   (2)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   | 19949 |  1811K|       |    58   (2)| 00:00:01 |
|*  7 |   TABLE ACCESS FULL  | T4   |     2 |   186 |       |    57   (0)| 00:00:01 |
------------------------------------------------------------------------------------- 

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

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

cotchte0421
关注
数据结构与算法基础知识和代码示例 Coding Interview University: Algorithms and Data Structure
AI天才研究院
10-09 1030
作者：禅与计算机程序设计艺术The Coding Interview University is a free online course available on Coursera that offers valuable insights into how to approach computer science interviews. It provides clear explanations of data structures and algorithms with real-world exa
FaceForensics++数据集下载,FaceForensics++: Learning to Detect Manipulated Facial Images
zik的博客
07-14 2747
获取方式： > GitHub链接获取填写表单[https://github.com/ondyari/FaceForensics](https://github.com/ondyari/FaceForensics) > 收到一个脚本。 > 整个数据集的处理在：[https://github.com/ondyari/FaceForensics/blob/master/dataset/README.md](https://github.com/ondyari/FaceForensics/blob/master/d
SWAP_JOIN_INPUTS Oracle Hint
weixin_34290390的博客
10-02 224
swap_join_inputs是针对哈希连接的hint，它的含义是让优化器交换原哈希连接的驱动表和被驱动表的顺序，即在依然走哈希连接的情况下让原哈希连接的驱动表变被驱动表，让原哈希连接的被驱动表变为驱动表。注意，在swap_join_inputs hint中指定的目标表应该是原哈希连接中的被驱动表，否则oracle会忽略该hint。/*+ swap_join_inputs(...
oracle swap_join_inputs,Oracle 特殊的连接方式—半连接,反连接,内外连接!
weixin_26905423的博客
04-04 467
这几种连接方式在平时开发sql当中也是非常常见的，对这几种连接方式没有什么好解释的，作为一个开发人员来讲，可以说对这几种连接方式非常熟悉了。半连接semi-join，指两个表做join的时候，只返回某一个表中的数据，在执行计划中看到nested loops semi/hash join semi就表示有半连接。反连接其实就是特殊的半连接，在半连接中，经常写in/exist的写法，反连接就是把in/...
HINT no_swap_join_inputs/swap_join_inputs
cuiwuzha3492的博客
08-29 326
10G以后 hash_join可以通过no_swap_join_inputs/swap_join_inputs来强制控制build表，配合leading或者ordered可以控制多表之前的连接顺序。 >...
oracle swap_join_inputs,Oracle诊断案例-SGA与Swap案例
weixin_33130113的博客
04-04 590
用户报告，服务器启动一段时间以后，无法建立数据库连接重新启动几分钟以后，再次无法连接系统无法正常使用.1.登陆系统SunOS 5.8login: rootPassword:Last login: Tue Mar 23 13:56:59 from 172.16.31.41Sun Microsystems Inc. SunOS 5.8GenericPatch October 2001You hav...
oracle Swap_join_inputs简析
u011165335的博客
08-19 1592
此hint仅针对hash_join有效,强行交换驱动关系;目标表为 原hash连接的被驱动表,适合的场景: 1.多表的hash内连接(大于2个以上) 2.多表的hash外连接(大于等于2个以上) 场景1: --2个表的内连接 select /*+ leading(d) use_hash(e) swap_join_inputs(e)*/ * from dept d,emp e where...
Oracle哈希连接（Hash Join）算法详解
为此，Oracle引入了多种技术来优化这一问题，例如位图向量过滤（Bitmap Vector Filtering）、角色互换（Role Swapping）以及柱状图（Histograms）。位图向量过滤利用位图数据结构减少不必要的计算；角色互换允许在...
A-1 Intersection Set of Prime Factors 分数 20 作者 陈越 单位 浙江大学 Given a positive integer n. Select two distinct digits from the decimal repersentation（十进制表示）of n, we obtain another integer m. What is the size of the intersection set（交集）of the prime factors of n and m? For example, given n=623457198, its prime factor set is A = {2, 3, 7, 13, 380621}. Swapping 2 and 9 gives us m=693457128, of which the prime factor set is B = {2, 3, 7, 13, 109, 971}. Then the intersection set of A and B is {2, 3, 7, 13}, with 4 factors. Input Specification: Each input file contains one test case, which gives a positive integer n (10<n≤10 9 ). It is guaranteed that there are at least 2 distinct digits in n. Output Specification: Swap any pair of digits in n to obtain m, you are supposed to find the m with the largest intersection set of the prime factors of n and m. Output in a line the number of the prime factors in the intersection set, together with m. The numbers must be separated by 1 space, and there must be no extra space at the beginning or the end of the line. In case such an m is not unique, output the one with the smallest value. Sample Input: 623457198 Sample Output: 4 123457698 Hint: There are two m's with 4 common factors. Besides the one given in the problem description, we can also swap 6 and 1 to obtain 123457698. This number has a prime factor set {2, 3, 7, 13, 23, 29, 113}, and so the intersection set is also {2, 3, 7, 13}. This number is in the ouput because it is smaller than 693457128.(c++)
最新发布
07-28
new_n = int(''.join(swapped)) if new_n not in seen: seen.add(new_n) yield new_n def solve(n): original_factors = prime_factors(n) max_intersection_size = 0 best_m = None for m in generate_...
刷题记录2021.3.19之100题
weixin_46006714的博客
03-19 1624
深夜刷题第一天1.A - LCM Challenge2.B - Let's Play Osu! 1.A - LCM Challenge A. LCM Challenge time limit per test2 seconds memory limit per test256 megabytes inputstandard input outputstandard output Some days ago, I learned the concept of LCM (least common multipl
Oracle SWAP_JOIN_INPUTS hint(原创)
czmmiao的专栏
02-22 408
考虑下列sqlselect    /*+        leading(t1 t2 t3 t4)        use_hash(t2) use_hash(t3) use_hash(t4)    */    count(t1.small_vc),    count(t2.small_vc),    count(t3.small_vc),    count(t4.small_vc)from   ...
SWAP_JOIN_INPUTS Oracle Hint(处理hash join强制大表（segment_size大）作为被驱动表)
张冲andy的博客
04-30 2592
swap_join_inputs是针对哈希连接的hint，它的含义是让优化器交换原哈希连接的驱动表和被驱动表的顺序，即在依然走哈希连接的情况下让原哈希连接的驱动表变被驱动表，让原哈希连接的被驱动表变为驱动表。 注意，在swap_join_inputs hint中指定的目标表应该是原哈希连接中的被驱动表，否则oracle会忽略该hint。 /*+ swap_join_inputs(原哈希
通过swap_join_inputs来控制多表连接hash join中的build table
cotchte0421的博客
08-04 226
hash_join可以通过no_swap_join_inputs/swap_join_inputs来强制控制build表，配合leading或者ordered可以控制多表之前的连接顺序----------------创建4个测试...
no_swap_join_inputs 与 leading
有教无类的专栏
01-13 2018
首先建立测试用表如下 create table t1 as select * from dba_objects; create table t2 as select * from dba_objects; create table t3 as select * from dba_objects; create table t4 as select * from dba_objects; 如下语
oracle swap_join_inputs,【学习笔记】如何使用SWAP_JOIN_INPUTS修改HASH连接的驱动表
weixin_33379297的博客
04-04 307
【学习笔记】如何使用SWAP_JOIN_INPUTS修改HASH连接的驱动表时间:2016-11-03 21:25来源:Oracle研究中心作者:HTZ点击:次天萃荷净Oracle研究中心学习笔记：分享一篇关于修改HASH连接的驱动表笔记，通过如何使用SWAP_JOIN_INPUTS修改HASH连接的驱动表详细过程笔记。下面是测试在HASH连接的时候，修改驱动表的顺序。HASH连...
oracle swap_join_inputs,如何确定oracle 是否使用了swap？
weixin_33235339的博客
04-04 162
I don't think we can unambiguously tell. You didn't say which OS. Here's an example on Linux. I'm checking my bash process. The output of "top -p $$" has:PID USER PRNIVIRTRESSHR S %CPU %M...
通三个提示来控制HASH_JOIN顺序和内建与探测表
海鲨数据库架构师
04-11 2598
hash_join可以通过no_swap_join_inputs/swap_join_inputs来强制控制build表，配合leading可以控制多表之前的连接顺序----------------创建4个测试表create table t1 as select * from dba_objects;create table t2 as select * from dba_objects;crea
Oracle 表连接之Hints
woi123的专栏
07-21 360
hash_join可以通过swap_join_inputs来强制指定build表(或者驱动表)，no_swap_join_inputs来强制 指定prob表(探查表,或者说被驱动表) 配合leading或者ordered可以控制多表之前的连接顺序 ----------------创建4个测试表 create table t1 as select * from dba_objects; ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值