Avoid left Outer Join and right outer join when designing the system.

Today, Galle asked me to optimize an SQL statement. The SQL statement is as follows:

SELECT  DISTINCT b.organ_id,                        c.company_name as organ_name,                        a.distri_date,                        a.distri_type,                        d.TYPE_NAME Capital_name,                        b.policy_code,                        b.apply_code send_code,                        i.ATTRIBUTE10 total_code,                        f.pay_mode,                        j.type_name as policy_type_name,                        e.Internal_Id AS product_code,                        round(a.distri_amount, 2) AS fee_amount,                        decode(a.posted,                               'Y',                               to_char(i.transaction_date, 'yyyy-mm-dd'),                               to_char(a.distri_date, 'yyyy-mm-dd')) As finish_time,                        F.DR_SEG1,                        F.DR_SEG2,                        F.DR_SEG3,                        F.DR_SEG4,                        F.DR_SEG5,                        F.DR_SEG6,                        f.dr_seg7,                        f.dr_seg8,                        f.dr_seg9,                        f.dr_seg10,                        f.cr_seg1,                        f.cr_seg2,                        f.cr_seg3,                        f.cr_seg4,                        f.cr_seg5,                        f.cr_seg6,                        f.cr_seg7,                        f.cr_seg8,                        f.cr_seg9,                        f.cr_seg10,                        f.je_posting_id as cred_id          FROM T_CAPITAL_DISTRIBUTE a,               t_contract_master b,               t_channel_type j,               t_company_organ c,               t_capital_distri_type d,               t_product_life e,               t_contract_product f,               (select * from T_BIZ_ACCOUNTING_INFO where DATA_TABLE = '7') F,               T_GL_BIZ_INTERFACE i,               (select  organ_id                  from t_company_organ                 start with organ_id = '101'                connect by parent_id = prior organ_id) o         WHERE a.policy_id = b.policy_id           and a.item_id = f.item_id(+)           AND b.organ_id = c.Organ_Id           AND a.distri_type = d.distri_type           AND a.product_id = e.product_id           and b.policy_type = j.INDIVIDUAL_GROUP           AND A.capital_id = F.FEE_ID(+)           AND A.cred_id = i.posting_id(+)           and a.organ_id = i.segment1(+)           and nvl(a.posted, 'N') = 'Y'           and a.cred_id = 493997           and i.transaction_date >= to_date('2011-11-01', 'yyyy-MM-dd')           and i.transaction_date < to_date('2011-11-30', 'yyyy-MM-dd') + 1           and a.distri_type = i.reference3(+)           and i.segment1 = o.organ_id(+);

Gael said that the SQL logic reads 10 million data records and runs 300 s. The SQL Execution plan is as follows:

SQL> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id  | Operation                             |  Name                          | Rows  | Bytes | Cost (%CPU)|-------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                      |                                |     1 |   356 |    27   (0)||   1 |  SORT UNIQUE                          |                                |     1 |   356 |    27   (0)||*  2 |   HASH JOIN OUTER                     |                                |     1 |   356 |    12   (9)||   3 |    NESTED LOOPS                       |                                |     1 |   350 |    10  (10)||   4 |     NESTED LOOPS                      |                                |     1 |   338 |     9  (12)||   5 |      NESTED LOOPS OUTER               |                                |     1 |   302 |     8  (13)||   6 |       NESTED LOOPS                    |                                |     1 |   171 |     7  (15)||   7 |        NESTED LOOPS                   |                                |     1 |   125 |     6  (17)||   8 |         NESTED LOOPS                  |                                |     1 |   100 |     5  (20)||   9 |          NESTED LOOPS OUTER           |                                |     1 |    86 |     4  (25)||  10 |           NESTED LOOPS                |                                |     1 |    76 |     3  (34)||  11 |            TABLE ACCESS BY INDEX ROWID| T_GL_BIZ_INTERFACE             |     1 |    24 |     2  (50)||* 12 |             INDEX SKIP SCAN           | IDX10                          |     1 |       |     3   (0)||* 13 |            TABLE ACCESS BY INDEX ROWID| T_CAPITAL_DISTRIBUTE           |     1 |    52 |     2  (50)||* 14 |             INDEX RANGE SCAN          | IDX_CAPITAL_DISTR__CRED_ORGAN  |    15 |       |     2   (0)||  15 |           TABLE ACCESS BY INDEX ROWID | T_CONTRACT_PRODUCT             |     1 |    10 |     2  (50)||* 16 |            INDEX UNIQUE SCAN          | PK_T_CONTRACT_PRODUCT          |     1 |       |     1   (0)||  17 |          TABLE ACCESS BY INDEX ROWID  | T_PRODUCT_LIFE                 |     1 |    14 |     2  (50)||* 18 |           INDEX UNIQUE SCAN           | PK_T_PRODUCT_LIFE              |     1 |       |            ||  19 |         TABLE ACCESS BY INDEX ROWID   | T_CAPITAL_DISTRI_TYPE          |     1 |    25 |     2  (50)||* 20 |          INDEX UNIQUE SCAN            | PK_T_CAPITAL_DISTRI_TYPE       |     1 |       |            ||  21 |        TABLE ACCESS BY INDEX ROWID    | T_CONTRACT_MASTER              |     1 |    46 |     2  (50)||* 22 |         INDEX UNIQUE SCAN             | PK_T_CONTRACT_MASTER           |     1 |       |     1   (0)||  23 |       TABLE ACCESS BY INDEX ROWID     | T_BIZ_ACCOUNTING_INFO          |     1 |   131 |     2  (50)||* 24 |        INDEX RANGE SCAN               | IDX_BIZ_ACCOUNTING_INFO__FEE_  |     1 |       |     2   (0)||  25 |      TABLE ACCESS BY INDEX ROWID      | T_COMPANY_ORGAN                |     1 |    36 |     2  (50)||* 26 |       INDEX UNIQUE SCAN               | PK_T_COMPANY_ORGAN             |     1 |       |            ||  27 |     TABLE ACCESS BY INDEX ROWID       | T_CHANNEL_TYPE                 |     1 |    12 |     2  (50)||* 28 |      INDEX UNIQUE SCAN                | PK_T_CHANNEL_TYPE              |     1 |       |            ||  29 |    VIEW                               |                                |     7 |    42 |            ||* 30 |     CONNECT BY WITH FILTERING         |                                |       |       |            ||  31 |      NESTED LOOPS                     |                                |       |       |            ||* 32 |       INDEX UNIQUE SCAN               | PK_T_COMPANY_ORGAN             |     1 |     6 |            ||  33 |       TABLE ACCESS BY USER ROWID      | T_COMPANY_ORGAN                |       |       |            ||  34 |      NESTED LOOPS                     |                                |       |       |            ||  35 |       BUFFER SORT                     |                                |     7 |    70 |            ||  36 |        CONNECT BY PUMP                |                                |       |       |            ||* 37 |       INDEX RANGE SCAN                | T_COMPANY_ORGAN_IDX_002        |     7 |    70 |     1   (0)|-------------------------------------------------------------------------------------------------------------   2 - access("I"."SEGMENT1"="O"."ORGAN_ID"(+))  12 - access("I"."TRANSACTION_DATE">=TO_DATE('2011-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND              "I"."POSTING_ID"=493997 AND "I"."TRANSACTION_DATE"<TO_DATE('2011-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))       filter("I"."POSTING_ID"=493997)  13 - filter("A"."DISTRI_TYPE"="I"."REFERENCE3")  14 - access("A"."CRED_ID"=493997 AND "A"."ORGAN_ID"="I"."SEGMENT1")       filter(NVL("A"."POSTED",'N')='Y')  16 - access("A"."ITEM_ID"="F"."ITEM_ID"(+))  18 - access("A"."PRODUCT_ID"="E"."PRODUCT_ID")  20 - access("A"."DISTRI_TYPE"="D"."DISTRI_TYPE")  22 - access("A"."POLICY_ID"="B"."POLICY_ID")  24 - access("A"."CAPITAL_ID"="T_BIZ_ACCOUNTING_INFO"."FEE_ID"(+) AND              "T_BIZ_ACCOUNTING_INFO"."DATA_TABLE"(+)=7)  26 - access("B"."ORGAN_ID"="C"."ORGAN_ID")  28 - access("B"."POLICY_TYPE"="J"."INDIVIDUAL_GROUP")  30 - filter("T_COMPANY_ORGAN"."ORGAN_ID"='101')  32 - access("T_COMPANY_ORGAN"."ORGAN_ID"='101')  37 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL)65 rows selected.

