服务器之家

服务器之家 > 正文

where条件顺序不同、性能不同示例探讨

时间:2020-01-07 15:49     来源/作者:MSSQL教程网

昨天在书上看到SQL语句优化时,where条件顺序不同,性能不同,这个建议在Oracle11G版本还合适吗?方式1优于方式2? 
方式1: 

复制代码代码如下:


select a.* 
from students s, 
class c 
where 
s.id = c.id 
s.id = 'xxxxxxxx' 


方式2: 

复制代码代码如下:


select a.* 
from students s, 
class c 
where 
s.id = 'xxxxxxxx' 
s.id = c.id 


10g中测试结果证明是一样的。 

Microsoft Windows [版本 5.2.3790] 
(C) 版权所有 1985-2003 Microsoft Corp. 
C:\Documents and Settings\Administrator>sqlplus / as sysdba 
SQL*Plus: Release 10.2.0.1.0 - Production on 星期六 5月 11 17:48:55 2013 
Copyright (c) 1982, 2005, Oracle. All rights reserved. 

连接到: 
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production 
With the Partitioning, OLAP and Data Mining options 
SQL> alter system flush shared_pool; 
系统已更改。 
SQL> alter system flush buffer_cache; 
系统已更改。 
SQL> set autotrace on; 
SQL> select * 
2 from COUNTRIES c, 
3 REGIONS r 
4 where c.REGION_ID=r.REGION_ID and c.REGION_ID='4'; 
REGIONS r 

第 3 行出现错误: 
ORA-00942: 表或视图不存在 

SQL> select * 
2 from hr.COUNTRIES c, 
3 hr. REGIONS r 
4 where c.REGION_ID=r.REGION_ID and c.REGION_ID='4'; 
CO COUNTRY_NAME REGION_ID REGION_ID 
-- ---------------------------------------- ---------- ---------- 
REGION_NAME 
------------------------- 
EG Egypt 4 4 
Middle East and Africa 
IL Israel 4 4 
Middle East and Africa 
KW Kuwait 4 4 
Middle East and Africa 

CO COUNTRY_NAME REGION_ID REGION_ID 
-- ---------------------------------------- ---------- ---------- 
REGION_NAME 
------------------------- 
NG Nigeria 4 4 
Middle East and Africa 
ZM Zambia 4 4 
Middle East and Africa 
ZW Zimbabwe 4 4 
Middle East and Africa 

已选择6行。 

执行计划 
---------------------------------------------------------- 
Plan hash value: 4030513296 
-------------------------------------------------------------------------------- 
---------------- 
| Id | Operation | Name | Rows | Bytes | Cost (% 
CPU)| Time | 
-------------------------------------------------------------------------------- 
---------------- 
| 0 | SELECT STATEMENT | | 6 | 168 | 2 
(0)| 00:00:01 | 
| 1 | NESTED LOOPS | | 6 | 168 | 2 
(0)| 00:00:01 | 
| 2 | TABLE ACCESS BY INDEX ROWID| REGIONS | 1 | 14 | 1 
(0)| 00:00:01 | 
|* 3 | INDEX UNIQUE SCAN | REG_ID_PK | 1 | | 0 
(0)| 00:00:01 | 
|* 4 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 6 | 84 | 1 
(0)| 00:00:01 | 
-------------------------------------------------------------------------------- 
---------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
3 - access("R"."REGION_ID"=4) 
4 - filter("C"."REGION_ID"=4) 

统计信息 
---------------------------------------------------------- 
628 recursive calls 
0 db block gets 
127 consistent gets 
20 physical reads 
0 redo size 
825 bytes sent via SQL*Net to client 
385 bytes received via SQL*Net from client 
2 SQL*Net roundtrips to/from client 
13 sorts (memory) 
0 sorts (disk) 
6 rows processed 
SQL> 

############# 

SQL> alter system flush shared_pool; 
系统已更改。 
SQL> alter system flush buffer_cache; 
系统已更改。 
select * 
from hr.COUNTRIES c, 
hr. REGIONS r 
where 
c.REGION_ID='4' 
6 and c.REGION_ID=r.REGION_ID; 
CO COUNTRY_NAME REGION_ID REGION_ID 
-- ---------------------------------------- ---------- ---------- 
REGION_NAME 
------------------------- 
EG Egypt 4 4 
Middle East and Africa 
IL Israel 4 4 
Middle East and Africa 
KW Kuwait 4 4 
Middle East and Africa 

CO COUNTRY_NAME REGION_ID REGION_ID 
-- ---------------------------------------- ---------- ---------- 
REGION_NAME 
------------------------- 
NG Nigeria 4 4 
Middle East and Africa 
ZM Zambia 4 4 
Middle East and Africa 
ZW Zimbabwe 4 4 
Middle East and Africa 

已选择6行。 

执行计划 
---------------------------------------------------------- 
Plan hash value: 4030513296 
-------------------------------------------------------------------------------- 
---------------- 
| Id | Operation | Name | Rows | Bytes | Cost (% 
CPU)| Time | 
-------------------------------------------------------------------------------- 
---------------- 
| 0 | SELECT STATEMENT | | 6 | 168 | 2 
(0)| 00:00:01 | 
| 1 | NESTED LOOPS | | 6 | 168 | 2 
(0)| 00:00:01 | 
| 2 | TABLE ACCESS BY INDEX ROWID| REGIONS | 1 | 14 | 1 
(0)| 00:00:01 | 
|* 3 | INDEX UNIQUE SCAN | REG_ID_PK | 1 | | 0 
(0)| 00:00:01 | 
|* 4 | INDEX FULL SCAN | COUNTRY_C_ID_PK | 6 | 84 | 1 
(0)| 00:00:01 | 
-------------------------------------------------------------------------------- 
---------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
3 - access("R"."REGION_ID"=4) 
4 - filter("C"."REGION_ID"=4) 

统计信息 
---------------------------------------------------------- 
656 recursive calls 
0 db block gets 
131 consistent gets 
22 physical reads 
0 redo size 
825 bytes sent via SQL*Net to client 
385 bytes received via SQL*Net from client 
2 SQL*Net roundtrips to/from client 
13 sorts (memory) 
0 sorts (disk) 
6 rows processed 
SQL>

标签:

相关文章

热门资讯

玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分
玄元剑仙肉身有什么用 玄元剑仙肉身境界等级划分 2019-06-21
男生常说24816是什么意思?女生说13579是什么意思?
男生常说24816是什么意思?女生说13579是什么意思? 2019-09-17
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情
华为nova5pro和p30pro哪个好 华为nova5pro和华为p30pro对比详情 2019-06-22
配置IIS网站web服务器的安全策略配置解决方案
配置IIS网站web服务器的安全策略配置解决方案 2019-05-23
Nginx服务器究竟是怎么执行PHP项目
Nginx服务器究竟是怎么执行PHP项目 2019-05-24
返回顶部