【SQL優化】UNION替換OR效率測試及總結

secooler發表於2009-09-24
SQL優化
大家在做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 --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-615470/，如需轉載，請註明出處，否則將追究法律責任。

相關文章