Oracle -未使用大表上的索引

Question

我有两个表MP_LM和MP_BS，这两个表如下所示连接到一个视图，并在select语句中使用。

CREATE VIEW MV_MP_LM_V (MP_ID,ID,PID,NAME,IS_D) AS
SELECT C.mp_ID,C.BS_ID ID,NULL      PID,C.BS_NM NAME,'Y'       IS_D FROM MP_BS C UNION
SELECT CM.mp_ID,CM.LM_ID ID,CM.IT_ID PID,CM.LM_NM NAME,CM.IS_D FROM MP_LC_REF_ORIG CM WHERE FLG = 'Y' ;

表MP_BS的主键如下。

ALTER TABLE MP_BS ADD CONSTRAINT MP_BS_PK PRIMARY KEY (MP_ID, BS_ID, BS_NM);

下面是我正在运行的Select。

SELECT DISTINCT
ID,
              PID,
              NAME,
              DECODE (pid,
                      NULL, DECODE (id, -1, NULL, id || '$'),
                      pid || '$' || id)
                 tree_id,
              DECODE (ID, -1, 0, 1) s1
         FROM  MV_MP_LM_V, TABLE (MV_SPLITARR5 (336700164 , ',', '$')) c
        WHERE     mp_ID = c.id1
              AND IS_D= 'Y'
              AND ID <> -1
              AND (   0 = 0
                   OR (    0 > 0

                            ))
     ORDER BY s1, DECODE (pid, NULL, 0, 1), name;

对于上面的select，在没有索引MP_BS_PK的情况下，select花费了35秒，下面是计划。

Plan hash value: 2313865915

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |   206M|   123G|       |    56M  (1)|188:17:27 |
|   1 |  SORT ORDER BY                       |                    |   206M|   123G|   131G|    56M  (1)|188:17:27 |
|   2 |   HASH UNIQUE                        |                    |   206M|   123G|   131G|    28M  (1)| 94:49:48 |
|*  3 |    HASH JOIN                         |                    |   206M|   123G|       |   410K  (1)| 01:22:09 |
|   4 |     COLLECTION ITERATOR PICKLER FETCH| MV_SPLITARR5       |  8168 | 16336 |       |    29   (0)| 00:00:01 |
|   5 |     VIEW                             | MV_MP_LM_V         |  2529K|  1546M|       |   409K  (1)| 01:21:56 |
|   6 |      SORT UNIQUE                     |                    |  2529K|  1489M|  1572M|   409K  (1)| 01:21:56 |
|   7 |       UNION-ALL                      |                    |       |       |       |            |          |
|*  8 |        TABLE ACCESS FULL             | MP_BS              |   121K|  2964K|       |   377   (1)| 00:00:05 |
|*  9 |        TABLE ACCESS FULL             | MP_LM_REF_ORIG     |  2408K|  1486M|       | 79820   (1)| 00:15:58 |
-------------------------------------------------------------------------------------------------------------------

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

   3 - access("MP_ID"=TO_NUMBER(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)))
   8 - filter("C"."BS_ID"<>(-1))
   9 - filter("CM"."LM_ID"<>(-1) AND "FLG"='Y' AND "CM"."IS_D"='Y')

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

对于pk，select需要10秒，下面是这方面的计划。

Plan hash value: 2486203413

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |       |  1929 | 26028   (1)| 00:05:13 |
|   1 |  SORT ORDER BY                       |                    |       |  1929 | 26028   (1)| 00:05:13 |
|   2 |   HASH UNIQUE                        |                    |       |  1929 | 26027   (1)| 00:05:13 |
|*  3 |    HASH JOIN                         |                    |     3 |  1929 | 26026   (1)| 00:05:13 |
|   4 |     VIEW                             | MV_MP_LM_V         |    10 |  6410 | 25997   (1)| 00:05:12 |
|   5 |      SORT UNIQUE                     |                    |       |       |            |          |
|   6 |       UNION-ALL                      |                    |       |       |            |          |
|*  7 |        INDEX FAST FULL SCAN          | MP_BS_PK           |   121K|  2964K|   192   (1)| 00:00:03 |
|*  8 |        TABLE ACCESS FULL             | MP_LM_REF_ORIG     |  2408K|  1486M| 79820   (1)| 00:15:58 |
|   9 |     COLLECTION ITERATOR PICKLER FETCH| MV_SPLITARR5       |  8168 | 16336 |    29   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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

   3 - access("MP_ID"=TO_NUMBER(SYS_OP_ATG(VALUE(KOKBF$),1,2,2)))
   7 - filter("C"."BS_ID"<>(-1))
   8 - filter("CM"."LM_ID"<>(-1) AND "FLG"='Y' AND "CM"."IS_D"='Y')

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

我研究了很多东西，但找不到为什么较小的表上的pk/index会提供更快的性能。

不是的。每个表中的行数为：

MP_LM_REF_ORIG :- 10241670
MP_BS :- 147575

我尝试在id为MP_ID的表MP_LM_REF_ORIG上创建索引，但没有使用该索引。

有人能解释一下MP_BS上的这个索引是如何帮助提高性能的吗？

Answer 1

至少有两个原因：

1. INDEX FAST FULL SCAN而不是TABLE ACCESS FULL，即不是对表进行完全扫描，而是对索引进行完全扫描，这可能是合理的小于相应的table
2. UNION需要删除重复行的扫描，并且在plan中，这由union all后跟sort unique表示。使用索引时，已对使用索引返回的数据进行了排序，因此性能可能会有另一方面的提高。