查看: 371|回复: 12

UNION 操作后,原先走索引的操作,改为走全表,为何?

[复制链接]
论坛徽章:
307
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
发表于 2018-8-9 17:11 | 显示全部楼层 |阅读模式
ORACLE 11204, RAC,

有两段带 ORDER BY , ROWNUM 的操作,单独执行,速度很快,走索引,但若用 UNION 合在一块,则变成走全表扫描,为何?


--1

SQL>SELECT  * FROM
(
         SELECT  t0.FNUMBER FNUMBER,
                 t0_L.FNAME FNAME  ,
                 t0.FLOTID FLOTID
         FROM    T_BD_LOTMASTER T0
                 LEFT OUTER JOIN T_BD_LOTMASTER_L T0_L
                 ON
                         (
                                 t0.FLOTID          = t0_L.FLOTID
                                 AND t0_L.FLocaleId = 2052
                         )
         WHERE
                 (
                         t0.FNUMBER LIKE N'1%'
                  2    3    4    5   )
         ORDER BY t0.FNUMBER ASC
)
           WHERE   ROWNUM <= 5  6    7    8    9   10   11   12   13   14   15   16   17   18   19  
20  /

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2621773990

--------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |     5 |  2635 |    12   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                 |                       |       |       |            |          |
|   2 |   VIEW                         |                       |     6 |  3162 |    12   (0)| 00:00:01 |
|   3 |    NESTED LOOPS OUTER          |                       |     6 |   516 |    12   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T_BD_LOTMASTER        |     6 |   246 |     7   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | IDX_BD_LOTMASTER_NO   |       |       |     3   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| T_BD_LOTMASTER_L      |     1 |    45 |     1   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | IDX_BD_LOTMASTER_L_ID |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)
   5 - access("T0"."FNUMBER" LIKE U'1%')
       filter("T0"."FNUMBER" LIKE U'1%')
   6 - filter("T0_L"."FLOCALEID"(+)=2052)
   7 - access("T0"."FLOTID"="T0_L"."FLOTID"(+))


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

--2

SQL>SELECT  *  FROM (
      SELECT  t0.FNUMBER FNUMBER,
              t0_L.FNAME FNAME  ,
              t0.FLOTID FLOTID
      FROM    T_BD_LOTMASTER T0
              LEFT OUTER JOIN T_BD_LOTMASTER_L T0_L
              ON
                      (
                              t0.FLOTID          = t0_L.FLOTID
                              AND t0_L.FLocaleId = 2052
                      )
      WHERE
              (
                      t0_L.FNAME LIKE N'1%'
              )
      ORDER BY t0.FNUMBER ASC )
      W  2    3  HERE   ROWNUM <= 5  4    5    6    7    8    9   10   11   12   13   14   15   16   17  
18  /

Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
Plan hash value: 1055305271

---------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                       |     5 |  2635 |    13   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                  |                       |       |       |            |          |
|   2 |   VIEW                          |                       |     5 |  2635 |    13   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                 |                       |     5 |   430 |    13   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                |                       |     6 |   430 |    13   (0)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| T_BD_LOTMASTER        |    13M|   522M|     8   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN           | IDX_BD_LOTMASTER_NO   |     6 |       |     3   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN           | IDX_BD_LOTMASTER_L_ID |     1 |       |     1   (0)| 00:00:01 |

|*  8 |     TABLE ACCESS BY INDEX ROWID | T_BD_LOTMASTER_L      |     1 |    45 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<=5)
   7 - access("T0"."FLOTID"="T0_L"."FLOTID")
   8 - filter("T0_L"."FNAME" LIKE U'1%' AND "T0_L"."FLOCALEID"=2052)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       3026  consistent gets
         13  physical reads
          0  redo size
        763  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          5  rows processed

--3