From the execution plan, there is basically no Technical Optimization for this SQL statement. You may say that the index skip scan should not be used here, and the index range scan should be used.

However, this is a small problem. It is not a decisive factor. After SQL optimization is technically unavailable, you should analyze the business immediately. Please observe this SQL statement carefully.

It has many external connections, which are special because the external connection driver table has a fixed sequence, such

A left join B, so a can only be used as a drive table (whether using nested loops outer or hash join outer). You cannot change the sequence of the drive table, even if you use leading or order hint.

It is precisely because there are many external connections in this SQL, and the SQL access sequence is fixed, so there is no way to raise the SQL optimization from the technology.

I asked Gael to remove the external connections (+) and run the SQL statement. It takes only 30 seconds to finish running the SQL statement. The execution plan is as follows:

----------------------------------------------------------------------------------------------------------------| Id  | Operation                                |  Name                          | Rows  | Bytes | Cost (%CPU)|----------------------------------------------------------------------------------------------------------------|   0 | SELECT STATEMENT                         |                                |     1 |   354 |    27   (0)||   1 |  SORT UNIQUE                             |                                |     1 |   354 |    27   (0)||   2 |   TABLE ACCESS BY INDEX ROWID            | T_BIZ_ACCOUNTING_INFO          |     1 |   131 |     2  (50)||   3 |    NESTED LOOPS                          |                                |     1 |   354 |    12   (9)||   4 |     NESTED LOOPS                         |                                |     1 |   223 |    11  (10)||   5 |      NESTED LOOPS                        |                                |     1 |   209 |    10  (10)||   6 |       NESTED LOOPS                       |                                |     1 |   199 |     9  (12)||   7 |        NESTED LOOPS                      |                                |     1 |   174 |     8  (13)||   8 |         NESTED LOOPS                     |                                |     1 |   138 |     7  (15)||   9 |          NESTED LOOPS                    |                                |     1 |   126 |     6  (17)||* 10 |           HASH JOIN                      |                                |     1 |    80 |     5  (20)||  11 |            TABLE ACCESS BY INDEX ROWID   | T_CAPITAL_DISTRIBUTE           |     1 |    50 |     2  (50)||  12 |             NESTED LOOPS                 |                                |     1 |    74 |     3  (34)||  13 |              TABLE ACCESS BY INDEX ROWID | T_GL_BIZ_INTERFACE             |     1 |    24 |     2  (50)||* 14 |               INDEX SKIP SCAN            | IDX10                          |     1 |       |     3   (0)||* 15 |              INDEX RANGE SCAN            | IDX14                          |     1 |       |     2   (0)||  16 |            VIEW                          |                                |     7 |    42 |            ||* 17 |             FILTER                       |                                |       |       |            ||* 18 |              CONNECT BY WITH FILTERING   |                                |       |       |            ||  19 |               NESTED LOOPS               |                                |       |       |            ||* 20 |                INDEX UNIQUE SCAN         | PK_T_COMPANY_ORGAN             |     1 |     6 |            ||  21 |                TABLE ACCESS BY USER ROWID| T_COMPANY_ORGAN                |       |       |            ||  22 |               NESTED LOOPS               |                                |       |       |            ||  23 |                BUFFER SORT               |                                |     7 |    70 |            ||  24 |                 CONNECT BY PUMP          |                                |       |       |            ||* 25 |                INDEX RANGE SCAN          | T_COMPANY_ORGAN_IDX_002        |     7 |    70 |     1   (0)||  26 |           TABLE ACCESS BY INDEX ROWID    | T_CONTRACT_MASTER              |     1 |    46 |     2  (50)||* 27 |            INDEX UNIQUE SCAN             | PK_T_CONTRACT_MASTER           |     1 |       |     1   (0)||  28 |          TABLE ACCESS BY INDEX ROWID     | T_CHANNEL_TYPE                 |     1 |    12 |     2  (50)||* 29 |           INDEX UNIQUE SCAN              | PK_T_CHANNEL_TYPE              |     1 |       |            ||  30 |         TABLE ACCESS BY INDEX ROWID      | T_COMPANY_ORGAN                |     1 |    36 |     2  (50)||* 31 |          INDEX UNIQUE SCAN               | PK_T_COMPANY_ORGAN             |     1 |       |            ||  32 |        TABLE ACCESS BY INDEX ROWID       | T_CAPITAL_DISTRI_TYPE          |     1 |    25 |     2  (50)||* 33 |         INDEX UNIQUE SCAN                | PK_T_CAPITAL_DISTRI_TYPE       |     1 |       |            ||  34 |       TABLE ACCESS BY INDEX ROWID        | T_CONTRACT_PRODUCT             |     1 |    10 |     2  (50)||* 35 |        INDEX UNIQUE SCAN                 | PK_T_CONTRACT_PRODUCT          |     1 |       |     1   (0)||  36 |      TABLE ACCESS BY INDEX ROWID         | T_PRODUCT_LIFE                 |     1 |    14 |     2  (50)||* 37 |       INDEX UNIQUE SCAN                  | PK_T_PRODUCT_LIFE              |     1 |       |            ||* 38 |     INDEX RANGE SCAN                     | IDX_BIZ_ACCOUNTING_INFO__FEE_  |     1 |       |     2   (0)|----------------------------------------------------------------------------------------------------------------10 - access("I"."SEGMENT1"="O"."ORGAN_ID")  14 - access("I"."TRANSACTION_DATE">=TO_DATE('2011-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND              "I"."POSTING_ID"=493997 AND "I"."TRANSACTION_DATE"<TO_DATE('2011-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))       filter("I"."POSTING_ID"=493997)  15 - access("A"."CRED_ID"=493997 AND "A"."ORGAN_ID"="I"."SEGMENT1" AND "A"."DISTRI_TYPE"="I"."REFERENCE3")       filter(NVL("A"."POSTED",'N')='Y' AND "A"."DISTRI_TYPE"="I"."REFERENCE3")  17 - filter(TO_DATE('2011-11-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')<TO_DATE('2011-12-01 00:00:00',              'yyyy-mm-dd hh24:mi:ss') AND TO_DATE('2011-12-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss')>TO_DATE('2011-11-01              00:00:00', 'yyyy-mm-dd hh24:mi:ss'))  18 - filter("T_COMPANY_ORGAN"."ORGAN_ID"='101')  20 - access("T_COMPANY_ORGAN"."ORGAN_ID"='101')  25 - access("T_COMPANY_ORGAN"."PARENT_ID"=NULL)  27 - access("A"."POLICY_ID"="B"."POLICY_ID")  29 - access("B"."POLICY_TYPE"="J"."INDIVIDUAL_GROUP")  31 - access("B"."ORGAN_ID"="C"."ORGAN_ID")  33 - access("A"."DISTRI_TYPE"="D"."DISTRI_TYPE")  35 - access("A"."ITEM_ID"="F"."ITEM_ID")  37 - access("A"."PRODUCT_ID"="E"."PRODUCT_ID")  38 - access("A"."CAPITAL_ID"="T_BIZ_ACCOUNTING_INFO"."FEE_ID" AND "T_BIZ_ACCOUNTING_INFO"."DATA_TABLE"=7)

Therefore, when designing the system, we should avoid left Outer Join and right Outer Join, especially large tables. Large tables should also be avoided as external connection driving tables.