一個提升本地索引性能的 SQL 優化案例

作者：胡呈清 2024-02-22 16:55:13

SQL 無法做分區裁剪時，使用了高效的唯一索引，當索引是全局索引時效率最高；當索引是本地索引時，需要訪問所有的索引分區，性能會下降。

數據庫版本：OceanBase 3.2.3.3

一、問題描述

在進行一次 Oracle 遷移 OB 時，有張表在 Oracle 上不能關 row movement，因此無法使用 OMS 遷移數據，在割接窗口期前使用 dbcat 單獨遷移表結構，窗口期內再導入數據的方式特殊處理該表。

這是張分區表，在 Oracle 上的主鍵約束不包含分區鍵，但是 OB 要求主鍵必須包含分區鍵，因此這種情況在遷移到 OB 時有兩種處理方式：

OMS 工具：遷移時會將主鍵轉成全局唯一索引 +NOT NULL 約束，等價 Oracle 的主鍵約束。表沒有顯示主鍵，但會有一個隱式主鍵（分區鍵+隱藏自增列）；
dbcat 工具：遷移時會把分區鍵加入到主鍵中，這是個本地索引。

這里最主要的區別是：Oracle 上的主鍵是全局索引，用 dbcat 遷移到 OB 時會變成本地索引，用 OMS 遷移則還是全局索引。然后以下 SQL 做 nested-loop join 時關聯字段是主鍵字段，每次到被驅動表上使用主鍵查找，需要對所有分區執行，因此慢了。

注：OMS、dbcat 都是遷移工具，不用深究，只需理解為什么會有這種區別即可，下面會做解釋。

select
  *
from
  (
    SELECT
      a.act_Id as actId,
      a.data_Id as dataId,
      ...
    from
      T1 a,
      T2 b
    where
      a.data_Id = b.data_Id
      and a.cmp_Status not in ('08')
      and a.crt_Dttm >= to_date('2023-09-15 04:37:49', 'YYYY-mm-dd hh24:mi:ss')
      and a.crt_Dttm < to_date('2023-10-14 04:37:49', 'YYYY-mm-dd hh24:mi:ss')
      ...
    order by
      a.reserve_Begin_Dttm asc,
      a.act_Limit_Date asc,
      a.act_Id asc
  )
where
  rownum <= 10

二、關于全局索引和本地索引

OB 的官方文檔上有非常詳細的說明：局部索引和全局索引[1]。因此本文只做些脈絡上的補充。

1. 什么是全局索引、什么是本地索引？

首先只有分區表才有全局索引、本地索引的區分。先以 MySQL InnoDB 為例，分區表的每個分區實際上都有獨立的表空間，完全可以把分區看成獨立的表，因此對于一個索引來說，它也只能是每個分區維護各自的索引結構，這個就是本地索引，并且 InnoDB 只有本地索引，沒有全局索引。

相反，一張表的所有分區如果只維護一個索引結構，這個就是全局索引。典型的 Oracle 支持全局索引，并且默認創建的都是全局索引。

2. 以 MySQL DBA 的視角來說，為什么要有全局索引？

從索引查找的效率上對比，分兩種情況：

如果 SQL 帶分區鍵查詢，分區裁剪后只需要查找少量幾個分區，則只需要對這幾個分區上的所有進行查找即可，可以降低系統資源的使用，效率更高；
如果 SQL 不帶分區鍵查詢，沒做分區裁剪，則本地索引需要對所有分區上的索引進行查找；同理，如果進行分區裁剪后還要查找多個分區也一樣，會使用更多的系統資源，效率更慢。全局索引則只需要對一個大的索引進行查找，顯然更節省成本。

3. Oracle 與 OB 主鍵的區別

Oracle 的主鍵約束 = 唯一索引+NOT NULL 約束；

OB 的數據結構上不同于 Oracle，Oracle 是堆表，索引上存的是數據行的指針，索引和數據是分開的。而 OB 是索引組織表，數據都在主鍵索引上，其他二級索引上存的是主鍵值。

因此對于分區表來說，OB 上每個分區的數據就是主鍵，主鍵必須是本地索引。然后由于主鍵有唯一約束，得保證全局唯一，而本地索引只能保證分區內唯一，怎么實現？不同的分區，分區鍵值一定是不一樣的，所以可以通過分區鍵的唯一來保證主鍵的全局唯一，這就是為什么 OB 上的分區表要求主鍵必須包含分區鍵。

同理 Oracle 為什么不要求主鍵必須包含分區鍵？因為 Oracle 的主鍵約束默認創建的是全局唯一索引，它本身就能保證全局唯一，不需要攜帶分區鍵實現。Oracle 如果要創建本地唯一索引，也是要求包含分區鍵的。

4. OB 上全局索引帶來的挑戰

OB 是一個分布式數據庫，全局索引和分區數據的分布位置肯定是不一樣的，因此如果查找全局索引后要回表，很容易產生分布式事務，如果要回表的數據量很大，需要多次 rpc 交互，查詢效率會下降很明顯。

通常 OB 上適合使用全局索引的場景是：

基數很大的索引（即效率很高），高頻的點查，并且 WHERE 條件中沒有分區鍵，無法進行分區裁剪；
非分布式架構。

三、分析過程

介紹完本地索引和全局索引，下面回到慢 SQL 的分析上。

1. 測試復現

遷移到 OB 上被驅動表 b 的相關索引是：PRIMARY KEY("DATA_ID", "POLICY_VALID_DATE")，Oracle 上對應的索引是：GLOBAL UNIQUE("DATA_ID")。

為了方便測試，在 OB 上再新建一張表，將兩個索引都建上：PRIMARY KEY("DATA_ID", "POLICY_VALID_DATE")，CONSTRAINT "UIDX_DATA_ID2" UNIQUE ("DATA_ID")。

復現情況如下：

被驅動表默認走主鍵，進行 nested-loop join，耗時 90 秒
加 hint /*+ index(b UIDX_DATA_ID2) */ 執行，被驅動表強制走全局唯一索引，進行 nested-loop join，耗時只需要 5 秒

注意：這里驅動表輸出 8 萬行，join 結果也是 8 萬行。

執行計劃對比，走主鍵的執行計劃：

==================================================================
|ID|OPERATOR                           |NAME    |EST. ROWS|COST  |
------------------------------------------------------------------
|0 |LIMIT                              |        |10       |237614|
|1 | PX COORDINATOR MERGE SORT         |        |10       |237614|
|2 |  EXCHANGE OUT DISTR               |:EX10001|10       |237565|
|3 |   LIMIT                           |        |10       |237565|
|4 |    TOP-N SORT                     |        |10       |237565|
|5 |     NESTED-LOOP JOIN              |        |353      |237420|
|6 |      EXCHANGE IN DISTR            |        |58       |234466|
|7 |       EXCHANGE OUT DISTR (BC2HOST)|:EX10000|58       |234297|
|8 |        PX PARTITION ITERATOR      |        |58       |234297|
|9 |         TABLE SCAN                |A       |58       |234297|
|10|      PX PARTITION ITERATOR        |        |7        |49    |
|11|       TABLE SCAN                  |B       |7        |49    |
==================================================================

