这个执行计划中的 FILTER 如何运行？

ZALBB

bell6248 发表于 2017-4-14 17:22
这种语句是应用端框架自动生成的， 语句写的太别扭了， 不能说是数据库不够智能， 只能说语句写的太复杂了

不管这个，后面我让开发该写了语句，改成NOT EXISTS, 但为何 NOT EXISTS 高效了，而 FILTER 却低效，得给个说法，

ZALBB

改成 NOT EXISTS 后，语句效率提高了很多， 但为何 原先的  FILTER 导致低效，原因在哪？ 得有个说法。

SQL> SELECT AR.FID RECID, '' RECBILLNO, AR.FALLAMOUNTFOR, '' FCURWRITTENOFFAMOUNTFOR, '' FNRECEIPTAMOUNT,
  2               CASE  WHEN (AR.FALLAMOUNTFOR < '') THEN (-'' * ABS(SUM(NVL(PML.FCURWRITTENOFFAMOUNTFOR, '')))) ELSE ABS(SUM(NVL(PML.FCURWRITTENOFFAMOUNTFOR, ''))) END FCHARGEOFFAMOUNT,
  3               PML.FSRCBILLNO FCHARGEOFFBILLNO
  4               FROM T_AR_RECMACTHLOGENTRY PML
  5               INNER JOIN T_AR_RECMacthLog PM ON (PM.FID = PML.FID AND (PM.FISACTIVATION <> '0'))
  6               INNER JOIN T_AR_RECEIVABLE AR ON AR.FID = PML.FTARGETBILLID
  7               WHERE (  (  (  (  (PML.FTARGETFROMID = 'AR_receivable' AND (PML.FSOURCEFROMID <>  'AR_receivable')  )
  8                                 AND (PML.FSOURCEFROMID <> 'AR_RECEIVEBILL') ) AND (PML.FSOURCEFROMID <> 'AR_REFUNDBILL')
  9                           )
10                           AND EXISTS (SELECT '' FROM T_AR_RECEIVABLEENTRY ARE INNER JOIN TMPBBEF6E241F2611E78CED98BE94F FLOW ON Flow.FRECID = ARE.FENTRYID WHERE ARE.FID= AR.FID))
11                        AND NOT EXISTS (SELECT 'X' FROM
12                                                           (SELECT TARME.FENTRYID FROM T_AR_MATCKENTRY TARME
13                                                            INNER JOIN T_AR_MATCK TARM ON (TARM.FID = TARME.FID AND TARME.FMATCHTYPE = '3')) "TEMP"
14                                                            LEFT OUTER JOIN T_AR_RECMACTHLOGENTRY TARR ON (TARR.FSRCROWID = "TEMP".FENTRYID AND TARR.FSOURCETYPE ='b9b2335770b84a3aa9b09b22767cd7e3')
15                                                            WHERE PML.FENTRYID= TARR.FENTRYID) )
16              GROUP BY AR.FID, PML.FSRCBILLNO, AR.FALLAMOUNTFOR, AR.FWRITTENOFFSTATUS
17  /

未选定行

已用时间:  00: 00: 01.91

执行计划
----------------------------------------------------------
Plan hash value: 3620968023

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                                |     1 |    96 |  5384   (1)| 00:00:01 |
|   1 |  HASH GROUP BY                        |                                |     1 |    96 |  5384   (1)| 00:00:01 |
|*  2 |   FILTER                              |                                |       |       |         |             |
|   3 |    NESTED LOOPS                       |                                |     1 |    96 |  5379   (1)| 00:00:01 |
|   4 |     NESTED LOOPS                      |                                |     1 |    96 |  5379   (1)| 00:00:01 |
|*  5 |      HASH JOIN                        |                                |     1 |    89 |  5378   (1)| 00:00:01 |
|   6 |       NESTED LOOPS                    |                                |     1 |    16 |     6  (17)| 00:00:01 |
|   7 |        NESTED LOOPS                   |                                |     1 |    16 |     6  (17)| 00:00:01 |
|   8 |         VIEW                          | VW_SQ_1                        |     1 |     5 |     5  (20)| 00:00:01 |
|   9 |          HASH UNIQUE                  |                                |     1 |    23 |     5  (20)| 00:00:01 |
|  10 |           NESTED LOOPS                |                                |     1 |    23 |     4   (0)| 00:00:01 |
|  11 |            NESTED LOOPS               |                                |     1 |    23 |     4   (0)| 00:00:01 |
|  12 |             TABLE ACCESS FULL         | TMPBBEF6E241F2611E78CED98BE94F |     1 |    13 |     3   (0)| 00:00:01 |
|* 13 |             INDEX UNIQUE SCAN         | PK_AR_RECEIVABLEENTRY          |     1 |       |     0   (0)| 00:00:01 |
|  14 |            TABLE ACCESS BY INDEX ROWID| T_AR_RECEIVABLEENTRY           |     1 |    10 |     1   (0)| 00:00:01 |
|* 15 |         INDEX UNIQUE SCAN             | PK_AR_RECEIVABLE               |     1 |       |     0   (0)| 00:00:01 |
|  16 |        TABLE ACCESS BY INDEX ROWID    | T_AR_RECEIVABLE                |     1 |    11 |     1   (0)| 00:00:01 |
|* 17 |       TABLE ACCESS FULL               | T_AR_RECMACTHLOGENTRY          | 55057 |  3924K|  5372   (1)| 00:00:01 |
|* 18 |      INDEX UNIQUE SCAN                | PK_AR_RECMACTHLOG              |     1 |       |     0   (0)| 00:00:01 |
|* 19 |     TABLE ACCESS BY INDEX ROWID       | T_AR_RECMACTHLOG               |     1 |     7 |     1   (0)| 00:00:01 |
|  20 |    NESTED LOOPS                       |                                |     1 |    60 |     4   (0)| 00:00:01 |
|  21 |     NESTED LOOPS                      |                                |     1 |    55 |     4   (0)| 00:00:01 |
|* 22 |      TABLE ACCESS BY INDEX ROWID      | T_AR_RECMACTHLOGENTRY          |     1 |    43 |     3   (0)| 00:00:01 |
|* 23 |       INDEX UNIQUE SCAN               | PK_AR_RECMACTHLOGENTRY         |     1 |       |     2   (0)| 00:00:01 |
|* 24 |      TABLE ACCESS BY INDEX ROWID      | T_AR_MATCKENTRY                |     1 |    12 |     1   (0)| 00:00:01 |
|* 25 |       INDEX UNIQUE SCAN               | PK_AR_MATCKENTRY               |     1 |       |     0   (0)| 00:00:01 |
|* 26 |     INDEX UNIQUE SCAN                 | PK_AR_MATCK                    |     1 |     5 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------

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

   2 - filter( NOT EXISTS (SELECT 0 FROM "T_AR_RECMACTHLOGENTRY" "TARR","T_AR_MATCK" "TARM","T_AR_MATCKENTRY"
              "TARME" WHERE "TARR"."FSRCROWID"="TARME"."FENTRYID" AND "TARME"."FMATCHTYPE"='3' AND "TARM"."FID"="TARME"."FID"
              AND "TARR"."FENTRYID"=:B1 AND "TARR"."FSOURCETYPE"='b9b2335770b84a3aa9b09b22767cd7e3'))
   5 - access("AR"."FID"="PML"."FTARGETBILLID")
  13 - access("FLOW"."FRECID"="ARE"."FENTRYID")
  15 - access("ITEM_1"="AR"."FID")
  17 - filter("PML"."FTARGETFROMID"='AR_receivable' AND "PML"."FSOURCEFROMID"<>'AR_RECEIVEBILL' AND
              "PML"."FSOURCEFROMID"<>'AR_receivable' AND "PML"."FSOURCEFROMID"<>'AR_REFUNDBILL')
  18 - access("PM"."FID"="PML"."FID")
  19 - filter("PM"."FISACTIVATION"<>'0')
  22 - filter("TARR"."FSOURCETYPE"='b9b2335770b84a3aa9b09b22767cd7e3')
  23 - access("TARR"."FENTRYID"=:B1)
  24 - filter("TARME"."FMATCHTYPE"='3')
  25 - access("TARR"."FSRCROWID"="TARME"."FENTRYID")
  26 - access("TARM"."FID"="TARME"."FID")

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - 5 Sql Plan Directives used for this statement


