含 NLJ 计划结果可能不对问题

OceanBase(SYS@SYS)>explain SELECT /*+xtracing(1,12,13) no_use_px*/ Z.ACTINSTID FROM temp_tab Z WHERE 1=1   AND Z.ACTINSTID = (       SELECT /*+LEADING(A,E,F,H,B,D,C,G)*/DISTINCT ACTINSTID FROM V_JBPM4_TASKTRACKS M, (             SELECT /*+merge*/MAX(ACTGENDATE) AS CC, WFINSTID CC1 FROM temp_tab2 N group by WFINSTID)       WHERE (ACTACTOR = 'wangli-067')         AND WFCODE = Z.WFCODE AND WFINSTID = Z.WFINSTID         AND M.ACTGENDATE = CC AND CC1 = M.WFINSTID)\G
*************************** 1. row ***************************
Query Plan: ===========================================================================================
|ID|OPERATOR                            |NAME                            |EST. ROWS|COST  |
-------------------------------------------------------------------------------------------
|0 |SUBPLAN FILTER                      |                                |2        |138530|
|1 | TABLE SCAN                         |Z                               |166      |65    |
|2 | MERGE DISTINCT                     |                                |1        |835   |
|3 |  SORT                              |                                |1        |835   |
|4 |   MERGE GROUP BY                   |                                |1        |835   |
|5 |    SORT                            |                                |1        |835   |
|6 |     NESTED-LOOP JOIN CARTESIAN     |                                |1        |835   |
|7 |      TABLE SCAN                    |N                               |2        |54    |
|8 |      MATERIAL                      |                                |1        |781   |
|9 |       SUBPLAN SCAN                 |V_JBPM4_TASKTRACKS              |1        |781   |
|10|        MERGE DISTINCT              |                                |1        |781   |
|11|         SORT                       |                                |1        |781   |
|12|          NESTED-LOOP JOIN          |                                |1        |781   |
|13|           NESTED-LOOP OUTER JOIN   |                                |1        |761   |
|14|            NESTED-LOOP JOIN        |                                |1        |199   |
|15|             NESTED-LOOP JOIN       |                                |1        |174   |
|16|              NESTED-LOOP OUTER JOIN|                                |1        |147   |
|17|               NESTED-LOOP JOIN     |                                |1        |146   |
|18|                NESTED-LOOP JOIN    |                                |1        |119   |
|19|                 TABLE SCAN         |A(JBPM4_HIST_PROCINST_ID)       |1        |92    |
|20|                 TABLE SCAN         |E(JBPM4_DEPLOYPROP_STRINGVAL)   |1        |26    |
|21|                TABLE SCAN          |F(JBPM4_DEPLOYPROP_DEPLOYMENT)  |1        |26    |
|22|               TABLE SCAN           |H(IDX_JBPM4_TASK_PROCINST)      |60       |230   |
|23|              TABLE SCAN            |B(IDX_JBPM4_HIST_ACTINST_HPROCI)|2        |89    |
|24|             TABLE GET              |D                               |1        |29    |
|25|            TABLE SCAN              |C(IDX_JBPM4_PART_TASK)          |66       |750   |
|26|           TABLE SCAN               |G(JBPM4_DEPLOYPROP_DEPLOYMENT)  |1        |26    |
===========================================================================================