Outputs & filters: 
-------------------------------------
  0 - output([A.ACT_ID(0x7e7d01e62040)], [A.DATA_ID(0x7e7d01e575a0)], [A.CMP_STATUS(0x7e7d01e5add0)], [A.CMP_TYPE_CD(0x7e7d01e59d00)], [A.CMP_TYPE_ID(0x7e7d01e62fb0)], [A.COUT_MODE(0x7e7d01e635c0)], [A.ASSOCIATE_ID(0x7e7d01e63bd0)], [A.ORGAN_ID(0x7e7d01e641e0)], [A.CUSTOMER_TYPE(0x7e7d01e647f0)], [A.CUSTOMER_NAME(0x7e7d01e64e00)], [A.RES_CODE(0x7e7d01e65410)], [A.CRT_DTTM(0x7e7d01e55070)], [A.USER_GRP_ID(0x7e7d01e65d40)], [A.USER_ID(0x7e7d01e66350)], [A.ACT_LIMIT_DATE(0x7e7d01e68970)], [A.CALL_CRT_DTTM(0x7e7d01e68f80)], [A.CONTACT_TEL1(0x7e7d01e69590)], [A.CONTACT_TEL2(0x7e7d01e77bb0)], [B.POLICY_PAY_ADDR(0x7e7d01e781c0)], [A.ORGAN3_ID(0x7e7d01e787d0)], [A.FAIL_COUT_NUM(0x7e7d01e78de0)], [A.SYS_TYPE(0x7e7d01e793f0)], [A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00)], [A.RESERVE_END_DTTM(0x7e7d01e7a010)], [B.SALE_CHANNEL(0x7e7d01e7a620)], [B.POLICY_NO(0x7e7d01e7ac30)], [B.FREE_LOOK_PERIOD(0x7e7d01e7b240)], [B.HOLDER_SEX(0x7e7d01e7b850)], [B.ORGAN4_ID(0x7e7d01e7be60)], [A.ASSIGN_DTTM(0x7e7d01e7c470)], [A.HANDLER_AGENT_CODE(0x7e7d01e7ca80)], [B.CANVASSER_CODE(0x7e7d01e7d090)], [B.CANVASSER_NAME(0x7e7d01e7d6a0)], [B.POLICY_OPE_DATE(0x7e7d01e7dcb0)], [A.APP_FLG(0x7e7d01e7e2c0)], [A.INTER_FLG(0x7e7d01e7e8d0)]), filter(nil), limit(?), offset(nil)
  1 - output([A.ACT_ID(0x7e7d01e62040)], [A.DATA_ID(0x7e7d01e575a0)], [A.CMP_STATUS(0x7e7d01e5add0)], [A.CMP_TYPE_CD(0x7e7d01e59d00)], [A.CMP_TYPE_ID(0x7e7d01e62fb0)], [A.COUT_MODE(0x7e7d01e635c0)], [A.ASSOCIATE_ID(0x7e7d01e63bd0)], [A.ORGAN_ID(0x7e7d01e641e0)], [A.CUSTOMER_TYPE(0x7e7d01e647f0)], [A.CUSTOMER_NAME(0x7e7d01e64e00)], [A.RES_CODE(0x7e7d01e65410)], [A.CRT_DTTM(0x7e7d01e55070)], [A.USER_GRP_ID(0x7e7d01e65d40)], [A.USER_ID(0x7e7d01e66350)], [A.ACT_LIMIT_DATE(0x7e7d01e68970)], [A.CALL_CRT_DTTM(0x7e7d01e68f80)], [A.CONTACT_TEL1(0x7e7d01e69590)], [A.CONTACT_TEL2(0x7e7d01e77bb0)], [B.POLICY_PAY_ADDR(0x7e7d01e781c0)], [A.ORGAN3_ID(0x7e7d01e787d0)], [A.FAIL_COUT_NUM(0x7e7d01e78de0)], [A.SYS_TYPE(0x7e7d01e793f0)], [A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00)], [A.RESERVE_END_DTTM(0x7e7d01e7a010)], [B.SALE_CHANNEL(0x7e7d01e7a620)], [B.POLICY_NO(0x7e7d01e7ac30)], [B.FREE_LOOK_PERIOD(0x7e7d01e7b240)], [B.HOLDER_SEX(0x7e7d01e7b850)], [B.ORGAN4_ID(0x7e7d01e7be60)], [A.ASSIGN_DTTM(0x7e7d01e7c470)], [A.HANDLER_AGENT_CODE(0x7e7d01e7ca80)], [B.CANVASSER_CODE(0x7e7d01e7d090)], [B.CANVASSER_NAME(0x7e7d01e7d6a0)], [B.POLICY_OPE_DATE(0x7e7d01e7dcb0)], [A.APP_FLG(0x7e7d01e7e2c0)], [A.INTER_FLG(0x7e7d01e7e8d0)]), filter(nil), sort_keys([A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00), ASC], [A.ACT_LIMIT_DATE(0x7e7d01e68970), ASC], [A.ACT_ID(0x7e7d01e62040), ASC])
  2 - output([A.ACT_ID(0x7e7d01e62040)], [A.DATA_ID(0x7e7d01e575a0)], [A.CMP_STATUS(0x7e7d01e5add0)], [A.CMP_TYPE_CD(0x7e7d01e59d00)], [A.CMP_TYPE_ID(0x7e7d01e62fb0)], [A.COUT_MODE(0x7e7d01e635c0)], [A.ASSOCIATE_ID(0x7e7d01e63bd0)], [A.ORGAN_ID(0x7e7d01e641e0)], [A.CUSTOMER_TYPE(0x7e7d01e647f0)], [A.CUSTOMER_NAME(0x7e7d01e64e00)], [A.RES_CODE(0x7e7d01e65410)], [A.CRT_DTTM(0x7e7d01e55070)], [A.USER_GRP_ID(0x7e7d01e65d40)], [A.USER_ID(0x7e7d01e66350)], [A.ACT_LIMIT_DATE(0x7e7d01e68970)], [A.CALL_CRT_DTTM(0x7e7d01e68f80)], [A.CONTACT_TEL1(0x7e7d01e69590)], [A.CONTACT_TEL2(0x7e7d01e77bb0)], [B.POLICY_PAY_ADDR(0x7e7d01e781c0)], [A.ORGAN3_ID(0x7e7d01e787d0)], [A.FAIL_COUT_NUM(0x7e7d01e78de0)], [A.SYS_TYPE(0x7e7d01e793f0)], [A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00)], [A.RESERVE_END_DTTM(0x7e7d01e7a010)], [B.SALE_CHANNEL(0x7e7d01e7a620)], [B.POLICY_NO(0x7e7d01e7ac30)], [B.FREE_LOOK_PERIOD(0x7e7d01e7b240)], [B.HOLDER_SEX(0x7e7d01e7b850)], [B.ORGAN4_ID(0x7e7d01e7be60)], [A.ASSIGN_DTTM(0x7e7d01e7c470)], [A.HANDLER_AGENT_CODE(0x7e7d01e7ca80)], [B.CANVASSER_CODE(0x7e7d01e7d090)], [B.CANVASSER_NAME(0x7e7d01e7d6a0)], [B.POLICY_OPE_DATE(0x7e7d01e7dcb0)], [A.APP_FLG(0x7e7d01e7e2c0)], [A.INTER_FLG(0x7e7d01e7e8d0)]), filter(nil), dop=1
  3 - output([A.ACT_ID(0x7e7d01e62040)], [A.DATA_ID(0x7e7d01e575a0)], [A.CMP_STATUS(0x7e7d01e5add0)], [A.CMP_TYPE_CD(0x7e7d01e59d00)], [A.CMP_TYPE_ID(0x7e7d01e62fb0)], [A.COUT_MODE(0x7e7d01e635c0)], [A.ASSOCIATE_ID(0x7e7d01e63bd0)], [A.ORGAN_ID(0x7e7d01e641e0)], [A.CUSTOMER_TYPE(0x7e7d01e647f0)], [A.CUSTOMER_NAME(0x7e7d01e64e00)], [A.RES_CODE(0x7e7d01e65410)], [A.CRT_DTTM(0x7e7d01e55070)], [A.USER_GRP_ID(0x7e7d01e65d40)], [A.USER_ID(0x7e7d01e66350)], [A.ACT_LIMIT_DATE(0x7e7d01e68970)], [A.CALL_CRT_DTTM(0x7e7d01e68f80)], [A.CONTACT_TEL1(0x7e7d01e69590)], [A.CONTACT_TEL2(0x7e7d01e77bb0)], [B.POLICY_PAY_ADDR(0x7e7d01e781c0)], [A.ORGAN3_ID(0x7e7d01e787d0)], [A.FAIL_COUT_NUM(0x7e7d01e78de0)], [A.SYS_TYPE(0x7e7d01e793f0)], [A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00)], [A.RESERVE_END_DTTM(0x7e7d01e7a010)], [B.SALE_CHANNEL(0x7e7d01e7a620)], [B.POLICY_NO(0x7e7d01e7ac30)], [B.FREE_LOOK_PERIOD(0x7e7d01e7b240)], [B.HOLDER_SEX(0x7e7d01e7b850)], [B.ORGAN4_ID(0x7e7d01e7be60)], [A.ASSIGN_DTTM(0x7e7d01e7c470)], [A.HANDLER_AGENT_CODE(0x7e7d01e7ca80)], [B.CANVASSER_CODE(0x7e7d01e7d090)], [B.CANVASSER_NAME(0x7e7d01e7d6a0)], [B.POLICY_OPE_DATE(0x7e7d01e7dcb0)], [A.APP_FLG(0x7e7d01e7e2c0)], [A.INTER_FLG(0x7e7d01e7e8d0)]), filter(nil), limit(?), offset(nil)
  4 - output([A.ACT_ID(0x7e7d01e62040)], [A.DATA_ID(0x7e7d01e575a0)], [A.CMP_STATUS(0x7e7d01e5add0)], [A.CMP_TYPE_CD(0x7e7d01e59d00)], [A.CMP_TYPE_ID(0x7e7d01e62fb0)], [A.COUT_MODE(0x7e7d01e635c0)], [A.ASSOCIATE_ID(0x7e7d01e63bd0)], [A.ORGAN_ID(0x7e7d01e641e0)], [A.CUSTOMER_TYPE(0x7e7d01e647f0)], [A.CUSTOMER_NAME(0x7e7d01e64e00)], [A.RES_CODE(0x7e7d01e65410)], [A.CRT_DTTM(0x7e7d01e55070)], [A.USER_GRP_ID(0x7e7d01e65d40)], [A.USER_ID(0x7e7d01e66350)], [A.ACT_LIMIT_DATE(0x7e7d01e68970)], [A.CALL_CRT_DTTM(0x7e7d01e68f80)], [A.CONTACT_TEL1(0x7e7d01e69590)], [A.CONTACT_TEL2(0x7e7d01e77bb0)], [B.POLICY_PAY_ADDR(0x7e7d01e781c0)], [A.ORGAN3_ID(0x7e7d01e787d0)], [A.FAIL_COUT_NUM(0x7e7d01e78de0)], [A.SYS_TYPE(0x7e7d01e793f0)], [A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00)], [A.RESERVE_END_DTTM(0x7e7d01e7a010)], [B.SALE_CHANNEL(0x7e7d01e7a620)], [B.POLICY_NO(0x7e7d01e7ac30)], [B.FREE_LOOK_PERIOD(0x7e7d01e7b240)], [B.HOLDER_SEX(0x7e7d01e7b850)], [B.ORGAN4_ID(0x7e7d01e7be60)], [A.ASSIGN_DTTM(0x7e7d01e7c470)], [A.HANDLER_AGENT_CODE(0x7e7d01e7ca80)], [B.CANVASSER_CODE(0x7e7d01e7d090)], [B.CANVASSER_NAME(0x7e7d01e7d6a0)], [B.POLICY_OPE_DATE(0x7e7d01e7dcb0)], [A.APP_FLG(0x7e7d01e7e2c0)], [A.INTER_FLG(0x7e7d01e7e8d0)]), filter(nil), sort_keys([A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00), ASC], [A.ACT_LIMIT_DATE(0x7e7d01e68970), ASC], [A.ACT_ID(0x7e7d01e62040), ASC]), topn(?)
  5 - output([A.ACT_ID(0x7e7d01e62040)], [A.DATA_ID(0x7e7d01e575a0)], [A.CMP_STATUS(0x7e7d01e5add0)], [A.CMP_TYPE_CD(0x7e7d01e59d00)], [A.CMP_TYPE_ID(0x7e7d01e62fb0)], [A.COUT_MODE(0x7e7d01e635c0)], [A.ASSOCIATE_ID(0x7e7d01e63bd0)], [A.ORGAN_ID(0x7e7d01e641e0)], [A.CUSTOMER_TYPE(0x7e7d01e647f0)], [A.CUSTOMER_NAME(0x7e7d01e64e00)], [A.RES_CODE(0x7e7d01e65410)], [A.CRT_DTTM(0x7e7d01e55070)], [A.USER_GRP_ID(0x7e7d01e65d40)], [A.USER_ID(0x7e7d01e66350)], [A.ACT_LIMIT_DATE(0x7e7d01e68970)], [A.CALL_CRT_DTTM(0x7e7d01e68f80)], [A.CONTACT_TEL1(0x7e7d01e69590)], [A.CONTACT_TEL2(0x7e7d01e77bb0)], [B.POLICY_PAY_ADDR(0x7e7d01e781c0)], [A.ORGAN3_ID(0x7e7d01e787d0)], [A.FAIL_COUT_NUM(0x7e7d01e78de0)], [A.SYS_TYPE(0x7e7d01e793f0)], [A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00)], [A.RESERVE_END_DTTM(0x7e7d01e7a010)], [B.SALE_CHANNEL(0x7e7d01e7a620)], [B.POLICY_NO(0x7e7d01e7ac30)], [B.FREE_LOOK_PERIOD(0x7e7d01e7b240)], [B.HOLDER_SEX(0x7e7d01e7b850)], [B.ORGAN4_ID(0x7e7d01e7be60)], [A.ASSIGN_DTTM(0x7e7d01e7c470)], [A.HANDLER_AGENT_CODE(0x7e7d01e7ca80)], [B.CANVASSER_CODE(0x7e7d01e7d090)], [B.CANVASSER_NAME(0x7e7d01e7d6a0)], [B.POLICY_OPE_DATE(0x7e7d01e7dcb0)], [A.APP_FLG(0x7e7d01e7e2c0)], [A.INTER_FLG(0x7e7d01e7e8d0)]), filter(nil), 
      conds(nil), nl_params_([A.DATA_ID(0x7e7d01e575a0)]), batch_join=false
  6 - output([A.CRT_DTTM(0x7e7d01e55070)], [A.DATA_ID(0x7e7d01e575a0)], [A.CMP_TYPE_CD(0x7e7d01e59d00)], [A.CMP_STATUS(0x7e7d01e5add0)], [A.ACT_ID(0x7e7d01e62040)], [A.CMP_TYPE_ID(0x7e7d01e62fb0)], [A.COUT_MODE(0x7e7d01e635c0)], [A.ASSOCIATE_ID(0x7e7d01e63bd0)], [A.ORGAN_ID(0x7e7d01e641e0)], [A.CUSTOMER_TYPE(0x7e7d01e647f0)], [A.CUSTOMER_NAME(0x7e7d01e64e00)], [A.RES_CODE(0x7e7d01e65410)], [A.USER_GRP_ID(0x7e7d01e65d40)], [A.USER_ID(0x7e7d01e66350)], [A.ACT_LIMIT_DATE(0x7e7d01e68970)], [A.CALL_CRT_DTTM(0x7e7d01e68f80)], [A.CONTACT_TEL1(0x7e7d01e69590)], [A.CONTACT_TEL2(0x7e7d01e77bb0)], [A.ORGAN3_ID(0x7e7d01e787d0)], [A.FAIL_COUT_NUM(0x7e7d01e78de0)], [A.SYS_TYPE(0x7e7d01e793f0)], [A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00)], [A.RESERVE_END_DTTM(0x7e7d01e7a010)], [A.ASSIGN_DTTM(0x7e7d01e7c470)], [A.HANDLER_AGENT_CODE(0x7e7d01e7ca80)], [A.APP_FLG(0x7e7d01e7e2c0)], [A.INTER_FLG(0x7e7d01e7e8d0)]), filter(nil)
  7 - output([A.CRT_DTTM(0x7e7d01e55070)], [A.DATA_ID(0x7e7d01e575a0)], [A.CMP_TYPE_CD(0x7e7d01e59d00)], [A.CMP_STATUS(0x7e7d01e5add0)], [A.ACT_ID(0x7e7d01e62040)], [A.CMP_TYPE_ID(0x7e7d01e62fb0)], [A.COUT_MODE(0x7e7d01e635c0)], [A.ASSOCIATE_ID(0x7e7d01e63bd0)], [A.ORGAN_ID(0x7e7d01e641e0)], [A.CUSTOMER_TYPE(0x7e7d01e647f0)], [A.CUSTOMER_NAME(0x7e7d01e64e00)], [A.RES_CODE(0x7e7d01e65410)], [A.USER_GRP_ID(0x7e7d01e65d40)], [A.USER_ID(0x7e7d01e66350)], [A.ACT_LIMIT_DATE(0x7e7d01e68970)], [A.CALL_CRT_DTTM(0x7e7d01e68f80)], [A.CONTACT_TEL1(0x7e7d01e69590)], [A.CONTACT_TEL2(0x7e7d01e77bb0)], [A.ORGAN3_ID(0x7e7d01e787d0)], [A.FAIL_COUT_NUM(0x7e7d01e78de0)], [A.SYS_TYPE(0x7e7d01e793f0)], [A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00)], [A.RESERVE_END_DTTM(0x7e7d01e7a010)], [A.ASSIGN_DTTM(0x7e7d01e7c470)], [A.HANDLER_AGENT_CODE(0x7e7d01e7ca80)], [A.APP_FLG(0x7e7d01e7e2c0)], [A.INTER_FLG(0x7e7d01e7e8d0)]), filter(nil), dop=1
  8 - output([A.CRT_DTTM(0x7e7d01e55070)], [A.DATA_ID(0x7e7d01e575a0)], [A.CMP_TYPE_CD(0x7e7d01e59d00)], [A.CMP_STATUS(0x7e7d01e5add0)], [A.ACT_ID(0x7e7d01e62040)], [A.CMP_TYPE_ID(0x7e7d01e62fb0)], [A.COUT_MODE(0x7e7d01e635c0)], [A.ASSOCIATE_ID(0x7e7d01e63bd0)], [A.ORGAN_ID(0x7e7d01e641e0)], [A.CUSTOMER_TYPE(0x7e7d01e647f0)], [A.CUSTOMER_NAME(0x7e7d01e64e00)], [A.RES_CODE(0x7e7d01e65410)], [A.USER_GRP_ID(0x7e7d01e65d40)], [A.USER_ID(0x7e7d01e66350)], [A.ACT_LIMIT_DATE(0x7e7d01e68970)], [A.CALL_CRT_DTTM(0x7e7d01e68f80)], [A.CONTACT_TEL1(0x7e7d01e69590)], [A.CONTACT_TEL2(0x7e7d01e77bb0)], [A.ORGAN3_ID(0x7e7d01e787d0)], [A.FAIL_COUT_NUM(0x7e7d01e78de0)], [A.SYS_TYPE(0x7e7d01e793f0)], [A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00)], [A.RESERVE_END_DTTM(0x7e7d01e7a010)], [A.ASSIGN_DTTM(0x7e7d01e7c470)], [A.HANDLER_AGENT_CODE(0x7e7d01e7ca80)], [A.APP_FLG(0x7e7d01e7e2c0)], [A.INTER_FLG(0x7e7d01e7e8d0)]), filter(nil), 
      force partition granule, asc.
  9 - output([A.CRT_DTTM(0x7e7d01e55070)], [A.DATA_ID(0x7e7d01e575a0)], [A.CMP_TYPE_CD(0x7e7d01e59d00)], [A.CMP_STATUS(0x7e7d01e5add0)], [A.ACT_ID(0x7e7d01e62040)], [A.CMP_TYPE_ID(0x7e7d01e62fb0)], [A.COUT_MODE(0x7e7d01e635c0)], [A.ASSOCIATE_ID(0x7e7d01e63bd0)], [A.ORGAN_ID(0x7e7d01e641e0)], [A.CUSTOMER_TYPE(0x7e7d01e647f0)], [A.CUSTOMER_NAME(0x7e7d01e64e00)], [A.RES_CODE(0x7e7d01e65410)], [A.USER_GRP_ID(0x7e7d01e65d40)], [A.USER_ID(0x7e7d01e66350)], [A.ACT_LIMIT_DATE(0x7e7d01e68970)], [A.CALL_CRT_DTTM(0x7e7d01e68f80)], [A.CONTACT_TEL1(0x7e7d01e69590)], [A.CONTACT_TEL2(0x7e7d01e77bb0)], [A.ORGAN3_ID(0x7e7d01e787d0)], [A.FAIL_COUT_NUM(0x7e7d01e78de0)], [A.SYS_TYPE(0x7e7d01e793f0)], [A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00)], [A.RESERVE_END_DTTM(0x7e7d01e7a010)], [A.ASSIGN_DTTM(0x7e7d01e7c470)], [A.HANDLER_AGENT_CODE(0x7e7d01e7ca80)], [A.APP_FLG(0x7e7d01e7e2c0)], [A.INTER_FLG(0x7e7d01e7e8d0)]), filter([A.CMP_TYPE_CD(0x7e7d01e59d00) = ?(0x7e7d01e595e0)], [(T_OP_IS, A.POOL_STATUS(0x7e7d01e58c10), NULL, 0)(0x7e7d01e58240)], [A.CMP_STATUS(0x7e7d01e5add0) != ?(0x7e7d01e5a110)]), 
      access([A.CRT_DTTM(0x7e7d01e55070)], [A.DATA_ID(0x7e7d01e575a0)], [A.POOL_STATUS(0x7e7d01e58c10)], [A.CMP_TYPE_CD(0x7e7d01e59d00)], [A.CMP_STATUS(0x7e7d01e5add0)], [A.ACT_ID(0x7e7d01e62040)], [A.CMP_TYPE_ID(0x7e7d01e62fb0)], [A.COUT_MODE(0x7e7d01e635c0)], [A.ASSOCIATE_ID(0x7e7d01e63bd0)], [A.ORGAN_ID(0x7e7d01e641e0)], [A.CUSTOMER_TYPE(0x7e7d01e647f0)], [A.CUSTOMER_NAME(0x7e7d01e64e00)], [A.RES_CODE(0x7e7d01e65410)], [A.USER_GRP_ID(0x7e7d01e65d40)], [A.USER_ID(0x7e7d01e66350)], [A.ACT_LIMIT_DATE(0x7e7d01e68970)], [A.CALL_CRT_DTTM(0x7e7d01e68f80)], [A.CONTACT_TEL1(0x7e7d01e69590)], [A.CONTACT_TEL2(0x7e7d01e77bb0)], [A.ORGAN3_ID(0x7e7d01e787d0)], [A.FAIL_COUT_NUM(0x7e7d01e78de0)], [A.SYS_TYPE(0x7e7d01e793f0)], [A.RESERVE_BEGIN_DTTM(0x7e7d01e79a00)], [A.RESERVE_END_DTTM(0x7e7d01e7a010)], [A.ASSIGN_DTTM(0x7e7d01e7c470)], [A.HANDLER_AGENT_CODE(0x7e7d01e7ca80)], [A.APP_FLG(0x7e7d01e7e2c0)], [A.INTER_FLG(0x7e7d01e7e8d0)]), partitions(p[116-117]), 
      is_index_back=false, filter_before_indexback[false,false,false], 
      range_key([A.CRT_DTTM(0x7e7d01e55070)], [A.__pk_increment(0x7e7d01f795d0)]), range(2023-09-15 04:37:49,MIN ; 2023-10-14 04:37:49,MIN), 
      range_cond([A.CRT_DTTM(0x7e7d01e55070) >= ?(0x7e7d01e5c560)], [A.CRT_DTTM(0x7e7d01e55070) < ?(0x7e7d01e5fb10)])
  10 - output([B.POLICY_PAY_ADDR(0x7e7d01e781c0)], [B.SALE_CHANNEL(0x7e7d01e7a620)], [B.POLICY_NO(0x7e7d01e7ac30)], [B.FREE_LOOK_PERIOD(0x7e7d01e7b240)], [B.HOLDER_SEX(0x7e7d01e7b850)], [B.ORGAN4_ID(0x7e7d01e7be60)], [B.CANVASSER_CODE(0x7e7d01e7d090)], [B.CANVASSER_NAME(0x7e7d01e7d6a0)], [B.POLICY_OPE_DATE(0x7e7d01e7dcb0)]), filter(nil), 
      access all, force partition granule.
  11 - output([B.POLICY_PAY_ADDR(0x7e7d01e781c0)], [B.SALE_CHANNEL(0x7e7d01e7a620)], [B.POLICY_NO(0x7e7d01e7ac30)], [B.FREE_LOOK_PERIOD(0x7e7d01e7b240)], [B.HOLDER_SEX(0x7e7d01e7b850)], [B.ORGAN4_ID(0x7e7d01e7be60)], [B.CANVASSER_CODE(0x7e7d01e7d090)], [B.CANVASSER_NAME(0x7e7d01e7d6a0)], [B.POLICY_OPE_DATE(0x7e7d01e7dcb0)]), filter(nil), 
      access([B.POLICY_PAY_ADDR(0x7e7d01e781c0)], [B.SALE_CHANNEL(0x7e7d01e7a620)], [B.POLICY_NO(0x7e7d01e7ac30)], [B.FREE_LOOK_PERIOD(0x7e7d01e7b240)], [B.HOLDER_SEX(0x7e7d01e7b850)], [B.ORGAN4_ID(0x7e7d01e7be60)], [B.CANVASSER_CODE(0x7e7d01e7d090)], [B.CANVASSER_NAME(0x7e7d01e7d6a0)], [B.POLICY_OPE_DATE(0x7e7d01e7dcb0)]), partitions(p[0-129]), 
      is_index_back=false, 
      range_key([B.DATA_ID(0x7e7d01e57890)], [B.POLICY_VALID_DATE(0x7e7d01e56550)]), range(MIN ; MAX), 
      range_cond([? = B.DATA_ID(0x7e7d01e57890)(0x7e887f48c800)])

