如何让in/exists 子查询(半连接)作为驱动表？

最新推荐文章于 2022-03-18 11:48:12 发布
最新推荐文章于 2022-03-18 11:48:12 发布
阅读量668 收藏
点赞数
分类专栏： oracle_sql oracle_optimize

怎么才能让子查询作为驱动表？ SQL如下：

[html]  view plain copy print ?
  1. select  rowid rid  
  2.    from its_car_pass7 v  
  3.   where 1 = 1  
  4.     and pass_datetime >=  
  5.         to_date('2013-07-06 :17:46:04', 'yyyy-mm-dd hh24:mi:ss')  
  6.     and pass_datetime <=  
  7.         to_date('2013-07-06 :18:46:06', 'yyyy-mm-dd hh24:mi:ss')  
  8.     and v.pass_device_unid in  
  9.         (select unid  
  10.            from its_base_device  
  11.           where dev_bay_unid in ('01685EFE4658C19D59C4DDAAEDD37393')  
  12.             and dev_type = '1'  
  13.             and dev_chk_flag = '1'  
  14.             and dev_delete_flag = 'N')  
  15.   order by v.pass_datetime asc   
  16.  /  

执行计划如下：

[html]  view plain copy print ?
  1. Execution Plan  
  2. ----------------------------------------------------------  
  3. Plan hash value: 3634433140  
  4.   
  5. --------------------------------------------------------------------------------------------------------------------  
  6. | Id  | Operation                     | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
  7. --------------------------------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT              |                    |     1 |   111 |     2  (50)| 00:00:01 |       |       |  
  9. |   1 |  SORT ORDER BY                |                    |     1 |   111 |     2  (50)| 00:00:01 |       |       |  
  10. |   2 |   NESTED LOOPS                |                    |       |       |            |          |       |       |  
  11. |   3 |    NESTED LOOPS               |                    |     1 |   111 |     1   (0)| 00:00:01 |       |       |  
  12. |   4 |     PARTITION RANGE SINGLE    |                    |     1 |    39 |     1   (0)| 00:00:01 |  1284 |  1284 |  
  13. |*  5 |      INDEX SKIP SCAN          | IDX_VT7_DEVICEID   |     1 |    39 |     1   (0)| 00:00:01 |  1284 |  1284 |  
  14. |*  6 |     INDEX UNIQUE SCAN         | PK_ITS_BASE_DEVICE |     1 |       |     0   (0)| 00:00:01 |       |       |  
  15. |*  7 |    TABLE ACCESS BY INDEX ROWID| ITS_BASE_DEVICE    |     1 |    72 |     0   (0)| 00:00:01 |       |       |  
  16. --------------------------------------------------------------------------------------------------------------------  
  17.   
  18. Predicate Information (identified by operation id):  
  19. ---------------------------------------------------  
  20.   
  21.    5 - access("PASS_DATETIME">=TO_DATE(' 2013-07-06 17:46:04', 'syyyy-mm-dd hh24:mi:ss') AND  
  22.               "PASS_DATETIME"<=TO_DATE(' 2013-07-06 18:46:06', 'syyyy-mm-dd hh24:mi:ss'))  
  23.        filter("PASS_DATETIME">=TO_DATE(' 2013-07-06 17:46:04', 'syyyy-mm-dd hh24:mi:ss') AND  
  24.               "PASS_DATETIME"<=TO_DATE(' 2013-07-06 18:46:06', 'syyyy-mm-dd hh24:mi:ss'))  
  25.    6 - access("V"."PASS_DEVICE_UNID"="UNID")  
  26.    7 - filter("DEV_BAY_UNID"='01685EFE4658C19D59C4DDAAEDD37393' AND "DEV_TYPE"='1' AND  
  27.               "DEV_DELETE_FLAG"='N' AND "DEV_CHK_FLAG"='1')  
  28.   
  29.   
  30. Statistics  
  31. ----------------------------------------------------------  
  32.           1  recursive calls  
  33.           0  db block gets  
  34.      110973  consistent gets  
  35.           0  physical reads  
  36.           0  redo size  
  37.       47861  bytes sent via SQL*Net to client  
  38.        1656  bytes received via SQL*Net from client  
  39.         105  SQL*Net roundtrips to/from client  
  40.           1  sorts (memory)  
  41.           0  sorts (disk)  
  42.        1560  rows processed  

