昨天在书上看到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
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 *
2fromCOUNTRIES c,
3REGIONS r
4where c.REGION_ID=r.REGION_ID and c.REGION_ID='4';
REGIONS r
*
第 3 行出现错误:
ORA-00942: 表或视图不存在
SQL> select *
2 from hr.COUNTRIES c,
3hr. REGIONS r
4 where c.REGION_ID=r.REGION_ID and c.REGION_ID='4';
CO COUNTRY_NAME
REGION_IDREGION_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_IDREGION_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
#############
第二次执行结果、:
SQL> alter system flush shared_pool;
系统已更改。
SQL> alter system flush buffer_cache;
系统已更改。
SQL> select *
2from hr.COUNTRIES c,
3hr. REGIONS r
4where
5c.REGION_ID='4'
6and c.REGION_ID=r.REGION_ID;
CO COUNTRY_NAME
REGION_IDREGION_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_IDREGION_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