SQL>set autot off;
SQL>explain plan for
  2  SELECT  *  FROM (
      SELECT  t0.FNUMBER FNUMBER,
              t0_L.FNAME FNAME  ,
              t0.FLOTID FLOTID
      FROM    T_BD_LOTMASTER T0
              LEFT OUTER JOIN T_BD_LOTMASTER_L T0_L
              ON
                      (
                              t0.FLOTID          = t0_L.FLOTID
                              AND t0_L.FLocaleId = 2052
                      )
      WHERE
              (
                      t0.FNUMBER LIKE N'1%'
              )
      ORDER BY t0.FNUMBER ASC )
      W  3    4  HERE   ROWNUM <= 5
      UNION
      SELECT  *  FROM (
      SELECT  t0.FNUMBER FNUMBER,
              t0_L.FNAME FNAME  ,
              t0.FLOTID FLOTID
      FROM    T_BD_LOTMASTER T0
              LEFT OUTER JOIN T_BD_LOTMASTER_L T0_L
              ON
                      (
                              t0.FLOTID          = t0_L.FLOTID
                              AND t0_L.FLocaleId = 2052
                      )
      WHERE
              (
                      t0_L.FNAME LIKE N'1%'
              )
      ORDER BY t0.FNUMBER ASC )
      WHERE   ROWNUM <= 5  5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36  
37  /

Explained.

Elapsed: 00:00:00.02
SQL>select * from display;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 599398803

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |    10 |  5270 |       |  3155K  (2)| 00:07:13 |
|   1 |  SORT UNIQUE                 |                            |    10 |  5270 |    13G|  3155K  (2)| 00:07:13 |
|   2 |   UNION-ALL                  |                            |       |       |       |            |          |
|*  3 |    COUNT STOPKEY             |                            |       |       |       |            |          |
|   4 |     VIEW                     |                            |    12M|  6452M|       |   365K  (4)| 00:00:51 |
|*  5 |      SORT ORDER BY STOPKEY   |                            |    12M|  1052M|  1179M|   365K  (4)| 00:00:51 |
|*  6 |       HASH JOIN OUTER        |                            |    12M|  1052M|   648M|   147K  (5)| 00:00:21 |
|*  7 |        VIEW                  | index$_join$_002           |    12M|   501M|       |   104K  (4)| 00:00:15 |
|*  8 |         HASH JOIN            |                            |       |       |       |            |          |
|*  9 |          INDEX RANGE SCAN    | IDX_BD_LOTMASTER_NO        |    12M|   501M|       | 64617   (3)| 00:00:09 |
|  10 |          INDEX FAST FULL SCAN| PK_BD_LOTMASTER            |    12M|   501M|       | 31235   (3)| 00:00:05 |
|* 11 |        TABLE ACCESS FULL     | T_BD_LOTMASTER_L           |    13M|   572M|       | 23642   (6)| 00:00:04 |
|* 12 |    COUNT STOPKEY             |                            |       |       |       |            |          |
|  13 |     VIEW                     |                            |    12M|  6444M|       |   385K  (4)| 00:00:53 |
|* 14 |      SORT ORDER BY STOPKEY   |                            |    12M|  1051M|  1178M|   385K  (4)| 00:00:53 |
|* 15 |       HASH JOIN              |                            |    12M|  1051M|   674M|   168K  (5)| 00:00:24 |
|  16 |        VIEW                  | index$_join$_006           |    13M|   522M|       |   124K  (3)| 00:00:18 |
|* 17 |         HASH JOIN            |                            |       |       |       |            |          |
|  18 |          INDEX FAST FULL SCAN| IDX_BD_LOTMASTER_FMASTERID |    13M|   522M|       |   104K  (2)| 00:00:15 |
|  19 |          INDEX FAST FULL SCAN| PK_BD_LOTMASTER            |    13M|   522M|       | 31235   (3)| 00:00:05 |
|* 20 |        TABLE ACCESS FULL     | T_BD_LOTMASTER_L           |    12M|   550M|       | 24059   (8)| 00:00:04 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(ROWNUM<=5)
   5 - filter(ROWNUM<=5)
   6 - access("T0"."FLOTID"="T0_L"."FLOTID"(+))
   7 - filter("T0"."FNUMBER" LIKE U'1%')
   8 - access(ROWID=ROWID)
   9 - access("T0"."FNUMBER" LIKE U'1%')
  11 - filter("T0_L"."FLOCALEID"(+)=2052)
  12 - filter(ROWNUM<=5)
  14 - filter(ROWNUM<=5)
  15 - access("T0"."FLOTID"="T0_L"."FLOTID")
  17 - access(ROWID=ROWID)
  20 - filter("T0_L"."FNAME" LIKE U'1%' AND "T0_L"."FLOCALEID"=2052)

43 rows selected.

Elapsed: 00:00:00.03


论坛徽章:
121
现任管理团队成员
日期:2011-05-07 01:45:08
发表于 2018-8-9 17:26 | 显示全部楼层
试试union all

确定需要去重么?不需要去重就用union all
需要的话,union all 后再distinc

使用道具 举报

回复
论坛徽章:
307
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
 楼主| 发表于 2018-8-9 17:34 | 显示全部楼层
zergduan 发表于 2018-8-9 17:26
试试union all

确定需要去重么?不需要去重就用union all

这是加上 ALL  的结果,执行计划没变,,,总觉得这里头有蹊跷,
SQL>EXPLAIN PLAN FOR
  2  SELECT  *  FROM (
      SELECT  t0.FNUMBER FNUMBER,
              t0_L.FNAME FNAME  ,
              t0.FLOTID FLOTID
      FROM    T_BD_LOTMASTER T0
              LEFT OUTER JOIN T_BD_LOTMASTER_L T0_L
              ON
                      (
                              t0.FLOTID          = t0_L.FLOTID
                              AND t0_L.FLocaleId = 2052
                      )
      WHERE
              (
                      t0.FNUMBER LIKE N'1%'
              )
      ORDER BY t0.FNUMBER ASC
      )
  3    4    5    6        WHERE   ROWNUM <= 5
      UNION ALL
      SELECT  *  FROM (
      SELECT  t0.FNUMBER FNUMBER,
              t0_L.FNAME FNAME  ,
              t0.FLOTID FLOTID
      FROM    T_BD_LOTMASTER T0
              LEFT OUTER JOIN T_BD_LOTMASTER_L T0_L
              ON
                      (
                              t0.FLOTID          = t0_L.FLOTID
                              AND t0_L.FLocaleId = 2052
                      )
      WHERE
              (
                      t0_L.FNAME LIKE N'1%'
         7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38  
39  /

Explained.

Elapsed: 00:00:00.02
SQL>SELECT * FROM DISPLAY;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4052943324

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |    10 |  5270 |       |   751K  (4)| 00:01:43 |
|   1 |  UNION-ALL                  |                            |       |       |       |            |          |
|*  2 |   COUNT STOPKEY             |                            |       |       |       |            |          |
|   3 |    VIEW                     |                            |    12M|  6452M|       |   365K  (4)| 00:00:51 |
|*  4 |     SORT ORDER BY STOPKEY   |                            |    12M|  1052M|  1179M|   365K  (4)| 00:00:51 |
|*  5 |      HASH JOIN OUTER        |                            |    12M|  1052M|   648M|   147K  (5)| 00:00:21 |
|*  6 |       VIEW                  | index$_join$_002           |    12M|   501M|       |   104K  (4)| 00:00:15 |
|*  7 |        HASH JOIN            |                            |       |       |       |            |          |
|*  8 |         INDEX RANGE SCAN    | IDX_BD_LOTMASTER_NO        |    12M|   501M|       | 64617   (3)| 00:00:09 |
|   9 |         INDEX FAST FULL SCAN| PK_BD_LOTMASTER            |    12M|   501M|       | 31235   (3)| 00:00:05 |
|* 10 |       TABLE ACCESS FULL     | T_BD_LOTMASTER_L           |    13M|   572M|       | 23642   (6)| 00:00:04 |
|* 11 |   COUNT STOPKEY             |                            |       |       |       |            |          |
|  12 |    VIEW                     |                            |    12M|  6444M|       |   385K  (4)| 00:00:53 |
|* 13 |     SORT ORDER BY STOPKEY   |                            |    12M|  1051M|  1178M|   385K  (4)| 00:00:53 |
|* 14 |      HASH JOIN              |                            |    12M|  1051M|   674M|   168K  (5)| 00:00:24 |
|  15 |       VIEW                  | index$_join$_006           |    13M|   522M|       |   124K  (3)| 00:00:18 |
|* 16 |        HASH JOIN            |                            |       |       |       |            |          |
|  17 |         INDEX FAST FULL SCAN| IDX_BD_LOTMASTER_FMASTERID |    13M|   522M|       |   104K  (2)| 00:00:15 |
|  18 |         INDEX FAST FULL SCAN| PK_BD_LOTMASTER            |    13M|   522M|       | 31235   (3)| 00:00:05 |
|* 19 |       TABLE ACCESS FULL     | T_BD_LOTMASTER_L           |    12M|   550M|       | 24059   (8)| 00:00:04 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(ROWNUM<=5)
   4 - filter(ROWNUM<=5)
   5 - access("T0"."FLOTID"="T0_L"."FLOTID"(+))
   6 - filter("T0"."FNUMBER" LIKE U'1%')
   7 - access(ROWID=ROWID)
   8 - access("T0"."FNUMBER" LIKE U'1%')
  10 - filter("T0_L"."FLOCALEID"(+)=2052)
  11 - filter(ROWNUM<=5)
  13 - filter(ROWNUM<=5)
  14 - access("T0"."FLOTID"="T0_L"."FLOTID")
  16 - access(ROWID=ROWID)
  19 - filter("T0_L"."FNAME" LIKE U'1%' AND "T0_L"."FLOCALEID"=2052)

42 rows selected.

Elapsed: 00:00:00.02
SQL>

使用道具 举报

回复
论坛徽章:
121
现任管理团队成员
日期:2011-05-07 01:45:08
发表于 2018-8-9 17:44 | 显示全部楼层
.... 第二个SQL 的 WHERE   ROWNUM <= 5 怎么没写到union的语句里?

使用道具 举报

回复
论坛徽章:
121
现任管理团队成员
日期:2011-05-07 01:45:08
发表于 2018-8-9 17:46 | 显示全部楼层
不成就家hint 让他走NL

使用道具 举报

回复
论坛徽章:
307
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
 楼主| 发表于 2018-8-9 17:55 | 显示全部楼层
zergduan 发表于 2018-8-9 17:44
.... 第二个SQL 的 WHERE   ROWNUM

第2个语句,和第一个语句一模一样,除了 where 查询条件略有改动外,,,

使用道具 举报

回复
论坛徽章:
307
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
 楼主| 发表于 2018-8-9 17:56 | 显示全部楼层
zergduan 发表于 2018-8-9 17:46
不成就家hint 让他走NL

不想强制,因为 like N'1%' 这段,有可能会变成这样 like N'%1%',

使用道具 举报

回复
论坛徽章:
121
现任管理团队成员
日期:2011-05-07 01:45:08
发表于 2018-8-10 10:04 | 显示全部楼层
ZALBB 发表于 2018-8-9 17:56
不想强制,因为 like N'1%' 这段,有可能会变成这样 like N'%1%',

这无所谓呀 N'1%’和N'%1%'对于nl没有区别呀

NL可以避免排序,可以让stopkey在连接内部起作用,本身就是正确的执行计划呀

使用道具 举报

回复
论坛徽章:
307
行业板块每日发贴之星
日期:2012-07-12 18:47:29双黄蛋
日期:2011-08-12 17:31:04咸鸭蛋
日期:2011-08-18 15:13:51迷宫蛋
日期:2011-08-18 16:58:25紫蛋头
日期:2011-08-31 10:57:28ITPUB十周年纪念徽章
日期:2011-09-27 16:30:47蜘蛛蛋
日期:2011-10-20 15:51:25迷宫蛋
日期:2011-10-29 11:12:59ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41鲜花蛋
日期:2011-11-09 20:33:30
 楼主| 发表于 2018-8-10 10:19 | 显示全部楼层
zergduan 发表于 2018-8-10 10:04
这无所谓呀 N'1%’和N'%1%'对于nl没有区别呀

NL可以避免排序,可以让stopkey在连接内部起作用,本身就 ...

若是 N'%1%', 可能全表扫描走哈希,速度更快,

使用道具 举报

回复
论坛徽章:
121
现任管理团队成员
日期:2011-05-07 01:45:08
发表于 2018-8-10 10:39 | 显示全部楼层
ZALBB 发表于 2018-8-10 10:19
若是 N'%1%', 可能全表扫描走哈希,速度更快,

N'%1% 这里的N什么意思?

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表