Used Hint:
-------------------------------------
  /*+
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$2" ("LIFE.A"@"SEL$2" "LIFE.B"@"SEL$2" ))
      USE_NL(@"SEL$2" ("LIFE.B"@"SEL$2" ))
      PQ_DISTRIBUTE(@"SEL$2" ("LIFE.B"@"SEL$2" ) BC2HOST NONE)
      NO_USE_NL_MATERIALIZATION(@"SEL$2" ("LIFE.B"@"SEL$2" ))
      FULL(@"SEL$2" "LIFE.A"@"SEL$2")
      FULL(@"SEL$2" "LIFE.B"@"SEL$2")
      END_OUTLINE_DATA
  */

Plan Type:
-------------------------------------
DISTRIBUTED

Optimization Info:
-------------------------------------
A:table_rows:11907770, physical_range_rows:595388, logical_range_rows:595388, index_back_rows:0, output_rows:57, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[IDX_LI_COUT_ACTIVITY_DATAID,IDX_LI_COUT_ACTIVIT_RESE,IDX_T_LI_COUT_ACTIVITY,IDX_T_LI_COUT_ACTIVITY_TEST2,T_LI_COUT_ACTIVITY], pruned_index_name[PK_T_LI_COUT_ACTIVITY,IDX_CUSTOMER_ID_CARD,IDX_LI_COUT_ACT_NEXTDTTM,IDX_LI_COUT_ACTIVITY_CALLCRT,IDX_LI_COUT_ACTIVITY_ASID,IDX_LI_COUT_ACTIVITY_BDATE,IDX_LI_COUT_ACTIVITY_CDT,IDX_LI_COUT_ACTIVITY_CPS,IDX_LI_COUT_ACTIVITY_ENDDATE,IDX_LI_COUT_ACTIVITY_LASTDTTM,IDX_LI_COUT_ACTIVITY_LDT,IDX_LI_COUT_ACTIVITY_LRT,IDX_LI_COUT_ACTIVITY_OWN,IDX_LI_COUT_ACT_FIRSTACTID,IDX_T_LI_COUT_ACTIVITY_SHUSHUI,IDX_GLO_CMP_TYPE_ID,IDX_GLO_ASSOC_CMP]
B:table_rows:114906166, physical_range_rows:6, logical_range_rows:6, index_back_rows:0, output_rows:6, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_with_indexback

走全局唯一索引的執行計劃：

=====================================================================
|ID|OPERATOR                      |NAME            |EST. ROWS|COST  |
---------------------------------------------------------------------
|0 |LIMIT                         |                |10       |235743|
|1 | PX COORDINATOR MERGE SORT    |                |10       |235743|
|2 |  EXCHANGE OUT DISTR          |:EX10000        |10       |235694|
|3 |   LIMIT                      |                |10       |235694|
|4 |    TOP-N SORT                |                |10       |235694|
|5 |     PX PARTITION ITERATOR    |                |55       |235668|
|6 |      NESTED-LOOP JOIN        |                |55       |235668|
|7 |       TABLE SCAN             |A               |58       |234297|
|8 |       TABLE LOOKUP           |B               |1        |23    |
|9 |        DISTRIBUTED TABLE SCAN|B(UIDX_DATA_ID2)|1        |12    |
=====================================================================

