FILTER 循环

SQL*Plus: Release 11.2.0.4.0 Production on ?..涓€ 4?.13 17:19:55 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

SQL> set linesize 200
SQL> set pagesize 200
SQL> alter session set statistics_level=all;

Session altered.

SQL> SELECT
  2       AUDIT_ID
  3  FROM
  4       (
  5            SELECT
  6                 PMP_AUDIT_INFO.*, PMP_AUDIT_STEP_INFO.AUDIT_OPR_NO AUDIT_OPR_NO
  7            FROM
  8                 PMP_AUDIT_INFO
  9            JOIN PMP_AUDIT_STEP_INFO ON PMP_AUDIT_INFO.AUDIT_ID = PMP_AUDIT_STEP_INFO.AUDIT_ID
10            WHERE
11                 audit_desc = (
12                      SELECT
13                           AUDIT_DESC
14                      FROM
15                           PMP_AUDIT_INFO
16                      WHERE
17                           AUDIT_ID = (
18                                SELECT
19                                     AUDIT_ID
20                                FROM
21                                     PBS_MCHT_CONTR_INFO_TMP
22                                WHERE
23                                     INSTR (AUDIT_OPR_NO, ',') > 0
24                                AND MCHT_ID = '8201912110101330'
25                           )
26  )
27  AND PMP_AUDIT_INFO.AUDIT_STATE = '02'
28  ORDER BY
29       APPLY_DATE_TIME DESC
30  );

AUDIT_ID
------------------------------------------------------------------------------------------------
82019121115385839024658765686186
82019121115350687655705785226563

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cm1wtzxv1z276, child number 0
-------------------------------------
SELECT     AUDIT_ID FROM      (    SELECT
PMP_AUDIT_INFO.*, PMP_AUDIT_STEP_INFO.AUDIT_OPR_NO AUDIT_OPR_NO
  FROM       PMP_AUDIT_INFO        JOIN PMP_AUDIT_STEP_INFO
ON PMP_AUDIT_INFO.AUDIT_ID = PMP_AUDIT_STEP_INFO.AUDIT_ID
WHERE      audit_desc = ( SELECT
 AUDIT_DESC        FROM
     PMP_AUDIT_INFO WHERE
AUDIT_ID = (    SELECT
      AUDIT_ID     FROM
     PBS_MCHT_CONTR_INFO_TMP
  WHERE     INSTR (AUDIT_OPR_NO, ',') >
0 AND MCHT_ID = '8201912110101330'
   ) ) AND PMP_AUDIT_INFO.AUDIT_STATE = '02' ORDER BY
  APPLY_DATE_TIME DESC )

Plan hash value: 199291123

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation       | Name | Starts | E-Rows | A-Rows |   A-Time | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |  |      1 |   |  2 |00:03:09.69 |      69M|  1 |    |    |       |
|   1 |  SORT ORDER BY       |  |      1 | 1 |  2 |00:03:09.69 |      69M|  1 |  2048 |  2048 | 2048  (0)|
|*  2 |   FILTER       |  |      1 |   |  2 |00:03:09.69 |      69M|  1 |    |    |       |
|*  3 |    HASH JOIN       |  |      1 |  50548 |  51490 |00:00:00.24 |    7053 |  0 |  7355K|  2379K| 8630K (0)|
|*  4 |     TABLE ACCESS FULL       | PMP_AUDIT_INFO |      1 |  50179 |  51490 |00:00:00.02 |    4031 |  0 |    |    |       |
|   5 |     TABLE ACCESS FULL       | PMP_AUDIT_STEP_INFO |      1 |    167K|    172K|00:00:00.05 |    3022 |  0 |    |    |       |
|   6 |    TABLE ACCESS BY INDEX ROWID| PMP_AUDIT_INFO |  24883 | 1 |  24598 |00:03:09.36 |      69M|  1 |    |    |       |
|*  7 |     INDEX UNIQUE SCAN       | PK_PMP_AUDIT_INFO |  24883 | 1 |  24598 |00:03:09.27 |      69M|  1 |    |    |       |
|*  8 |      FILTER       |  |  16054 |   |  15871 |00:03:09.13 |      69M|  0 |    |    |       |
|*  9 |       TABLE ACCESS FULL       | PBS_MCHT_CONTR_INFO_TMP |  15871 | 1 |  15871 |00:03:09.07 |      69M|  0 |    |    |       |
-------------------------------------------------------------------------------------------------------------------------------------------------------

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

   2 - filter("PMP_AUDIT_INFO"."AUDIT_DESC"=)
   3 - access("PMP_AUDIT_INFO"."AUDIT_ID"="PMP_AUDIT_STEP_INFO"."AUDIT_ID")
   4 - filter("PMP_AUDIT_INFO"."AUDIT_STATE"='02')
   7 - access("AUDIT_ID"=)
   8 - filter(INSTR(:B1,',')>0)
   9 - filter("MCHT_ID"='8201912110101330')


44 rows selected.