Optimize self-connection using analysis functions

The first day of spring festival is also a headache. Geer sent me another SQL statement. He said that the SQL dB file sequential read is very high and it takes 120 seconds to run, I can help optimize the logic read by 1626677. The SQL statement is as follows:

select distinct decode(length(a.category_id),                                5,                                decode(a.origin_type, 801, 888888, 999999),                                a.category_id) category_id,                         a.notice_code,                         a.treat_status,                         lr.real_name as receiver_name,                         f.send_code,                         f.policy_code,                         g.real_name agent_name,                         f.organ_id,                         f.dept_id,                         a.policy_id,                         a.change_id,                         a.case_id,                         a.group_policy_id,                         a.fee_id,                         a.auth_id,                         a.pay_id,                         cancel_appoint.appoint_time cancel_appoint_time,                         a.insert_time,                         a.send_time,                         a.end_time,                         f.agency_code,                         a.REPLY_TIME,                         a.REPLY_EMP_ID,                         a.FIRST_DUTY,                         a.NEED_SEND_PRINT,                         11 source           from t_policy_problem        a,                t_policy                f,                t_agent                 g,                t_letter_receiver       lr,                t_problem_category      pc,                t_policy_cancel_appoint cancel_appoint          where f.agent_id = g.agent_id(+)            and a.policy_id = f.policy_id(+)            and lr.main_receiver = 'Y'            and a.category_id = pc.category_id            and a.item_id = lr.item_id            and a.policy_id = cancel_appoint.policy_id(+)            And a.Item_Id = (Select Max(item_id)                               From t_Policy_Problem                              Where notice_code = a.notice_code)            and a.policy_id is not null            and a.notice_code is not null            and a.change_id is null            and a.case_id is null            and a.group_policy_id is null            and a.origin_type not in (801, 802)            and a.pay_id is null            and a.category_id not in                (130103, 130104, 130102, 140102, 140101)            and f.policy_type = 1            and (a.fee_id is null or                (a.fee_id is not null and a.origin_type = 701))            and f.organ_id in                (select distinct organ_id                   from T_COMPANY_ORGAN                  start with organ_id = '107'                 connect by parent_id = prior organ_id)            and pc.NEED_PRITN = 'Y'SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                           |  Name                       | Rows  | Bytes | Cost (%CPU)|--------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                    |                             |     1 |   242 |   731   (1)||   1 |  SORT UNIQUE                        |                             |     1 |   242 |   729   (0)||*  2 |   FILTER                            |                             |       |       |            ||*  3 |    HASH JOIN                        |                             |     1 |   242 |   714   (1)||   4 |     NESTED LOOPS                    |                             |     1 |   236 |   712   (1)||   5 |      NESTED LOOPS OUTER             |                             |     1 |   219 |   711   (1)||   6 |       NESTED LOOPS                  |                             |     1 |   203 |   710   (1)||   7 |        NESTED LOOPS                 |                             |     1 |   196 |   709   (1)||   8 |         NESTED LOOPS OUTER          |                             |     1 |   121 |   708   (1)||*  9 |          TABLE ACCESS FULL          | T_POLICY_PROBLEM            |     1 |   107 |   706   (0)||  10 |          TABLE ACCESS BY INDEX ROWID| T_POLICY_CANCEL_APPOINT     |     1 |    14 |     2  (50)||* 11 |           INDEX UNIQUE SCAN         | UK1_POLICY_CANCEL_APPOINT   |     1 |       |            ||* 12 |         TABLE ACCESS BY INDEX ROWID | T_POLICY                    |     1 |    75 |     2  (50)||* 13 |          INDEX UNIQUE SCAN          | PK_T_POLICY                 |     1 |       |     1   (0)||* 14 |        TABLE ACCESS BY INDEX ROWID  | T_PROBLEM_CATEGORY          |     1 |     7 |     2  (50)||* 15 |         INDEX UNIQUE SCAN           | PK_T_PROBLEM_CATEGORY       |     1 |       |            ||  16 |       TABLE ACCESS BY INDEX ROWID   | T_AGENT                     |     1 |    16 |     2  (50)||* 17 |        INDEX UNIQUE SCAN            | PK_T_AGENT                  |     1 |       |            ||* 18 |      INDEX RANGE SCAN               | T_LETTER_RECEIVER_IDX_001   |     1 |    17 |     2   (0)||  19 |     VIEW                            | VW_NSO_1                    |     7 |    42 |            ||* 20 |      CONNECT BY WITH FILTERING      |                             |       |       |            ||  21 |       NESTED LOOPS                  |                             |       |       |            ||* 22 |        INDEX UNIQUE SCAN            | PK_T_COMPANY_ORGAN          |     1 |     6 |            ||  23 |        TABLE ACCESS BY USER ROWID   | T_COMPANY_ORGAN             |       |       |            ||  24 |       NESTED LOOPS                  |                             |       |       |            ||  25 |        BUFFER SORT                  |                             |     7 |    70 |            ||  26 |         CONNECT BY PUMP             |                             |       |       |            ||* 27 |        INDEX RANGE SCAN             | T_COMPANY_ORGAN_IDX_002     |     7 |    70 |     1   (0)||  28 |    SORT AGGREGATE                   |                             |     1 |    21 |            ||  29 |     TABLE ACCESS BY INDEX ROWID     | T_POLICY_PROBLEM            |     1 |    21 |     2  (50)||* 30 |      INDEX RANGE SCAN               | IDX_POLICY_PROBLEM__N_CODE  |     1 |       |     3   (0)|--------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - filter("SYS_ALIAS_1"."ITEM_ID"= (SELECT /*+ */ MAX("T_POLICY_PROBLEM"."ITEM_ID") FROM              "T_POLICY_PROBLEM" "T_POLICY_PROBLEM" WHERE "T_POLICY_PROBLEM"."NOTICE_CODE"=:B1))   3 - access("F"."ORGAN_ID"="VW_NSO_1"."$nso_col_1")   9 - filter("SYS_ALIAS_1"."POLICY_ID" IS NOT NULL AND "SYS_ALIAS_1"."NOTICE_CODE" IS NOT NULL AND              "SYS_ALIAS_1"."CHANGE_ID" IS NULL AND "SYS_ALIAS_1"."CASE_ID" IS NULL AND              "SYS_ALIAS_1"."GROUP_POLICY_ID" IS NULL AND TO_NUMBER("SYS_ALIAS_1"."ORIGIN_TYPE")<>801 AND              TO_NUMBER("SYS_ALIAS_1"."ORIGIN_TYPE")<>802 AND "SYS_ALIAS_1"."PAY_ID" IS NULL AND              "SYS_ALIAS_1"."CATEGORY_ID"<>130103 AND "SYS_ALIAS_1"."CATEGORY_ID"<>130104 AND              "SYS_ALIAS_1"."CATEGORY_ID"<>130102 AND "SYS_ALIAS_1"."CATEGORY_ID"<>140102 AND              "SYS_ALIAS_1"."CATEGORY_ID"<>140101 AND ("SYS_ALIAS_1"."FEE_ID" IS NULL OR "SYS_ALIAS_1"."FEE_ID" IS              NOT NULL AND TO_NUMBER("SYS_ALIAS_1"."ORIGIN_TYPE")=701))  11 - access("SYS_ALIAS_1"."POLICY_ID"="CANCEL_APPOINT"."POLICY_ID"(+))  12 - filter(TO_NUMBER("F"."POLICY_TYPE")=1)  13 - access("SYS_ALIAS_1"."POLICY_ID"="F"."POLICY_ID")  14 - filter("PC"."NEED_PRITN"='Y')  15 - access("SYS_ALIAS_1"."CATEGORY_ID"="PC"."CATEGORY_ID")       filter("PC"."CATEGORY_ID"<>130103 AND "PC"."CATEGORY_ID"<>130104 AND "PC"."CATEGORY_ID"<>130102              AND "PC"."CATEGORY_ID"<>140102 AND "PC"."CATEGORY_ID"<>140101)  17 - access("F"."AGENT_ID"="G"."AGENT_ID"(+))  18 - access("LR"."MAIN_RECEIVER"='Y' AND "SYS_ALIAS_1"."ITEM_ID"="LR"."ITEM_ID")  20 - filter("T_COMPANY_ORGAN"."ORGAN_ID"='107')  22 - access("T_COMPANY_ORGAN"."ORGAN_ID"='107')  27 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL)  30 - access("T_POLICY_PROBLEM"."NOTICE_CODE"=:B1)64 rows selected.Statistics----------------------------------------------------------          7  recursive calls          0  db block gets    1626677  consistent gets      30677  physical reads        128  redo size    2291351  bytes sent via SQL*Net to client      13277  bytes received via SQL*Net from client       1060  SQL*Net roundtrips to/from client          6  sorts (memory)          0  sorts (disk)      15878  rows processed