Outputs & filters: 
-------------------------------------
  0 - output([A.ACT_ID(0x7f03a5ae63e0)], [A.DATA_ID(0x7f03a5adb940)], [A.CMP_STATUS(0x7f03a5adf170)], [A.CMP_TYPE_CD(0x7f03a5ade0a0)], [A.CMP_TYPE_ID(0x7f03a5ae7350)], [A.COUT_MODE(0x7f03a5ae7960)], [A.ASSOCIATE_ID(0x7f03a5ae7f70)], [A.ORGAN_ID(0x7f03a5ae8580)], [A.CUSTOMER_TYPE(0x7f03a5ae8b90)], [A.CUSTOMER_NAME(0x7f03a5ae91a0)], [A.RES_CODE(0x7f03a5ae97b0)], [A.CRT_DTTM(0x7f03a5ad9410)], [A.USER_GRP_ID(0x7f03a5aea0e0)], [A.USER_ID(0x7f03a5aea6f0)], [A.ACT_LIMIT_DATE(0x7f03a5aecd10)], [A.CALL_CRT_DTTM(0x7f03a5aed320)], [A.CONTACT_TEL1(0x7f03a5aed930)], [A.CONTACT_TEL2(0x7f03a5afbf50)], [B.POLICY_PAY_ADDR(0x7f03a5afc560)], [A.ORGAN3_ID(0x7f03a5afcb70)], [A.FAIL_COUT_NUM(0x7f03a5afd180)], [A.SYS_TYPE(0x7f03a5afd790)], [A.RESERVE_BEGIN_DTTM(0x7f03a5afdda0)], [A.RESERVE_END_DTTM(0x7f03a5afe3b0)], [B.SALE_CHANNEL(0x7f03a5afe9c0)], [B.POLICY_NO(0x7f03a5afefd0)], [B.FREE_LOOK_PERIOD(0x7f03a5aff5e0)], [B.HOLDER_SEX(0x7f03a5affbf0)], [B.ORGAN4_ID(0x7f03a5b00200)], [A.ASSIGN_DTTM(0x7f03a5b00810)], [A.HANDLER_AGENT_CODE(0x7f03a5b00e20)], [B.CANVASSER_CODE(0x7f03a5b01430)], [B.CANVASSER_NAME(0x7f03a5b01a40)], [B.POLICY_OPE_DATE(0x7f03a5b02050)], [A.APP_FLG(0x7f03a5b02660)], [A.INTER_FLG(0x7f03a5b02c70)]), filter(nil), limit(?), offset(nil)
  1 - output([A.ACT_ID(0x7f03a5ae63e0)], [A.DATA_ID(0x7f03a5adb940)], [A.CMP_STATUS(0x7f03a5adf170)], [A.CMP_TYPE_CD(0x7f03a5ade0a0)], [A.CMP_TYPE_ID(0x7f03a5ae7350)], [A.COUT_MODE(0x7f03a5ae7960)], [A.ASSOCIATE_ID(0x7f03a5ae7f70)], [A.ORGAN_ID(0x7f03a5ae8580)], [A.CUSTOMER_TYPE(0x7f03a5ae8b90)], [A.CUSTOMER_NAME(0x7f03a5ae91a0)], [A.RES_CODE(0x7f03a5ae97b0)], [A.CRT_DTTM(0x7f03a5ad9410)], [A.USER_GRP_ID(0x7f03a5aea0e0)], [A.USER_ID(0x7f03a5aea6f0)], [A.ACT_LIMIT_DATE(0x7f03a5aecd10)], [A.CALL_CRT_DTTM(0x7f03a5aed320)], [A.CONTACT_TEL1(0x7f03a5aed930)], [A.CONTACT_TEL2(0x7f03a5afbf50)], [B.POLICY_PAY_ADDR(0x7f03a5afc560)], [A.ORGAN3_ID(0x7f03a5afcb70)], [A.FAIL_COUT_NUM(0x7f03a5afd180)], [A.SYS_TYPE(0x7f03a5afd790)], [A.RESERVE_BEGIN_DTTM(0x7f03a5afdda0)], [A.RESERVE_END_DTTM(0x7f03a5afe3b0)], [B.SALE_CHANNEL(0x7f03a5afe9c0)], [B.POLICY_NO(0x7f03a5afefd0)], [B.FREE_LOOK_PERIOD(0x7f03a5aff5e0)], [B.HOLDER_SEX(0x7f03a5affbf0)], [B.ORGAN4_ID(0x7f03a5b00200)], [A.ASSIGN_DTTM(0x7f03a5b00810)], [A.HANDLER_AGENT_CODE(0x7f03a5b00e20)], [B.CANVASSER_CODE(0x7f03a5b01430)], [B.CANVASSER_NAME(0x7f03a5b01a40)], [B.POLICY_OPE_DATE(0x7f03a5b02050)], [A.APP_FLG(0x7f03a5b02660)], [A.INTER_FLG(0x7f03a5b02c70)]), filter(nil), sort_keys([A.RESERVE_BEGIN_DTTM(0x7f03a5afdda0), ASC], [A.ACT_LIMIT_DATE(0x7f03a5aecd10), ASC], [A.ACT_ID(0x7f03a5ae63e0), ASC])
  2 - output([A.ACT_ID(0x7f03a5ae63e0)], [A.DATA_ID(0x7f03a5adb940)], [A.CMP_STATUS(0x7f03a5adf170)], [A.CMP_TYPE_CD(0x7f03a5ade0a0)], [A.CMP_TYPE_ID(0x7f03a5ae7350)], [A.COUT_MODE(0x7f03a5ae7960)], [A.ASSOCIATE_ID(0x7f03a5ae7f70)], [A.ORGAN_ID(0x7f03a5ae8580)], [A.CUSTOMER_TYPE(0x7f03a5ae8b90)], [A.CUSTOMER_NAME(0x7f03a5ae91a0)], [A.RES_CODE(0x7f03a5ae97b0)], [A.CRT_DTTM(0x7f03a5ad9410)], [A.USER_GRP_ID(0x7f03a5aea0e0)], [A.USER_ID(0x7f03a5aea6f0)], [A.ACT_LIMIT_DATE(0x7f03a5aecd10)], [A.CALL_CRT_DTTM(0x7f03a5aed320)], [A.CONTACT_TEL1(0x7f03a5aed930)], [A.CONTACT_TEL2(0x7f03a5afbf50)], [B.POLICY_PAY_ADDR(0x7f03a5afc560)], [A.ORGAN3_ID(0x7f03a5afcb70)], [A.FAIL_COUT_NUM(0x7f03a5afd180)], [A.SYS_TYPE(0x7f03a5afd790)], [A.RESERVE_BEGIN_DTTM(0x7f03a5afdda0)], [A.RESERVE_END_DTTM(0x7f03a5afe3b0)], [B.SALE_CHANNEL(0x7f03a5afe9c0)], [B.POLICY_NO(0x7f03a5afefd0)], [B.FREE_LOOK_PERIOD(0x7f03a5aff5e0)], [B.HOLDER_SEX(0x7f03a5affbf0)], [B.ORGAN4_ID(0x7f03a5b00200)], [A.ASSIGN_DTTM(0x7f03a5b00810)], [A.HANDLER_AGENT_CODE(0x7f03a5b00e20)], [B.CANVASSER_CODE(0x7f03a5b01430)], [B.CANVASSER_NAME(0x7f03a5b01a40)], [B.POLICY_OPE_DATE(0x7f03a5b02050)], [A.APP_FLG(0x7f03a5b02660)], [A.INTER_FLG(0x7f03a5b02c70)]), filter(nil), dop=1
  3 - output([A.ACT_ID(0x7f03a5ae63e0)], [A.DATA_ID(0x7f03a5adb940)], [A.CMP_STATUS(0x7f03a5adf170)], [A.CMP_TYPE_CD(0x7f03a5ade0a0)], [A.CMP_TYPE_ID(0x7f03a5ae7350)], [A.COUT_MODE(0x7f03a5ae7960)], [A.ASSOCIATE_ID(0x7f03a5ae7f70)], [A.ORGAN_ID(0x7f03a5ae8580)], [A.CUSTOMER_TYPE(0x7f03a5ae8b90)], [A.CUSTOMER_NAME(0x7f03a5ae91a0)], [A.RES_CODE(0x7f03a5ae97b0)], [A.CRT_DTTM(0x7f03a5ad9410)], [A.USER_GRP_ID(0x7f03a5aea0e0)], [A.USER_ID(0x7f03a5aea6f0)], [A.ACT_LIMIT_DATE(0x7f03a5aecd10)], [A.CALL_CRT_DTTM(0x7f03a5aed320)], [A.CONTACT_TEL1(0x7f03a5aed930)], [A.CONTACT_TEL2(0x7f03a5afbf50)], [B.POLICY_PAY_ADDR(0x7f03a5afc560)], [A.ORGAN3_ID(0x7f03a5afcb70)], [A.FAIL_COUT_NUM(0x7f03a5afd180)], [A.SYS_TYPE(0x7f03a5afd790)], [A.RESERVE_BEGIN_DTTM(0x7f03a5afdda0)], [A.RESERVE_END_DTTM(0x7f03a5afe3b0)], [B.SALE_CHANNEL(0x7f03a5afe9c0)], [B.POLICY_NO(0x7f03a5afefd0)], [B.FREE_LOOK_PERIOD(0x7f03a5aff5e0)], [B.HOLDER_SEX(0x7f03a5affbf0)], [B.ORGAN4_ID(0x7f03a5b00200)], [A.ASSIGN_DTTM(0x7f03a5b00810)], [A.HANDLER_AGENT_CODE(0x7f03a5b00e20)], [B.CANVASSER_CODE(0x7f03a5b01430)], [B.CANVASSER_NAME(0x7f03a5b01a40)], [B.POLICY_OPE_DATE(0x7f03a5b02050)], [A.APP_FLG(0x7f03a5b02660)], [A.INTER_FLG(0x7f03a5b02c70)]), filter(nil), limit(?), offset(nil)
  4 - output([A.ACT_ID(0x7f03a5ae63e0)], [A.DATA_ID(0x7f03a5adb940)], [A.CMP_STATUS(0x7f03a5adf170)], [A.CMP_TYPE_CD(0x7f03a5ade0a0)], [A.CMP_TYPE_ID(0x7f03a5ae7350)], [A.COUT_MODE(0x7f03a5ae7960)], [A.ASSOCIATE_ID(0x7f03a5ae7f70)], [A.ORGAN_ID(0x7f03a5ae8580)], [A.CUSTOMER_TYPE(0x7f03a5ae8b90)], [A.CUSTOMER_NAME(0x7f03a5ae91a0)], [A.RES_CODE(0x7f03a5ae97b0)], [A.CRT_DTTM(0x7f03a5ad9410)], [A.USER_GRP_ID(0x7f03a5aea0e0)], [A.USER_ID(0x7f03a5aea6f0)], [A.ACT_LIMIT_DATE(0x7f03a5aecd10)], [A.CALL_CRT_DTTM(0x7f03a5aed320)], [A.CONTACT_TEL1(0x7f03a5aed930)], [A.CONTACT_TEL2(0x7f03a5afbf50)], [B.POLICY_PAY_ADDR(0x7f03a5afc560)], [A.ORGAN3_ID(0x7f03a5afcb70)], [A.FAIL_COUT_NUM(0x7f03a5afd180)], [A.SYS_TYPE(0x7f03a5afd790)], [A.RESERVE_BEGIN_DTTM(0x7f03a5afdda0)], [A.RESERVE_END_DTTM(0x7f03a5afe3b0)], [B.SALE_CHANNEL(0x7f03a5afe9c0)], [B.POLICY_NO(0x7f03a5afefd0)], [B.FREE_LOOK_PERIOD(0x7f03a5aff5e0)], [B.HOLDER_SEX(0x7f03a5affbf0)], [B.ORGAN4_ID(0x7f03a5b00200)], [A.ASSIGN_DTTM(0x7f03a5b00810)], [A.HANDLER_AGENT_CODE(0x7f03a5b00e20)], [B.CANVASSER_CODE(0x7f03a5b01430)], [B.CANVASSER_NAME(0x7f03a5b01a40)], [B.POLICY_OPE_DATE(0x7f03a5b02050)], [A.APP_FLG(0x7f03a5b02660)], [A.INTER_FLG(0x7f03a5b02c70)]), filter(nil), sort_keys([A.RESERVE_BEGIN_DTTM(0x7f03a5afdda0), ASC], [A.ACT_LIMIT_DATE(0x7f03a5aecd10), ASC], [A.ACT_ID(0x7f03a5ae63e0), ASC]), topn(?)
  5 - output([A.ACT_ID(0x7f03a5ae63e0)], [A.DATA_ID(0x7f03a5adb940)], [A.CMP_STATUS(0x7f03a5adf170)], [A.CMP_TYPE_CD(0x7f03a5ade0a0)], [A.CMP_TYPE_ID(0x7f03a5ae7350)], [A.COUT_MODE(0x7f03a5ae7960)], [A.ASSOCIATE_ID(0x7f03a5ae7f70)], [A.ORGAN_ID(0x7f03a5ae8580)], [A.CUSTOMER_TYPE(0x7f03a5ae8b90)], [A.CUSTOMER_NAME(0x7f03a5ae91a0)], [A.RES_CODE(0x7f03a5ae97b0)], [A.CRT_DTTM(0x7f03a5ad9410)], [A.USER_GRP_ID(0x7f03a5aea0e0)], [A.USER_ID(0x7f03a5aea6f0)], [A.ACT_LIMIT_DATE(0x7f03a5aecd10)], [A.CALL_CRT_DTTM(0x7f03a5aed320)], [A.CONTACT_TEL1(0x7f03a5aed930)], [A.CONTACT_TEL2(0x7f03a5afbf50)], [B.POLICY_PAY_ADDR(0x7f03a5afc560)], [A.ORGAN3_ID(0x7f03a5afcb70)], [A.FAIL_COUT_NUM(0x7f03a5afd180)], [A.SYS_TYPE(0x7f03a5afd790)], [A.RESERVE_BEGIN_DTTM(0x7f03a5afdda0)], [A.RESERVE_END_DTTM(0x7f03a5afe3b0)], [B.SALE_CHANNEL(0x7f03a5afe9c0)], [B.POLICY_NO(0x7f03a5afefd0)], [B.FREE_LOOK_PERIOD(0x7f03a5aff5e0)], [B.HOLDER_SEX(0x7f03a5affbf0)], [B.ORGAN4_ID(0x7f03a5b00200)], [A.ASSIGN_DTTM(0x7f03a5b00810)], [A.HANDLER_AGENT_CODE(0x7f03a5b00e20)], [B.CANVASSER_CODE(0x7f03a5b01430)], [B.CANVASSER_NAME(0x7f03a5b01a40)], [B.POLICY_OPE_DATE(0x7f03a5b02050)], [A.APP_FLG(0x7f03a5b02660)], [A.INTER_FLG(0x7f03a5b02c70)]), filter(nil), 
      partition wise, force partition granule, asc.
  6 - output([A.ACT_ID(0x7f03a5ae63e0)], [A.DATA_ID(0x7f03a5adb940)], [A.CMP_STATUS(0x7f03a5adf170)], [A.CMP_TYPE_CD(0x7f03a5ade0a0)], [A.CMP_TYPE_ID(0x7f03a5ae7350)], [A.COUT_MODE(0x7f03a5ae7960)], [A.ASSOCIATE_ID(0x7f03a5ae7f70)], [A.ORGAN_ID(0x7f03a5ae8580)], [A.CUSTOMER_TYPE(0x7f03a5ae8b90)], [A.CUSTOMER_NAME(0x7f03a5ae91a0)], [A.RES_CODE(0x7f03a5ae97b0)], [A.CRT_DTTM(0x7f03a5ad9410)], [A.USER_GRP_ID(0x7f03a5aea0e0)], [A.USER_ID(0x7f03a5aea6f0)], [A.ACT_LIMIT_DATE(0x7f03a5aecd10)], [A.CALL_CRT_DTTM(0x7f03a5aed320)], [A.CONTACT_TEL1(0x7f03a5aed930)], [A.CONTACT_TEL2(0x7f03a5afbf50)], [B.POLICY_PAY_ADDR(0x7f03a5afc560)], [A.ORGAN3_ID(0x7f03a5afcb70)], [A.FAIL_COUT_NUM(0x7f03a5afd180)], [A.SYS_TYPE(0x7f03a5afd790)], [A.RESERVE_BEGIN_DTTM(0x7f03a5afdda0)], [A.RESERVE_END_DTTM(0x7f03a5afe3b0)], [B.SALE_CHANNEL(0x7f03a5afe9c0)], [B.POLICY_NO(0x7f03a5afefd0)], [B.FREE_LOOK_PERIOD(0x7f03a5aff5e0)], [B.HOLDER_SEX(0x7f03a5affbf0)], [B.ORGAN4_ID(0x7f03a5b00200)], [A.ASSIGN_DTTM(0x7f03a5b00810)], [A.HANDLER_AGENT_CODE(0x7f03a5b00e20)], [B.CANVASSER_CODE(0x7f03a5b01430)], [B.CANVASSER_NAME(0x7f03a5b01a40)], [B.POLICY_OPE_DATE(0x7f03a5b02050)], [A.APP_FLG(0x7f03a5b02660)], [A.INTER_FLG(0x7f03a5b02c70)]), filter(nil), 
      conds(nil), nl_params_([A.DATA_ID(0x7f03a5adb940)]), batch_join=false
  7 - output([A.CRT_DTTM(0x7f03a5ad9410)], [A.DATA_ID(0x7f03a5adb940)], [A.CMP_TYPE_CD(0x7f03a5ade0a0)], [A.CMP_STATUS(0x7f03a5adf170)], [A.ACT_ID(0x7f03a5ae63e0)], [A.CMP_TYPE_ID(0x7f03a5ae7350)], [A.COUT_MODE(0x7f03a5ae7960)], [A.ASSOCIATE_ID(0x7f03a5ae7f70)], [A.ORGAN_ID(0x7f03a5ae8580)], [A.CUSTOMER_TYPE(0x7f03a5ae8b90)], [A.CUSTOMER_NAME(0x7f03a5ae91a0)], [A.RES_CODE(0x7f03a5ae97b0)], [A.USER_GRP_ID(0x7f03a5aea0e0)], [A.USER_ID(0x7f03a5aea6f0)], [A.ACT_LIMIT_DATE(0x7f03a5aecd10)], [A.CALL_CRT_DTTM(0x7f03a5aed320)], [A.CONTACT_TEL1(0x7f03a5aed930)], [A.CONTACT_TEL2(0x7f03a5afbf50)], [A.ORGAN3_ID(0x7f03a5afcb70)], [A.FAIL_COUT_NUM(0x7f03a5afd180)], [A.SYS_TYPE(0x7f03a5afd790)], [A.RESERVE_BEGIN_DTTM(0x7f03a5afdda0)], [A.RESERVE_END_DTTM(0x7f03a5afe3b0)], [A.ASSIGN_DTTM(0x7f03a5b00810)], [A.HANDLER_AGENT_CODE(0x7f03a5b00e20)], [A.APP_FLG(0x7f03a5b02660)], [A.INTER_FLG(0x7f03a5b02c70)]), filter([A.CMP_TYPE_CD(0x7f03a5ade0a0) = ?(0x7f03a5add980)], [(T_OP_IS, A.POOL_STATUS(0x7f03a5adcfb0), NULL, 0)(0x7f03a5adc5e0)], [A.CMP_STATUS(0x7f03a5adf170) != ?(0x7f03a5ade4b0)]), 
      access([A.CRT_DTTM(0x7f03a5ad9410)], [A.DATA_ID(0x7f03a5adb940)], [A.POOL_STATUS(0x7f03a5adcfb0)], [A.CMP_TYPE_CD(0x7f03a5ade0a0)], [A.CMP_STATUS(0x7f03a5adf170)], [A.ACT_ID(0x7f03a5ae63e0)], [A.CMP_TYPE_ID(0x7f03a5ae7350)], [A.COUT_MODE(0x7f03a5ae7960)], [A.ASSOCIATE_ID(0x7f03a5ae7f70)], [A.ORGAN_ID(0x7f03a5ae8580)], [A.CUSTOMER_TYPE(0x7f03a5ae8b90)], [A.CUSTOMER_NAME(0x7f03a5ae91a0)], [A.RES_CODE(0x7f03a5ae97b0)], [A.USER_GRP_ID(0x7f03a5aea0e0)], [A.USER_ID(0x7f03a5aea6f0)], [A.ACT_LIMIT_DATE(0x7f03a5aecd10)], [A.CALL_CRT_DTTM(0x7f03a5aed320)], [A.CONTACT_TEL1(0x7f03a5aed930)], [A.CONTACT_TEL2(0x7f03a5afbf50)], [A.ORGAN3_ID(0x7f03a5afcb70)], [A.FAIL_COUT_NUM(0x7f03a5afd180)], [A.SYS_TYPE(0x7f03a5afd790)], [A.RESERVE_BEGIN_DTTM(0x7f03a5afdda0)], [A.RESERVE_END_DTTM(0x7f03a5afe3b0)], [A.ASSIGN_DTTM(0x7f03a5b00810)], [A.HANDLER_AGENT_CODE(0x7f03a5b00e20)], [A.APP_FLG(0x7f03a5b02660)], [A.INTER_FLG(0x7f03a5b02c70)]), partitions(p[116-117]), 
      is_index_back=false, filter_before_indexback[false,false,false], 
      range_key([A.CRT_DTTM(0x7f03a5ad9410)], [A.__pk_increment(0x7f03a5bfd970)]), range(2023-09-15 04:37:49,MIN ; 2023-10-14 04:37:49,MIN), 
      range_cond([A.CRT_DTTM(0x7f03a5ad9410) >= ?(0x7f03a5ae0900)], [A.CRT_DTTM(0x7f03a5ad9410) < ?(0x7f03a5ae3eb0)])
  8 - output([B.POLICY_PAY_ADDR(0x7f03a5afc560)], [B.SALE_CHANNEL(0x7f03a5afe9c0)], [B.POLICY_NO(0x7f03a5afefd0)], [B.FREE_LOOK_PERIOD(0x7f03a5aff5e0)], [B.HOLDER_SEX(0x7f03a5affbf0)], [B.ORGAN4_ID(0x7f03a5b00200)], [B.CANVASSER_CODE(0x7f03a5b01430)], [B.CANVASSER_NAME(0x7f03a5b01a40)], [B.POLICY_OPE_DATE(0x7f03a5b02050)]), filter(nil), 
      partitions(p[0-129])
  9 - output([B.DATA_ID(0x7f03a5adbc30)], [B.POLICY_VALID_DATE(0x7f03a5ada8f0)]), filter(nil), 
      access([B.DATA_ID(0x7f03a5adbc30)], [B.POLICY_VALID_DATE(0x7f03a5ada8f0)]), partitions(p0), 
      is_index_back=false, 
      range_key([B.DATA_ID(0x7f03a5adbc30)], [B.shadow_pk_0(0x7e791da35600)], [B.shadow_pk_1(0x7e791da358f0)]), range(MIN ; MAX), 
      range_cond([? = B.DATA_ID(0x7f03a5adbc30)(0x7e791da4df70)])

