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

已选择6行。

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>

微信