Actually, 15878 rows will be returned, but one record will be returned in the execution plan. This may be a statistical problem for beginners (if you think this SQL statement is slow, the statistics are inaccurate, so you are a beginner .)

Because index unique scan exists, it is normal to return one record (it is not normal if one record is not returned). In addition, in Step 9, the filtering conditions are too complex, when calculating the base, it is easy for CBO to calculate less. Here it is equal to 1.

Let's get down to the point where this SQL statement is actually the most boring

And a. item_id = (select max (item_id)
From t_policy_problem
Where notice_code = A. notice_code)

This is actually equivalent to the t_policy_problem table for self-join, but this self-join is very poor, it will cause t_policy_problem table to scan twice, as shown in the execution plan, in the ninth step, it performs a full table scan. Then, in the last step 28 and 29.30, it performs an index, then returns to the table, and finally filters the index, well, this is the performance bottleneck of this SQL statement, so the SQL rewrite is as follows:

WITH t_Policy_Problem_w AS(SELECT tp.*,max(item_id) OVER (PARTITION BY notice_code)max_item_idFROM t_Policy_Problem tp)select distinct decode(length(a.category_id),                                5,                                decode(a.origin_type, 801, 888888, 999999),                                a.category_id) category_id,                         a.notice_code,                         a.treat_status,                         lr.real_name as receiver_name,                         f.send_code,                         f.policy_code,                         g.real_name agent_name,                         f.organ_id,                         f.dept_id,                         a.policy_id,                         a.change_id,                         a.case_id,                         a.group_policy_id,                         a.fee_id,                         a.auth_id,                         a.pay_id,                         cancel_appoint.appoint_time cancel_appoint_time,                         a.insert_time,                         a.send_time,                         a.end_time,                         f.agency_code,                         a.REPLY_TIME,                         a.REPLY_EMP_ID,                         a.FIRST_DUTY,                         a.NEED_SEND_PRINT,                         11 source           from t_Policy_Problem_w        a,                t_policy                f,                t_agent                 g,                t_letter_receiver       lr,                t_problem_category      pc,                t_policy_cancel_appoint cancel_appoint          where             a.item_id=a.max_item_id            and f.agent_id = g.agent_id(+)            and a.policy_id = f.policy_id(+)            and lr.main_receiver = 'Y'            and a.category_id = pc.category_id            and a.item_id = lr.item_id            and a.policy_id = cancel_appoint.policy_id(+)            and a.policy_id is not null            and a.notice_code is not null            and a.change_id is null            and a.case_id is null            and a.group_policy_id is null            and a.origin_type not in (801, 802)            and a.pay_id is null            and a.category_id not in                (130103, 130104, 130102, 140102, 140101)            and f.policy_type = 1            and (a.fee_id is null or                (a.fee_id is not null and a.origin_type = 701))            and f.organ_id in                (select distinct organ_id                   from T_COMPANY_ORGAN                  start with organ_id = '107'                 connect by parent_id = prior organ_id)            and pc.NEED_PRITN = 'Y'----------------------------------------------------------------------------------------------------------------| Id  | Operation                            |  Name                      | Rows  | Bytes |TempSpc| Cost (%CPU)|----------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                     |                            | 21241 |  5289K|       | 17992   (0)||   1 |  SORT UNIQUE                         |                            | 21241 |  5289K|    11M| 17992   (0)||*  2 |   HASH JOIN                          |                            | 21241 |  5289K|  5192K| 17192   (1)||*  3 |    HASH JOIN OUTER                   |                            | 21248 |  4938K|  4856K| 16727   (1)||*  4 |     HASH JOIN OUTER                  |                            | 21248 |  4606K|  4568K| 15994   (1)||*  5 |      HASH JOIN                       |                            | 21248 |  4316K|       | 15920   (1)||*  6 |       TABLE ACCESS FULL              | T_PROBLEM_CATEGORY         |   371 |  2597 |       |     4   (0)||*  7 |       HASH JOIN                      |                            | 29477 |  5786K|  5712K| 15915   (1)||*  8 |        HASH JOIN                     |                            | 62888 |  4974K|       |  9575   (1)||   9 |         VIEW                         | VW_NSO_1                   |     7 |    42 |       |            ||* 10 |          CONNECT BY WITH FILTERING   |                            |       |       |       |            ||  11 |           NESTED LOOPS               |                            |       |       |       |            ||* 12 |            INDEX UNIQUE SCAN         | PK_T_COMPANY_ORGAN         |     1 |     6 |       |            ||  13 |            TABLE ACCESS BY USER ROWID| T_COMPANY_ORGAN            |       |       |       |            ||  14 |           NESTED LOOPS               |                            |       |       |       |            ||  15 |            BUFFER SORT               |                            |     7 |    70 |       |            ||  16 |             CONNECT BY PUMP          |                            |       |       |       |            ||* 17 |            INDEX RANGE SCAN          | T_COMPANY_ORGAN_IDX_002    |     7 |    70 |       |     1   (0)||* 18 |         TABLE ACCESS FULL            | T_POLICY                   |   637K|    45M|       |  9569   (0)||* 19 |        VIEW                          |                            |   300K|    34M|       |            ||  20 |         WINDOW SORT                  |                            |   300K|    30M|    88M|  5648   (0)||  21 |          TABLE ACCESS FULL           | T_POLICY_PROBLEM           |   300K|    30M|       |   706   (0)||  22 |      TABLE ACCESS FULL               | T_POLICY_CANCEL_APPOINT    |    86 |  1204 |       |     2   (0)||  23 |     TABLE ACCESS FULL                | T_AGENT                    | 88982 |  1390K|       |   619   (0)||* 24 |    INDEX FAST FULL SCAN              | T_LETTER_RECEIVER_IDX_001  |   300K|  4987K|       |   251   (0)|----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("A"."ITEM_ID"="LR"."ITEM_ID")   3 - access("F"."AGENT_ID"="G"."AGENT_ID"(+))   4 - access("A"."POLICY_ID"="CANCEL_APPOINT"."POLICY_ID"(+))   5 - access("A"."CATEGORY_ID"="PC"."CATEGORY_ID")   6 - filter("PC"."NEED_PRITN"='Y' AND "PC"."CATEGORY_ID"<>130103 AND "PC"."CATEGORY_ID"<>130104 AND              "PC"."CATEGORY_ID"<>130102 AND "PC"."CATEGORY_ID"<>140102 AND "PC"."CATEGORY_ID"<>140101)   7 - access("A"."POLICY_ID"="F"."POLICY_ID")   8 - access("F"."ORGAN_ID"="VW_NSO_1"."$nso_col_1")  10 - filter("T_COMPANY_ORGAN"."ORGAN_ID"='107')  12 - access("T_COMPANY_ORGAN"."ORGAN_ID"='107')  17 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL)  18 - filter(TO_NUMBER("F"."POLICY_TYPE")=1)  19 - filter("A"."ITEM_ID"="A"."MAX_ITEM_ID" AND "A"."POLICY_ID" IS NOT NULL AND "A"."NOTICE_CODE" IS NOT NULL              AND "A"."CHANGE_ID" IS NULL AND "A"."CASE_ID" IS NULL AND "A"."GROUP_POLICY_ID" IS NULL AND              TO_NUMBER("A"."ORIGIN_TYPE")<>801 AND TO_NUMBER("A"."ORIGIN_TYPE")<>802 AND "A"."PAY_ID" IS NULL AND              "A"."CATEGORY_ID"<>130103 AND "A"."CATEGORY_ID"<>130104 AND "A"."CATEGORY_ID"<>130102 AND              "A"."CATEGORY_ID"<>140102 AND "A"."CATEGORY_ID"<>140101 AND ("A"."FEE_ID" IS NULL OR "A"."FEE_ID" IS NOT NULL              AND TO_NUMBER("A"."ORIGIN_TYPE")=701))  24 - filter("LR"."MAIN_RECEIVER"='Y')53 rows selected.Statistics----------------------------------------------------------          0  recursive calls          0  db block gets     115995  consistent gets      42204  physical reads          0  redo size    2182416  bytes sent via SQL*Net to client      13289  bytes received via SQL*Net from client       1060  SQL*Net roundtrips to/from client          7  sorts (memory)          0  sorts (disk)      15879  rows processed

The analysis function max over (partition by) is used to remove the self-join, reduce the number of table accesses, and kill the filter. Now the logical read is reduced to 115995, and the performance is improved by nearly 15 times.
There is still room for further optimization of this SQL statement. In addition, this SQL statement also needs to be rewritten. However, if it is too simple, it will not be said. In fact, there are also SQL statements sent by Gael in the past.

And a. item_id = (select max (item_id)
From t_policy_problem
Where notice_code = A. notice_code)

However, I didn't change the SQL statement for him at the time. I was too lazy at the time. Today I am in a good mood at work and have plenty of time. Let's do it.

In this case, you need to learn the self-join optimization method. You can use the analysis function to remove the self-join and reduce the number of table accesses.