where条件顺序不同、性能不同示例探讨
前端之家收集整理的这篇文章主要介绍了
where条件顺序不同、性能不同示例探讨，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
昨天在书上看到sql语句优化时，where条件顺序不同，性能不同，这个建议在Oracle11G版本还合适吗？方式1优于方式2？
方式1：
<div class="codetitle"><a style="CURSOR: pointer" data="14168" class="copybut" id="copybut14168" onclick="doCopy('code14168')"> 代码如下:
<div class="codebody" id="code14168">
select a.
from students s,
class c
where
s.id = c.id
s.id = 'xxxxxxxx'
方式2：
<div class="codetitle">
<a style="CURSOR: pointer" data="5882" class="copybut" id="copybut5882" onclick="doCopy('code5882')"> 代码如下:
<div class="codebody" id="code5882">
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
sqlPlus: 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
sqlNet to client
385 bytes received via sqlNet from client
2
sqlNet 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
sqlNet to client
385 bytes received via sqlNet from client
2
sql*Net roundtrips to/from client
13 sorts (memory)
0 sorts (disk)
6 rows processed
sql>
原文链接：https://www.f2er.com/mssql/63494.html
