准备测试数据:
SQL> create table tmp01 as select object_id,object_name from dba_objects;
表已创建。
已用时间: 00: 00: 03.35
SQL> create table tmp02 as select object_id,object_name from dba_objects where mod(object_id,2)=0;
表已创建。
已用时间: 00: 00: 00.21
SQL> insert into tmp01 select * from tmp01;
已创建72522行。
已用时间: 00: 00: 00.39
SQL> insert into tmp01 select * from tmp01;
已创建145044行。
已用时间: 00: 00: 00.20
SQL> insert into tmp01 select * from tmp01;
已创建290088行。
已用时间: 00: 00: 00.29
SQL> insert into tmp01 select * from tmp01;
已创建580176行。
已用时间: 00: 00: 00.76
SQL> commit;
提交完成。
已用时间: 00: 00: 00.01
SQL> insert into tmp02 select * from tmp02;
已创建36151行。
已用时间: 00: 00: 00.17
SQL> insert into tmp02 select * from tmp02;
已创建72302行。
已用时间: 00: 00: 00.06
SQL> insert into tmp02 select * from tmp02;
已创建144604行。
已用时间: 00: 00: 00.14
SQL> commit;
提交完成。
已用时间: 00: 00: 00.01
SQL> create index idx_tmp01_id on tmp01(object_id);
索引已创建。
已用时间: 00: 00: 00.87
SQL> create index idx_tmp02_id on tmp02(object_id);
索引已创建。
已用时间: 00: 00: 00.20
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',tabname => 'TMP01',cascade => true);
PL/SQL 过程已成功完成。
已用时间: 00: 00: 04.89
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCOTT',tabname => 'TMP02',cascade => true);
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.53
SQL>
我们要查询object_id在tmp01里面而不在tmp02里面的记录,怎么写?
开发一般会这样写,因为这样很容易理解,也很容易想到:
SQL> set autot trace exp stat
SQL> select count(*)
2 from tmp01
3 where tmp01.object_id not in (select tmp02.object_id from tmp02);
已用时间: 00: 00: 00.34
执行计划
----------------------------------------------------------
Plan hash value: 1947072100
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | | 3076 (1)| 00:00:37 |
| 1 | SORT AGGREGATE | | 1 | 10 | | | |
|* 2 | HASH JOIN RIGHT ANTI NA| | 586K| 5723K| 4808K| 3076 (1)| 00:00:37 |
| 3 | TABLE ACCESS FULL | TMP02 | 289K| 1412K| | 381 (1)| 00:00:05 |
| 4 | TABLE ACCESS FULL | TMP01 | 1160K| 5665K| | 1516 (1)| 00:00:19 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TMP01"."OBJECT_ID"="TMP02"."OBJECT_ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6927 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
其实可以使用左外连接或者not exists:
SQL> select count(*)
2 from tmp01 left join tmp02
3 on tmp01.object_id = tmp02.object_id
4 where tmp02.object_id is null;
已用时间: 00: 00: 00.40
执行计划
----------------------------------------------------------
Plan hash value: 2699572186
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | | 2872 (1)| 00:00:35 |
| 1 | SORT AGGREGATE | | 1 | 10 | | | |
|* 2 | HASH JOIN RIGHT ANTI | | 586K| 5723K| 4808K| 2872 (1)| 00:00:35 |
| 3 | INDEX FAST FULL SCAN| IDX_TMP02_ID | 289K| 1412K| | 177 (2)| 00:00:03 |
| 4 | TABLE ACCESS FULL | TMP01 | 1160K| 5665K| | 1516 (1)| 00:00:19 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TMP01"."OBJECT_ID"="TMP02"."OBJECT_ID")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
6195 consistent gets
0 physical reads
0 redo size
425 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
巧妙地走索引,并且一致读取也略低。
SQL> create table t_notin1(name varchar2(10));
表已创建。
SQL> create table t_notin2(name varchar2(10));
表已创建。
SQL> insert into t_notin1 values('t1');
已创建 1 行。
SQL> insert into t_notin1 values('t2');
已创建 1 行。
SQL> insert into t_notin2 values('');
已创建 1 行。
SQL> insert into t_notin2 values('');
已创建 1 行。
SQL> insert into t_notin2 values('t1');
已创建 1 行。
SQL> commit;
提交完成。
SQL>
SQL> select * from t_notin1;
NAME
----------
t1
t2
SQL> select nvl(name,'NULL') from t_notin2;
NVL(NAME,'
----------
NULL
NULL
t1
SQL>
找出在t_notin1中而不在t_notin2中的记录,注意t_notin2中有null记录。怎么写?not in?
SQL> select * from t_notin1 where name not in (select name from t_notin2);
未选定行
SQL>
无记录返回,为什么?not in必须要求where的子查询不返回null值。此时必须改用外连接:
SQL> select t_notin1.name from t_notin1 left join t_notin2 on t_notin1.name=t_notin2.name where t_notin2.name is null;
NAME
----------
t2
或者使用not exists方法:
SQL> select * from t_notin1 where not exists (select 1 from t_notin2 where t_notin1.name=t_notin2.name);
NAME
----------
t2
SQL>