优化案例2-----or 等价改写 union all 的经典案例

最新推荐文章于 2024-05-18 11:23:29 发布

Oracle小王子

最新推荐文章于 2024-05-18 11:23:29 发布

阅读量6.6k

点赞数 3

本文链接：https://blog.csdn.net/yws_1990/article/details/51296515

版权

今天一个之前帮他优化过很多SQL的哥们找我说，有一个分页SQL 很慢，程序debug监控到：前端显示出来第一页的数据就要30秒钟，一共要显示1万2000多条数据，在PL SQL DEV里面手工执行它的SQL ，全出刷出来就需要大概20多秒的时间。让我帮他优化一下。

原SQL语句如下：

select C_ID,
       C_CONTENT,
       C_CODE,
       C_KEY_WORD,
       C_PHRASE,
       lb,
       C_PUBLISH,
       FK_CREATE_ID,
       C_CREATE_TIME,
       FK_DEPT_ID,
       C_STATUS,
       C_TYPE,
       jgmc,
       bbname
  from (select temp.*
          from (select ST.C_ID,
                       ST.C_CONTENT,
                       ST.C_CODE,
                       ST.C_KEY_WORD,
                       ST.C_PHRASE,
                       decode(tmp.c_type,
                              1,
                              '岗位胜任能力',
                              2,
                              '职业技能鉴定',
                              3,
                              '技能专家选聘',
                              4,
                              '专项考试',
                              5,
                              '专业技术资格',
                              '') as lb,
                       ST.C_PUBLISH,
                       ST.FK_CREATE_ID,
                       ST.C_CREATE_TIME,
                       ST.FK_DEPT_ID,
                       ST.C_STATUS,
                       ST.C_TYPE,
                       jg.jgmc,
                       tmp.c_name as bbname
                  from t_ksgl_st st
                  left join (select bbst.fk_st_id, bb.c_name, bb.c_type
                              from T_KSGL_TKBBST bbst
                              join t_ksgl_tkbb bb
                                on bb.c_id = bbst.fk_tkbb_id) tmp
                    on st.c_id = tmp.fk_st_id
                  left join dm_gy_jg jg
                    on st.fk_dept_id = jg.id
                 where st.c_deleted = 'N'
                   and ((st.c_publish = '1' and exists
                        (select *
                            from (select jg.id
                                    from dm_gy_jg jg
                                   where (jg.dw_id = '7f304de353c358egcfg267f023db8f25' and jg.jgsx = '1')
                                      or jg.id = '7f304de353c358egcfg267f023db8f25') t
                           where t.id = st.fk_dept_id)) or
                       (st.c_publish = '0' and st.fk_create_id = '80ec73e9e66e4153a70a209cd68c17c4'))
                 ) temp)
 group by C_ID,
          C_CONTENT,
          C_CODE,
          C_KEY_WORD,
          C_PHRASE,
          lb,
          C_PUBLISH,
          FK_CREATE_ID,
          C_CREATE_TIME,
          FK_DEPT_ID,
          C_STATUS,
          C_TYPE,
          jgmc,
          bbname
 order by c_create_time desc

执行计划为：