Used Hint:
-------------------------------------
  /*+
      INDEX(@"SEL$2" "LIFE.B"@"SEL$2" "UIDX_DATA_ID2")
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      LEADING(@"SEL$2" ("LIFE.A"@"SEL$2" "LIFE.B"@"SEL$2" ))
      USE_NL(@"SEL$2" ("LIFE.B"@"SEL$2" ))
      PQ_DISTRIBUTE(@"SEL$2" ("LIFE.B"@"SEL$2" ) NONE NONE)
      NO_USE_NL_MATERIALIZATION(@"SEL$2" ("LIFE.B"@"SEL$2" ))
      FULL(@"SEL$2" "LIFE.A"@"SEL$2")
      INDEX(@"SEL$2" "LIFE.B"@"SEL$2" "UIDX_DATA_ID2")
      END_OUTLINE_DATA
  */

Plan Type:
-------------------------------------
DISTRIBUTED

Optimization Info:
-------------------------------------
A:table_rows:11907770, physical_range_rows:595388, logical_range_rows:595388, index_back_rows:0, output_rows:57, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[IDX_LI_COUT_ACTIVITY_DATAID,IDX_LI_COUT_ACTIVIT_RESE,IDX_T_LI_COUT_ACTIVITY,IDX_T_LI_COUT_ACTIVITY_TEST2,T_LI_COUT_ACTIVITY], pruned_index_name[PK_T_LI_COUT_ACTIVITY,IDX_CUSTOMER_ID_CARD,IDX_LI_COUT_ACT_NEXTDTTM,IDX_LI_COUT_ACTIVITY_CALLCRT,IDX_LI_COUT_ACTIVITY_ASID,IDX_LI_COUT_ACTIVITY_BDATE,IDX_LI_COUT_ACTIVITY_CDT,IDX_LI_COUT_ACTIVITY_CPS,IDX_LI_COUT_ACTIVITY_ENDDATE,IDX_LI_COUT_ACTIVITY_LASTDTTM,IDX_LI_COUT_ACTIVITY_LDT,IDX_LI_COUT_ACTIVITY_LRT,IDX_LI_COUT_ACTIVITY_OWN,IDX_LI_COUT_ACT_FIRSTACTID,IDX_T_LI_COUT_ACTIVITY_SHUSHUI,IDX_GLO_CMP_TYPE_ID,IDX_GLO_ASSOC_CMP]
B:table_rows:114906166, physical_range_rows:1, logical_range_rows:1, index_back_rows:1, output_rows:1, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_with_indexback

2. 拆解 SQL

從執行計劃來看，都是 A nested-loop join B。驅動表 A 表都是走主鍵，不用管，被驅動表 B 走主鍵和走全局唯一索引是有區別的，構造一個簡單的查詢測試即可看出對比：

默認走主鍵，要掃 130 個分區，耗時 7ms
加 hint /*+ index(b UIDX_DATA_ID2) */，走全局唯一索引，耗時 700us

select
  *
from
  T2 b
where
  data_id = 13260601;

走主鍵的執行計劃中，最關鍵的信息是 partitions(p[0-129])，要到所有分區上進行查找：

====================================================
|ID|OPERATOR               |NAME    |EST. ROWS|COST|
----------------------------------------------------
|0 |PX COORDINATOR         |        |1        |58  |
|1 | EXCHANGE OUT DISTR    |:EX10000|1        |46  |
|2 |  PX PARTITION ITERATOR|        |1        |46  |
|3 |   TABLE SCAN          |B       |1        |46  |
====================================================