这里我们就不管统计信息是否准确了，也不管SQL优化的问题，就单单讨论哥们问的问题吧。

那哥们说，怎么才能让子查询作为驱动表呢？他自己试了很多方法就是搞不定。 那我们来亲自搞搞吧

[html]  view plain copy print ?
  1. explain plan for   select  rowid rid  
  2.    from its_car_pass7 v  
  3.   where 1 = 1  
  4.     and pass_datetime >=  
  5.         to_date('2013-07-06 :17:46:04', 'yyyy-mm-dd hh24:mi:ss')  
  6.     and pass_datetime <=  
  7.         to_date('2013-07-06 :18:46:06', 'yyyy-mm-dd hh24:mi:ss')  
  8.     and v.pass_device_unid in  
  9.         (select unid  
  10.            from its_base_device  
  11.           where dev_bay_unid in ('01685EFE4658C19D59C4DDAAEDD37393')  
  12.             and dev_type = '1'  
  13.             and dev_chk_flag = '1'  
  14.             and dev_delete_flag = 'N')  
  15.   order by v.pass_datetime asc   
  16.  /  

执行计划如下

[html]  view plain copy print ?
  1. SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'ADVANCED -PROJECTION'));  
  2.   
  3. -----------------------------------------------------------  
  4. Plan hash value: 2191740724  
  5. ---------------------------------------------------------------------------------------------------------------------------  
  6. | Id  | Operation                            | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
  7. ---------------------------------------------------------------------------------------------------------------------------  
  8. |   0 | SELECT STATEMENT                     |                    |     1 |   111 |  2092K  (1)| 06:58:26 |       |       |  
  9. |   1 |  NESTED LOOPS                        |                    |       |       |            |          |       |       |  
  10. |   2 |   NESTED LOOPS                       |                    |     1 |   111 |  2092K  (1)| 06:58:26 |       |       |  
  11. |   3 |    PARTITION RANGE SINGLE            |                    |     1 |    39 |  2092K  (1)| 06:58:26 |  1284 |  1284 |  
  12. |   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| ITS_CAR_PASS7      |     1 |    39 |  2092K  (1)| 06:58:26 |  1284 |  1284 |  
  13. |*  5 |      INDEX RANGE SCAN                | IDX_VT7_DATETIME   |     1 |       |  6029   (1)| 00:01:13 |  1284 |  1284 |  
  14. |*  6 |    INDEX UNIQUE SCAN                 | PK_ITS_BASE_DEVICE |     1 |       |     0   (0)| 00:00:01 |       |       |  
  15. |*  7 |   TABLE ACCESS BY INDEX ROWID        | ITS_BASE_DEVICE    |     1 |    72 |     0   (0)| 00:00:01 |       |       |  
  16. ---------------------------------------------------------------------------------------------------------------------------  
  17.    
  18. Query Block Name / Object Alias (identified by operation id):  
  19. -------------------------------------------------------------  
  20.    
  21.    1 - SEL$5DA710D3  
  22.    4 - SEL$5DA710D3 / V@SEL$1  
  23.    5 - SEL$5DA710D3 / V@SEL$1  
  24.    6 - SEL$5DA710D3 / ITS_BASE_DEVICE@SEL$2  
  25.    7 - SEL$5DA710D3 / ITS_BASE_DEVICE@SEL$2  
  26.    
  27. Outline Data  
  28. -------------  
  29.    
  30.   /*+  
  31.       BEGIN_OUTLINE_DATA  
  32.       NLJ_BATCHING(@"SEL$5DA710D3" "ITS_BASE_DEVICE"@"SEL$2")  
  33.       USE_NL(@"SEL$5DA710D3" "ITS_BASE_DEVICE"@"SEL$2")  
  34.       LEADING(@"SEL$5DA710D3" "V"@"SEL$1" "ITS_BASE_DEVICE"@"SEL$2")  
  35.       INDEX(@"SEL$5DA710D3" "ITS_BASE_DEVICE"@"SEL$2" ("ITS_BASE_DEVICE"."UNID"))  
  36.       INDEX_RS_ASC(@"SEL$5DA710D3" "V"@"SEL$1" ("ITS_CAR_PASS7"."PASS_DATETIME"))  
  37.       OUTLINE(@"SEL$2")  
  38.       OUTLINE(@"SEL$1")  
  39.       UNNEST(@"SEL$2")  
  40.       OUTLINE_LEAF(@"SEL$5DA710D3")  
  41.       FIRST_ROWS  
  42.       DB_VERSION('11.2.0.3')  
  43.       OPTIMIZER_FEATURES_ENABLE('11.2.0.3')  
  44.       IGNORE_OPTIM_EMBEDDED_HINTS  
  45.       END_OUTLINE_DATA  
  46.   */  
  47.    
  48. Predicate Information (identified by operation id):  
  49. ---------------------------------------------------  
  50.    
  51.    5 - access("PASS_DATETIME">=TO_DATE(' 2013-07-06 17:46:04', 'syyyy-mm-dd hh24:mi:ss') AND   
  52.               "PASS_DATETIME"<=TO_DATE(' 2013-07-06 18:46:06', 'syyyy-mm-dd hh24:mi:ss'))  
  53.    6 - access("V"."PASS_DEVICE_UNID"="UNID")  
  54.    7 - filter("DEV_BAY_UNID"='01685EFE4658C19D59C4DDAAEDD37393' AND "DEV_TYPE"='1' AND "DEV_DELETE_FLAG"='N' AND   
  55.               "DEV_CHK_FLAG"='1')  


 执行计划又变了，我们也先别管执行计划为啥变了，驱动表仍然是 ITS_CAR_PASS7，现在我们来改变驱动表

