温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

索引系列八--索引特性之有序难优化union

发布时间:2020-07-25 20:47:03 来源:网络 阅读:529 作者:1415699306 栏目:关系型数据库

----UNION 是需要排序的

drop table t1 purge;

create table t1 as select * from dba_objects where object_id is not null;

alter table t1 modify OBJECT_ID not null;

drop table t2 purge;

create table t2 as select * from dba_objects where object_id is not null;

alter table t2 modify OBJECT_ID not null;

set linesize 1000

set autotrace traceonly


select object_id from t1

union

select object_id from t2;

执行计划

------------------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |   136K|  1732K|       |  1241  (55)| 00:00:15 |

|   1 |  SORT UNIQUE        |      |   136K|  1732K|  2705K|  1241  (55)| 00:00:15 |

|   2 |   UNION-ALL         |      |       |       |       |            |          |

|   3 |    TABLE ACCESS FULL| T1   | 57994 |   736K|       |   292   (1)| 00:00:04 |

|   4 |    TABLE ACCESS FULL| T2   | 78456 |   996K|       |   292   (1)| 00:00:04 |

------------------------------------------------------------------------------------

统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

       2094  consistent gets

          0  physical reads

          0  redo size

    1062305  bytes sent via SQL*Net to client

      54029  bytes received via SQL*Net from client

       4876  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      73120  rows processed

      

      

      

--发现索引无法消除UNION 排序(INDEX FAST FULL SCAN)

create index idx_t1_object_id on t1(object_id);

create index idx_t2_object_id on t2(object_id);

set autotrace traceonly

set linesize 1000


select  object_id from t1

union

select  object_id from t2;

执行计划

---------------------------------------------------------------------------------------------------

| Id  | Operation              | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |                  |   136K|  1732K|       |   755  (57)| 00:00:10 |

|   1 |  SORT UNIQUE           |                  |   136K|  1732K|  2705K|   755  (57)| 00:00:10 |

|   2 |   UNION-ALL            |                  |       |       |       |            |          |

|   3 |    INDEX FAST FULL SCAN| IDX_T1_OBJECT_ID | 57994 |   736K|       |    49   (0)| 00:00:01 |

|   4 |    INDEX FAST FULL SCAN| IDX_T2_OBJECT_ID | 78456 |   996K|       |    49   (0)| 00:00:01 |

---------------------------------------------------------------------------------------------------

统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        340  consistent gets

          0  physical reads

          0  redo size

    1062305  bytes sent via SQL*Net to client

      54029  bytes received via SQL*Net from client

       4876  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      73120  rows processed

      

--INDEX FULL SCAN的索引依然无法消除UNION排序

select /*+index(t1)*/ object_id from t1

union

select /*+index(t2)*/  object_id from t2; 

执行计划

----------------------------------------------------------------------------------------------

| Id  | Operation         | Name             | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |                  |   136K|  1732K|       |  1010  (56)| 00:00:13 |

|   1 |  SORT UNIQUE      |                  |   136K|  1732K|  2705K|  1010  (56)| 00:00:13 |

|   2 |   UNION-ALL       |                  |       |       |       |            |          |

|   3 |    INDEX FULL SCAN| IDX_T1_OBJECT_ID | 57994 |   736K|       |   177   (1)| 00:00:03 |

|   4 |    INDEX FULL SCAN| IDX_T2_OBJECT_ID | 78456 |   996K|       |   177   (1)| 00:00:03 |

----------------------------------------------------------------------------------------------

统计信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

        326  consistent gets

          0  physical reads

          0  redo size

    1062305  bytes sent via SQL*Net to client

      54029  bytes received via SQL*Net from client

       4876  SQL*Net roundtrips to/from client

          1  sorts (memory)

          0  sorts (disk)

      73120  rows processed

      


--结论:索引无法消除UNION 排序,一般来说在使用UNION时要确定必要性,在数据不会重复时只需UNION ALL即可。      


向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

AI