Outputs & filters:
-------------------------------------
  0 - output([Z.ACTINSTID]), filter([Z.ACTINSTID = subquery(1)]),
      exec_params_([Z.WFINSTID], [Z.WFCODE]), onetime_exprs_(nil), init_plan_idxs_(nil)
  1 - output([Z.WFCODE], [Z.WFINSTID], [Z.ACTINSTID]), filter(nil),
      access([Z.WFCODE], [Z.WFINSTID], [Z.ACTINSTID]), partitions(p0)
  2 - output([M.ACTINSTID]), filter(nil),
      distinct([M.ACTINSTID])
  3 - output([M.ACTINSTID]), filter(nil), sort_keys([M.ACTINSTID, ASC])
  4 - output([M.ACTINSTID]), filter([M.ACTGENDATE = T_FUN_MAX(N.ACTGENDATE)]),
      group([M.WFINSTSTATE], [M.ACTINSTID], [M.WFVER], [M.ACTCODE], [M.ACTCREDATE], [M.ACTENDDATE], [M.ACTGENDATE], [M.CURRACTCODE], [M.CURRACTINSTID], [M.INSTENDACTCODE]), agg_func([T_FUN_MAX(N.ACTGENDATE)])
  5 - output([M.ACTINSTID], [M.WFINSTSTATE], [M.WFVER], [M.ACTCODE], [M.ACTCREDATE], [M.ACTENDDATE], [M.ACTGENDATE], [M.CURRACTCODE], [M.CURRACTINSTID], [M.INSTENDACTCODE], [N.ACTGENDATE]), filter(nil), sort_keys([M.WFINSTSTATE, ASC], [M.ACTINSTID, ASC], [M.WFVER, ASC], [M.ACTCODE, ASC], [M.ACTCREDATE, ASC], [M.ACTENDDATE, ASC], [M.ACTGENDATE, ASC], [M.CURRACTCODE, ASC], [M.CURRACTINSTID, ASC], [M.INSTENDACTCODE, ASC])
  6 - output([M.ACTINSTID], [M.WFINSTSTATE], [M.WFVER], [M.ACTCODE], [M.ACTCREDATE], [M.ACTENDDATE], [M.ACTGENDATE], [M.CURRACTCODE], [M.CURRACTINSTID], [M.INSTENDACTCODE], [N.ACTGENDATE]), filter(nil),
      conds(nil), nl_params_(nil)
  7 - output([N.ACTGENDATE]), filter([? = N.WFINSTID]),
      access([N.WFINSTID], [N.ACTGENDATE]), partitions(p0)
  8 - output([M.ACTINSTID], [M.WFINSTSTATE], [M.WFVER], [M.ACTCODE], [M.ACTCREDATE], [M.ACTENDDATE], [M.ACTGENDATE], [M.CURRACTCODE], [M.CURRACTINSTID], [M.INSTENDACTCODE]), filter(nil)
  9 - output([M.ACTGENDATE], [M.ACTINSTID], [M.WFVER], [M.WFINSTSTATE], [M.ACTCODE], [M.ACTCREDATE], [M.ACTENDDATE], [M.CURRACTCODE], [M.CURRACTINSTID], [M.INSTENDACTCODE]), filter(nil),
      access([M.ACTACTOR], [M.WFCODE], [M.WFINSTID], [M.ACTGENDATE], [M.ACTINSTID], [M.WFVER], [M.WFINSTSTATE], [M.ACTCODE], [M.ACTSTATE], [M.ACTCREDATE], [M.ACTENDDATE], [M.CURRACTCODE], [M.CURRACTINSTID], [M.INSTENDACTCODE])
  10 - output([F.STRINGVAL_], [G.LONGVAL_], [A.ID_], [A.STATE_], [B.ACTIVITY_NAME_], [B.HTASK_], [D.STATE_], [B.START_], [B.END_], [B.DATE_], [H.NAME_], [H.DBID_], [CASE WHEN D.STATE_ = ? THEN D.ASSIGNEE_ ELSE CASE WHEN (T_OP_IS, C.USERID_, NULL, 0) THEN H.ASSIGNEE_ ELSE C.USERID_ END END], [A.ENDACTIVITY_]), filter(nil),
      distinct([A.STATE_], [B.HTASK_], [G.LONGVAL_], [B.ACTIVITY_NAME_], [B.START_], [B.END_], [B.DATE_], [H.NAME_], [H.DBID_], [A.ENDACTIVITY_])
  11 - output([F.STRINGVAL_], [G.LONGVAL_], [A.ID_], [A.STATE_], [B.ACTIVITY_NAME_], [B.HTASK_], [D.STATE_], [B.START_], [B.END_], [B.DATE_], [H.NAME_], [H.DBID_], [A.ENDACTIVITY_], [CASE WHEN D.STATE_ = ? THEN D.ASSIGNEE_ ELSE CASE WHEN (T_OP_IS, C.USERID_, NULL, 0) THEN H.ASSIGNEE_ ELSE C.USERID_ END END]), filter(nil), sort_keys([A.STATE_, ASC], [B.HTASK_, ASC], [G.LONGVAL_, ASC], [B.ACTIVITY_NAME_, ASC], [B.START_, ASC], [B.END_, ASC], [B.DATE_, ASC], [H.NAME_, ASC], [H.DBID_, ASC], [A.ENDACTIVITY_, ASC])
  12 - output([F.STRINGVAL_], [G.LONGVAL_], [A.ID_], [A.STATE_], [B.ACTIVITY_NAME_], [B.HTASK_], [D.STATE_], [B.START_], [B.END_], [B.DATE_], [H.NAME_], [H.DBID_], [A.ENDACTIVITY_], [CASE WHEN D.STATE_ = ? THEN D.ASSIGNEE_ ELSE CASE WHEN (T_OP_IS, C.USERID_, NULL, 0) THEN H.ASSIGNEE_ ELSE C.USERID_ END END]), filter(nil),
      conds(nil), nl_params_([E.DEPLOYMENT_], [CASE WHEN D.STATE_ = ? THEN D.ASSIGNEE_ ELSE CASE WHEN (T_OP_IS, C.USERID_, NULL, 0) THEN H.ASSIGNEE_ ELSE C.USERID_ END END])
  13 - output([F.STRINGVAL_], [A.ID_], [A.STATE_], [B.ACTIVITY_NAME_], [B.HTASK_], [D.STATE_], [B.START_], [B.END_], [B.DATE_], [H.NAME_], [H.DBID_], [D.ASSIGNEE_], [C.USERID_], [H.ASSIGNEE_], [A.ENDACTIVITY_], [E.DEPLOYMENT_]), filter(nil),
      conds(nil), nl_params_([B.HTASK_])
  14 - output([F.STRINGVAL_], [A.ID_], [A.STATE_], [B.ACTIVITY_NAME_], [B.HTASK_], [D.STATE_], [B.START_], [B.END_], [B.DATE_], [H.NAME_], [H.DBID_], [D.ASSIGNEE_], [H.ASSIGNEE_], [A.ENDACTIVITY_], [E.DEPLOYMENT_]), filter(nil),
      conds(nil), nl_params_([B.HTASK_])
  15 - output([F.STRINGVAL_], [A.ID_], [A.STATE_], [B.ACTIVITY_NAME_], [B.HTASK_], [B.START_], [B.END_], [B.DATE_], [H.NAME_], [H.DBID_], [H.ASSIGNEE_], [A.ENDACTIVITY_], [E.DEPLOYMENT_]), filter(nil),
      conds(nil), nl_params_([A.DBID_])
  16 - output([F.STRINGVAL_], [A.ID_], [A.STATE_], [H.NAME_], [H.DBID_], [H.ASSIGNEE_], [A.ENDACTIVITY_], [E.DEPLOYMENT_], [A.DBID_]), filter(nil),
      conds(nil), nl_params_([A.DBID_])
  17 - output([F.STRINGVAL_], [A.ID_], [A.STATE_], [A.ENDACTIVITY_], [E.DEPLOYMENT_], [A.DBID_]), filter(nil),
      conds(nil), nl_params_([E.DEPLOYMENT_])
  18 - output([A.ID_], [A.STATE_], [A.ENDACTIVITY_], [E.DEPLOYMENT_], [A.DBID_]), filter(nil),
      conds(nil), nl_params_([A.PROCDEFID_])
  19 - output([A.DBID_], [A.PROCDEFID_], [A.ID_], [A.STATE_], [A.ENDACTIVITY_]), filter(nil),
      access([A.DBID_], [A.PROCDEFID_], [A.ID_], [A.STATE_], [A.ENDACTIVITY_]), partitions(p0)
  20 - output([E.DEPLOYMENT_]), filter([E.KEY_ = ?]),
      access([E.DEPLOYMENT_], [E.KEY_]), partitions(p0)
  21 - output([F.STRINGVAL_]), filter([F.STRINGVAL_ = ?], [F.KEY_ = ?]),
      access([F.KEY_], [F.STRINGVAL_]), partitions(p0)
  22 - output([H.NAME_], [H.DBID_], [H.ASSIGNEE_]), filter(nil),
      access([H.NAME_], [H.DBID_], [H.ASSIGNEE_]), partitions(p0)
  23 - output([B.HTASK_], [B.START_], [B.END_], [B.ACTIVITY_NAME_], [B.DATE_]), filter([B.TYPE_ = ?]),
      access([B.HTASK_], [B.TYPE_], [B.START_], [B.END_], [B.ACTIVITY_NAME_], [B.DATE_]), partitions(p0)
  24 - output([D.ASSIGNEE_], [D.STATE_]), filter(nil),
      access([D.ASSIGNEE_], [D.STATE_]), partitions(p0)
  25 - output([C.USERID_]), filter(nil),
      access([C.USERID_]), partitions(p0)
  26 - output([G.LONGVAL_]), filter([? = ?], [G.KEY_ = ?]),
      access([G.KEY_], [G.LONGVAL_]), partitions(p0)