统计信息
----------------------------------------------------------
        330  recursive calls
          0  db block gets
     126794  consistent gets
        429  physical reads
          0  redo size
        813  bytes sent via SQL*Net to client
        540  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

ZALBB

(SELECT DISTINCT TARR.FENTRYID FROM
                                                           (SELECT TARME.FENTRYID FROM T_AR_MATCKENTRY TARME
                                                            INNER JOIN T_AR_MATCK TARM ON (TARM.FID = TARME.FID AND TARME.FMATCHTYPE = 3)) "TEMP"
                                                           LEFT OUTER JOIN T_AR_RECMACTHLOGENTRY TARR ON (TARR.FSRCROWID = "TEMP".FENTRYID AND TARR.FSOURCETYPE ='b9b2335770b84a3aa9b09b22767cd7e3'))

这个子查询，返回 19万多的记录，

ZALBB

我大概知道原因了，

〇〇

是什么原因

ZALBB

dingjun123 发表于 2017-4-14 16:13
FILTER分2种：
1.只有一个子节点，这是纯粹的过滤

既然 FILTER 操作，类似NL，而且还能建个HASH TABLE来保存已经读取过度结果，减少再入第2层子查询，那应该比 NL 操作更快，为何第1层子查询结果集大时，效率就降低了？

ZALBB

〇〇 发表于 2017-4-14 18:44
是什么原因

我一直在思考，既然 FILTER 操作类似于 NL，为何其 第1层子查询结果集大时，效率就大大降低了？而不像NL那样高效返回结果？ 如上案例，我把 NOT IN 改成 NOT EXISTS，FILTER 操作变成了嵌套循环，效率就提高了很多，为何？ 这其中一定有和 NL 操作不一样的地方，

我在网上搜索关于 FILTER 操作的信息，始终没找到这样的说明，FILTER 操作带 两层节点时，是怎么流转数据来完成操作？ 没见有人说得清楚这其中的细节？

〇〇

ZALBB 发表于 2017-4-15 11:49
我一直在思考，既然 FILTER 操作类似于 NL，为何其 第1层子查询结果集大时，效率就大大降低了？而不像NL ...

转到深入版看有没有人回答

carcase

|  12 |             TABLE ACCESS FULL         | TMPBBEF6E241F2611E78CED98BE94F |     1 |    13 |     3   (0)| 00:00:01 |  这一步是临时表？ 确认只有1行数据？

|* 17 |       TABLE ACCESS FULL               | T_AR_RECMACTHLOGENTRY          | 55057 |  3924K|  5372   (1)| 00:00:01 | 对应的是不是
|*  5 |      HASH JOIN                        |                                |     1 |    89 |  5378   (1)| 00:00:01 | ，是不是应该建立一个索引  （"PML"."FTARGETBILLID"） ？

为什么还会有  - dynamic statistics used: dynamic sampling (level=2) ? 是哪个表缺乏统计信息？ 是否可以提高动态采用的级别
逻辑读还有126794，返回的行数却为0，明显不对称。
福哥有qq吗？ 加个好友（我的qq是2330114）

ZALBB

carcase 发表于 2017-4-17 08:59
|  12 |             TABLE ACCESS FULL         | TMPBBEF6E241F2611E78CED98BE94F |     1 |    13 |     ...

上面那个表 TMP，确实只有一条记录，问题不在这里，