Oracle插入SQL查询性能调整

Question

我有一个插入查询，大约需要5-6分钟才能完成，我计划将时间减少到1分钟以下。 我看到合并加入笛卡尔在解释计划中，因此寻找避免这种情况的方法。

以下是涉及查询并解释计划。

DB：Oracle数据库19C标准版2版本19.0.0.0-生产

INSERT INTO dictdm_discounts_perf
 (
   DISCOUNT_MIGRATION_ID,
   DISCOUNT_NAME,
   DISCOUNT_LEVEL,
   RBM_PRODUCT_ID,
   DISCOUNTED_OFFER_MIGR_ID,
   DISCOUNTED_FLAT_OFFER_NAME,
   DISCOUNTED_OFFERING_ID,
   DISCOUNTED_OFFERING_NAME,
   APPLY_MODE,
   VALID_TO,
   VALID_FROM,
   DURATION,
   DURATION_UNIT,
   PRICE_COMPONENT_SPEC_ID,
   PRICE_COMPONENT_SPEC_NAME,
   ALT_PRICE,
   ABSOLUTE_ALTERATION,
   ALTERATION_IN_PERCENT,
   TARIFF_ID,
   DISCOUNT_RULE,
   LEGACY_TARIFF_ID,
   DISC_GROUP_ID,
   DISC_GROUP_NAME
 )
 select
    dsct.DISCOUNT_ID DISCOUNT_MIGRATION_ID
   ,dsct.NAME DISCOUNT_NAME
   ,(select lv.value from nc_list_values lv
     where lv.list_value_id = DISCOUNT_LEVEL_VALUE_ID) DISCOUNT_LEVEL
   ,dsct.DISCOUNT_PRODUCT_ID RBM_PRODUCT_ID
   ,do.OFFER_MIGRATION_ID DISCOUNTED_OFFER_MIGR_ID
   ,do.FLAT_OFFERING_NAME DISCOUNTED_FLAT_OFFER_NAME
   ,do.OFFERING_ID DISCOUNTED_OFFERING_ID
   ,do.OFFERING_NAME DISCOUNTED_OFFERING_NAME
   ,(select lv.value from nc_list_values lv where lv.list_value_id = dsct.APPLY_MODE_VALUE_ID) APPLY_MODE
   ,dsct.AVAILABLE_TO VALID_TO
   ,dsct.AVAILABLE_FROM VALID_FROM
   ,dsct.DURATION DURATION
   ,(select lv.value from nc_list_values lv where lv.list_value_id = dsct.DURATION_UNIT_VALUE_ID) DURATION_UNIT
   ,pa.PRICE_SPEC_ID PRICE_COMPONENT_SPEC_ID
   ,(select o.name from nc_objects o where o.object_id = pa.PRICE_SPEC_ID) PRICE_COMPONENT_SPEC_NAME
   ,pa.PRICE_ALTERATION_ID ALT_PRICE
   ,decode(pa.DISC_AMOUNT_TYPE_VALUE_ID,9154646390213102438/*Absolute*/,pa.DISCOUNT_AMOUNT,null) ABSOLUTE_ALTERATION
   ,decode(pa.DISC_AMOUNT_TYPE_VALUE_ID,9154646390213102439/*Percentage*/,pa.DISCOUNT_AMOUNT,null) ALTERATION_IN_PERCENT
   ,pp.EXTERNAL_PRICE_PLAN_ID TARIFF_ID
   ,rl.RULE_ID DISCOUNT_RULE
   ,dsct.LEGACY_TARIFF_ID LEGACY_TARIFF_ID
   ,PD.DISCOUNT_GROUP_ID DISC_GROUP_ID
   ,O.NAME DISC_GROUP_NAME
 from
   POC_DISCOUNT dsct
   join POC_DISCOUNT_TO_OBJECT d2o on d2o.DISCOUNT_ID = dsct.DISCOUNT_ID
   join DICTDM_OFFERINGS do on decode(d2o.TYPE_ID,
                                       9155001372713095116/*Offering*/, do.OFFERING_ID,
                                       9155431485713090836/*Falt Offering*/, do.OFFER_MIGRATION_ID) = d2o.OBJECT_ID
               or (d2o.TYPE_ID = 9155001372713095117/*Category*/ and instr(do.OFFERING_CATEGORY_ID,d2o.OBJECT_ID)>0)
   left join POC_PRICE_ALTERATION pa on pa.DISCOUNT_ID = dsct.DISCOUNT_ID
   left join PRICE_PLAN pp on pp.FLAT_OBJECT_ID = dsct.DISCOUNT_ID and pp.DISCOUNTED_OFFERING_ID = do.OFFERING_ID
   left join POC_DISCOUNT_TO_RULE rl on rl.DISCOUNT_ID = dsct.DISCOUNT_ID
   left join POC_DISCOUNT_TO_GROUP PD ON dsct.DISCOUNT_ID=PD.DISCOUNT_ID
   left join NC_OBJECTS O ON O.OBJECT_ID=PD.DISCOUNT_GROUP_ID
   where not exists (
      select 0 from nc_params ofst
      where ofst.object_id = do.offering_id
      and ofst.attr_id = 7021759771013444983 /*Status*/
      and ofst.list_value_id = 9140545748713273967 /*Discontinued*/
   );

