昨天在书上看到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>