ITPub博客

首页 > Linux操作系统 > Linux操作系统 > union 优化方法

union 优化方法

原创 Linux操作系统 作者:lovehewenyu 时间:2013-10-25 16:56:49 0 删除 编辑
union 优化方法

union优化思路:
union=>remove duplicate=>sort=>index(don't remove sort)=>index fast full scan(read many index blocks once)
union总结:
返回值仅是索引列值的情况下,给返回值列加索引是可以优化union!

union and union all 简介
union:合并结果集并去除重复(remove duplicate)记录
union all:合并结果集并不去除重复记录


实验如下:
SQL> create table t_union as select * from dba_objects;

Table created.

SQL> alter table t_union modify object_id not null;

Table altered.

SQL> create table t_union_1 as select * from dba_objects;

Table created.

SQL> alter table t_union_1 modify object_id not null;

Table altered.

SQL> select object_id from t_union
  2  union
  3  select object_id from t_union_1;

81512 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 299238292

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |   169K|  2153K|       |  1460  (52)| 00:00:18 |
|   1 |  SORT UNIQUE        |           |   169K|  2153K|  3352K|  1460  (52)| 00:00:18 |
|   2 |   UNION-ALL         |           |       |       |       |            |          |
|   3 |    TABLE ACCESS FULL| T_UNION   | 82154 |  1042K|       |   325   (1)| 00:00:04 |
|   4 |    TABLE ACCESS FULL| T_UNION_1 | 87476 |  1110K|       |   325   (1)| 00:00:04 |
-----------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2332  consistent gets
          0  physical reads
          0  redo size
    1185377  bytes sent via SQL*Net to client
      60189  bytes received via SQL*Net from client
       5436  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      81512  rows processed

=>TempSpc和1  sorts (memory)证明了union是需要排序的;UNION-ALL代表了我们执行合并操作<=

SQL> select object_id from t_union
  2  union all
  3  select object_id from t_union_1;

163023 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3974255051

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |   169K|  2153K|   650  (51)| 00:00:08 |
|   1 |  UNION-ALL         |           |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T_UNION   | 82154 |  1042K|   325   (1)| 00:00:04 |
|   3 |   TABLE ACCESS FULL| T_UNION_1 | 87476 |  1110K|   325   (1)| 00:00:04 |
--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      13034  consistent gets
          0  physical reads
          0  redo size
    2370239  bytes sent via SQL*Net to client
     119963  bytes received via SQL*Net from client
      10870  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     163023  rows processed

=>sorts值为0,也证明了union all不需要排序的;UNION-ALL代表了我们执行合并操作<=


给返回值列,添加索引看union and union all是否得到优化
SQL> create index t1 on t_union(object_id);

Index created.

SQL> create index t2 on t_union_1(object_id);

Index created.

SQL> select object_id from t_union
  2   union
  3   select object_id from t_union_1;

81512 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3228413298

---------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |   169K|  2153K|       |   919  (52)| 00:00:12 |
|   1 |  SORT UNIQUE           |      |   169K|  2153K|  3352K|   919  (52)| 00:00:12 |
|   2 |   UNION-ALL            |      |       |       |       |            |          |
|   3 |    INDEX FAST FULL SCAN| T1   | 82154 |  1042K|       |    54   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| T2   | 87476 |  1110K|       |    54   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        378  consistent gets
          0  physical reads
          0  redo size
    1185377  bytes sent via SQL*Net to client
      60189  bytes received via SQL*Net from client
       5436  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      81512  rows processed

1、索引优化了union ==>INDEX FAST FULL SCAN
2、索引不能消除union的排序 ==>1  sorts (memory) 、TempSpc

SQL> select object_id from t_union
  2   union all
  3   select object_id from t_union_1;

163023 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 937658497

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |   169K|  2153K|   109  (51)| 00:00:02 |
|   1 |  UNION-ALL            |      |       |       |            |          |
|   2 |   INDEX FAST FULL SCAN| T1   | 82154 |  1042K|    54   (0)| 00:00:01 |
|   3 |   INDEX FAST FULL SCAN| T2   | 87476 |  1110K|    54   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      11221  consistent gets
          0  physical reads
          0  redo size
    2370239  bytes sent via SQL*Net to client
     119963  bytes received via SQL*Net from client
      10870  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     163023  rows processed

1、索引不能很好的优化union all


参考:《收获,不止ORACLE》


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26442936/viewspace-775041/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
10年老鸟,Oracle,Mysql,EMC 存储,NBU备份

注册时间:2012-02-03

  • 博文量
    268
  • 访问量
    1296797