活用UNION ALL

要学会活学活用。把所有的知识点结合起来。　这样才能即快又准的做优化。

例：

11:23:42 SQL> create table test as select * from dba_objects;

Table created.

Elapsed: 00:00:04.50
11:24:05 SQL> create index test_idx on test(object_id);

Index created.

Elapsed: 00:00:01.34

11:25:03 SQL> select max(object_id), min(object_id) from test;

Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   300   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| TEST | 81195 |  1030K|   300   (1)| 00:00:04 |
---------------------------------------------------------------------------

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


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
       1148  consistent gets
       1071  physical reads
          0  redo size
        613  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

分析下：

可以看到这个TEST有是有OBJECT_ID索引的，　我们只需查到最大的一个和最小一个OBJECT_ID。　为何就走了全表呢，　　这是因为我们在创建索引的时候，索引默认是按照升序排列的。　ORACLE　没有办法去一次性去取最大的一个值和最小的一个值。

下面我们来用UNION ALL来做改写。

11:25:35 SQL> select max(object_id) from test
11:27:40   2  union all
11:27:42   3  select min(object_id) from test;

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 2153991076

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     2 |    26 |     4  (50)| 00:00:01 |
|   1 |  UNION-ALL                  |          |       |       |            |          |
|   2 |   SORT AGGREGATE            |          |     1 |    13 |            |          |
|   3 |    INDEX FULL SCAN (MIN/MAX)| TEST_IDX |     1 |    13 |     2   (0)| 00:00:01 |
|   4 |   SORT AGGREGATE            |          |     1 |    13 |            |          |
|   5 |    INDEX FULL SCAN (MIN/MAX)| TEST_IDX |     1 |    13 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
        152  consistent gets
          0  physical reads
          0  redo size
        588  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

注：　在拿到语句的时候别急着看执行计划，　要先去分析下语句，　有可能不需要你去分析执行计划，优化方案就出来了。

 

还有一种方法就是使用标量子查询的方式。

select (select max(object_id) from test) maxid, (select min(object_id) from test) minid from dual;
SQL> select (select max(object_id) from test) maxid, (select min(object_id) from test) minid from dual;


Execution Plan
----------------------------------------------------------
Plan hash value: 4179602045

-------------------------------------------------------------------------------------
| Id  | Operation                  | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |        |     1 |       |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE            |        |     1 |    13 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| IDX_ID |     1 |    13 |     2   (0)| 00:00:01 |
|   3 |  SORT AGGREGATE            |        |     1 |    13 |            |          |
|   4 |   INDEX FULL SCAN (MIN/MAX)| IDX_ID |     1 |    13 |     2   (0)| 00:00:01 |
|   5 |  FAST DUAL                 |        |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
        164  consistent gets
          2  physical reads
          0  redo size
        611  bytes sent via SQL*Net to client
        544  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed