Optimize SQL with Gael-a typical case of subquery Optimization

When it was about to get off work last Friday, Galle sent an SQL

select tpc.policy_id,       tcm.policy_code,       tpf.organ_id,       to_char(tpf.insert_time, 'YYYY-MM-DD') As insert_time,       tpc.change_id,       d.policy_code,       e.company_name,       f.real_name,       tpf.fee_type,       sum(tpf.pay_balance) as pay_balance,       c.actual_type,       tpc.notice_code,       d.policy_type,       g.mode_name as pay_mode  from t_policy_change    tpc,       t_contract_master  tcm,       t_policy_fee       tpf,       t_fee_type         c,       t_contract_master  d,       t_company_customer e,       t_customer         f,       t_pay_mode         g where tpc.change_id = tpf.change_id   and tpf.policy_id = d.policy_id   and tcm.policy_id = tpc.policy_id   and tpf.receiv_status = 1    and tpf.fee_status = 1   and tpf.payment_id is null   and tpf.fee_type = c.type_id   and tpf.pay_mode = g.mode_id   and d.company_id = e.company_id(+)   and d.applicant_id = f.customer_id(+)   and tpf.organ_id in       (select          organ_id          from t_company_organ         start with organ_id = '101'        connect by prior organ_id = parent_id) group by tpc.policy_id,          tpc.change_id,          tpf.fee_type,          to_char(tpf.insert_time, 'YYYY-MM-DD'),          c.actual_type,          d.policy_code,          g.mode_name,          e.company_name,          f.real_name,          tpc.notice_code,          d.policy_type,          tpf.organ_id,          tcm.policy_code order by change_id, fee_typeSQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                           |  Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)|----------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                    |                             | 45962 |    11M|       | 45650   (0)||   1 |  SORT GROUP BY                      |                             | 45962 |    11M|    23M| 45650   (0)||*  2 |   HASH JOIN                         |                             | 45962 |    11M|       | 43908   (0)||   3 |    INDEX FULL SCAN                  | T_FEE_TYPE_IDX_003          |   106 |   636 |       |     1   (0)||   4 |    NESTED LOOPS OUTER               |                             | 45962 |    11M|       | 43906   (0)||*  5 |     HASH JOIN                       |                             | 45962 |  7271K|  6824K| 43905   (0)||   6 |      NESTED LOOPS                   |                             | 45961 |  6283K|       | 42312   (0)||*  7 |       HASH JOIN SEMI                |                             | 45961 |  5655K|    50M| 33120   (1)||*  8 |        HASH JOIN OUTER              |                             |   400K|    45M|    44M| 32315   (1)||*  9 |         HASH JOIN                   |                             |   400K|    39M|    27M| 26943   (0)||* 10 |          HASH JOIN                  |                             |   400K|    23M|       | 16111   (0)||  11 |           TABLE ACCESS FULL         | T_PAY_MODE                  |    25 |   525 |       |     2   (0)||* 12 |           TABLE ACCESS FULL         | T_POLICY_FEE                |   400K|    15M|       | 16107   (0)||  13 |          TABLE ACCESS FULL          | T_CONTRACT_MASTER           |  1136K|    46M|       |  9437   (0)||  14 |         VIEW                        | index_join_007            |  2028K|    30M|       |            ||* 15 |          HASH JOIN                  |                             |   400K|    45M|    44M| 32315   (1)||  16 |           INDEX FAST FULL SCAN      | PK_T_CUSTOMER               |  2028K|    30M|       |   548   (0)||  17 |           INDEX FAST FULL SCAN      | IDX_CUSTOMER__BIR_REAL_GEN  |  2028K|    30M|       |   548   (0)||  18 |        VIEW                         | VW_NSO_1                    |     7 |    42 |       |            ||* 19 |         CONNECT BY WITH FILTERING   |                             |       |       |       |            ||  20 |          NESTED LOOPS               |                             |       |       |       |            ||* 21 |           INDEX UNIQUE SCAN         | PK_T_COMPANY_ORGAN          |     1 |     6 |       |            ||  22 |           TABLE ACCESS BY USER ROWID| T_COMPANY_ORGAN             |       |       |       |            ||  23 |          NESTED LOOPS               |                             |       |       |       |            ||  24 |           BUFFER SORT               |                             |     7 |    70 |       |            ||  25 |            CONNECT BY PUMP          |                             |       |       |       |            ||* 26 |           INDEX RANGE SCAN          | T_COMPANY_ORGAN_IDX_002     |     7 |    70 |       |     1   (0)||  27 |       TABLE ACCESS BY INDEX ROWID   | T_POLICY_CHANGE             |     1 |    14 |       |     2  (50)||* 28 |        INDEX UNIQUE SCAN            | PK_T_POLICY_CHANGE          |     1 |       |       |     1   (0)||  29 |      INDEX FAST FULL SCAN           | IDX1_ACCEPT_DATE            |  1136K|    23M|       |   899   (0)||  30 |     TABLE ACCESS BY INDEX ROWID     | T_COMPANY_CUSTOMER          |     1 |    90 |       |     2  (50)||* 31 |      INDEX UNIQUE SCAN              | PK_T_COMPANY_CUSTOMER       |     1 |       |       |            |----------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("TPF"."FEE_TYPE"="C"."TYPE_ID")   5 - access("TCM"."POLICY_ID"="TPC"."POLICY_ID")   7 - access("TPF"."ORGAN_ID"="VW_NSO_1"."$nso_col_1")   8 - access("D"."APPLICANT_ID"="F"."CUSTOMER_ID"(+))   9 - access("TPF"."POLICY_ID"="D"."POLICY_ID")  10 - access("TPF"."PAY_MODE"="G"."MODE_ID")  12 - filter("TPF"."CHANGE_ID" IS NOT NULL AND TO_NUMBER("TPF"."RECEIV_STATUS")=1 AND "TPF"."FEE_STATUS"=1 AND              "TPF"."PAYMENT_ID" IS NULL)  15 - access("indexjoin_alias_012".ROWID="indexjoin_alias_011".ROWID)  19 - filter("T_COMPANY_ORGAN"."ORGAN_ID"='101')  21 - access("T_COMPANY_ORGAN"."ORGAN_ID"='101')  26 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL)  28 - access("TPC"."CHANGE_ID"="TPF"."CHANGE_ID")  31 - access("D"."COMPANY_ID"="E"."COMPANY_ID"(+))55 rows selectedStatistics----------------------------------------------------------         21  recursive calls          0  db block gets     125082  consistent gets      21149  physical reads          0  redo size       2448  bytes sent via SQL*Net to client        656  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          4  sorts (memory)          0  sorts (disk)         11  rows processed

 

