“蘇瑞”通过精心收集，向本站投稿了3篇where条件顺序不同、性能不同示例探讨，下面是小编整理后的where条件顺序不同、性能不同示例探讨，希望对大家有所帮助。
篇1：where条件顺序不同、性能不同示例探讨
-10-10SQL Server Table中XML列的操作代码
2011-07-07sqlserver下将数据库记录的列记录转换成行记录的方法
-07-07SQL Server 查询处理中的各个阶段(SQL执行顺序)示例
-10-10返回SQL执行时间的存储过程
-03-03用sql语句实现分离和附加数据库的方法
-06-06SQL Server数据类型char、nchar、varchar、nvarchar的区别浅析
-09-09CPQuery 解决拼接SQL的新方法
2013-03-03SQL 级联删除与级联更新的方法
2012-11-11SQL SERVER如何判断某个字段包含大写字母
-05-05一道sql面试题附答案
篇2：where条件顺序不同、性能不同示例探讨
最近更 新
SQL中JOIN和UNION区别、用法及示例介绍
SQL Server 不删除信息重新恢复自动编号列
SQLServer 通用的分区增加和删除的算法
SQL Server的复制功能
SQL创建的几种存储过程
sqlserver中通过osql/ocmd批处理批量执行
sqlserver 数据库压缩与数据库日志(ldf)压
SQL重复记录查询的几种方法
sql中参数过多利用变量替换参数的方法
sqlserver数据库主键的生成方式小结(sqls
热 点 排 行
SQL Server 2008图文安装教程
SQL Server 2012 安装图解教程(附
sqlserver中distinct的用法(不重
SQL Server导入、导出、备份数据
SQL语句去掉重复记录，获取重复记
SQL Server数据库入门学习总结
SQL Server错误代码大全及解释（
sql convert函数使用小结
sql 时间函数 整理的比较全了
用SQL语句添加删除修改字段、一些
篇3：where条件顺序不同，性能是否也不同的问题
where条件顺序不同，性能是否也不同的问题
昨天在书上看到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>
