【SQL优化】四月份培训的一个sql优化案例2

下面跟一张当时培训的照片：

还是拿当时的sql说话， 优化前：

select count(*)

  from (select distinct k.accept_id as num

          from cct_log_eomsintf_201504 k

         where k.op_type in (4, 6)

           and k.id = (select max(id)

                         from cct_log_eomsintf_201504 b

                        where b.accept_id = k.accept_id)

           and (k.op_return is null or k.op_return <> '0')

           and k.creation_time > sysdate - 2 / 24

        union all

        select distinct k.accept_id as num

          from cct_log_eomsintf_201503 k

         where k.op_type in (4, 6)

           and k.id = (select max(id)

                         from cct_log_eomsintf_201503 b

                        where b.accept_id = k.accept_id)

           and (k.op_return is null or k.op_return <> '0')

           and k.creation_time > sysdate - 2 / 24)

           

Execution Plan

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

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

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

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

|   0 | SELECT STATEMENT                  |                         |     1 |       | 25770   (1)|

|   1 |  SORT AGGREGATE                   |                         |     1 |       |            |

|   2 |   VIEW                            |                         |     2 |       | 25770   (1)|

|   3 |    UNION-ALL                      |                         |       |       |            |

|   4 |     HASH UNIQUE                   |                         |     1 |    88 |  9098   (1)|

|*  5 |      FILTER                       |                         |       |       |            |

|   6 |       HASH GROUP BY               |                         |     1 |    88 |  9098   (1)|

|   7 |        TABLE ACCESS BY INDEX ROWID|CCT_LOG_EOMSINTF_201504 |     5 |   170 |     8   (0)|

|   8 |         NESTED LOOPS              |                         |     1 |    88 |  9097   (1)|

|*  9 |          TABLE ACCESS FULL        |CCT_LOG_EOMSINTF_201504 |     1 |    54 |  9089   (1)|

|* 10 |          INDEX RANGE SCAN         | ACCEPT_ID_IDX_201504    |     5 |       |     2   (0)|

|  11 |     HASH UNIQUE                   |                         |     1 |    87 | 16672   (1)|

|* 12 |      FILTER                       |                         |       |       |            |

|  13 |       HASH GROUP BY               |                         |     1 |    87 | 16672   (1)|

|  14 |        TABLE ACCESS BY INDEX ROWID|CCT_LOG_EOMSINTF_201503 |    88 |  2992 |     8   (0)|

|  15 |         NESTED LOOPS              |                         |    10 |   870 | 16671   (1)|

|* 16 |          TABLE ACCESS FULL        |CCT_LOG_EOMSINTF_201503 |     1 |    53 | 16663   (1)|

|* 17 |          INDEX RANGE SCAN         | ACCEPT_ID_IDX_201503    |     6 |       |     2   (0)|

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

优化后：

select count(*)

  from (select distinct (num)

          from (select k.accept_id as num,

                       ROW_NUMBER() OVER(ORDER BY id desc) as newid

                  from cct_log_eomsintf_201504 k

                 where k.op_type in (4, 6)

                   and (k.op_return is null or k.op_return <> '0')

                   and k.creation_time > sysdate - 2 / 24)

         where newid = 1

        union all

        select distinct (num)

          from (select k.accept_id as num,

                       ROW_NUMBER() OVER(ORDER BY id desc) as newid

                  from cct_log_eomsintf_201503 k

                 where k.op_type in (4, 6)

                   and (k.op_return is null or k.op_return <> '0')

                   and k.creation_time > sysdate - 2 / 24)

         where newid = 1)

Execution Plan

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

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

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

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

|   0 | SELECT STATEMENT             |                         |     1 |       | 25756   (1)|

|   1 |  SORT AGGREGATE              |                         |     1 |       |            |

|   2 |   VIEW                       |                         |     2 |       | 25756   (1)|

|   3 |    UNION-ALL                 |                         |       |       |            |

|   4 |     HASH UNIQUE              |                         |     1 |    27 |  9091   (1)|

|*  5 |      VIEW                    |                         |     1 |    27 |  9090   (1)|

|*  6 |       WINDOW SORT PUSHED RANK|                         |     1 |    42 |  9090   (1)|

|*  7 |        TABLE ACCESS FULL     | CCT_LOG_EOMSINTF_201504 |     1 |    42 |  9089   (1)|

|   8 |     HASH UNIQUE              |                         |     1 |    27 | 16665   (1)|

|*  9 |      VIEW                    |                         |     1 |    27 | 16664   (1)|

|* 10 |       WINDOW SORT PUSHED RANK|                         |     1 |    41 | 16664   (1)|

|* 11 |        TABLE ACCESS FULL     |CCT_LOG_EOMSINTF_201503 |     1 |    41 | 16663   (1)|

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

不多说：

优化前，数据表被访问2次。

优化后，数据表只被访问1次。