It takes 21 seconds for this SQL statement to run. It takes about yuan to read the SQL statement in logic and asks if I can optimize the SQL statement. It took me about one minute to optimize this SQL statement, because it was too simple.
You can see that this SQL statement is a typical join statement. To deal with such SQL statements, the table must be indexed. But from the execution plan, there is a 10 million rows table t_contract_master which uses a full table scan,
The rows of t_policy_1_table are also scanned in the full table, so it is not slow. Then, the SQL filter condition has an in subquery.
(Select
Organ_id
From t_company_organ
Start with organ_id = '123'
Connect by prior organ_id = parent_id)
In terms of the Execution Plan, CBO performs unnest on the son's query, because CBO generally thinks that the performance of the subquery is better than that of the filter after the subquery is unnest.

So I asked Gael to query the number of rows returned by the subquery.
Select organ_id
From t_company_organ
Start with organ_id = '123'
Connect by prior organ_id = parent_id --- Gael says it returns 1 line

For a subquery, if it returns very little data (one row is returned here), it can be used as a filter, and the filter is basically executed in the final de-phase of the SQL statement, in this way, t_policy_fee can be indexed.
So I added an hint to this subquery to disable the extension of the subquery.

Select TPC. policy_id, TCM. policy_code, TPF. organ_id, to_char (TPF. insert_time, 'yyyy-MM-DD ') as insert_time, TPC. change_id, D. policy_code, E. company_name, F. real_name, TPF. fee_type, sum (TPF. pay_balance) as pay_balance, C. actual_type, TPC. notice_code, D. policy_type, G. mode_name as pay_mode from t_policy_change TPC, t_contract_master TCM, t_policy_effectpf, t_fee_type C, t_contract_master D, t_company_customer E, t_customer F, t_pay_mode g where TPC. change_id = TPF. change_id and TPF. policy_id = D. policy_id and TCM. policy_id = TPC. policy_id and TPF. pai_status = '1' --- there were no quotation marks here. I forgot to write ''to the development sb. I asked gel to add it. If it was not added, indexes and TPF could not be used. fee_status = 1 and TPF. payment_id is null and TPF. fee_type = C. type_id and TPF. pay_mode = G. mode_id and D. company_id = E. company_id (+) and D. applicant_id = f. customer_id (+) and TPF. organ_id in (select/* + no_unnest */-- The organ_id added after the hint here from t_company_organ start with organ_id = '2013' connect by prior organ_id = parent_id) group by TPC. policy_id, TPC. change_id, TPF. fee_type, to_char (TPF. insert_time, 'yyyy-MM-DD '), C. actual_type, D. policy_code, G. mode_name, E. company_name, F. real_name, TPC. notice_code, D. policy_type, TPF. organ_id, TCM. policy_code order by change_id, fee_typesql> select * from table (dbms_xplan.display); plan_table_output tables | ID | operation | Name | rows | bytes | tempspc | cost (% CPU) | statement | 0 | SELECT statement | 20026 | Statement 8 k | 68615 (30) | 1 | sort group by | 20026 | Statement 8 k | 10 M | 28563 (0) | * 2 | filter | 3 | nested loops | 20026 | Bytes 8 k | 27812 (0) | 4 | nested loops | 20026 | 4498k | 23807 (0) | 5 | nested loops outer | 20026 | 4224k | 19802 (0) | 6 | nested loops outer | 20026 | 3911k | 15797 (0) | 7 | nested loops | 20026 | 2151k | 15796 (0) | * 8 | hash join | 20026 | 1310k | 11791 (0) | 9 | index full scan | t_fee_type_idx_003 | 106 | 636 | 1 (0) | * 10 | hash join | 20026 | 1192k | 11789 (0) | 11 | table access full | t_pay_mode | 25 | 525 | 2 (0) | * 12 | table access by index rowid | t_policy_fee | 20026 | 782k | 11786 (0) | * 13 | index range scan | idx_policy_fee1_1__status | 1243k | 10188 (0) | 14 | table access by index rowid | t_contract_master | 1 | 43 | 2 (50) | * 15 | index unique scan | pk_t_contract_master | 1 | 1 (0) | 16 | table access by index rowid | t_company_customer | 1 | 90 | 2 (50) | * 17 | index unique scan | pk_t_company_customer | 1 | 18 | table access by index rowid | t_customer | 1 | 16 | 2 (50) | * 19 | index unique scan | pk_t_customer | 1 | 1 (0) | 20 | table access by index rowid | t_policy_change | 1 | 14 | 2 (50) | * 21 | index unique scan | pk_t_policy_change | 1 | 1 (0) | 22 | table access by index rowid | t_contract_master | 1 | 22 | 2 (50) | * 23 | index unique scan | pk_t_contract_master | 1 | 1 (0) | * 24 | filter | * 25 | connect by with filtering | 26 | nested loops | * 27 | index unique scan | pk_t_company_organ | 1 | 6 | 28 | table access by user rowid | t_company_organ | 29 | nested loops | 30 | buffer sort | 7 | 70 | 31 | connect by pump | * 32 | index range scan | t_company_organ_idx_002 | 7 | 70 | 1 (0) | inclupredicate information (identified by Operation ID): Limit 2-filter (exists (select/* + no_unnest */0 from "t_company_organ" "t_company_organ" where "t_company_organ ". "parent_id" = NULL and ("t_company_organ ". "organ_id" =: B1) 8-access ("sys_alias_1 ". "fee_type" = "C ". "type_id") 10-access ("sys_alias_1 ". "pay_mode" = "G ". "mode_id") 12-filter ("sys_alias_1 ". "change_id" is not null and "sys_alias_1 ". "fee_status" = 1 and "sys_alias_1 ". "payment_id" is null) 13-access ("sys_alias_1 ". "cmd_status" = '1') 15-access ("sys_alias_1 ". "policy_id" = "D ". "policy_id") 17-access ("D ". "company_id" = "E ". "company_id" (+) 19-access ("D ". "applicant_id" = "F ". "customer_id" (+) 21-access ("TPC ". "change_id" = "sys_alias_1 ". "change_id") 23-access ("TCM ". "policy_id" = "TPC ". "policy_id") 24-filter ("t_company_organ ". "organ_id" =: B1) 25-filter ("t_company_organ ". "organ_id" = '000000') 27-access ("t_company_organ ". "organ_id" = '000000') 32-access ("t_company_organ ". "parent_id" = NULL) 58 rows selected. statistics defaults 0 recursive cballs 0 dB block gets 2817 consistent gets 0 physical reads 0 redo size 2268 bytes sent via SQL * Net to client 656 bytes received via SQL * Net From Client 2 SQL * Net roundtrips to/from client 40 sorts (memory) 0 sorts (Disk) 9 rows processed

In the end, this SQL statement can run in less than 1 second, and the logical read is reduced to 2817. So far, I have not continued to optimize the SQL statement. At this time, I will stop optimizing the SQL statement. Other force-optimization problems will occur.
This optimization case is very simple. I am sorry to post it on my blog. What you need to learn through this article is that if the subquery returns very little data, you may wish to let it go through the filter