Outputs & filters: 
-------------------------------------
  0 - output([INTERNAL_FUNCTION(B.DATA_ID(0x7e6e391fc690), B.BATCH_ID(0x7e6e391fca80), B.CMP_TYPE_ID(0x7e6e391fcd70), B.ORGAN_ID(0x7e6e391fd060), B.ORGAN3_ID(0x7e6e391fd350), B.POLICY_NO(0x7e6e391fd640), B.CLASS_CODE(0x7e6e391fd930), B.POLICY_ATTACH_FLG(0x7e6e391fdc20), B.POLICY_STATUS(0x7e6e391fdf10), B.POLICY_OPE_DATE(0x7e6e391fe200), B.POLICY_PAY_DATE(0x7e6e391fe4f0), B.POLICY_PREMIUM(0x7e6e391fe7e0), B.POLICY_PAYMETHOD(0x7e6e391fead0), B.POLICY_PAYYEARS(0x7e6e391fedc0), B.POLICY_PAY_ADDR(0x7e6e391ff0b0), B.POLICY_POSTCODE(0x7e6e391ff3a0), B.PAYMENT_TEL_AREA(0x7e6e392016a0), B.POLICY_PAYMENT_TEL(0x7e6e39201990), B.CUSTOMER_ID(0x7e6e39201c80), B.HOLDER_IDCARD(0x7e6e39201f70), B.HOLDER_NAME(0x7e6e39202260), B.HOLDER_SEX(0x7e6e39202550), B.WORK_TEL_AREA(0x7e6e39202840), B.HOLDER_WORK_TEL(0x7e6e39202b30), B.FAMILY_TEL_AREA(0x7e6e39202e20), B.HOLDER_FAMILY_TEL(0x7e6e39203110), B.MOBILE_TEL_AREA(0x7e6e39203400), B.HOLDER_MOBILE_NO(0x7e6e392036f0), B.RECOGNIZEE_IDCARD(0x7e6e392039e0), B.RECOGNIZEE_NAME(0x7e6e39203cd0), B.RECOGNIZEE_GENDER(0x7e6e39203fc0), B.RECOGNIZEE_AGE(0x7e6e392042b0), B.HOLDER_REC_REL(0x7e6e392045a0), B.POLICY_APPDATE(0x7e6e39204890), B.CANVASSER_CODE(0x7e6e39204b80), B.CANVASSER_NAME(0x7e6e39204e70), B.CANVASSER_TEL(0x7e6e39205160), B.POLICY_VALID_DATE(0x7e6e391fb6b0), B.SALE_CHANNEL(0x7e6e39205450), B.BANK_FLG(0x7e6e39205740), B.REC_DATE(0x7e6e39205a30), B.REC_INPUT_DTTM(0x7e6e39205d20), B.SET_CODE(0x7e6e39206010), B.ACCO_NO(0x7e6e39206300), B.BANK_NAME(0x7e6e392065f0), B.HOLDER_BIRTH_DATE(0x7e6e392068e0), B.CUSTOMER_TYPE(0x7e6e39206bd0), B.OWNER_SOURCE_ID(0x7e6e39206ec0), B.INSURED_SOURCE_ID(0x7e6e392071b0), B.BUSIMAN_FLG(0x7e6e392074a0), B.OPE_END_DATE(0x7e6e39207790), B.SALE_TYPE(0x7e6e39207a80), B.OPERATING_AGENCIES(0x7e6e39207d70), B.SMS_REC_INPUT_DTTM(0x7e6e39208060), B.PAYMENT_STANDARD(0x7e6e39208350), B.PREMIUM_STANDARD(0x7e6e39208640), B.POLICY_PIECES(0x7e6e39208930), B.DIGITAL_FLG(0x7e6e39208c20), B.INSURE_METHOD(0x7e6e39208f10), B.SOURCE_SYSTEM_FLG(0x7e6e39209200), B.HOLDER_IDCARD2(0x7e6e392094f0), B.ACK_TYPE(0x7e6e392097e0), B.CHANNEL_CODE(0x7e6e39209ad0), B.ACTIVITY_CODE(0x7e6e39209dc0), B.GENJOB_FLG(0x7e6e3920a0b0), B.HOLDER_AGE(0x7e6e3920a3a0), B.ORGAN4_ID(0x7e6e3920a690), B.HESITATE_DAY(0x7e6e3920a980), B.LOWEST_RATE(0x7e6e3920ac70), B.CRT_USER_ID(0x7e6e3920af60), B.CRT_DTTM(0x7e6e3920b250), B.LASTUPT_USER_ID(0x7e6e3920b540), B.LASTUPT_DTTM(0x7e6e3920b830), B.ENABLE_FLG(0x7e6e3920bb20), B.ACC_CREATE_FLG(0x7e6e3920be10), B.FREE_LOOK_PERIOD(0x7e6e3920c100), B.NEWFLAG(0x7e6e3920c3f0), B.PROTECT_FLG(0x7e6e3920c6e0), B.DEPTNAME(0x7e6e3920c9d0), B.SUB_SALE_TYPE(0x7e6e3920ccc0), B.DOUBLE_RECORD(0x7e6e3920cfb0), B.BQ_OPTION(0x7e6e3920d2a0), B.HOLDER_IDCARD_TYPE(0x7e6e3920d590), B.HOLDER_OTHER_IDCARD(0x7e6e3920d880), B.RECOGNIZEE_IDCARD_TYPE(0x7e6e3920db70), B.RECOGNIZEE_OTHER_IDCARD(0x7e6e3920de60), B.SUBAMT(0x7e6e3920e150), B.RENEW_FLG(0x7e6e3920e440), B.PRDCT_TYPE(0x7e6e3920e730), B.VIDEO_FLG(0x7e6e3920ea20), B.YB_BANK_NAME(0x7e6e3920ed10), B.MULTI_RECOGNIZEE_AGE(0x7e6e3920f000))(0x7e6e3932de70)]), filter(nil)
  1 - output([INTERNAL_FUNCTION(B.DATA_ID(0x7e6e391fc690), B.BATCH_ID(0x7e6e391fca80), B.CMP_TYPE_ID(0x7e6e391fcd70), B.ORGAN_ID(0x7e6e391fd060), B.ORGAN3_ID(0x7e6e391fd350), B.POLICY_NO(0x7e6e391fd640), B.CLASS_CODE(0x7e6e391fd930), B.POLICY_ATTACH_FLG(0x7e6e391fdc20), B.POLICY_STATUS(0x7e6e391fdf10), B.POLICY_OPE_DATE(0x7e6e391fe200), B.POLICY_PAY_DATE(0x7e6e391fe4f0), B.POLICY_PREMIUM(0x7e6e391fe7e0), B.POLICY_PAYMETHOD(0x7e6e391fead0), B.POLICY_PAYYEARS(0x7e6e391fedc0), B.POLICY_PAY_ADDR(0x7e6e391ff0b0), B.POLICY_POSTCODE(0x7e6e391ff3a0), B.PAYMENT_TEL_AREA(0x7e6e392016a0), B.POLICY_PAYMENT_TEL(0x7e6e39201990), B.CUSTOMER_ID(0x7e6e39201c80), B.HOLDER_IDCARD(0x7e6e39201f70), B.HOLDER_NAME(0x7e6e39202260), B.HOLDER_SEX(0x7e6e39202550), B.WORK_TEL_AREA(0x7e6e39202840), B.HOLDER_WORK_TEL(0x7e6e39202b30), B.FAMILY_TEL_AREA(0x7e6e39202e20), B.HOLDER_FAMILY_TEL(0x7e6e39203110), B.MOBILE_TEL_AREA(0x7e6e39203400), B.HOLDER_MOBILE_NO(0x7e6e392036f0), B.RECOGNIZEE_IDCARD(0x7e6e392039e0), B.RECOGNIZEE_NAME(0x7e6e39203cd0), B.RECOGNIZEE_GENDER(0x7e6e39203fc0), B.RECOGNIZEE_AGE(0x7e6e392042b0), B.HOLDER_REC_REL(0x7e6e392045a0), B.POLICY_APPDATE(0x7e6e39204890), B.CANVASSER_CODE(0x7e6e39204b80), B.CANVASSER_NAME(0x7e6e39204e70), B.CANVASSER_TEL(0x7e6e39205160), B.POLICY_VALID_DATE(0x7e6e391fb6b0), B.SALE_CHANNEL(0x7e6e39205450), B.BANK_FLG(0x7e6e39205740), B.REC_DATE(0x7e6e39205a30), B.REC_INPUT_DTTM(0x7e6e39205d20), B.SET_CODE(0x7e6e39206010), B.ACCO_NO(0x7e6e39206300), B.BANK_NAME(0x7e6e392065f0), B.HOLDER_BIRTH_DATE(0x7e6e392068e0), B.CUSTOMER_TYPE(0x7e6e39206bd0), B.OWNER_SOURCE_ID(0x7e6e39206ec0), B.INSURED_SOURCE_ID(0x7e6e392071b0), B.BUSIMAN_FLG(0x7e6e392074a0), B.OPE_END_DATE(0x7e6e39207790), B.SALE_TYPE(0x7e6e39207a80), B.OPERATING_AGENCIES(0x7e6e39207d70), B.SMS_REC_INPUT_DTTM(0x7e6e39208060), B.PAYMENT_STANDARD(0x7e6e39208350), B.PREMIUM_STANDARD(0x7e6e39208640), B.POLICY_PIECES(0x7e6e39208930), B.DIGITAL_FLG(0x7e6e39208c20), B.INSURE_METHOD(0x7e6e39208f10), B.SOURCE_SYSTEM_FLG(0x7e6e39209200), B.HOLDER_IDCARD2(0x7e6e392094f0), B.ACK_TYPE(0x7e6e392097e0), B.CHANNEL_CODE(0x7e6e39209ad0), B.ACTIVITY_CODE(0x7e6e39209dc0), B.GENJOB_FLG(0x7e6e3920a0b0), B.HOLDER_AGE(0x7e6e3920a3a0), B.ORGAN4_ID(0x7e6e3920a690), B.HESITATE_DAY(0x7e6e3920a980), B.LOWEST_RATE(0x7e6e3920ac70), B.CRT_USER_ID(0x7e6e3920af60), B.CRT_DTTM(0x7e6e3920b250), B.LASTUPT_USER_ID(0x7e6e3920b540), B.LASTUPT_DTTM(0x7e6e3920b830), B.ENABLE_FLG(0x7e6e3920bb20), B.ACC_CREATE_FLG(0x7e6e3920be10), B.FREE_LOOK_PERIOD(0x7e6e3920c100), B.NEWFLAG(0x7e6e3920c3f0), B.PROTECT_FLG(0x7e6e3920c6e0), B.DEPTNAME(0x7e6e3920c9d0), B.SUB_SALE_TYPE(0x7e6e3920ccc0), B.DOUBLE_RECORD(0x7e6e3920cfb0), B.BQ_OPTION(0x7e6e3920d2a0), B.HOLDER_IDCARD_TYPE(0x7e6e3920d590), B.HOLDER_OTHER_IDCARD(0x7e6e3920d880), B.RECOGNIZEE_IDCARD_TYPE(0x7e6e3920db70), B.RECOGNIZEE_OTHER_IDCARD(0x7e6e3920de60), B.SUBAMT(0x7e6e3920e150), B.RENEW_FLG(0x7e6e3920e440), B.PRDCT_TYPE(0x7e6e3920e730), B.VIDEO_FLG(0x7e6e3920ea20), B.YB_BANK_NAME(0x7e6e3920ed10), B.MULTI_RECOGNIZEE_AGE(0x7e6e3920f000))(0x7e6e3932de70)]), filter(nil), dop=1
  2 - output([B.POLICY_VALID_DATE(0x7e6e391fb6b0)], [B.DATA_ID(0x7e6e391fc690)], [B.BATCH_ID(0x7e6e391fca80)], [B.CMP_TYPE_ID(0x7e6e391fcd70)], [B.ORGAN_ID(0x7e6e391fd060)], [B.ORGAN3_ID(0x7e6e391fd350)], [B.POLICY_NO(0x7e6e391fd640)], [B.CLASS_CODE(0x7e6e391fd930)], [B.POLICY_ATTACH_FLG(0x7e6e391fdc20)], [B.POLICY_STATUS(0x7e6e391fdf10)], [B.POLICY_OPE_DATE(0x7e6e391fe200)], [B.POLICY_PAY_DATE(0x7e6e391fe4f0)], [B.POLICY_PREMIUM(0x7e6e391fe7e0)], [B.POLICY_PAYMETHOD(0x7e6e391fead0)], [B.POLICY_PAYYEARS(0x7e6e391fedc0)], [B.POLICY_PAY_ADDR(0x7e6e391ff0b0)], [B.POLICY_POSTCODE(0x7e6e391ff3a0)], [B.PAYMENT_TEL_AREA(0x7e6e392016a0)], [B.POLICY_PAYMENT_TEL(0x7e6e39201990)], [B.CUSTOMER_ID(0x7e6e39201c80)], [B.HOLDER_IDCARD(0x7e6e39201f70)], [B.HOLDER_NAME(0x7e6e39202260)], [B.HOLDER_SEX(0x7e6e39202550)], [B.WORK_TEL_AREA(0x7e6e39202840)], [B.HOLDER_WORK_TEL(0x7e6e39202b30)], [B.FAMILY_TEL_AREA(0x7e6e39202e20)], [B.HOLDER_FAMILY_TEL(0x7e6e39203110)], [B.MOBILE_TEL_AREA(0x7e6e39203400)], [B.HOLDER_MOBILE_NO(0x7e6e392036f0)], [B.RECOGNIZEE_IDCARD(0x7e6e392039e0)], [B.RECOGNIZEE_NAME(0x7e6e39203cd0)], [B.RECOGNIZEE_GENDER(0x7e6e39203fc0)], [B.RECOGNIZEE_AGE(0x7e6e392042b0)], [B.HOLDER_REC_REL(0x7e6e392045a0)], [B.POLICY_APPDATE(0x7e6e39204890)], [B.CANVASSER_CODE(0x7e6e39204b80)], [B.CANVASSER_NAME(0x7e6e39204e70)], [B.CANVASSER_TEL(0x7e6e39205160)], [B.SALE_CHANNEL(0x7e6e39205450)], [B.BANK_FLG(0x7e6e39205740)], [B.REC_DATE(0x7e6e39205a30)], [B.REC_INPUT_DTTM(0x7e6e39205d20)], [B.SET_CODE(0x7e6e39206010)], [B.ACCO_NO(0x7e6e39206300)], [B.BANK_NAME(0x7e6e392065f0)], [B.HOLDER_BIRTH_DATE(0x7e6e392068e0)], [B.CUSTOMER_TYPE(0x7e6e39206bd0)], [B.OWNER_SOURCE_ID(0x7e6e39206ec0)], [B.INSURED_SOURCE_ID(0x7e6e392071b0)], [B.BUSIMAN_FLG(0x7e6e392074a0)], [B.OPE_END_DATE(0x7e6e39207790)], [B.SALE_TYPE(0x7e6e39207a80)], [B.OPERATING_AGENCIES(0x7e6e39207d70)], [B.SMS_REC_INPUT_DTTM(0x7e6e39208060)], [B.PAYMENT_STANDARD(0x7e6e39208350)], [B.PREMIUM_STANDARD(0x7e6e39208640)], [B.POLICY_PIECES(0x7e6e39208930)], [B.DIGITAL_FLG(0x7e6e39208c20)], [B.INSURE_METHOD(0x7e6e39208f10)], [B.SOURCE_SYSTEM_FLG(0x7e6e39209200)], [B.HOLDER_IDCARD2(0x7e6e392094f0)], [B.ACK_TYPE(0x7e6e392097e0)], [B.CHANNEL_CODE(0x7e6e39209ad0)], [B.ACTIVITY_CODE(0x7e6e39209dc0)], [B.GENJOB_FLG(0x7e6e3920a0b0)], [B.HOLDER_AGE(0x7e6e3920a3a0)], [B.ORGAN4_ID(0x7e6e3920a690)], [B.HESITATE_DAY(0x7e6e3920a980)], [B.LOWEST_RATE(0x7e6e3920ac70)], [B.CRT_USER_ID(0x7e6e3920af60)], [B.CRT_DTTM(0x7e6e3920b250)], [B.LASTUPT_USER_ID(0x7e6e3920b540)], [B.LASTUPT_DTTM(0x7e6e3920b830)], [B.ENABLE_FLG(0x7e6e3920bb20)], [B.ACC_CREATE_FLG(0x7e6e3920be10)], [B.FREE_LOOK_PERIOD(0x7e6e3920c100)], [B.NEWFLAG(0x7e6e3920c3f0)], [B.PROTECT_FLG(0x7e6e3920c6e0)], [B.DEPTNAME(0x7e6e3920c9d0)], [B.SUB_SALE_TYPE(0x7e6e3920ccc0)], [B.DOUBLE_RECORD(0x7e6e3920cfb0)], [B.BQ_OPTION(0x7e6e3920d2a0)], [B.HOLDER_IDCARD_TYPE(0x7e6e3920d590)], [B.HOLDER_OTHER_IDCARD(0x7e6e3920d880)], [B.RECOGNIZEE_IDCARD_TYPE(0x7e6e3920db70)], [B.RECOGNIZEE_OTHER_IDCARD(0x7e6e3920de60)], [B.SUBAMT(0x7e6e3920e150)], [B.RENEW_FLG(0x7e6e3920e440)], [B.PRDCT_TYPE(0x7e6e3920e730)], [B.VIDEO_FLG(0x7e6e3920ea20)], [B.YB_BANK_NAME(0x7e6e3920ed10)], [B.MULTI_RECOGNIZEE_AGE(0x7e6e3920f000)]), filter(nil), 
      force partition granule, asc.
  3 - output([B.POLICY_VALID_DATE(0x7e6e391fb6b0)], [B.DATA_ID(0x7e6e391fc690)], [B.BATCH_ID(0x7e6e391fca80)], [B.CMP_TYPE_ID(0x7e6e391fcd70)], [B.ORGAN_ID(0x7e6e391fd060)], [B.ORGAN3_ID(0x7e6e391fd350)], [B.POLICY_NO(0x7e6e391fd640)], [B.CLASS_CODE(0x7e6e391fd930)], [B.POLICY_ATTACH_FLG(0x7e6e391fdc20)], [B.POLICY_STATUS(0x7e6e391fdf10)], [B.POLICY_OPE_DATE(0x7e6e391fe200)], [B.POLICY_PAY_DATE(0x7e6e391fe4f0)], [B.POLICY_PREMIUM(0x7e6e391fe7e0)], [B.POLICY_PAYMETHOD(0x7e6e391fead0)], [B.POLICY_PAYYEARS(0x7e6e391fedc0)], [B.POLICY_PAY_ADDR(0x7e6e391ff0b0)], [B.POLICY_POSTCODE(0x7e6e391ff3a0)], [B.PAYMENT_TEL_AREA(0x7e6e392016a0)], [B.POLICY_PAYMENT_TEL(0x7e6e39201990)], [B.CUSTOMER_ID(0x7e6e39201c80)], [B.HOLDER_IDCARD(0x7e6e39201f70)], [B.HOLDER_NAME(0x7e6e39202260)], [B.HOLDER_SEX(0x7e6e39202550)], [B.WORK_TEL_AREA(0x7e6e39202840)], [B.HOLDER_WORK_TEL(0x7e6e39202b30)], [B.FAMILY_TEL_AREA(0x7e6e39202e20)], [B.HOLDER_FAMILY_TEL(0x7e6e39203110)], [B.MOBILE_TEL_AREA(0x7e6e39203400)], [B.HOLDER_MOBILE_NO(0x7e6e392036f0)], [B.RECOGNIZEE_IDCARD(0x7e6e392039e0)], [B.RECOGNIZEE_NAME(0x7e6e39203cd0)], [B.RECOGNIZEE_GENDER(0x7e6e39203fc0)], [B.RECOGNIZEE_AGE(0x7e6e392042b0)], [B.HOLDER_REC_REL(0x7e6e392045a0)], [B.POLICY_APPDATE(0x7e6e39204890)], [B.CANVASSER_CODE(0x7e6e39204b80)], [B.CANVASSER_NAME(0x7e6e39204e70)], [B.CANVASSER_TEL(0x7e6e39205160)], [B.SALE_CHANNEL(0x7e6e39205450)], [B.BANK_FLG(0x7e6e39205740)], [B.REC_DATE(0x7e6e39205a30)], [B.REC_INPUT_DTTM(0x7e6e39205d20)], [B.SET_CODE(0x7e6e39206010)], [B.ACCO_NO(0x7e6e39206300)], [B.BANK_NAME(0x7e6e392065f0)], [B.HOLDER_BIRTH_DATE(0x7e6e392068e0)], [B.CUSTOMER_TYPE(0x7e6e39206bd0)], [B.OWNER_SOURCE_ID(0x7e6e39206ec0)], [B.INSURED_SOURCE_ID(0x7e6e392071b0)], [B.BUSIMAN_FLG(0x7e6e392074a0)], [B.OPE_END_DATE(0x7e6e39207790)], [B.SALE_TYPE(0x7e6e39207a80)], [B.OPERATING_AGENCIES(0x7e6e39207d70)], [B.SMS_REC_INPUT_DTTM(0x7e6e39208060)], [B.PAYMENT_STANDARD(0x7e6e39208350)], [B.PREMIUM_STANDARD(0x7e6e39208640)], [B.POLICY_PIECES(0x7e6e39208930)], [B.DIGITAL_FLG(0x7e6e39208c20)], [B.INSURE_METHOD(0x7e6e39208f10)], [B.SOURCE_SYSTEM_FLG(0x7e6e39209200)], [B.HOLDER_IDCARD2(0x7e6e392094f0)], [B.ACK_TYPE(0x7e6e392097e0)], [B.CHANNEL_CODE(0x7e6e39209ad0)], [B.ACTIVITY_CODE(0x7e6e39209dc0)], [B.GENJOB_FLG(0x7e6e3920a0b0)], [B.HOLDER_AGE(0x7e6e3920a3a0)], [B.ORGAN4_ID(0x7e6e3920a690)], [B.HESITATE_DAY(0x7e6e3920a980)], [B.LOWEST_RATE(0x7e6e3920ac70)], [B.CRT_USER_ID(0x7e6e3920af60)], [B.CRT_DTTM(0x7e6e3920b250)], [B.LASTUPT_USER_ID(0x7e6e3920b540)], [B.LASTUPT_DTTM(0x7e6e3920b830)], [B.ENABLE_FLG(0x7e6e3920bb20)], [B.ACC_CREATE_FLG(0x7e6e3920be10)], [B.FREE_LOOK_PERIOD(0x7e6e3920c100)], [B.NEWFLAG(0x7e6e3920c3f0)], [B.PROTECT_FLG(0x7e6e3920c6e0)], [B.DEPTNAME(0x7e6e3920c9d0)], [B.SUB_SALE_TYPE(0x7e6e3920ccc0)], [B.DOUBLE_RECORD(0x7e6e3920cfb0)], [B.BQ_OPTION(0x7e6e3920d2a0)], [B.HOLDER_IDCARD_TYPE(0x7e6e3920d590)], [B.HOLDER_OTHER_IDCARD(0x7e6e3920d880)], [B.RECOGNIZEE_IDCARD_TYPE(0x7e6e3920db70)], [B.RECOGNIZEE_OTHER_IDCARD(0x7e6e3920de60)], [B.SUBAMT(0x7e6e3920e150)], [B.RENEW_FLG(0x7e6e3920e440)], [B.PRDCT_TYPE(0x7e6e3920e730)], [B.VIDEO_FLG(0x7e6e3920ea20)], [B.YB_BANK_NAME(0x7e6e3920ed10)], [B.MULTI_RECOGNIZEE_AGE(0x7e6e3920f000)]), filter(nil), 
      access([B.POLICY_VALID_DATE(0x7e6e391fb6b0)], [B.DATA_ID(0x7e6e391fc690)], [B.BATCH_ID(0x7e6e391fca80)], [B.CMP_TYPE_ID(0x7e6e391fcd70)], [B.ORGAN_ID(0x7e6e391fd060)], [B.ORGAN3_ID(0x7e6e391fd350)], [B.POLICY_NO(0x7e6e391fd640)], [B.CLASS_CODE(0x7e6e391fd930)], [B.POLICY_ATTACH_FLG(0x7e6e391fdc20)], [B.POLICY_STATUS(0x7e6e391fdf10)], [B.POLICY_OPE_DATE(0x7e6e391fe200)], [B.POLICY_PAY_DATE(0x7e6e391fe4f0)], [B.POLICY_PREMIUM(0x7e6e391fe7e0)], [B.POLICY_PAYMETHOD(0x7e6e391fead0)], [B.POLICY_PAYYEARS(0x7e6e391fedc0)], [B.POLICY_PAY_ADDR(0x7e6e391ff0b0)], [B.POLICY_POSTCODE(0x7e6e391ff3a0)], [B.PAYMENT_TEL_AREA(0x7e6e392016a0)], [B.POLICY_PAYMENT_TEL(0x7e6e39201990)], [B.CUSTOMER_ID(0x7e6e39201c80)], [B.HOLDER_IDCARD(0x7e6e39201f70)], [B.HOLDER_NAME(0x7e6e39202260)], [B.HOLDER_SEX(0x7e6e39202550)], [B.WORK_TEL_AREA(0x7e6e39202840)], [B.HOLDER_WORK_TEL(0x7e6e39202b30)], [B.FAMILY_TEL_AREA(0x7e6e39202e20)], [B.HOLDER_FAMILY_TEL(0x7e6e39203110)], [B.MOBILE_TEL_AREA(0x7e6e39203400)], [B.HOLDER_MOBILE_NO(0x7e6e392036f0)], [B.RECOGNIZEE_IDCARD(0x7e6e392039e0)], [B.RECOGNIZEE_NAME(0x7e6e39203cd0)], [B.RECOGNIZEE_GENDER(0x7e6e39203fc0)], [B.RECOGNIZEE_AGE(0x7e6e392042b0)], [B.HOLDER_REC_REL(0x7e6e392045a0)], [B.POLICY_APPDATE(0x7e6e39204890)], [B.CANVASSER_CODE(0x7e6e39204b80)], [B.CANVASSER_NAME(0x7e6e39204e70)], [B.CANVASSER_TEL(0x7e6e39205160)], [B.SALE_CHANNEL(0x7e6e39205450)], [B.BANK_FLG(0x7e6e39205740)], [B.REC_DATE(0x7e6e39205a30)], [B.REC_INPUT_DTTM(0x7e6e39205d20)], [B.SET_CODE(0x7e6e39206010)], [B.ACCO_NO(0x7e6e39206300)], [B.BANK_NAME(0x7e6e392065f0)], [B.HOLDER_BIRTH_DATE(0x7e6e392068e0)], [B.CUSTOMER_TYPE(0x7e6e39206bd0)], [B.OWNER_SOURCE_ID(0x7e6e39206ec0)], [B.INSURED_SOURCE_ID(0x7e6e392071b0)], [B.BUSIMAN_FLG(0x7e6e392074a0)], [B.OPE_END_DATE(0x7e6e39207790)], [B.SALE_TYPE(0x7e6e39207a80)], [B.OPERATING_AGENCIES(0x7e6e39207d70)], [B.SMS_REC_INPUT_DTTM(0x7e6e39208060)], [B.PAYMENT_STANDARD(0x7e6e39208350)], [B.PREMIUM_STANDARD(0x7e6e39208640)], [B.POLICY_PIECES(0x7e6e39208930)], [B.DIGITAL_FLG(0x7e6e39208c20)], [B.INSURE_METHOD(0x7e6e39208f10)], [B.SOURCE_SYSTEM_FLG(0x7e6e39209200)], [B.HOLDER_IDCARD2(0x7e6e392094f0)], [B.ACK_TYPE(0x7e6e392097e0)], [B.CHANNEL_CODE(0x7e6e39209ad0)], [B.ACTIVITY_CODE(0x7e6e39209dc0)], [B.GENJOB_FLG(0x7e6e3920a0b0)], [B.HOLDER_AGE(0x7e6e3920a3a0)], [B.ORGAN4_ID(0x7e6e3920a690)], [B.HESITATE_DAY(0x7e6e3920a980)], [B.LOWEST_RATE(0x7e6e3920ac70)], [B.CRT_USER_ID(0x7e6e3920af60)], [B.CRT_DTTM(0x7e6e3920b250)], [B.LASTUPT_USER_ID(0x7e6e3920b540)], [B.LASTUPT_DTTM(0x7e6e3920b830)], [B.ENABLE_FLG(0x7e6e3920bb20)], [B.ACC_CREATE_FLG(0x7e6e3920be10)], [B.FREE_LOOK_PERIOD(0x7e6e3920c100)], [B.NEWFLAG(0x7e6e3920c3f0)], [B.PROTECT_FLG(0x7e6e3920c6e0)], [B.DEPTNAME(0x7e6e3920c9d0)], [B.SUB_SALE_TYPE(0x7e6e3920ccc0)], [B.DOUBLE_RECORD(0x7e6e3920cfb0)], [B.BQ_OPTION(0x7e6e3920d2a0)], [B.HOLDER_IDCARD_TYPE(0x7e6e3920d590)], [B.HOLDER_OTHER_IDCARD(0x7e6e3920d880)], [B.RECOGNIZEE_IDCARD_TYPE(0x7e6e3920db70)], [B.RECOGNIZEE_OTHER_IDCARD(0x7e6e3920de60)], [B.SUBAMT(0x7e6e3920e150)], [B.RENEW_FLG(0x7e6e3920e440)], [B.PRDCT_TYPE(0x7e6e3920e730)], [B.VIDEO_FLG(0x7e6e3920ea20)], [B.YB_BANK_NAME(0x7e6e3920ed10)], [B.MULTI_RECOGNIZEE_AGE(0x7e6e3920f000)]), partitions(p[0-129]), 
      is_index_back=false, 
      range_key([B.DATA_ID(0x7e6e391fc690)], [B.POLICY_VALID_DATE(0x7e6e391fb6b0)]), range(13260601,MIN ; 13260601,MAX), 
      range_cond([B.DATA_ID(0x7e6e391fc690) = 13260601(0x7e6e391fbf70)])

