【SQL最佳化】UNION替換OR效率測試及總結
大家在做SQL最佳化的過程中，可能都知道一個事實：某些情況下使用UNION替換OR可以提高SQL的執行效率。
您知道這個“某些情況”指的是什麼麼？
解釋一下，“某些情況”指的是：使用的表欄位要有索引。
這個實驗，給大家展示一下這個結論
1.建立測試表
sec@ora10g> drop table t;
Table dropped.
sec@ora10g> create table t as select * from all_objects;
Table created.
sec@ora10g> alter table t rename column owner to x;
Table altered.
sec@ora10g> alter table t rename column object_name to y;
Table altered.
sec@ora10g> update t set x = 'secooler';
4785 rows updated.
OK，透過上面的初始化，我們得到了這個測試表T，我們關心它的第一個和第二個欄位，修改名字後是x欄位和y欄位
x欄位內容統一修改為“secooler”，以便模擬使用這個欄位得到大量返回結果
y欄位指定特定值後，模擬返回一條記錄
2.開啟autotrace，跟蹤不同的SQL執行（為使執行計劃穩定，請多次執行，得到穩定輸出結果）
用到的測試SQL語句是以下三條
1）返回記錄多的條件放在where子句的前面
select * from t where x = 'secooler' or y = 'T';
2）返回記錄多的條件放在where子句的後面
select * from t where y = 'T' or x = 'secooler';
3）使用UNION改寫上面的OR語句
select * from t where x = 'secooler'
union
select * from t where y = 'T'
/
3.先看一下，在沒有建立索引情況下的實驗效果
sec@ora10g> set autotrace traceonly
sec@ora10g> select * from t where x = 'secooler' or y = 'T';
4782 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4583 | 572K| 16 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 4583 | 572K| 16 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"='secooler' OR "Y"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
111 consistent gets
0 physical reads
0 redo size
206142 bytes sent via SQL*Net to client
1009 bytes received via SQL*Net from client
49 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4782 rows processed
sec@ora10g> select * from t where y = 'T' or x = 'secooler';
4782 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4583 | 572K| 16 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 4583 | 572K| 16 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("Y"='T' OR "X"='secooler')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
111 consistent gets
0 physical reads
0 redo size
206142 bytes sent via SQL*Net to client
1009 bytes received via SQL*Net from client
49 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4782 rows processed
sec@ora10g> select * from t where x = 'secooler'
union
select * from t where y = 'T'
/
2 3 4
4782 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2618920678
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4584 | 573K| | 168 (12)| 00:00:03 |
| 1 | SORT UNIQUE | | 4584 | 573K| 1448K| 168 (12)| 00:00:03 |
| 2 | UNION-ALL | | | | | | |
|* 3 | TABLE ACCESS FULL| T | 4583 | 572K| | 16 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T | 1 | 128 | | 16 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("X"='secooler')
4 - filter("Y"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
126 consistent gets
0 physical reads
0 redo size
253890 bytes sent via SQL*Net to client
1009 bytes received via SQL*Net from client
49 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4782 rows processed
4.在沒有建立索引情況下的實驗結論
1）無論是返回記錄多的條件放在where子句的前面還是後面，從執行計劃上看，效率是一樣的。
2）沒有建立索引的情況下，使用UNION改寫後效率沒有提高，反而下降了
5.在看一下，建立所需的索引情況後的實驗效果
sec@ora10g> create index idx1_t on t(x);
Index created.
sec@ora10g> create index idx2_t on t(y);
Index created.
sec@ora10g> select * from t where x = 'secooler' or y = 'T';
4782 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4583 | 572K| 16 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 4583 | 572K| 16 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"='secooler' OR "Y"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
111 consistent gets
0 physical reads
0 redo size
206142 bytes sent via SQL*Net to client
1009 bytes received via SQL*Net from client
49 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4782 rows processed
sec@ora10g> select * from t where y = 'T' or x = 'secooler';
4782 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4583 | 572K| 16 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 4583 | 572K| 16 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("Y"='T' OR "X"='secooler')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
111 consistent gets
0 physical reads
0 redo size
206142 bytes sent via SQL*Net to client
1009 bytes received via SQL*Net from client
49 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4782 rows processed
sec@ora10g> select * from t where x = 'secooler'
union
select * from t where y = 'T'
/
2 3 4
4782 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4276936497
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4584 | 573K| | 153 (3)| 00:00:02 |
| 1 | SORT UNIQUE | | 4584 | 573K| 1448K| 153 (3)| 00:00:02 |
| 2 | UNION-ALL | | | | | | |
|* 3 | TABLE ACCESS FULL | T | 4583 | 572K| | 16 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 128 | | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX2_T | 1 | | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("X"='secooler')
5 - access("Y"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
66 consistent gets
0 physical reads
0 redo size
253890 bytes sent via SQL*Net to client
1009 bytes received via SQL*Net from client
49 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4782 rows processed
6.在建立所需的索引後的實驗結果
1）無論是返回記錄多的條件放在where子句的前面還是後面，從執行計劃上看，效率還是一樣的。
2）從“consistent gets”引數上看，使用UNION改寫OR後，效率得到有效的提升。
7.小結
透過上面的實驗，可以得到在CBO模式下，無論是返回記錄多的條件放在where子句的前面還是後面，從執行計劃上看，效率是一樣的。
另外一個重要結論就是：在考慮使用UNION改寫OR的時候，一定要注意檢視使用的欄位是否已經建立了索引。
Goodluck.
-- The End --
您知道這個“某些情況”指的是什麼麼？
解釋一下，“某些情況”指的是：使用的表欄位要有索引。
這個實驗，給大家展示一下這個結論
1.建立測試表
sec@ora10g> drop table t;
Table dropped.
sec@ora10g> create table t as select * from all_objects;
Table created.
sec@ora10g> alter table t rename column owner to x;
Table altered.
sec@ora10g> alter table t rename column object_name to y;
Table altered.
sec@ora10g> update t set x = 'secooler';
4785 rows updated.
OK，透過上面的初始化，我們得到了這個測試表T，我們關心它的第一個和第二個欄位，修改名字後是x欄位和y欄位
x欄位內容統一修改為“secooler”，以便模擬使用這個欄位得到大量返回結果
y欄位指定特定值後，模擬返回一條記錄
2.開啟autotrace，跟蹤不同的SQL執行（為使執行計劃穩定，請多次執行，得到穩定輸出結果）
用到的測試SQL語句是以下三條
1）返回記錄多的條件放在where子句的前面
select * from t where x = 'secooler' or y = 'T';
2）返回記錄多的條件放在where子句的後面
select * from t where y = 'T' or x = 'secooler';
3）使用UNION改寫上面的OR語句
select * from t where x = 'secooler'
union
select * from t where y = 'T'
/
3.先看一下，在沒有建立索引情況下的實驗效果
sec@ora10g> set autotrace traceonly
sec@ora10g> select * from t where x = 'secooler' or y = 'T';
4782 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4583 | 572K| 16 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 4583 | 572K| 16 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"='secooler' OR "Y"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
111 consistent gets
0 physical reads
0 redo size
206142 bytes sent via SQL*Net to client
1009 bytes received via SQL*Net from client
49 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4782 rows processed
sec@ora10g> select * from t where y = 'T' or x = 'secooler';
4782 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4583 | 572K| 16 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 4583 | 572K| 16 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("Y"='T' OR "X"='secooler')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
111 consistent gets
0 physical reads
0 redo size
206142 bytes sent via SQL*Net to client
1009 bytes received via SQL*Net from client
49 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4782 rows processed
sec@ora10g> select * from t where x = 'secooler'
union
select * from t where y = 'T'
/
2 3 4
4782 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2618920678
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4584 | 573K| | 168 (12)| 00:00:03 |
| 1 | SORT UNIQUE | | 4584 | 573K| 1448K| 168 (12)| 00:00:03 |
| 2 | UNION-ALL | | | | | | |
|* 3 | TABLE ACCESS FULL| T | 4583 | 572K| | 16 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| T | 1 | 128 | | 16 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("X"='secooler')
4 - filter("Y"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
126 consistent gets
0 physical reads
0 redo size
253890 bytes sent via SQL*Net to client
1009 bytes received via SQL*Net from client
49 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4782 rows processed
4.在沒有建立索引情況下的實驗結論
1）無論是返回記錄多的條件放在where子句的前面還是後面，從執行計劃上看，效率是一樣的。
2）沒有建立索引的情況下，使用UNION改寫後效率沒有提高，反而下降了
5.在看一下，建立所需的索引情況後的實驗效果
sec@ora10g> create index idx1_t on t(x);
Index created.
sec@ora10g> create index idx2_t on t(y);
Index created.
sec@ora10g> select * from t where x = 'secooler' or y = 'T';
4782 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4583 | 572K| 16 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 4583 | 572K| 16 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("X"='secooler' OR "Y"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
111 consistent gets
0 physical reads
0 redo size
206142 bytes sent via SQL*Net to client
1009 bytes received via SQL*Net from client
49 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4782 rows processed
sec@ora10g> select * from t where y = 'T' or x = 'secooler';
4782 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4583 | 572K| 16 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 4583 | 572K| 16 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("Y"='T' OR "X"='secooler')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
111 consistent gets
0 physical reads
0 redo size
206142 bytes sent via SQL*Net to client
1009 bytes received via SQL*Net from client
49 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4782 rows processed
sec@ora10g> select * from t where x = 'secooler'
union
select * from t where y = 'T'
/
2 3 4
4782 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4276936497
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4584 | 573K| | 153 (3)| 00:00:02 |
| 1 | SORT UNIQUE | | 4584 | 573K| 1448K| 153 (3)| 00:00:02 |
| 2 | UNION-ALL | | | | | | |
|* 3 | TABLE ACCESS FULL | T | 4583 | 572K| | 16 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| T | 1 | 128 | | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX2_T | 1 | | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("X"='secooler')
5 - access("Y"='T')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
66 consistent gets
0 physical reads
0 redo size
253890 bytes sent via SQL*Net to client
1009 bytes received via SQL*Net from client
49 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4782 rows processed
6.在建立所需的索引後的實驗結果
1）無論是返回記錄多的條件放在where子句的前面還是後面，從執行計劃上看，效率還是一樣的。
2）從“consistent gets”引數上看，使用UNION改寫OR後，效率得到有效的提升。
7.小結
透過上面的實驗，可以得到在CBO模式下，無論是返回記錄多的條件放在where子句的前面還是後面，從執行計劃上看，效率是一樣的。
另外一個重要結論就是：在考慮使用UNION改寫OR的時候，一定要注意檢視使用的欄位是否已經建立了索引。
Goodluck.
-- The End --
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29065182/viewspace-1156624/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【SQL優化】UNION替換OR效率測試及總結SQL優化
- sql 優化過程之union 替換 orSQL優化
- unity替換mesh測試Unity
- UNION效率比UNION ALL效率高——SQL優化之Everything is possibleSQL優化
- SQL最佳化案例-union代替or（九）SQL
- 【轉】UNION效率比UNION ALL效率高——SQL優化之Everything is possibleSQL優化
- IP替換的IPAT的接管測試
- SQL Server 替換SQLServer
- 前端單元測試總結及測試工具介紹前端
- Oracle SQL最佳化總結OracleSQL
- python測試request代理IP是否替換Python
- 測試總結①
- run_stats---sql效率測試工具(轉)SQL
- 網際網路App應用程式測試流程及測試總結APP
- DataGuard寫測試實驗步驟及總結
- 【JUnit測試】總結
- 測試流程總結
- 介面測試總結
- SQL Server中TEXT/NTEXT欄位內容替換方法總結(SQL 2005及以上建議使用VARCHAR(MAX)/NVARCHAR(MAX)代替)...SQLServer
- oracle SQL效能最佳化大總結OracleSQL
- SQL Union和SQL Union All用法(轉)SQL
- [測試分析] for與foreach 執行效率測試結果 (轉)
- 效能測試總結(二)---測試流程篇
- sql中union和union allSQL
- 百萬資料查詢測試 只需1秒--Sql語句效率測試SQL
- 軟體測試要學什麼（4）軟體測試流程及常見測試點總結
- web測試方法總結Web
- 功能測試點總結
- 測試入門總結
- 測試總結報告
- APP黑盒測試總結APP
- 迴歸測試總結
- [總結]無線測試
- 初識效能測試（測試小白麵試總結）
- 從測試小白到測試組長，談談我的測試過程及管理經驗總結
- 測試經驗總結：測試員的角色
- 單域控制器中的exchange替換(個人總結)
- HTML試題及答案（總結）HTML