数据库|mysql教程
查询,exists,not,一点,补充,查询,一点,补充,之
数据库-mysql教程
商业源码中心,pxe自动安装ubuntu,2个tomcat改端口号,分析网页api爬虫,学习php语言有前途吗教程,seo源码下载lzw
子查询的一点补充，之前小鱼写过一篇关于in和exists性能的分析 /archives/ 其实这个都是子查询，而在最新的oracle 11g中，in和exists基本不太可能产生变化，因为11g的cbo不仅可以unnest展开子查询为表连接，还新增了null-aware
在线教学视频源码,ubuntu 开机死机,网址tomcat端口号,病毒 爬虫 电脑,php传数据到html,荔湾区seo外包哪家有名lzw
易语言劲舞团辅助源码,发布vscode插件,ubuntu20安装手动分区,运行tomcat容器,sqlite小数列定义,jquery做曲线图插件,前端框架用啥好,爬虫的主要任务,php 300,潍坊seo技术,网站漂浮图片,手机网页悬浮,bo-blog 模板,php小偷程序能承受多大访问量lzw
其实这个都是子查询，而在最新的oracle 11g中，in和exists基本不太可能产生变化，因为11g的cbo不仅可以unnest展开子查询为表连接，还新增了null-aware anti join的算法，由于in对null敏感。 而在oracle 11g之前，如果关联列上面没有not null的约束，那么此时not in的写法就无法对子查询进行展开，一般我们会看见形如下面的filter执行计划： C:\Users\Administrator>sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Tue May 13 10:14:42 Copyright (c) 1982, , Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> set autotrace traceonly; SQL> set linesize 140; SQL> select * from table02 where object_id not in (select object_id from table01); Execution Plan ---------------------------------------------------------- Plan hash value: 206984988 ------------------------------------------------------------------------------ | Id | Operation| Name | Rows | Bytes | Cost (%CPU)| Time| ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 52376 | 9053K| 3430 (1)| 00:00:42 | |* 1 | FILTER | | | | || | 2 | TABLE ACCESS FULL| TABLE02 | 52408 | 9058K| 154 (2)| 00:00:02 | |* 3 | TABLE ACCESS FULL| TABLE01 | 50979 | 647K|2 (0)| 00:00:01 | ------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TABLE01" "TABLE01" WHERE LNNVL("OBJECT_ID" :B1))) 3 - filter(LNNVL("OBJECT_ID" :B1)) Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 14 recursive calls 0 db block gets 17188464 consistent gets 0 physical reads 0 redo size 1403 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 而这个执行成本往往非常高，而如果我们添加一个not null的约束，或者改写下sql或者添加not null约束来取消这个特别消耗成本的filter 1）改写成minus写法： SQL> select * from table02 a minus 2 select * from table02 where object_id in (select object_id from table01); Execution Plan ---------------------------------------------------------- Plan hash value: 1546480765 -------------------------------------------------------------------------------- -------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Ti me| -------------------------------------------------------------------------------- -------- | 0 | SELECT STATEMENT| | 52408 | 18M| | 4674 (54)| 00 :00:57 | | 1 | MINUS| | | | | | | | 2 | SORT UNIQUE | | 52408 | 9058K| 21M| 2189 (1)| 00 :00:27 | | 3 | TABLE ACCESS FULL | TABLE02 | 52408 | 9058K| | 154 (2)| 00 :00:02 | | 4 | SORT UNIQUE | | 52409 | 9724K| 19M| 2484 (1)| 00 :00:30 | |* 5 | HASH JOIN | | 52409 | 9724K| | 308 (2)| 00 :00:04 | | 6 |TABLE ACCESS FULL| TABLE01 | 53662 | 681K| | 153 (1)| 00 :00:02 | | 7 |TABLE ACCESS FULL| TABLE02 | 52408 | 9058K| | 154 (2)| 00 :00:02 | -------------------------------------------------------------------------------- -------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("OBJECT_ID"="OBJECT_ID") Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 13 recursive calls 0 db block gets 2296 consistent gets 0 physical reads 0 redo size 1403 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 1 rows processed 这里逻辑读降了好多，虽然cost感觉好像比上述的filter执行成本还要大，但是sql的相应时间确明显比filter好太多了。 2 给子表和主表增加not null的约束: SQL> alter table table01 modify object_id not null; Table altered. SQL> alter table table02 modify object_id not null; Table altered. SQL> select * from table02 where object_id not in (select object_id from table01); Execution Plan ---------------------------------------------------------- Plan hash value: 35610947 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time| -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT| |1 | 190 | 308 (2)| 00:00:04 | |* 1 | HASH JOIN RIGHT ANTI| |1 | 190 | 308 (2)| 00:00:04 | | 2 | TABLE ACCESS FULL | TABLE01 | 53662 | 681K| 153 (1)| 00:00:02 | | 3 | TABLE ACCESS FULL | TABLE02 | 52408 | 9058K| 154 (2)| 00:00:02 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBJECT_ID"="OBJECT_ID") Note ----- - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 265 recursive calls 0 db block gets 1557 consistent gets 0 physical reads 0 redo size 1403 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1 rows processed 注意这里需要对子表和主表都添加not null约束，不然在10g的cbo下，oracle还是会选择性能较差的filter。 我们看看各个版本优化器对于in和exists处理的变化（Table01和table02的object_id上都有not null约束） SQL> select /*+ optimizer_features_enable(8.1.7)*/* from table02 b where exists (select 1 from table01 a where a.object_id=b.object_id); 50075 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 206984988 -------------------------------------------------------------- | Id | Operation| Name | Rows | Bytes | Cost | -------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2806 | 485K| 67 | |* 1 | FILTER | | | | | | 2 | TABLE ACCESS FULL| TABLE02 | 2806 | 485K| 67 | |* 3 | TABLE ACCESS FULL| TABLE01 | 561 | 7293 | 67 | -------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( EXISTS (SELECT 0 FROM "TABLE01" "A" WHERE "A"."OBJECT_ID"=:B1)) 3 - filter("A"."OBJECT_ID"=:B1) Note ----- - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 17191469 consistent gets 0 physical reads 0 redo size 2569714 bytes sent via SQL*Net to client 37210 bytes received via SQL*Net from client 3340 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50075 rows processed SQL> select /*+ optimizer_features_enable(8.1.7)*/* from table02 b where not exists (select 1 from table01 a where a.object_id=b.object_id); Execution Plan ---------------------------------------------------------- Plan hash value: 206984988 -------------------------------------------------------------- | Id | Operation| Name | Rows | Bytes | Cost | -------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2806 | 485K| 67 | |* 1 | FILTER | | | | | | 2 | TABLE ACCESS FULL| TABLE02 | 2806 | 485K| 67 | |* 3 | TABLE ACCESS FULL| TABLE01 | 561 | 7293 | 67 | -------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT 0 FROM "TABLE01" "A" WHERE "A"."OBJECT_ID"=:B1)) 3 - filter("A"."OBJECT_ID"=:B1) Note ----- - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 17191469 consistent gets 0 physical reads 0 redo size 2569714 bytes sent via SQL*Net to client 37210 bytes received via SQL*Net from client 3340 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select /*+ optimizer_features_enable(8.1.7)*/* from table02 b where objec t_id in (select object_id from table01 a); 50075 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2067593584 ------------------------------------------------------------------------- | Id | Operation | Name| Rows | Bytes |TempSpc| Cost | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT|| 31M| 5705M| | 469 | |* 1 | HASH JOIN || 31M| 5705M| | 469 | | 2 | VIEW| VW_NSO_1 | 56115 | 712K| | 251 | | 3 | SORT UNIQUE || 56115 | 712K| 2216K| 251 | | 4 |TABLE ACCESS FULL| TABLE01 | 56115 | 712K| | 67 | | 5 | TABLE ACCESS FULL | TABLE02 | 56115 | 9699K| | 67 | ------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBJECT_ID"="$nso_col_1") Note ----- - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4684 consistent gets 0 physical reads 0 redo size 2569714 bytes sent via SQL*Net to client 37210 bytes received via SQL*Net from client 3340 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 50075 rows processed SQL> select /*+ optimizer_features_enable(8.1.7)*/* from table02 b where objec t_id not in (select object_id from table01 a); Execution Plan ---------------------------------------------------------- Plan hash value: 206984988 -------------------------------------------------------------- | Id | Operation| Name | Rows | Bytes | Cost | -------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2806 | 485K| 67 | |* 1 | FILTER | | | | | | 2 | TABLE ACCESS FULL| TABLE02 | 2806 | 485K| 67 | |* 3 | TABLE ACCESS FULL| TABLE01 | 561 | 7293 | 67 | -------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter( NOT EXISTS (SELECT 0 FROM "TABLE01" "A" WHERE "OBJECT_ID"=:B1)) 3 - filter("OBJECT_ID"=:B1) Note ----- - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4684 consistent gets 0 physical reads 0 redo size 2569714 bytes sent via SQL*Net to client 37210 bytes received via SQL*Net from client 3340 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 这里看出在8i的优化器模式下，in的子查询被展开为表连接了，其余的not in、exists、not exists的子查询并不被选择展开为表连接，而是采用一种filter的关联方式，虽然这里的执行成本初看来filter的cost更小，但是sq的相应时间消耗资源的比例确实天壤之别，很多情况我们并不能以cost值去衡量这个sql性能。 SQL> select /*+ optimizer_features_enable(9.2.0)*/* from table02 b where exis ts (select 1 from table01 a where a.object_id=b.object_id); 50075 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 268410134 ----------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | ----------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 50075 | 5183K| | 236 | |* 1 | HASH JOIN SEMI| | 50075 | 5183K| 5136K| 236 | | 2 | TABLE ACCESS FULL | TABLE02 | 50076 | 4547K| | 68 | | 3 | VIEW | VW_SQ_1 | 50075 | 635K| | 68 | | 4 | TABLE ACCESS FULL| TABLE01 | 50075 | 244K| | 68 | ----------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBJECT_ID"="B"."OBJECT_ID") Note ----- - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 4684 consistent gets 0 physical reads 0 redo size 2569714 bytes sent via SQL*Net to client 37210 bytes received via SQL*Net from client 3340 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50075 rows processed SQL> select /*+ optimizer_features_enable(9.2.0)*/* from table02 b where not e xists (select 1 from table01 a where a.object_id=b.object_id); Execution Plan ---------------------------------------------------------- Plan hash value: 2991049530 ---------------------------------------------------------------------- | Id | Operation| Name | Rows | Bytes |TempSpc| Cost | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5629 | 1044K| | 324 | |* 1 | HASH JOIN ANTI | | 5629 | 1044K| 10M| 324 | | 2 | TABLE ACCESS FULL| TABLE02 | 58373 |9M| | 68 | | 3 | TABLE ACCESS FULL| TABLE01 | 52744 | 669K| | 68 | ---------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("A"."OBJECT_ID"="B"."OBJECT_ID") Note ----- - cpu costing is off (consider enabling it) - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4684 consistent gets 0 physical reads 0 redo size 2569714 bytes sent via SQL*Net to client 37210 bytes received via SQL*Net from client 3340 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select /*+ optimizer_features_enable(9.2.0)*/* from table02 b where objec t_id in (select object_id from table01 a)； Execution Plan ---------------------------------------------------------- Plan hash value: 1361234999 ------------------------------------------------------------------------ | Id | Operation | Name| Rows | Bytes |TempSpc| Cost | ------------------------------------------------------------------------ | 0 | SELECT STATEMENT || 50075 | 5183K| | 236 | |* 1 | HASH JOIN SEMI|| 50075 | 5183K| 5136K| 236 | | 2 | TABLE ACCESS FULL | TABLE02 | 50076 | 4547K| | 68 | | 3 | VIEW | VW_NSO_1 | 50075 | 635K| | 68 | | 4 | TABLE ACCESS FULL| TABLE01 | 50075 | 244K| | 68 | ------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBJECT_ID"="$nso_col_1") Note ----- - cpu costing is off (consider enabling it) Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4684 consistent gets 0 physical reads 0 redo size 2569714 bytes sent via SQL*Net to client 37210 bytes received via SQL*Net from client 3340 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 50075 rows processed SQL> select /*+ optimizer_features_enable(9.2.0)*/* from table02 b where objec t_id not in (select object_id from table01 a); Execution Plan ---------------------------------------------------------- Plan hash value: 2991049530 ---------------------------------------------------------------------- | Id | Operation| Name | Rows | Bytes |TempSpc| Cost | ---------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 5629 | 1044K| | 324 | |* 1 | HASH JOIN ANTI | | 5629 | 1044K| 10M| 324 | | 2 | TABLE ACCESS FULL| TABLE02 | 58373 |9M| | 68 | | 3 | TABLE ACCESS FULL| TABLE01 | 52744 | 669K| | 68 | ---------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("OBJECT_ID"="OBJECT_ID") Note ----- - cpu costing is off (consider enabling it) - dynamic sampling used for this statement Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4684 consistent gets 0 physical reads 0 redo size 2569714 bytes sent via SQL*Net to client 37210 bytes received via SQL*Net from client 3340 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed 看出在9i优化器环境下，in和exists都被展开为表连接，此时cbo走的hash join的连接方式。 由于主表和子表的object_id上有not null的约束，所以这里not in和not exists执行计划也都相同，对于关联数据较多的sql，hash join往往比nested loop高效很多。 oracle 10g的优化器对于in、exists、not in和not exists区别并不大，到了11g的优化器，新增了null aware anti join算法，此时并不需要表中有not null约束，也能走hash join的连接方式。 关于in、exists、not in和not exists一直是很多朋友纠结的问题，小鱼这里简单总结下： 在oracle 8I下，in是可以展开为表连接的，而not in、exists、not exists会选择filter执行计划，如果被驱动表没有高效索引，驱动表数据返回较多，这个执行计划往往存在很严重的性能问题 在oracle 9I到oracle 10g下，in和exists没有多大性能的区别，而not in和not exists则可能有所区别，主要看关联列是否有not null约束，如果没有也只能走filter的执行计划，而有则会选择hash join和filter的中优秀的执行方式 在oracle 11g下，由于新增了null-aware anti join的算法，in和exists基本没有区别了，既可以走hash join也可以走filter。 从此in、exists、not in、not exists的经典问题可能并不绝对了，虽然优化器有诸多的缺陷，但是cbo确实在不断的改进自己，这个是值得庆幸的！ 而现在我们来看看返回结果上有什么区别： SQL> select * from t01; ID NAME ---------- ---------- 1 xiaoyu 2 xiaobai 3 SQL> select * from t02; ID NAME ---------- ---------- 10 xiaoyu 20 xiaotian SQL> select * from t01 where t01.name in (select name from t02); ID NAME ---------- ---------- 1 xiaoyu SQL> select * from t01 where exists (select 1 from t02 where t01.name=t02.name); ID NAME ---------- ---------- 1 xiaoyu 来看看not in和not exists： SQL> select * from t01 where t01.name not in (select name from t02); ID NAME ---------- ---------- 2 xiaobai SQL> select * from t01 where not exists (select 1 from t02 where t01.name=t02.na me); ID NAME ---------- ---------- 3 2 xiaobai 看出这里的子查询中in和exists返回结果没有区别，not in的只返回一行数据，而not exists确返回了两行数据，其实我们应该是希望返回两行数据的，那么如果我们再t02表上面添加一个name null的rows来看看 SQL> insert into t02 values(30,null); 1 row created. SQL> commit; Commit complete. SQL> select * from t01 where name in (select name from t02); ID NAME ---------- ---------- 1 xiaoyu SQL> select * from t01 where exists (select 1 from t02 where t01.name=t02.name); ID NAME ---------- ---------- 1 xiaoyu SQL> select * from t01 where name not in (select name from t02); no rows selected SQL> select * from t01 where not exists (select 1 from t02 where t01.name=t02.na me); ID NAME ---------- ---------- 3 2 xiaobai 这里看出in和exists对于null处理没有变化，但是not in和not exists就不同了，not exists对于子表的null会直接略掉，也就是认为满足这个not exists的条件，而not in对于子表的null是敏感的，换句话说只要子表有null值，则not in不返回任何结果集。 关于in和exists补充就到此为止了，话说最近手头正有个子查询不展开的案例，该走hash join的走的是filter，整理完后会与大家分享！子查询的一点补充，之前小鱼写过一篇关于in和exists性能的分析 /archives/
原文地址：子查询in、exists、not in、not exists一点补充, 感谢原作者分享。
