CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章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> 。

最后此篇关于where条件顺序不同、性能不同示例探讨的文章就讲到这里了,如果你想了解更多关于where条件顺序不同、性能不同示例探讨的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。