where條件順序不同、性能不同示例探討_Mssql數據庫教程
推薦：如何將sql執行的錯誤消息記錄到本地文件中實現過程sql語句的錯誤信息都可以在sys.messages表里面找到，下面與大家分享下將sql 執行的錯誤消息記錄到本地文件中，不會的朋友可以參考下哈
昨天在書上看到SQL語句優化時，where條件順序不同，性能不同，這個建議在Oracle11G版本還合適嗎？方式1優于方式2？方式1：
復制代碼 代碼如下:m.ghpqjb.com
select a.*
from students s,
class c
where
s.id = c.id
s.id = 'xxxxxxxx'
方式2：
復制代碼 代碼如下:m.ghpqjb.com
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>
分享：sql server 2008中的apply運算符使用方法sql server 2008中的apply運算符使用方法，需要的朋友可以參考一下
相關Mssql數據庫教程：
- sql 語句練習與答案
- 深入C++ string.find()函數的用法總結
- SQL Server中刪除重復數據的幾個方法
- sql刪除重復數據的詳細方法
- SQL SERVER 2000安裝教程圖文詳解
- 使用sql server management studio 2008 無法查看數據庫,提示 無法為該請求檢索數據 錯誤916解決方法
- SQLServer日志清空語句(sql2000,sql2005,sql2008)
- Sql Server 2008完全卸載方法(其他版本類似)
- sql server 2008 不允許保存更改，您所做的更改要求刪除并重新創建以下表
- SQL Server 2008 清空刪除日志文件(瞬間日志變幾M)
- Win7系統安裝MySQL5.5.21圖解教程
- 將DataTable作為存儲過程參數的用法實例詳解
- 相關鏈接：
- 教程說明：
Mssql數據庫教程-where條件順序不同、性能不同示例探討
。