【實驗】where子句的解析順序及執行效率
有一種聲音叫做：where子句的解析順序是從右向左（如果上下書寫的話，就是從下到上），因此能夠過濾掉大部分資料的條件要書寫到where子句的後面，同時具有表關聯的條件要放到where子句的前面。
這種說法在Oracle 10gR2的CBO和RBO模式下是什麼樣的呢？
這裡做一個這樣的實驗，我們在RBO和CBO兩種情況下對where子句中關於過濾記錄數有懸殊區別的兩個條件的順序調整，看一下效果。
我們能得到一個結論是：
在Oracle 10.2.0.3環境下測試，無論在RBO還是在CBO最佳化條件下沒有表連線的情況下效率是一樣的。
（由於ORACLE 的自動化最佳化策略，根據不同的資料分析結果，可能執行計劃不相同。具體以您自己的實驗資料為準，本實驗僅供參考。）
實驗參考如下。
1.測試環境是Oracle 10.2.0.3
sec@ora10g> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
2.建立包含3千萬資料的測試表
big_table的id是表的主鍵（索引可用）
@big_table 30000000
3.我們使用的兩個條件
1）符合OWNER = 'SEC'條件的記錄數大概在6萬（返回小資料量的條件）
sec@ora10g> select count(*) from big_table where WNER = 'SEC';
COUNT(*)
----------
54595
2）符合id > 20000000條件的記錄數為1千萬（返回大資料量的條件）
sec@ora10g> select count(*) from big_table where id > 20000000;
COUNT(*)
----------
10000000
4.測試場景，及測試結論
1）CBO最佳化環境下，小資料量返回條件在where子句下方（where子句的最後一個條件）
2）CBO最佳化環境下，小資料量返回條件在where子句上方（where子句的最前一個條件）
3）RBO最佳化環境下，小資料量返回條件在where子句下方（where子句的最後一個條件）
4）RBO最佳化環境下，小資料量返回條件在where子句上方（where子句的最前一個條件）
測試結論：
以上四種場景測試效率一樣。
5.具體的測試場景的展開
1）CBO最佳化環境下，小資料量返回條件在where子句下方（where子句的最後一個條件）
sec@ora10g> select count(*) from big_table
where id >= 20000000
and WNER = 'SEC';
2 3
COUNT(*)
----------
18265
Elapsed: 00:00:04.26
Execution Plan
----------------------------------------------------------
Plan hash value: 599409829
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 89421 (1)| 00:17:54 |
| 1 | SORT AGGREGATE | | 1 | 23 | | |
|* 2 | TABLE ACCESS FULL| BIG_TABLE | 100K| 2255K| 89421 (1)| 00:17:54 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SEC' AND "ID">=20000000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
405329 consistent gets
320902 physical reads
936 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2）CBO最佳化環境下，小資料量返回條件在where子句上方（where子句的最前一個條件）
sec@ora10g> select count(*) from big_table
where WNER = 'SEC'
and id >= 20000000;
2 3
COUNT(*)
----------
18265
Elapsed: 00:00:04.27
Execution Plan
----------------------------------------------------------
Plan hash value: 599409829
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 89421 (1)| 00:17:54 |
| 1 | SORT AGGREGATE | | 1 | 23 | | |
|* 2 | TABLE ACCESS FULL| BIG_TABLE | 100K| 2255K| 89421 (1)| 00:17:54 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SEC' AND "ID">=20000000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
405329 consistent gets
320902 physical reads
980 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3）RBO最佳化環境下，小資料量返回條件在where子句下方（where子句的最後一個條件）
sec@ora10g> select /*+ RULE */ count(*) from big_table
where id >= 20000000
and WNER = 'SEC';
2 3
COUNT(*)
----------
18265
Elapsed: 00:00:03.10
Execution Plan
----------------------------------------------------------
Plan hash value: 3098837282
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE |
|* 3 | INDEX RANGE SCAN | BIG_TABLE_PK |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SEC')
3 - access("ID">=20000000)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
157420 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
4）RBO最佳化環境下，小資料量返回條件在where子句上方（where子句的最前一個條件）
sec@ora10g> select /*+ RULE */ count(*) from big_table
where WNER = 'SEC'
and id >= 20000000;
2 3
COUNT(*)
----------
18265
Elapsed: 00:00:03.09
Execution Plan
----------------------------------------------------------
Plan hash value: 3098837282
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE |
|* 3 | INDEX RANGE SCAN | BIG_TABLE_PK |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SEC')
3 - access("ID">=20000000)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
157420 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
6.關於複雜計算條件和關聯條件的排列組合方式，如果您有興趣，也可以進一步做一下測試。我們的原則是：用真實的實驗講話！
-- The End --
這種說法在Oracle 10gR2的CBO和RBO模式下是什麼樣的呢？
這裡做一個這樣的實驗，我們在RBO和CBO兩種情況下對where子句中關於過濾記錄數有懸殊區別的兩個條件的順序調整，看一下效果。
我們能得到一個結論是：
在Oracle 10.2.0.3環境下測試，無論在RBO還是在CBO最佳化條件下沒有表連線的情況下效率是一樣的。
（由於ORACLE 的自動化最佳化策略，根據不同的資料分析結果，可能執行計劃不相同。具體以您自己的實驗資料為準，本實驗僅供參考。）
實驗參考如下。
1.測試環境是Oracle 10.2.0.3
sec@ora10g> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
2.建立包含3千萬資料的測試表
big_table的id是表的主鍵（索引可用）
@big_table 30000000
3.我們使用的兩個條件
1）符合OWNER = 'SEC'條件的記錄數大概在6萬（返回小資料量的條件）
sec@ora10g> select count(*) from big_table where WNER = 'SEC';
COUNT(*)
----------
54595
2）符合id > 20000000條件的記錄數為1千萬（返回大資料量的條件）
sec@ora10g> select count(*) from big_table where id > 20000000;
COUNT(*)
----------
10000000
4.測試場景，及測試結論
1）CBO最佳化環境下，小資料量返回條件在where子句下方（where子句的最後一個條件）
2）CBO最佳化環境下，小資料量返回條件在where子句上方（where子句的最前一個條件）
3）RBO最佳化環境下，小資料量返回條件在where子句下方（where子句的最後一個條件）
4）RBO最佳化環境下，小資料量返回條件在where子句上方（where子句的最前一個條件）
測試結論：
以上四種場景測試效率一樣。
5.具體的測試場景的展開
1）CBO最佳化環境下，小資料量返回條件在where子句下方（where子句的最後一個條件）
sec@ora10g> select count(*) from big_table
where id >= 20000000
and WNER = 'SEC';
2 3
COUNT(*)
----------
18265
Elapsed: 00:00:04.26
Execution Plan
----------------------------------------------------------
Plan hash value: 599409829
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 89421 (1)| 00:17:54 |
| 1 | SORT AGGREGATE | | 1 | 23 | | |
|* 2 | TABLE ACCESS FULL| BIG_TABLE | 100K| 2255K| 89421 (1)| 00:17:54 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SEC' AND "ID">=20000000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
405329 consistent gets
320902 physical reads
936 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
2）CBO最佳化環境下，小資料量返回條件在where子句上方（where子句的最前一個條件）
sec@ora10g> select count(*) from big_table
where WNER = 'SEC'
and id >= 20000000;
2 3
COUNT(*)
----------
18265
Elapsed: 00:00:04.27
Execution Plan
----------------------------------------------------------
Plan hash value: 599409829
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 89421 (1)| 00:17:54 |
| 1 | SORT AGGREGATE | | 1 | 23 | | |
|* 2 | TABLE ACCESS FULL| BIG_TABLE | 100K| 2255K| 89421 (1)| 00:17:54 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SEC' AND "ID">=20000000)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
405329 consistent gets
320902 physical reads
980 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
3）RBO最佳化環境下，小資料量返回條件在where子句下方（where子句的最後一個條件）
sec@ora10g> select /*+ RULE */ count(*) from big_table
where id >= 20000000
and WNER = 'SEC';
2 3
COUNT(*)
----------
18265
Elapsed: 00:00:03.10
Execution Plan
----------------------------------------------------------
Plan hash value: 3098837282
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE |
|* 3 | INDEX RANGE SCAN | BIG_TABLE_PK |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SEC')
3 - access("ID">=20000000)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
157420 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
4）RBO最佳化環境下，小資料量返回條件在where子句上方（where子句的最前一個條件）
sec@ora10g> select /*+ RULE */ count(*) from big_table
where WNER = 'SEC'
and id >= 20000000;
2 3
COUNT(*)
----------
18265
Elapsed: 00:00:03.09
Execution Plan
----------------------------------------------------------
Plan hash value: 3098837282
-----------------------------------------------------
| Id | Operation | Name |
-----------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE |
|* 3 | INDEX RANGE SCAN | BIG_TABLE_PK |
-----------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SEC')
3 - access("ID">=20000000)
Note
-----
- rule based optimizer used (consider using cbo)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
157420 consistent gets
0 physical reads
0 redo size
517 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
6.關於複雜計算條件和關聯條件的排列組合方式，如果您有興趣，也可以進一步做一下測試。我們的原則是：用真實的實驗講話！
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-615630/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle中where子句中條件的物理執行順序Oracle
- SQL語句各子句的執行順序SQL
- 【Oracle】where條件執行順序(上篇的問題延伸)Oracle
- connect by與where條件執行順序問題
- JUnit 標籤執行順序解析
- SQL 查詢語句的執行順序解析SQL
- JavaScript的執行順序JavaScript
- Sql執行順序SQL
- 圖解React的生命週期及執行順序圖解React
- SQL 優先順序join＞whereSQL
- SQLite中的WHERE子句SQLite
- 利用訊號量實現執行緒順序執行執行緒
- SQL Server中SELECT語句執行順序解析SQLServer
- JavaScript執行順序分析JavaScript
- 任務執行順序
- for語句執行順序
- laravel Event執行順序Laravel
- mySQL 執行語句執行順序MySql
- Jmeter之八大可執行元件及執行順序JMeter元件
- 關於 Promise 的執行順序Promise
- Spring Aop的執行順序Spring
- SQL 語句的執行順序SQL
- CSS規則的執行順序CSS
- Java類的基本執行順序Java
- SQL 執行順序 你懂的SQL
- pipeline的執行順序
- mysql 語句的執行順序MySql
- Oracle SQL語句執行流程與順序原理解析OracleSQL
- thinkphp where in order 按照順序in的迴圈排序PHP排序
- sql mysql 執行順序 （4）MySql
- SQL語句執行順序SQL
- js執行順序Event LoopJSOOP
- JavaScript for迴圈 執行順序JavaScript
- JavaScript 執行順序淺析JavaScript
- 多執行緒實現順序迴圈列印執行緒
- 多優先順序執行緒池實踐執行緒
- 路由的中介軟體執行順序路由
- DISTINCT 和 TOP合用的執行順序