---------------------------------------------------------------------------------------------------
| Id   | Operation                       | Name          | Rows    | Bytes     | Cost  | Time     |
---------------------------------------------------------------------------------------------------
|    0 | SELECT STATEMENT                |               |   20897 |   7940860 | 93715 | 00:18:45 |
|    1 |   SORT GROUP BY                 |               |   20897 |   7940860 | 93715 | 00:18:45 |
|  * 2 |    FILTER                       |               |         |           |       |          |
|  * 3 |     HASH JOIN RIGHT OUTER       |               |  670394 | 254749720 | 39509 | 00:07:55 |
|    4 |      VIEW                       |               | 1302361 |  82048743 |  5325 | 00:01:04 |
|  * 5 |       HASH JOIN                 |               | 1302361 | 168004569 |  5325 | 00:01:04 |
|    6 |        TABLE ACCESS FULL        | T_KSGL_TKBB   |    3421 |    215523 |    27 | 00:00:01 |
|    7 |        TABLE ACCESS FULL        | T_KSGL_TKBBST | 1304287 |  86082942 |  5293 | 00:01:04 |
|  * 8 |      HASH JOIN RIGHT OUTER      |               |  453444 | 143741748 | 22504 | 00:04:31 |
|    9 |       TABLE ACCESS FULL         | DM_GY_JG      |   48328 |   2174760 |   635 | 00:00:08 |
| * 10 |       TABLE ACCESS FULL         | T_KSGL_ST     |  453444 | 123336768 | 15648 | 00:03:08 |
| * 11 |     TABLE ACCESS BY INDEX ROWID | DM_GY_JG      |       1 |        68 |     2 | 00:00:01 |
| * 12 |      INDEX UNIQUE SCAN          | PK_DM_GY_JG   |       1 |           |     1 | 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
------------------------------------------
* 2 - filter("ST"."FK_CREATE_ID"='80ec73e9e66e4153a70a209cd68c17c4' AND "ST"."C_PUBLISH"='0' OR "ST"."C_PUBLISH"='1' AND EXISTS (SELECT 0 FROM "DM_GY_JG" "JG" WHERE "JG"."ID"=:B1 AND
  ("JG"."DW_ID"='7f304de353c358egcfg267f023db8f25' AND "JG"."JGSX"='1' OR "JG"."ID"='7f304de353c358egcfg267f023db8f25')))
* 3 - access("ST"."C_ID"="TMP"."FK_ST_ID"(+))
* 5 - access("BB"."C_ID"="BBST"."FK_TKBB_ID")
* 8 - access("ST"."FK_DEPT_ID"="JG"."ID"(+))
* 10 - filter("ST"."C_DELETED"='N')
* 11 - filter("JG"."DW_ID"='7f304de353c358egcfg267f023db8f25' AND "JG"."JGSX"='1' OR "JG"."ID"='7f304de353c358egcfg267f023db8f25')
* 12 - access("JG"."ID"=:B1)

其实主体部分就是temp 括号里面的部分，分页框架是外面程序套的，但是由于最终需要Group by ，那么只能查询出来所有的数据到temp中，然后汇总Group by，这个分页是不能彻底优化的，只有去抓主要的主体部分SQL 去提速，优化。

可以看到，ID=2 是FILTER，被FILTER的部分是 DM_GY_JG 和它的主键 PK_DM_GY_JG，从执行计划来看，ID=12 走的是索引唯一性扫描，然后回表，照理说，索引唯一性扫描只能获取到1条rowid，然后回表取数，不会有性能问题，但是，具体问题要具体分析，如果FILTER的驱动表返回的数据量很大，也就意味着ID=11和ID=12 被扫描的次数将会特别多，还要考虑索引和表的体积，因为INDEX UNIQUE SCAN 是索引单块读，一次只能读取一个索引块，那么那么这样效率就很低了。

首先检查FILTER 驱动表将会返回的结果集数：

select count(distinct st.fk_create_id)
  from t_ksgl_st st
  left join (select bbst.fk_st_id, bb.c_name, bb.c_type
               from T_KSGL_TKBBST bbst
               join t_ksgl_tkbb bb
                 on bb.c_id = bbst.fk_tkbb_id) tmp
    on st.c_id = tmp.fk_st_id
  left join dm_gy_jg jg
    on st.fk_dept_id = jg.id
 where st.c_deleted = 'N'

------结果 88

也就是说将88个值给FILTER的被驱动表，也就是说 ID=11和ID=12 将会被执行88次单块读。

再看表和索引的体积：