Plan hash value: 3564192537

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                             |                         | 10987 |  5203K|  4703   (1)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL                     | DICTDM_DISCOUNTS        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID                | NC_LIST_VALUES          |     1 |    23 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                         | XPKNC_LIST_VALUES       |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID                | NC_LIST_VALUES          |     1 |    23 |     1   (0)| 00:00:01 |
|*  5 |    INDEX UNIQUE SCAN                         | XPKNC_LIST_VALUES       |     1 |       |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID                | NC_LIST_VALUES          |     1 |    23 |     1   (0)| 00:00:01 |
|*  7 |    INDEX UNIQUE SCAN                         | XPKNC_LIST_VALUES       |     1 |       |     1   (0)| 00:00:01 |
|   8 |   TABLE ACCESS BY INDEX ROWID                | NC_OBJECTS              |     1 |    32 |     1   (0)| 00:00:01 |
|*  9 |    INDEX UNIQUE SCAN                         | XPKNC_OBJECTS           |     1 |       |     1   (0)| 00:00:01 |
|* 10 |   HASH JOIN RIGHT OUTER                      |                         | 10987 |  5203K|  4696   (1)| 00:00:01 |
|  11 |    TABLE ACCESS FULL                         | POC_PRICE_ALTERATION    |    48 |  2160 |     3   (0)| 00:00:01 |
|* 12 |    HASH JOIN RIGHT OUTER                     |                         |  9613 |  4130K|  4693   (1)| 00:00:01 |
|  13 |     INDEX FULL SCAN                          | PK_POC_DISCOUNT_TO_RULE |     9 |    81 |     1   (0)| 00:00:01 |
|  14 |     NESTED LOOPS OUTER                       |                         |  9176 |  3862K|  4692   (1)| 00:00:01 |
|  15 |      NESTED LOOPS OUTER                      |                         |  9176 |  3575K|  4691   (1)| 00:00:01 |
|* 16 |       HASH JOIN RIGHT OUTER                  |                         |  9176 |  3342K|  4690   (1)| 00:00:01 |
|  17 |        TABLE ACCESS FULL                     | PRICE_PLAN              |     1 |    39 |     3   (0)| 00:00:01 |
|* 18 |        HASH JOIN                             |                         |  9176 |  2992K|  4687   (1)| 00:00:01 |
|  19 |         TABLE ACCESS FULL                    | POC_DISCOUNT_TO_OBJECT  |    43 |  1032 |     3   (0)| 00:00:01 |
|  20 |         MERGE JOIN CARTESIAN                 |                         |   132K|    39M|  4683   (1)| 00:00:01 |
|  21 |          NESTED LOOPS ANTI                   |                         |  3167 |   451K|   385   (0)| 00:00:01 |
|  22 |           TABLE ACCESS FULL                  | DICTDM_OFFERINGS        |  5660 |   608K|   102   (0)| 00:00:01 |
|* 23 |           TABLE ACCESS BY INDEX ROWID BATCHED| NC_PARAMS               |    44 |  1584 |     1   (0)| 00:00:01 |
|* 24 |            INDEX RANGE SCAN                  | XIF12NC_PARAMS          |     1 |       |     1   (0)| 00:00:01 |
|  25 |          BUFFER SORT                         |                         |    42 |  6888 |  4682   (1)| 00:00:01 |
|  26 |           TABLE ACCESS FULL                  | POC_DISCOUNT            |    42 |  6888 |     1   (0)| 00:00:01 |
|  27 |       TABLE ACCESS BY INDEX ROWID BATCHED    | POC_DISCOUNT_TO_GROUP   |     1 |    26 |     1   (0)| 00:00:01 |
|* 28 |        INDEX RANGE SCAN                      | IDX_DISCOUNT_TO_GROUP_D |     1 |       |     1   (0)| 00:00:01 |
|  29 |      TABLE ACCESS BY INDEX ROWID             | NC_OBJECTS              |     1 |    32 |     1   (0)| 00:00:01 |
|* 30 |       INDEX UNIQUE SCAN                      | XPKNC_OBJECTS           |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("LV"."LIST_VALUE_ID"=:B1)
   5 - access("LV"."LIST_VALUE_ID"=:B1)
   7 - access("LV"."LIST_VALUE_ID"=:B1)
   9 - access("O"."OBJECT_ID"=:B1)
  10 - access("PA"."DISCOUNT_ID"(+)="DSCT"."DISCOUNT_ID")
  12 - access("RL"."DISCOUNT_ID"(+)="DSCT"."DISCOUNT_ID")
  16 - access("PP"."DISCOUNTED_OFFERING_ID"(+)="DO"."OFFERING_ID" AND 
              "PP"."FLAT_OBJECT_ID"(+)="DSCT"."DISCOUNT_ID")
  18 - access("D2O"."DISCOUNT_ID"="DSCT"."DISCOUNT_ID")
       filter("D2O"."OBJECT_ID"=DECODE("D2O"."TYPE_ID",9155001372713095116,"DO"."OFFERING_ID",915543148571309083
              6,"DO"."OFFER_MIGRATION_ID") OR "D2O"."TYPE_ID"=9155001372713095117 AND 
              INSTR("DO"."OFFERING_CATEGORY_ID",TO_CHAR("D2O"."OBJECT_ID"))>0)
  23 - filter("OFST"."LIST_VALUE_ID"=9140545748713273967)
  24 - access("OFST"."OBJECT_ID"="DO"."OFFERING_ID" AND "OFST"."ATTR_ID"=7021759771013444983)
  28 - access("DSCT"."DISCOUNT_ID"="PD"."DISCOUNT_ID"(+))
  30 - access("O"."OBJECT_ID"(+)="PD"."DISCOUNT_GROUP_ID")