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的时候，一定要注意查看使用的字段是否已经创建了索引。