遮天辰东小说,辰东完美世界有声小说,有声小说打包下载

主頁(yè) > 知識(shí)庫(kù) > where條件順序不同、性能不同示例探討

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

昨天在書上看到SQL語(yǔ)句優(yōu)化時(shí)，where條件順序不同，性能不同，這個(gè)建議在Oracle11G版本還合適嗎？方式1優(yōu)于方式2？
方式1：

復(fù)制代碼代碼如下:

 
select a.* 
from students s, 
class c 
where 
s.id = c.id 
s.id = 'xxxxxxxx' 

方式2：

復(fù)制代碼代碼如下:

 
select a.* 
from students s, 
class c 
where 
s.id = 'xxxxxxxx' 
s.id = c.id 

10g中測(cè)試結(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)錯(cuò)誤:
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í)行計(jì)劃
----------------------------------------------------------
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)計(jì)信息
----------------------------------------------------------
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í)行計(jì)劃
----------------------------------------------------------
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)計(jì)信息
----------------------------------------------------------
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>

標(biāo)簽：貴州上海江蘇重慶拉薩呼和浩特昭通新鄉(xiāng)

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《where條件順序不同、性能不同示例探討》，本文關(guān)鍵詞 where,條件,順序,不同,性能,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。

濮阳杆衣贸易有限公司

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

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