where條件順序不同、性能不同示例探討

更新時間：2013年05月19日 15:57:34 作者：

where條件順序不同，性能不同，這個建議在Oracle11G版本還合適嗎，想提高性能的的朋友可以參考下哈

昨天在書上看到SQL語句優(yōu)化時，where條件順序不同，性能不同，這個建議在Oracle11G版本還合適嗎？方式1優(yōu)于方式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中測試結(jié)果證明是一樣的。

Microsoft Windows [版本 5.2.3790]
(C) 版權(quán)所有 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;
系統(tǒng)已更改。
SQL> alter system flush buffer_cache;
系統(tǒng)已更改。
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 行出現(xiàn)錯誤:
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行。

執(zhí)行計劃
----------------------------------------------------------
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)

統(tǒng)計信息
----------------------------------------------------------
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;
系統(tǒng)已更改。
SQL> alter system flush buffer_cache;
系統(tǒng)已更改。
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行。

執(zhí)行計劃
----------------------------------------------------------
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)

統(tǒng)計信息
----------------------------------------------------------
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>