`
bestxiaok
  • 浏览: 453525 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

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

分享到:
评论

相关推荐

    Oracle提高sql执行效率的心得建议

    - 对于两个索引列,UNION通常比OR有更高的效率。 - IS NULL操作会破坏索引,可以考虑其他方式来处理空值。 9. **避免不必要的操作**: - DISTINCT、UNION、MINUS、INTERSECT和ORDER BY等操作都会涉及排序,消耗...

    SQL百万级数据库优化大全

    可以尝试使用`UNION ALL`来代替`OR`,以提高查询效率。 - **案例**:将`SELECT * FROM t WHERE num = 10 OR num = 20`修改为`SELECT * FROM t WHERE num = 10 UNION ALL SELECT * FROM t WHERE num = 20`。 **5. ...

    SQL语法优化策略 、T-SQL编程注意事项

    6. **避免`OR`与`UNION`**:在`JOIN`操作中,尽量避免在`WHERE`子句中使用`OR`,可能的话,用`UNION`来替换,因为`UNION`更容易优化。 7. **避免函数与表达式**:在索引字段上使用函数(如`UPPER(field)`)会导致...

    CTF中SQL注入常见题型整理.pdf

    这里使用了异或运算符`^`来替换单引号,从而绕过过滤机制。 **实战技巧** 1. **了解异或操作**:熟悉异或操作的特性和如何在SQL注入中利用它。 2. **多种绕过技巧**:尝试结合使用多种技术来实现注入。 3. **测试...

    开发中使用到数据库语句优化

    - 避免在`WHERE`子句中使用`OR`,可以考虑使用`UNION ALL`替代。 - 使用`JOIN`时,确保关联条件是索引,且数据量较小的一方作为驱动表。 3. **SQL语句重构**: - 避免在循环中执行单条SQL,改为一次性处理多条...

    SQL编码规范.pdf

    - **建议6.7 用UNION替换OR(适用于索引列)**:在某些情况下,使用UNION可以提高查询效率。 - **建议6.8 如何删除重复记录**:通过使用临时表或其他技术手段来高效地删除重复记录。 - **建议6.9 用TRUNCATE替代DELETE...

    mySql优化方法简单≈易学

    - **解决方案**:使用 `UNION ALL` 替换 `OR`,可以更高效地处理多个独立的查询。 **5. 避免使用 `LIKE` 开头的通配符** - **问题分析**:使用 `%` 开头的 `LIKE` 语句(例如 `LIKE '%abc%'`)通常会导致全表...

    editplus支持sql

    它不仅支持多种编程语言的语法高亮显示,还具备代码折叠、自动完成等功能,极大地提高了程序员的工作效率。然而,默认情况下,EditPlus并不直接支持SQL(Structured Query Language)这种数据库查询语言。 #### 二...

    oracle中使用group by优化distinct

    8. **使用`UNION ALL`**:在某些情况下,将`DISTINCT`替换为多个`UNION ALL`子查询可能更有效,因为`UNION ALL`不进行去重操作,只合并结果集。 在实际应用中,优化SQL查询需要根据具体的数据库结构、数据分布和...

    Fetch_Rewards_Challenge:在Python中比较文本相似性而不导入任何库

    一种常见的方法是使用编辑距离(Levenshtein Distance),它计算将一个字符串转换为另一个字符串所需的最少单字符编辑(插入、删除或替换)次数。 接下来,我们可以利用Python的内置数据结构,如列表和字典,来实现...

    语言程序设计课后习题答案

    面向对象的软件工程是面向对象方法在软件工程领域的全面应用,它包括面向对象的分析(OOA)、面向对象的设计(OOD)、面向对象的编程(OOP)、面向对象的测试(OOT)和面向对象的软件维护(OOSM)等主要内容。...

Global site tag (gtag.js) - Google Analytics