第一次意义上的sql优化，得瑟一下，虽然只是对sql的改写而已，但中间涉及到not in, or 和ort exist ，union， union all 和null值的处理。还是做下笔记。
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。
NOT EXISTS 的作用与 EXISTS 正相反。如果子查询没有返回行，则满足 NOT EXISTS 中的 WHERE 子句
起因是巡检的时候发现cpu使用很高，就检查了cpu的top 10
System: xxxx Mon Apr 14 09:52:25 2014
Load averages: 2.22, 2.56, 2.75
1055 processes: 918 sleeping, 108 running, 29 zombies
Cpu states: (avg)
LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS
2.22 16.1% 5.3% 77.7% 0.9% 0.0% 0.0% 0.0% 0.0%
System Page Size: 4Kbytes
Memory: 31740652K (7913112K) real, 35398200K (9552472K) virtual, 8472480K free Page# 1/82
CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND
28 ? 28996 oracle 178 22 50750M 118M run 44:46 176.38 176.07 oracleods1
24 ? 22336 oracle 178 20 50640M 8496K run 277:45 171.64 171.34 oracleods1
16 ? 8200 oracle 178 20 50637M 5636K run 0:29 125.10 98.55 oracleods1
2 ? 8427 oracle 178 20 50637M 5636K run 0:12 102.05 53.84 oracleods1
4 ? 9004 oracle 178 20 50640M 8900K sleep 9886:22 49.43 49.35 oracleods1
4 ? 9385 oracle 178 20 50645M 9128K sleep 0:47 44.09 44.02 oracleods1
16 ? 8442 oracle 178 20 50637M 5632K sleep 0:08 83.33 41.95 oracleods1
1 ? 8436 oracle 178 20 50637M 5632K sleep 0:08 82.80 41.68 oracleods1
1 ? 8446 oracle 178 20 50637M 5632K sleep 0:09 85.73 40.98 oracleods1
13 ? 8407 oracle 178 20 248M 5180K run 0:12 67.42 40.01 oracleods1
26 ? 8449 oracle 178 20 50637M 5624K sleep 0:08 92.36 39.07 oracleods1
29 ? 8490 oracle 178 20 248M 5180K run 0:07 102.37 33.75 oracleods1
SQL> select sql_id, prev_sql_id, s.username, status,logon_time from v$session s , v$process p where s.paddr = p.addr and spid = 28996;
SQL_ID PREV_SQL_ID USERNAME STATUS LOGON_TIME
------------- ------------- ------------------------------ -------- ------------
gpffra78um5zh 08p8kbs837tdp xxx ACTIVE 14-APR-14
NAME VALUE_STRING
------------------------------------------------------------ --------------------
:B1
:B3 20100621
:B2 20140413
new 1: select * from table(dbms_xplan.display_cursor('gpffra78um5zh'))
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gpffra78um5zh, child number 0
-------------------------------------
INSERT INTO STAT_ZHONGHE_TOTAL_MONTH (MONTH_ID, REGION_ID, REGION_NAME, AGENT_ID,
AGENT_NAME, AGENT_LEVEL_NAME, K_I_NUM, K_D_NUM, K_SCORE, R_I_NUM, R_D_NUM, R_SCORE,
T_I_NUM, T_D_NUM, T_SCORE, Z_I_NUM, Z_D_NUM, Z_SCORE, TOTAL_SCORE, GEN_DATE) SELECT
:B1 , B.REGION_ID, B.REGION_NAME, B.AGENT_ID, B.AGENT_NAME, B.AGENT_LEVEL_NAME, 0
K_I_NUM, 0 K_D_NUM, 0 K_SCORE, 0 R_I_NUM, 0 R_D_NUM, 0 R_SCORE, 0 T_I_NUM, 0 T_D_NUM,
0 T_SCORE, 0 Z_I_NUM, 0 Z_D_NUM, 0 Z_SCORE, 0, SYSDATE FROM DIM_AGENT_CHANNEL_TEMP B
WHERE B.TEAM_ID NOT IN (SELECT A.DEV_DEPT_ID FROM LIST_ZHONGHE_DETAIL_DAY A WHERE
A.MONTH_DAY_ID >= :B3 AND A.MONTH_DAY_ID <= :B2 AND A.DEV_DEPT_ID IS NOT NULL ) OR
B.TEAM_ID IS NULL
Plan hash value: 1761209397
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 14 (100)| |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | DIM_AGENT_CHANNEL_TEMP | 3386 | 591K| 14 (0)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL| LIST_ZHONGHE_DETAIL_DAY | 1272K| 23M| 7587 (2)| 00:01:32 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("B"."TEAM_ID" IS NULL OR IS NULL))
3 - filter(:B3<=:B2)
4 - filter(("A"."DEV_DEPT_ID" IS NOT NULL AND "A"."MONTH_DAY_ID">=:B3 AND
"A"."MONTH_DAY_ID"<=:B2 AND LNNVL("A"."DEV_DEPT_ID"<>TO_NUMBER(:B1))))
Note
-----
- dynamic sampling used for this statement
36 rows selected.
SQL> select gen_date,count(gen_date) from odsapp.STAT_ZHONGHE_TOTAL_MONTH group by gen_date;
GEN_DATE COUNT(GEN_DATE)
------------------- ---------------
2014-04-14 09:04:46 615
2014-04-14 09:04:57 2604
该库的自动统计信息收集给关了。没有统计信息更新。
SQL> select last_analyzed, rownum from dba_tables where table_name in('LIST_ZHONGHE_DETAIL_DAY','DIM_AGENT_CHANNEL_TEMP');
LAST_ANALYZE ROWNUM
------------ ----------
1
2
表的数据量：
SQL> select segment_name,bytes/1024/1024/1024 from dba_segments where segment_name in('LIST_ZHONGHE_DETAIL_DAY','DIM_AGENT_CHANNEL_TEMP');
SEGMENT_NAME BYTES/1024/1024/1024
--------------------------------------------------------------------------------- --------------------
LIST_ZHONGHE_DETAIL_DAY .263671875
DIM_AGENT_CHANNEL_TEMP .000976563
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'xxxx',tabname=>'DIM_AGENT_CHANNEL_TEMP',ESTIMATE_PERCENT=>20,degree=>2);
SQL> select table_name,last_analyzed, num_rows from dba_tables where table_name in('LIST_ZHONGHE_DETAIL_DAY','DIM_AGENT_CHANNEL_TEMP');
TABLE_NAME LAST_ANALYZE NUM_ROWS
------------------------------ ------------ ----------
DIM_AGENT_CHANNEL_TEMP 14-APR-14 3386
LIST_ZHONGHE_DETAIL_DAY 14-APR-14 1316065
因为是全表扫，检查是否有索引：
SQL> select table_name,index_name, last_analyzed, status from dba_indexes where table_name in ('LIST_ZHONGHE_DETAIL_DAY','DIM_AGENT_CHANNEL_TEMP') and owner='xxx';
no rows selected
SQL> select table_name,index_name, last_analyzed, status from dba_indexes where table_name in ('LIST_ZHONGHE_DETAIL_DAY','DIM_AGENT_CHANNEL_TEMP');
no rows selected
事实上LIST_ZHONGHE_DETAIL_DAY 表的MONTH_DAY_ID 的数值范围和sql中指定的差不多，就算有索引，应该也会走全表。
SQL>
SQL> set line 300 pages 3000 long 999999 timing on autotrace traceonly
SQL> SELECT
B.REGION_ID,
B.REGION_NAME,
B.AGENT_ID,
B.AGENT_NAME,
B.AGENT_LEVEL_NAME,
0 K_I_NUM,
0 K_D_NUM,
0 K_SCORE,
0 R_I_NUM,
0 R_D_NUM,
0 R_SCORE,
0 T_I_NUM,
0 T_D_NUM,
0 T_SCORE,
0 Z_I_NUM,
0 Z_D_NUM,
0 Z_SCORE,
0,
SYSDATE
FROM xxx.DIM_AGENT_CHANNEL_TEMP B
WHERE B.TEAM_ID NOT IN (SELECT A.DEV_DEPT_ID
FROM xxx.LIST_ZHONGHE_DETAIL_DAY A
WHERE A.MONTH_DAY_ID >= '20100621'
AND A.MONTH_DAY_ID <= '20140413'
AND A.DEV_DEPT_ID IS NOT NULL)
OR B.TEAM_ID IS NULL;
2604 rows selected.
Elapsed: 01:26:01.23
Execution Plan
----------------------------------------------------------
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 212 | 16536 | 14 (0)|
| 1 | FILTER | | | | |
| 2 | TABLE ACCESS FULL| DIM_AGENT_CHANNEL_TEMP | 3386 | 257K| 14 (0)|
| 3 | TABLE ACCESS FULL| LIST_ZHONGHE_DETAIL_DAY | 2 | 28 | 14 (0)|
-----------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
466 recursive calls
0 db block gets
92299781 consistent gets
0 physical reads
0 redo size
164109 bytes sent via SQL*Net to client
2395 bytes received via SQL*Net from client
175 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
2604 rows processed
SQL>
SELECT B.REGION_ID,
B.REGION_NAME,
B.AGENT_ID,
B.AGENT_NAME,
B.AGENT_LEVEL_NAME,
0 K_I_NUM,
0 K_D_NUM,
0 K_SCORE,
0 R_I_NUM,
0 R_D_NUM,
0 R_SCORE,
0 T_I_NUM,
0 T_D_NUM,
0 T_SCORE,
0 Z_I_NUM,
0 Z_D_NUM,
0 Z_SCORE,
0,
SYSDATE
FROM xxx.DIM_AGENT_CHANNEL_TEMP B
WHERE not exists (SELECT A.DEV_DEPT_ID
FROM xxx.LIST_ZHONGHE_DETAIL_DAY A
WHERE A.dev_dept_id =B.TEAM_ID
and A.MONTH_DAY_ID >= '20100621'
AND A.MONTH_DAY_ID <= '20140413'
AND A.DEV_DEPT_ID IS NOT NULL)
union
SELECT B.REGION_ID,
B.REGION_NAME,
B.AGENT_ID,
B.AGENT_NAME,
B.AGENT_LEVEL_NAME,
0 K_I_NUM,
0 K_D_NUM,
0 K_SCORE,
0 R_I_NUM,
0 R_D_NUM,
0 R_SCORE,
0 T_I_NUM,
0 T_D_NUM,
0 T_SCORE,
0 Z_I_NUM,
0 Z_D_NUM,
0 Z_SCORE,
0,
SYSDATE
FROM xxx.DIM_AGENT_CHANNEL_TEMP B
WHERE B.TEAM_ID IS NULL;
2127 rows selected.
Elapsed: 00:00:05.37
Execution Plan
----------------------------------------------------------
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3430 | 307K| 7636 (2)|
| 1 | SORT UNIQUE | | 3430 | 307K| 7636 (2)|
| 2 | UNION-ALL | | | | |
| 3 | HASH JOIN ANTI | | 3385 | 304K| 7620 (2)|
| 4 | TABLE ACCESS FULL| DIM_AGENT_CHANNEL_TEMP | 3386 | 257K| 14 (0)|
| 5 | TABLE ACCESS FULL| LIST_ZHONGHE_DETAIL_DAY | 1310K| 17M| 7595 (2)|
| 6 | TABLE ACCESS FULL | DIM_AGENT_CHANNEL_TEMP | 45 | 3510 | 14 (0)|
-------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
824 recursive calls
0 db block gets
34637 consistent gets
0 physical reads
0 redo size
131900 bytes sent via SQL*Net to client
2043 bytes received via SQL*Net from client
143 SQL*Net roundtrips to/from client
34 sorts (memory)
0 sorts (disk)
2127 rows processed
改用not exists后速度快了很多，但是结果集不一样了。比以前的少了，想到用的是union，会去重。用union all试试。
单独用not exists ：
SQL>
SELECT B.REGION_ID,
B.REGION_NAME,
B.AGENT_ID,
B.AGENT_NAME,
B.AGENT_LEVEL_NAME,
0 K_I_NUM,
0 K_D_NUM,
0 K_SCORE,
0 R_I_NUM,
0 R_D_NUM,
0 R_SCORE,
0 T_I_NUM,
0 T_D_NUM,
0 T_SCORE,
0 Z_I_NUM,
0 Z_D_NUM,
0 Z_SCORE,
0,
SYSDATE
FROM xxx.DIM_AGENT_CHANNEL_TEMP B
WHERE not exists (SELECT A.DEV_DEPT_ID
FROM xxx.LIST_ZHONGHE_DETAIL_DAY A
WHERE A.dev_dept_id =B.TEAM_ID
and A.MONTH_DAY_ID >= '20100621'
AND A.MONTH_DAY_ID <= '20140413'
AND A.DEV_DEPT_ID IS NOT NULL)
;
2604 rows selected.
Elapsed: 00:00:01.21
Execution Plan
----------------------------------------------------------
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3385 | 304K| 7620 (2)|
| 1 | HASH JOIN ANTI | | 3385 | 304K| 7620 (2)|
| 2 | TABLE ACCESS FULL| DIM_AGENT_CHANNEL_TEMP | 3386 | 257K| 14 (0)|
| 3 | TABLE ACCESS FULL| LIST_ZHONGHE_DETAIL_DAY | 1310K| 17M| 7595 (2)|
-----------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
34395 consistent gets
0 physical reads
0 redo size
216685 bytes sent via SQL*Net to client
2395 bytes received via SQL*Net from client
175 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2604 rows processed
把 WHERE B.TEAM_ID IS NULL;给去掉后，结果集和修改前一直了，但是为啥？
原因就是null值不能做运算。 在计算not exists的子查询不做null的判断。所以进一步修改sql，将 B.TEAM_ID IS not NULL 加到exists 后面。
这个位置的原因，理解的还不够，希望有人能看见帮忙指点一下。
SQL>
SELECT B.REGION_ID,
B.REGION_NAME,
B.AGENT_ID,
B.AGENT_NAME,
B.AGENT_LEVEL_NAME,
0 K_I_NUM,
0 K_D_NUM,
0 K_SCORE,
0 R_I_NUM,
0 R_D_NUM,
0 R_SCORE,
0 T_I_NUM,
0 T_D_NUM,
0 T_SCORE,
0 Z_I_NUM,
0 Z_D_NUM,
0 Z_SCORE,
0,
SYSDATE
FROM xxx.DIM_AGENT_CHANNEL_TEMP B
WHERE not exists (SELECT A.DEV_DEPT_ID
FROM xxx.LIST_ZHONGHE_DETAIL_DAY A
WHERE A.dev_dept_id =B.TEAM_ID
and A.MONTH_DAY_ID >= '20100621'
AND A.MONTH_DAY_ID <= '20140413'
)
and b.team_id is not null
union all
SELECT B.REGION_ID,
B.REGION_NAME,
B.AGENT_ID,
B.AGENT_NAME,
B.AGENT_LEVEL_NAME,
0 K_I_NUM,
0 K_D_NUM,
0 K_SCORE,
0 R_I_NUM,
0 R_D_NUM,
0 R_SCORE,
0 T_I_NUM,
0 T_D_NUM,
0 T_SCORE,
0 Z_I_NUM,
0 Z_D_NUM,
0 Z_SCORE,
0,
SYSDATE
FROM xxx.DIM_AGENT_CHANNEL_TEMP B
WHERE B.TEAM_ID IS NULL;
2604 rows selected.
Elapsed: 00:00:01.21
Execution Plan
----------------------------------------------------------
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3385 | 303K| 7632 (2)|
| 1 | UNION-ALL | | | | |
| 2 | HASH JOIN ANTI | | 3340 | 300K| 7618 (2)|
| 3 | TABLE ACCESS FULL| DIM_AGENT_CHANNEL_TEMP | 3341 | 254K| 14 (0)|
| 4 | TABLE ACCESS FULL| LIST_ZHONGHE_DETAIL_DAY | 1310K| 17M| 7593 (2)|
| 5 | TABLE ACCESS FULL | DIM_AGENT_CHANNEL_TEMP | 45 | 3510 | 14 (0)|
------------------------------------------------------------------------------------
Note
-----
- 'PLAN_TABLE' is old version
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
34459 consistent gets
0 physical reads
0 redo size
216685 bytes sent via SQL*Net to client
2395 bytes received via SQL*Net from client
175 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2604 rows processed
SQL>
这下结果集一样了
相关推荐
关于Exists,not Exists.in ,not in效率的说明。关于Exists,not Exists.in ,not in效率的说明。关于Exists,not Exists.in ,not in效率的说明。关于Exists,not Exists.in ,not in效率的说明。关于Exists,not Exists.in...
exists 和 not exists的详细解释
里面自己根据网上的资源整理出来的一份sql中in，exists，not in，not exists的使用方法以及注意事项等，有助于初学的朋友们借鉴。
基本数据插入 except和intersect和exists和not exists和union和union all sql server
SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句.txt欢迎下载！
NULL 博文链接：https://576017120.iteye.com/blog/1624774
总结：exists 和not exists语句强调是否返回结果集，不要求知道返回什么，与in的区别就是，in只能返回一个字段值，exists允许返回多个字段 提醒：文章中提供了exists和not exists的常用示例，已经经过本人测试，...
MySQL优化之in,exists,not in,not exists的区别in与existsin查询过程结论：exists查询过程：结论：not in与not existsnot in查询过程：结论：not exists查询过程：结论： 首先我们使用两个用户表作为实例 insert ...
sql语句优化之用EXISTS替代IN、用NOT EXISTS替代NOT IN的语句
NOT EXISTS ⇔ NOT IN ⇒ NOT NULL 问题 结果不一样 疑问 如果等效
NOT IN、JOIN、IS NULL、NOT EXISTS效率对比 语句一：select count(*) from A where A.a not in (select a from B) 语句二：select count(*) from A left join B on A.a = B.a where B.a is null 语句三：select ...
使用EXISTS（或NOT EXISTS）关键字引入一个子查询时，子查询实际上不产生任何数据；它只返回TRUE或FALS值。 指定一个子查询，检测行的存在。 如果子查询包含行，则返回 TRUE IN效率要差(返回子查询)
“exists”和“in”是Oracle中，都是查询某集合的值是否存在在另一个集合，但对不同的数据有不同的用法，主要是在效率问题上存在很大的差别，以下有两个简单例子，以说明 “exists”和“in”的效率问题。
一个是问in exist的区别，一个是not in和not exists的区别
SQL语句优化——in,not in,exists,not exists, left join...on博客所需SQL语句2.txt，欢迎下载！
非上传者作品。本人只是学习的时候百度到的比较的文档，拿出来和大家分享一下，希望对大家有所帮助！
Vitamio缺少的libffmpeg.so文件 解决 Native libs libffmpeg.so not exists!报错
not exists的子查询，对于子查询不返回行和子查询返回行的查询结果是有区别的 这些细小的差别千万不要被我们所忽视，一旦项目庞大了，想跟踪到具体的错误所花费的时间也是可观的。尽量把这些不必要的错误扼杀在摇篮...