select segment_name,bytes/1024/1024 from user_segments where segment_name in ('DM_GY_JG','PK_DM_GY_JG

DM_GY_JG --- 17M
PK_DM_GY_JG --- 3M

单个主键的索引体积已经到了3M，如果这88个值离散度比较高，那么就通过该索引回表会引起很大的性能问题。

下面再来看引起FILTER 的 where 条件中的OR EXISTS 部分：

                   and ((st.c_publish = '1' and exists
                        (select *
                            from (select jg.id
                                    from dm_gy_jg jg
                                   where (jg.dw_id = '7f304de353c358egcfg267f023db8f25' and jg.jgsx = '1')
                                      or jg.id = '7f304de353c358egcfg267f023db8f25') t
                           where t.id = st.fk_dept_id)) or
                       (st.c_publish = '0' and st.fk_create_id = '80ec73e9e66e4153a70a209cd68c17c4'))

整体结构就是：

and ((xxxx and exists) or (xxxx and xxxx))

那么就可以在or 中间拆分成两个部分然后做union all

原主体部分为：

select ST.C_ID,
                       ST.C_CONTENT,
                       ST.C_CODE,
                       ST.C_KEY_WORD,
                       ST.C_PHRASE,
                       decode(tmp.c_type,
                              1,
                              '岗位胜任能力',
                              2,
                              '职业技能鉴定',
                              3,
                              '技能专家选聘',
                              4,
                              '专项考试',
                              5,
                              '专业技术资格',
                              '') as lb,
                       ST.C_PUBLISH,
                       ST.FK_CREATE_ID,
                       ST.C_CREATE_TIME,
                       ST.FK_DEPT_ID,
                       ST.C_STATUS,
                       ST.C_TYPE,
                       jg.jgmc,
                       tmp.c_name as bbname
                  from t_ksgl_st st
                  left join (select bbst.fk_st_id, bb.c_name, bb.c_type
                              from T_KSGL_TKBBST bbst
                              join t_ksgl_tkbb bb
                                on bb.c_id = bbst.fk_tkbb_id) tmp
                    on st.c_id = tmp.fk_st_id
                  left join dm_gy_jg jg
                    on st.fk_dept_id = jg.id
                 where st.c_deleted = 'N'
                   and ((st.c_publish = '1' and exists
                        (select *
                            from (select jg.id
                                    from dm_gy_jg jg
                                   where (jg.dw_id = '7f304de353c358egcfg267f023db8f25' and jg.jgsx = '1')
                                      or jg.id = '7f304de353c358egcfg267f023db8f25') t
                           where t.id = st.fk_dept_id)) or
                       (st.c_publish = '0' and st.fk_create_id = '80ec73e9e66e4153a70a209cd68c17c4'))

拆分成UINION ALL 以后为：

select ST.C_ID,
        ST.C_CONTENT,
        ST.C_CODE,
        ST.C_KEY_WORD,
        ST.C_PHRASE,
        decode(tmp.c_type,
               1,
               '岗位胜任能力',
               2,
               '职业技能鉴定',
               3,
               '技能专家选聘',
               4,
               '专项考试',
               5,
               '专业技术资格',
               '') as lb,
        ST.C_PUBLISH,
        ST.FK_CREATE_ID,
        ST.C_CREATE_TIME,
        ST.FK_DEPT_ID,
        ST.C_STATUS,
        ST.C_TYPE,
        jg.jgmc,
        tmp.c_name as bbname
   from t_ksgl_st st
   left join (select bbst.fk_st_id, bb.c_name, bb.c_type
                from T_KSGL_TKBBST bbst
                join t_ksgl_tkbb bb
                  on bb.c_id = bbst.fk_tkbb_id) tmp
     on st.c_id = tmp.fk_st_id
   left join dm_gy_jg jg
     on st.fk_dept_id = jg.id
  where st.c_deleted = 'N'
    and ((st.c_publish = '1' and exists
         (select *
             from (select jg.id
                     from dm_gy_jg jg
                    where (jg.dw_id = '7f304de353c358egcfg267f023db8f25' and
                          jg.jgsx = '1')
                       or jg.id = '7f304de353c358egcfg267f023db8f25') t
            where t.id = st.fk_dept_id)))
 union all (select ST.C_ID,
                   ST.C_CONTENT,
                   ST.C_CODE,
                   ST.C_KEY_WORD,
                   ST.C_PHRASE,
                   decode(tmp.c_type,
                          1,
                          '岗位胜任能力',
                          2,
                          '职业技能鉴定',
                          3,
                          '技能专家选聘',
                          4,
                          '专项考试',
                          5,
                          '专业技术资格',
                          '') as lb,
                   ST.C_PUBLISH,
                   ST.FK_CREATE_ID,
                   ST.C_CREATE_TIME,
                   ST.FK_DEPT_ID,
                   ST.C_STATUS,
                   ST.C_TYPE,
                   jg.jgmc,
                   tmp.c_name as bbname
              from t_ksgl_st st
              left join (select bbst.fk_st_id, bb.c_name, bb.c_type
                          from T_KSGL_TKBBST bbst
                          join t_ksgl_tkbb bb
                            on bb.c_id = bbst.fk_tkbb_id) tmp
                on st.c_id = tmp.fk_st_id
              left join dm_gy_jg jg
                on st.fk_dept_id = jg.id
             where st.c_deleted = 'N'
               and (st.c_publish = '0' and
                   st.fk_create_id = '80ec73e9e66e4153a70a209cd68c17c4'))

完整的SQL 改为：

select C_ID,
       C_CONTENT,
       C_CODE,
       C_KEY_WORD,
       C_PHRASE,
       lb,
       C_PUBLISH,
       FK_CREATE_ID,
       C_CREATE_TIME,
       FK_DEPT_ID,
       C_STATUS,
       C_TYPE,
       jgmc,
       bbname
  from (select temp.*
          from (select ST.C_ID,
                       ST.C_CONTENT,
                       ST.C_CODE,
                       ST.C_KEY_WORD,
                       ST.C_PHRASE,
                       decode(tmp.c_type,
                              1,
                              '岗位胜任能力',
                              2,
                              '职业技能鉴定',
                              3,
                              '技能专家选聘',
                              4,
                              '专项考试',
                              5,
                              '专业技术资格',
                              '') as lb,
                       ST.C_PUBLISH,
                       ST.FK_CREATE_ID,
                       ST.C_CREATE_TIME,
                       ST.FK_DEPT_ID,
                       ST.C_STATUS,
                       ST.C_TYPE,
                       jg.jgmc,
                       tmp.c_name as bbname
                  from t_ksgl_st st
                  left join (select bbst.fk_st_id, bb.c_name, bb.c_type
                              from T_KSGL_TKBBST bbst
                              join t_ksgl_tkbb bb
                                on bb.c_id = bbst.fk_tkbb_id) tmp
                    on st.c_id = tmp.fk_st_id
                  left join dm_gy_jg jg
                    on st.fk_dept_id = jg.id
                 where st.c_deleted = 'N'
                   and ((st.c_publish = '1' and exists
                        (select *
                            from (select jg.id
                                    from dm_gy_jg jg
                                   where (jg.dw_id =
                                         '7f304de353c358egcfg267f023db8f25' and
                                         jg.jgsx = '1')
                                      or jg.id =
                                         '7f304de353c358egcfg267f023db8f25') t
                           where t.id = st.fk_dept_id)))
                union all (select ST.C_ID,
                                 ST.C_CONTENT,
                                 ST.C_CODE,
                                 ST.C_KEY_WORD,
                                 ST.C_PHRASE,
                                 decode(tmp.c_type,
                                        1,
                                        '岗位胜任能力',
                                        2,
                                        '职业技能鉴定',
                                        3,
                                        '技能专家选聘',
                                        4,
                                        '专项考试',
                                        5,
                                        '专业技术资格',
                                        '') as lb,
                                 ST.C_PUBLISH,
                                 ST.FK_CREATE_ID,
                                 ST.C_CREATE_TIME,
                                 ST.FK_DEPT_ID,
                                 ST.C_STATUS,
                                 ST.C_TYPE,
                                 jg.jgmc,
                                 tmp.c_name as bbname
                            from t_ksgl_st st
                            left join (select bbst.fk_st_id,
                                             bb.c_name,
                                             bb.c_type
                                        from T_KSGL_TKBBST bbst
                                        join t_ksgl_tkbb bb
                                          on bb.c_id = bbst.fk_tkbb_id) tmp
                              on st.c_id = tmp.fk_st_id
                            left join dm_gy_jg jg
                              on st.fk_dept_id = jg.id
                           where st.c_deleted = 'N'
                             and (st.c_publish = '0' and
                                 st.fk_create_id =
                                 '80ec73e9e66e4153a70a209cd68c17c4'))) temp)
 group by C_ID,
          C_CONTENT,
          C_CODE,
          C_KEY_WORD,
          C_PHRASE,
          lb,
          C_PUBLISH,
          FK_CREATE_ID,
          C_CREATE_TIME,
          FK_DEPT_ID,
          C_STATUS,
          C_TYPE,
          jgmc,
          bbname
 order by c_create_time desc

修改后SQL的执行计划为：

---------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |               |   152 |   192K| 26514   (2)| 00:05:19 |
|   1 |  SORT GROUP BY                          |               |   152 |   192K| 26514   (2)| 00:05:19 |
|   2 |   VIEW                                  |               |   152 |   192K| 26513   (2)| 00:05:19 |
|   3 |    UNION-ALL                            |               |       |       |            |          |
|*  4 |     HASH JOIN OUTER                     |               |    20 | 28280 |  5890   (2)| 00:01:11 |
|   5 |      NESTED LOOPS OUTER                 |               |    14 |  5418 |  1772   (1)| 00:00:22 |
|   6 |       NESTED LOOPS                      |               |    14 |  4788 |  1758   (1)| 00:00:22 |
|   7 |        SORT UNIQUE                      |               |    35 |  2380 |    14   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS BY INDEX ROWID     | DM_GY_JG      |    35 |  2380 |    14   (0)| 00:00:01 |
|   9 |          BITMAP CONVERSION TO ROWIDS    |               |       |       |            |          |
|  10 |           BITMAP OR                     |               |       |       |            |          |
|  11 |            BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|* 12 |             INDEX RANGE SCAN            | DW_ID         |       |       |     3   (0)| 00:00:01 |
|  13 |            BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          |
|* 14 |             INDEX RANGE SCAN            | PK_DM_GY_JG   |       |       |     1   (0)| 00:00:01 |
|* 15 |        TABLE ACCESS BY INDEX ROWID      | T_KSGL_ST     |    14 |  3836 |  1743   (1)| 00:00:21 |
|* 16 |         INDEX RANGE SCAN                | IN_ST_DEPT_ID | 23066 |       |   146   (1)| 00:00:02 |
|  17 |       TABLE ACCESS BY INDEX ROWID       | DM_GY_JG      |     1 |    45 |     1   (0)| 00:00:01 |
|* 18 |        INDEX UNIQUE SCAN                | PK_DM_GY_JG   |     1 |       |     0   (0)| 00:00:01 |
|  19 |      VIEW                               |               |  1328K|  1301M|  4102   (2)| 00:00:50 |
|* 20 |       HASH JOIN                         |               |  1328K|   163M|  4102   (2)| 00:00:50 |
|  21 |        TABLE ACCESS FULL                | T_KSGL_TKBB   |  3571 |   219K|    27   (0)| 00:00:01 |
|  22 |        INDEX FAST FULL SCAN             | IDX_YWS6      |  1330K|    83M|  4059   (1)| 00:00:49 |
|* 23 |     HASH JOIN OUTER                     |               |   132 |   173K| 20623   (1)| 00:04:08 |
|  24 |      NESTED LOOPS OUTER                 |               |    90 | 28710 | 16504   (1)| 00:03:19 |
|* 25 |       TABLE ACCESS FULL                 | T_KSGL_ST     |    90 | 24660 | 16414   (1)| 00:03:17 |
|  26 |       TABLE ACCESS BY INDEX ROWID       | DM_GY_JG      |     1 |    45 |     1   (0)| 00:00:01 |
|* 27 |        INDEX UNIQUE SCAN                | PK_DM_GY_JG   |     1 |       |     0   (0)| 00:00:01 |
|  28 |      VIEW                               |               |  1328K|  1301M|  4102   (2)| 00:00:50 |
|* 29 |       HASH JOIN                         |               |  1328K|   163M|  4102   (2)| 00:00:50 |
|  30 |        TABLE ACCESS FULL                | T_KSGL_TKBB   |  3571 |   219K|    27   (0)| 00:00:01 |
|  31 |        INDEX FAST FULL SCAN             | IDX_YWS6      |  1330K|    83M|  4059   (1)| 00:00:49 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("ST"."C_ID"="TMP"."FK_ST_ID"(+))
   8 - filter("JG"."DW_ID"='7f304de353c358egcfg267f023db8f25' AND "JG"."JGSX"='1' OR 
              "JG"."ID"='7f304de353c358egcfg267f023db8f25')
  12 - access("JG"."DW_ID"='7f304de353c358egcfg267f023db8f25')
  14 - access("JG"."ID"='7f304de353c358egcfg267f023db8f25')
  15 - filter("ST"."C_PUBLISH"='1' AND "ST"."C_DELETED"='N')
  16 - access("JG"."ID"="ST"."FK_DEPT_ID")
  18 - access("ST"."FK_DEPT_ID"="JG"."ID"(+))
  20 - access("BB"."C_ID"="BBST"."FK_TKBB_ID")
  23 - access("ST"."C_ID"="TMP"."FK_ST_ID"(+))
  25 - filter("ST"."FK_CREATE_ID"='80ec73e9e66e4153a70a209cd68c17c4' AND "ST"."C_PUBLISH"='0' 
              AND "ST"."C_DELETED"='N')
  27 - access("ST"."FK_DEPT_ID"="JG"."ID"(+))
  29 - access("BB"."C_ID"="BBST"."FK_TKBB_ID")

可以看到FILTER消失了。

进一步调整：
可以看到ID=16 ID=15 T_KSGL_ST 作为Nested loop的被驱动表有回表再过滤，说明ID=16 使用的索引有问题

创建合适的索引：

    create index idx_9 on T_KSGL_ST(FK_DEPT_ID,C_PUBLISH,C_DELETED);

select count(*) from T_KSGL_ST; --- 1000355 该表有100多万条数据

看到ID=25 T_KSGL_ST 做了Nested loop的驱动表，且有过滤条件，查看过滤条件的选择性：

select count(*) from T_KSGL_ST ST WHERE "ST"."FK_CREATE_ID"='80ec73e9e66e4153a70a209cd68c17c4' AND "ST"."C_PUBLISH"='0' AND "ST"."C_DELETED"='N';

---结果为3

应该建立相应的索引

create index idx_10 on T_KSGL_ST(FK_CREATE_ID,C_PUBLISH,C_DELETED);

再次执行新SQL的执行计划:

-------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |             |   152 |   192K|  9576   (2)| 00:01:55 |
|   1 |  SORT GROUP BY                          |             |   152 |   192K|  9576   (2)| 00:01:55 |
|   2 |   VIEW                                  |             |   152 |   192K|  9575   (2)| 00:01:55 |
|   3 |    UNION-ALL                            |             |       |       |            |          |
|*  4 |     HASH JOIN OUTER                     |             |    20 | 28280 |  5355   (2)| 00:01:05 |
|   5 |      NESTED LOOPS OUTER                 |             |    14 |  5418 |  1237   (1)| 00:00:15 |
|   6 |       NESTED LOOPS                      |             |    14 |  4788 |  1223   (1)| 00:00:15 |
|   7 |        SORT UNIQUE                      |             |    35 |  2380 |    14   (0)| 00:00:01 |
|*  8 |         TABLE ACCESS BY INDEX ROWID     | DM_GY_JG    |    35 |  2380 |    14   (0)| 00:00:01 |
|   9 |          BITMAP CONVERSION TO ROWIDS    |             |       |       |            |          |
|  10 |           BITMAP OR                     |             |       |       |            |          |
|  11 |            BITMAP CONVERSION FROM ROWIDS|             |       |       |            |          |
|* 12 |             INDEX RANGE SCAN            | DW_ID       |       |       |     3   (0)| 00:00:01 |
|  13 |            BITMAP CONVERSION FROM ROWIDS|             |       |       |            |          |
|* 14 |             INDEX RANGE SCAN            | PK_DM_GY_JG |       |       |     1   (0)| 00:00:01 |
|  15 |        TABLE ACCESS BY INDEX ROWID      | T_KSGL_ST   |    14 |  3836 |  1208   (1)| 00:00:15 |
|* 16 |         INDEX RANGE SCAN                | IDX_9       | 17183 |       |    91   (2)| 00:00:02 |
|  17 |       TABLE ACCESS BY INDEX ROWID       | DM_GY_JG    |     1 |    45 |     1   (0)| 00:00:01 |
|* 18 |        INDEX UNIQUE SCAN                | PK_DM_GY_JG |     1 |       |     0   (0)| 00:00:01 |
|  19 |      VIEW                               |             |  1328K|  1301M|  4102   (2)| 00:00:50 |
|* 20 |       HASH JOIN                         |             |  1328K|   163M|  4102   (2)| 00:00:50 |
|  21 |        TABLE ACCESS FULL                | T_KSGL_TKBB |  3571 |   219K|    27   (0)| 00:00:01 |
|  22 |        INDEX FAST FULL SCAN             | IDX_YWS6    |  1330K|    83M|  4059   (1)| 00:00:49 |
|* 23 |     HASH JOIN OUTER                     |             |   132 |   173K|  4219   (2)| 00:00:51 |
|  24 |      NESTED LOOPS OUTER                 |             |    90 | 28710 |   100   (0)| 00:00:02 |
|  25 |       TABLE ACCESS BY INDEX ROWID       | T_KSGL_ST   |    90 | 24660 |    10   (0)| 00:00:01 |
|* 26 |        INDEX RANGE SCAN                 | IDX_10      |    96 |       |     3   (0)| 00:00:01 |
|  27 |       TABLE ACCESS BY INDEX ROWID       | DM_GY_JG    |     1 |    45 |     1   (0)| 00:00:01 |
|* 28 |        INDEX UNIQUE SCAN                | PK_DM_GY_JG |     1 |       |     0   (0)| 00:00:01 |
|  29 |      VIEW                               |             |  1328K|  1301M|  4102   (2)| 00:00:50 |
|* 30 |       HASH JOIN                         |             |  1328K|   163M|  4102   (2)| 00:00:50 |
|  31 |        TABLE ACCESS FULL                | T_KSGL_TKBB |  3571 |   219K|    27   (0)| 00:00:01 |
|  32 |        INDEX FAST FULL SCAN             | IDX_YWS6    |  1330K|    83M|  4059   (1)| 00:00:49 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("ST"."C_ID"="TMP"."FK_ST_ID"(+))
   8 - filter("JG"."DW_ID"='7f304de353c358egcfg267f023db8f25' AND "JG"."JGSX"='1' OR 
              "JG"."ID"='7f304de353c358egcfg267f023db8f25')
  12 - access("JG"."DW_ID"='7f304de353c358egcfg267f023db8f25')
  14 - access("JG"."ID"='7f304de353c358egcfg267f023db8f25')
  16 - access("JG"."ID"="ST"."FK_DEPT_ID" AND "ST"."C_PUBLISH"='1' AND "ST"."C_DELETED"='N')
  18 - access("ST"."FK_DEPT_ID"="JG"."ID"(+))
  20 - access("BB"."C_ID"="BBST"."FK_TKBB_ID")
  23 - access("ST"."C_ID"="TMP"."FK_ST_ID"(+))
  26 - access("ST"."FK_CREATE_ID"='80ec73e9e66e4153a70a209cd68c17c4' AND "ST"."C_PUBLISH"='0' 
              AND "ST"."C_DELETED"='N')
  28 - access("ST"."FK_DEPT_ID"="JG"."ID"(+))
  30 - access("BB"."C_ID"="BBST"."FK_TKBB_ID")

优化的成果就是程序第一页显示只要0.9秒数据全部刷完需要9秒。

Oracle小王子

关注

3
点赞
踩
6

收藏

觉得还不错? 一键收藏
1
评论
优化案例2-----or 等价改写 union all 的经典案例

今天一个之前帮他优化过很多SQL的哥们找我说，有一个分页SQL 很慢，程序debug监控到：前端显示出来第一页的数据就要30秒钟，一共要显示1万2000多条数据，在PL SQL DEV里面手工执行它的SQL ，全出刷出来就需要大概20多秒的时间。让我帮他优化一下。原SQL语句如下： select C_ID, C_CONTENT, C_CODE
复制链接

扫一扫

优化案例2-----or 等价改写 union all 的经典案例

“相关推荐”对你有帮助么？