[html]  view plain copy print ?
  1. select /*+ leading(ITS_BASE_DEVICE@SEL$2) */ rowid rid  
  2.    from its_car_pass7 v  
  3.   where 1 = 1  
  4.     and pass_datetime >=  
  5.         to_date('2013-07-06 :17:46:04', 'yyyy-mm-dd hh24:mi:ss')  
  6.     and pass_datetime <=  
  7.         to_date('2013-07-06 :18:46:06', 'yyyy-mm-dd hh24:mi:ss')  
  8.     and v.pass_device_unid in  
  9.         (select unid  
  10.            from its_base_device  
  11.           where dev_bay_unid in ('01685EFE4658C19D59C4DDAAEDD37393')  
  12.             and dev_type = '1'  
  13.             and dev_chk_flag = '1'  
  14.             and dev_delete_flag = 'N')  
  15.   order by v.pass_datetime asc   
  16.  /  
  17.   
  18. Execution Plan  
  19. ----------------------------------------------------------  
  20. Plan hash value: 712001411  
  21.   
  22. -------------------------------------------------------------------------------------------------------------------------------  
  23. | Id  | Operation                          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |  
  24. -------------------------------------------------------------------------------------------------------------------------------  
  25. |   0 | SELECT STATEMENT                   |                          |     1 |   111 |    25   (4)| 00:00:01 |       |       |  
  26. |   1 |  SORT ORDER BY                     |                          |     1 |   111 |    25   (4)| 00:00:01 |       |       |  
  27. |*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| ITS_CAR_PASS7            |     1 |    39 |     2   (0)| 00:00:01 |  1284 |  1284 |  
  28. |   3 |    NESTED LOOPS                    |                          |     1 |   111 |    24   (0)| 00:00:01 |       |       |  
  29. |*  4 |     TABLE ACCESS BY INDEX ROWID    | ITS_BASE_DEVICE          |     6 |   432 |    12   (0)| 00:00:01 |       |       |  
  30. |*  5 |      INDEX RANGE SCAN              | IDX_DEVICE_DEV_BAY_UNID  |     7 |       |     1   (0)| 00:00:01 |       |       |  
  31. |   6 |     PARTITION RANGE SINGLE         |                          |    44M|       |     2   (0)| 00:00:01 |  1284 |  1284 |  
  32. |*  7 |      INDEX RANGE SCAN              | IDX_VT7_PASS_DEVICE_UNID |    44M|       |     2   (0)| 00:00:01 |  1284 |  1284 |  
  33. -------------------------------------------------------------------------------------------------------------------------------  
  34.   
  35. Predicate Information (identified by operation id):  
  36. ---------------------------------------------------  
  37.   
  38.    2 - filter("PASS_DATETIME">=TO_DATE(' 2013-07-06 17:46:04', 'syyyy-mm-dd hh24:mi:ss') AND  
  39.               "PASS_DATETIME"<=TO_DATE(' 2013-07-06 18:46:06', 'syyyy-mm-dd hh24:mi:ss'))  
  40.    4 - filter("DEV_TYPE"='1' AND "DEV_DELETE_FLAG"='N' AND "DEV_CHK_FLAG"='1')  
  41.    5 - access("DEV_BAY_UNID"='01685EFE4658C19D59C4DDAAEDD37393')  
  42.    7 - access("V"."PASS_DEVICE_UNID"="UNID")  
  43.   
  44.   
  45. Statistics  
  46. ----------------------------------------------------------  
  47.           1  recursive calls  
  48.           0  db block gets  
  49.       18645  consistent gets  
  50.         130  physical reads  
  51.           0  redo size  
  52.       47861  bytes sent via SQL*Net to client  
  53.        1657  bytes received via SQL*Net from client  
  54.         105  SQL*Net roundtrips to/from client  
  55.           1  sorts (memory)  
  56.           0  sorts (disk)  
  57.        1560  rows processed  