Used Hint:
-------------------------------------
  /*+
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "LIFE.B"@"SEL$1")
      END_OUTLINE_DATA
  */

Plan Type:
-------------------------------------
DISTRIBUTED

Optimization Info:
-------------------------------------
B:table_rows:114906166, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_with_indexback

走全局唯一索引的執行計劃中，1 號算子只需要訪問 1 個分區 partitions(p0)，0 號回表算子實際上也只需要訪問 1 個分區，因為全局索引的葉子節點上有主鍵值，而主鍵是包含分區鍵的，所以回表時是知道這一行數據的分區鍵值的，因此可以進行分區裁剪。這里需要注意的是執行計劃顯示上錯誤 partitions(p[0-129])。

============================================================
|ID|OPERATOR               |NAME            |EST. ROWS|COST|
------------------------------------------------------------
|0 |TABLE LOOKUP           |B               |1        |92  |
|1 | DISTRIBUTED TABLE SCAN|B(UIDX_DATA_ID2)|1        |46  |
============================================================

Outputs & filters: 
-------------------------------------
  0 - output([B.DATA_ID(0x7efef3480a70)], [B.BATCH_ID(0x7efef3480e60)], [B.CMP_TYPE_ID(0x7efef3481150)], [B.ORGAN_ID(0x7efef3481440)], [B.ORGAN3_ID(0x7efef3481730)], [B.POLICY_NO(0x7efef3481a20)], [B.CLASS_CODE(0x7efef3481d10)], [B.POLICY_ATTACH_FLG(0x7efef3482000)], [B.POLICY_STATUS(0x7efef34822f0)], [B.POLICY_OPE_DATE(0x7efef34825e0)], [B.POLICY_PAY_DATE(0x7efef34828d0)], [B.POLICY_PREMIUM(0x7efef3482bc0)], [B.POLICY_PAYMETHOD(0x7efef3482eb0)], [B.POLICY_PAYYEARS(0x7efef34831a0)], [B.POLICY_PAY_ADDR(0x7efef3483490)], [B.POLICY_POSTCODE(0x7efef3483780)], [B.PAYMENT_TEL_AREA(0x7efef3485a80)], [B.POLICY_PAYMENT_TEL(0x7efef3485d70)], [B.CUSTOMER_ID(0x7efef3486060)], [B.HOLDER_IDCARD(0x7efef3486350)], [B.HOLDER_NAME(0x7efef3486640)], [B.HOLDER_SEX(0x7efef3486930)], [B.WORK_TEL_AREA(0x7efef3486c20)], [B.HOLDER_WORK_TEL(0x7efef3486f10)], [B.FAMILY_TEL_AREA(0x7efef3487200)], [B.HOLDER_FAMILY_TEL(0x7efef34874f0)], [B.MOBILE_TEL_AREA(0x7efef34877e0)], [B.HOLDER_MOBILE_NO(0x7efef3487ad0)], [B.RECOGNIZEE_IDCARD(0x7efef3487dc0)], [B.RECOGNIZEE_NAME(0x7efef34880b0)], [B.RECOGNIZEE_GENDER(0x7efef34883a0)], [B.RECOGNIZEE_AGE(0x7efef3488690)], [B.HOLDER_REC_REL(0x7efef3488980)], [B.POLICY_APPDATE(0x7efef3488c70)], [B.CANVASSER_CODE(0x7efef3488f60)], [B.CANVASSER_NAME(0x7efef3489250)], [B.CANVASSER_TEL(0x7efef3489540)], [B.POLICY_VALID_DATE(0x7efef347fa90)], [B.SALE_CHANNEL(0x7efef3489830)], [B.BANK_FLG(0x7efef3489b20)], [B.REC_DATE(0x7efef3489e10)], [B.REC_INPUT_DTTM(0x7efef348a100)], [B.SET_CODE(0x7efef348a3f0)], [B.ACCO_NO(0x7efef348a6e0)], [B.BANK_NAME(0x7efef348a9d0)], [B.HOLDER_BIRTH_DATE(0x7efef348acc0)], [B.CUSTOMER_TYPE(0x7efef348afb0)], [B.OWNER_SOURCE_ID(0x7efef348b2a0)], [B.INSURED_SOURCE_ID(0x7efef348b590)], [B.BUSIMAN_FLG(0x7efef348b880)], [B.OPE_END_DATE(0x7efef348bb70)], [B.SALE_TYPE(0x7efef348be60)], [B.OPERATING_AGENCIES(0x7efef348c150)], [B.SMS_REC_INPUT_DTTM(0x7efef348c440)], [B.PAYMENT_STANDARD(0x7efef348c730)], [B.PREMIUM_STANDARD(0x7efef348ca20)], [B.POLICY_PIECES(0x7efef348cd10)], [B.DIGITAL_FLG(0x7efef348d000)], [B.INSURE_METHOD(0x7efef348d2f0)], [B.SOURCE_SYSTEM_FLG(0x7efef348d5e0)], [B.HOLDER_IDCARD2(0x7efef348d8d0)], [B.ACK_TYPE(0x7efef348dbc0)], [B.CHANNEL_CODE(0x7efef348deb0)], [B.ACTIVITY_CODE(0x7efef348e1a0)], [B.GENJOB_FLG(0x7efef348e490)], [B.HOLDER_AGE(0x7efef348e780)], [B.ORGAN4_ID(0x7efef348ea70)], [B.HESITATE_DAY(0x7efef348ed60)], [B.LOWEST_RATE(0x7efef348f050)], [B.CRT_USER_ID(0x7efef348f340)], [B.CRT_DTTM(0x7efef348f630)], [B.LASTUPT_USER_ID(0x7efef348f920)], [B.LASTUPT_DTTM(0x7efef348fc10)], [B.ENABLE_FLG(0x7efef348ff00)], [B.ACC_CREATE_FLG(0x7efef34901f0)], [B.FREE_LOOK_PERIOD(0x7efef34904e0)], [B.NEWFLAG(0x7efef34907d0)], [B.PROTECT_FLG(0x7efef3490ac0)], [B.DEPTNAME(0x7efef3490db0)], [B.SUB_SALE_TYPE(0x7efef34910a0)], [B.DOUBLE_RECORD(0x7efef3491390)], [B.BQ_OPTION(0x7efef3491680)], [B.HOLDER_IDCARD_TYPE(0x7efef3491970)], [B.HOLDER_OTHER_IDCARD(0x7efef3491c60)], [B.RECOGNIZEE_IDCARD_TYPE(0x7efef3491f50)], [B.RECOGNIZEE_OTHER_IDCARD(0x7efef3492240)], [B.SUBAMT(0x7efef3492530)], [B.RENEW_FLG(0x7efef3492820)], [B.PRDCT_TYPE(0x7efef3492b10)], [B.VIDEO_FLG(0x7efef3492e00)], [B.YB_BANK_NAME(0x7efef34930f0)], [B.MULTI_RECOGNIZEE_AGE(0x7efef34933e0)]), filter(nil), 
      partitions(p[0-129])
  1 - output([B.DATA_ID(0x7efef3480a70)], [B.POLICY_VALID_DATE(0x7efef347fa90)]), filter(nil), 
      access([B.DATA_ID(0x7efef3480a70)], [B.POLICY_VALID_DATE(0x7efef347fa90)]), partitions(p0), 
      is_index_back=false, 
      range_key([B.DATA_ID(0x7efef3480a70)], [B.shadow_pk_0(0x7efef357f740)], [B.shadow_pk_1(0x7efef357fa30)]), range(13260601,MIN,MIN ; 13260601,MAX,MAX), 
      range_cond([B.DATA_ID(0x7efef3480a70) = 13260601(0x7efef3480350)])

Used Hint:
-------------------------------------
  /*+
      INDEX(@"SEL$1" "LIFE.B"@"SEL$1" "UIDX_DATA_ID2")
  */

Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      INDEX(@"SEL$1" "LIFE.B"@"SEL$1" "UIDX_DATA_ID2")
      END_OUTLINE_DATA
  */

Plan Type:
-------------------------------------
LOCAL

Optimization Info:
-------------------------------------
B:table_rows:114906166, physical_range_rows:1, logical_range_rows:1, index_back_rows:1, output_rows:1, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_with_indexback

4結論

這個問題中 OB 集群是非分布式架構（指定了一個 primary zone），全局索引不會帶來分布式事務問題。SQL 無法做分區裁剪時，使用了高效的唯一索引，當索引是全局索引時效率最高；當索引是本地索引時，需要訪問所有的索引分區，性能會下降。

參考資料

[1]

局部索引和全局索引: https://links.jianshu.com/go?to=https%3A%2F%2Fwww.oceanbase.com%2Fdocs%2Fenterprise-oceanbase-database-cn-10000000000356624

責任編輯：武曉燕來源：愛可生開源社區

SQL 優化索引