驱动表改了之后，逻辑读从11W降低为1.8W 


rudy_gao
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
浅谈MySQL中的子查询优化技巧
09-10
在某些情况下，子查询可能被延迟执行，或者被转换为临时表或连接操作，这可能会导致性能下降。例如，上述例子中的SQL语句： ```sql SELECT tradedto0_.* FROM a1 tradedto0_ WHERE tradedto0_.tradestatus='1' ...
oracle查询优化
05-07
优化的关键在于确定驱动表（driving table），通常选择数据量较小或索引覆盖度较高的表作为驱动表。当涉及到多表查询时，例如三个表之间的关联查询，正确识别中间表（intersection table）至关重要，这有助于减少...
exist 和 in 的区别，以及小表驱动大表的原理
piano_diano的博客
03-18 2434
【exist 和 in 的区别】 原作者： https://www.cnblogs.com/liyasong 表展示 首先，查询中涉及到的两个表，一个user和一个order表，具体表的内容如下： in 的使用 确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。 具体sql语句如下： SELECT * FROM `user` WHERE `user`
exists查询慢_优化体系--mysql如何选择驱动表及驱动表查询优化总结
weixin_39528366的博客
11-25 313
概述有的时候我们在操作数据库时会将两个或多个数据表关联起来通过一些条件筛选数据，在关联表时我们要遵循一些原则，这样会使我们编写的SQL 语句在效率上快很多。一、需要优化的查询使用explain常见的扫描类型有：system>const>eq_ref>ref>range>index>ALL 其扫描速度由快到慢；如果Extra出现了Using temporary；有...
oracle子查询作为表,如何让子查询作为驱动表
weixin_39985365的博客
04-03 304
之前的sql和执行计划SQL> explain plan for SELECT MAX(EID) FUTU_QT_FUTUBPVL_EID,2 B.MEMSNAME,3 SUBSTR(B.FUTURECODE, 1, 2) FUTURECODE,4 B.TRADEDATE,5 SUM(B.VLOUME) VLOUME,6 ...
sql 中in、exists在驱动表选择上的执行效率测试分析
mysql-龙召的专栏
01-22 1465
都说exits快，怎么快？哪快？为啥快？ 1 环境准备 环境为oracle 12c db 1.只查询需要的列，不用* 2.IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况  create table a as select * from all_objects; insert into a select * from a; / / / commit;
Oracle 语句优化分析说明第1/2页
09-11
例如，使用IN或EXISTS代替嵌套的子查询，或者通过JOIN操作来整合查询。 9. **使用表别名**：在多表连接中，使用别名可以简化查询，减少解析时间，并防止列名冲突导致的语法错误。 10. **索引优化**：创建和使用...
oracle数据库性能调优（3）
06-10
在本系列文章中，作者杨万富展示了如何使用Oracle 10g版本来分析嵌套循环执行计划，并在后续文章中会探讨散列连接和归并连接，以及IN子查询和EXISTS子查询的分析，这些都对深入理解Oracle数据库的性能调优具有重要...
oracle半连接（in ,exists）
数据库
04-25 323
半连接和反连接，其实就是in，exists，之前我对这几个玩意其实也很迷茫，看网上都有的说exists比in好，等等之类的，其实并不然，等了解了他的一些内部机制，就不会再这么盲目了。 那么我先看几个相关参数： _always_semi_join=CHOOSE 这个说明是按最小成本选择半连接 这个参数可以通过select NAME_KSPVLD_VALUES name, VALUE_KSPV...
oracle驱动表以及如何确定驱动表
热门推荐
星琳之梦的博客
03-20 1万+
驱动表普遍认为是由SQL语句的写法决定的，简单的说，就是FROM语句后面的表列表中的最后一个。由于SQL语句是从后向前进行分析，Oracle会根据FROM语句从后到前将各个表依次连接起来。 SQL> CREATE TABLE T1 AS SELECT * FROM USER_TABLES; 表已创建。 SQL> CREATE TABLE T2 AS SELECT * FROM U
SQL优化经典案例----让in/exists子查询作为驱动表
mengxiangfeiyang的专栏
05-20 1346
SQL优化经典案例----让in/exists子查询作为驱动表   ---http://blog.sina.com.cn/s/blog_61cd89f60102efam.html     在NL连接方式中，用小表或小的结果集作业驱动表，是提高SQL执行效率的重要因素之一，下面我们看一下让in/exists子查询作为驱动表优化的案例 select  rowid rid   
mysql表连接驱动表_了解MySQL中的驱动表
weixin_34701183的博客
01-18 267
一、为什么要用小表驱动大表1、驱动表的定义当进行多表连接查询时， [驱动表] 的定义为：1)指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]2)未指定联接条件时，行数少的表为[驱动表](重要)忠告：如果你搞不清楚该让谁做驱动表、谁 join 谁，请让 MySQL 运行时自行判断既然“未指定联接条件时，行数少的表为[驱动表]”了，而且你也对自己写出的复杂的 Nested Loop Join...
Mysql性能优化 - 使用半连接优化子查询，派生表和视图引用
简简单单Onlinezuozuo
09-17 5748
Mysql性能优化 - 使用半连接优化子查询，派生表和视图引用1.半连接策略两个表之间进行inner join 从另外一个表返回指定条件的一些匹配数据，他可能是1对1的，也可能是1对多的。 这样会导致一些不必要的麻烦，但是，最主要的是我们能够通过join获取到匹配项，而不是到底匹配了多少数据。// 例如 有两个表： class和roster（就读于每个班级的学生）课程中的课程，列表类和类名册，类别。
inner join 和 exists 效率_掌握 MySQL 这 19 个骚操作，效率至少提高3倍
weixin_39686192的博客
12-01 719
1、EXPLAIN做MySQL优化，我们要善用EXPLAIN查看SQL执行计划。下面来个简单的示例，标注(1、2、3、4、5)我们要重点关注的数据：type列，连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。key列，使用到的索引名。如果没有选择索引，值是NULL。可以采取强制索引方式。key_len列，索引长度。rows列，扫描行数。该值是个预估值。extra列，详细说...
Hash Join 一定是选择小表作为驱动表吗
落落的专栏 专注SQL调优 性能调优
03-18 6822
今天下午，群里面有人讨论HASH JOIN，选择驱动表的问题，我回答是 选择返回结果集小的表作为驱动表，而有些同志不同意，他们认为Oracle一定会选择小表作为驱动表，为了弄明白我以前是否理解错误，现在实验一把:SQL> create table t1(id number,name varchar2(100));表已创建。SQL> create table t2(id number,job v
SQL执行优化：避开索引陷阱与Exists/In效率解析
这是因为In会先执行子查询，而Exists则是由父查询驱动子查询。 在Oracle SQL中，优化器模式是决定查询执行策略的关键因素，它分析SQL语句并选择最合适的执行路径。访问表的方式，包括全表扫描（不使用索引）和基于...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值