Oracle 创建MVW需要15分钟。刷新相同的MVW需要12个小时 (或大部分失败)

我们有一个具体化的观点，已经经历了一些调整。创建新的MVW时，需要15分钟。

然后使用外壳刷新MVW，其中按顺序刷新许多MVW。作为刷新的一部分，为了使其更快，在刷新之前删除了MVW上的索引，并在刷新之后重新创建。

问题是刷新最多需要12个小时 (并且大多数情况下会因快照太旧而失败)。

当我查看在刷新期间执行的Insert语句时，Select子句与调谐的SQL不同。这是什么原因导致的？例如在调谐的Create MVW中，我们已经移动了一个inner Select，使其成为wit语句，并在其中添加了/* materialize */ hint。然后，在通过刷新执行的insert语句中，wit语句消失了，并且再次位于主SELECT中。注意-这是一个原子刷新，所以逐行。

刷新或创建中是否有可能影响此设置？我以为mvw刷新会从初始创建中执行select？

CREATE MATERIALIZED VIEW COSMOS_REPORTS.DIS_SHIPMENT_MED_NUMBERS_MVW (MEDICATION_NUMBER,LOT_NUMBER,SUBINVENTORY_CODE,PATIENT_NUMBER,VISIT_NUMBER,SERIAL_STATE,CONTAINER_NUMBER,DISTRIBUTION_ORDER_NO,USER_STATUS,SHIPPED_FLAG,INVENTORY_ITEM_ID,ORGANIZATION_ID,LINE_ID,HEADER_ID,TRIAL_SITE_ID,SHIP_TO_CONTACT_ID,CURRENT_ORGANIZATION_ID,STATE_CODE,TRANSACTION_ID)
CACHE
LOGGING
NOCOMPRESS
PARALLEL ( DEGREE 4 INSTANCES 1 )
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
WITH PRIMARY KEY
AS 
WITH flc
     AS (SELECT /*+ materialize */
               TO_NUMBER (flc.lookup_code) lookup_code, flc.meaning
           FROM apps.fnd_lookup_values flc
          WHERE     flc.view_application_id = 700
                AND flc.lookup_type = 'SERIAL_NUM_STATUS')
SELECT xmnol.medication_num medication_number,
       xmnol.lot_number,
       xmnol.subinventory_code, etc......

INSERT /*+ BYPASS_RECURSIVE_CHECK */
      INTO  "COSMOS_REPORTS"."DIS_SHIPMENT_MED_NUMBERS_MVW"
   (SELECT "XMNOL"."MEDICATION_NUM" "MEDICATION_NUMBER",
           "XMNOL"."LOT_NUMBER" "LOT_NUMBER",
           "XMNOL"."SUBINVENTORY_CODE" "SUBINVENTORY_CODE",
           'N/A' "PATIENT_NUMBER",
           'N/A' "VISIT_NUMBER",
           "ISNV"."SERIAL_STATE" "SERIAL_STATE",
           NVL ("DDDV"."CONTAINER_NAME", 'N/A') "CONTAINER_NUMBER",
           "OOHA"."DISTRIBUTION_ORDER_NO" "DISTRIBUTION_ORDER_NO",
           "OOHA"."USER_STATUS" "USER_STATUS",
           'No' "SHIPPED_FLAG",
           "XMNOL"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
           "XMNOL"."ORGANIZATION_ID" "ORGANIZATION_ID",
           "XMNOL"."LINE_ID" "LINE_ID",
           "OOHA"."HEADER_ID" "HEADER_ID",
           "OOHA"."TRIAL_SITE_ID" "TRIAL_SITE_ID",
           "OOHA"."SHIP_TO_CONTACT_ID" "SHIP_TO_CONTACT_ID",
           "ISNV"."CURRENT_ORGANIZATION_ID" "CURRENT_ORGANIZATION_ID",
           "ISNV"."CURRENT_STATUS" "STATE_CODE",
           0 "TRANSACTION_ID"
      FROM "BOLINF"."XXAC_MED_NOS_ORD_LINES" "XMNOL",
           (SELECT TO_CHAR ("OOHA"."ORDER_NUMBER") "DISTRIBUTION_ORDER_NO",
                   "OOHA"."ATTRIBUTE16" "USER_STATUS",
                   "OOHA"."HEADER_ID" "HEADER_ID",
                   "OOHA"."SHIP_TO_ORG_ID" "TRIAL_SITE_ID",
                   "OOHA"."SHIP_TO_CONTACT_ID" "SHIP_TO_CONTACT_ID"
              FROM "APPS"."OE_ORDER_HEADERS_ALL" "OOHA"
             WHERE     UPPER ("OOHA"."CONTEXT") = 'DISTRIBUTION'
                   AND EXISTS
                          (SELECT 'X'
                             FROM "COSMOS_REPORTS"."COSMOS_REPORT_PARAMETERS" "COSMOS_REPORT_PARAMETERS"
                            WHERE     "COSMOS_REPORT_PARAMETERS"."PARAM_CODE" =
                                         'DIS_STATUS_ASSIGNED_MEDNO'
                                  AND "COSMOS_REPORT_PARAMETERS"."PARAM_VALUE1" =
                                         'NON_RESERVED'
                                  AND "COSMOS_REPORT_PARAMETERS"."PARAM_VALUE" =
                                         UPPER ("OOHA"."ATTRIBUTE16"))) "OOHA",
           (SELECT DISTINCT
                   NVL ("WSH_DELIVERY_DETAILS"."CONTAINER_NAME", 'N/A')
                      "CONTAINER_NAME",
                   "WSH_DELIVERY_DETAILS"."SOURCE_HEADER_ID"
                      "SOURCE_HEADER_ID"
              FROM "APPS"."WSH_DELIVERY_DETAILS" "WSH_DELIVERY_DETAILS"
             WHERE     "WSH_DELIVERY_DETAILS"."CONTAINER_NAME" IS NOT NULL
                   AND "WSH_DELIVERY_DETAILS"."SOURCE_HEADER_ID" IS NOT NULL) "DDDV",
           (SELECT "MSN"."SERIAL_NUMBER" "SERIAL_NUMBER",
                   "MSN"."CURRENT_SUBINVENTORY_CODE"
                      "CURRENT_SUBINVENTORY_CODE",
                   "MSN"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
                   "MSN"."CURRENT_ORGANIZATION_ID" "CURRENT_ORGANIZATION_ID",
                   "MSN"."LOT_NUMBER" "LOT_NUMBER",
                   "FLC"."MEANING" "SERIAL_STATE",
                   "MSN"."CURRENT_STATUS" "CURRENT_STATUS"
              FROM "APPS"."MTL_SERIAL_NUMBERS" "MSN",
                   (SELECT TO_NUMBER ("FLC"."LOOKUP_CODE") "LOOKUP_CODE",
                           "FLC"."MEANING" "MEANING"
                      FROM "APPS"."FND_LOOKUP_VALUES" "FLC"
                     WHERE     "FLC"."VIEW_APPLICATION_ID" = 700
                           AND "FLC"."LOOKUP_TYPE" = 'SERIAL_NUM_STATUS') "FLC"
             WHERE     "MSN"."CURRENT_STATUS" = "FLC"."LOOKUP_CODE"
                   AND "MSN"."ATTRIBUTE1" IS NULL
                   AND "MSN"."ATTRIBUTE2" IS NULL) "ISNV"
     WHERE     UPPER ("XMNOL"."STATUS_FLAG") = 'B'
           AND "OOHA"."HEADER_ID" = "XMNOL"."HEADER_ID"
           AND "OOHA"."HEADER_ID" = "DDDV"."SOURCE_HEADER_ID"(+)
           AND "XMNOL"."INVENTORY_ITEM_ID" = "ISNV"."INVENTORY_ITEM_ID"
           AND "XMNOL"."ORGANIZATION_ID" = "ISNV"."CURRENT_ORGANIZATION_ID"
           AND "XMNOL"."LOT_NUMBER" = "ISNV"."LOT_NUMBER"
           AND "XMNOL"."MEDICATION_NUM" = "ISNV"."SERIAL_NUMBER")
   UNION ALL
   (SELECT "MUT"."SERIAL_NUMBER" "MEDICATION_NUMBER",
           "MMT"."LOT_NUMBER" "LOT_NUMBER",
           "MMT"."SUBINVENTORY_CODE" "SUBINVENTORY_CODE",
           NVL ("ISNV"."PATIENT_NUMBER", 'N/A') "PATIENT_NUMBER",
           NVL ("ISNV"."VISIT_NUMBER", 'N/A') "VISIT_NUMBER",
           "ISNV"."SERIAL_STATE" "SERIAL_STATE",
           NVL ("DDDV"."CONTAINER_NAME", 'N/A') "CONTAINER_NUMBER",
           "DSHV"."DISTRIBUTION_ORDER_NO" "DISTRIBUTION_ORDER_NO",
           "DSHV"."USER_STATUS" "USER_STATUS",
           CASE
              WHEN UPPER ("DDDV"."RELEASED_STATUS") = 'C' THEN 'Yes'
              ELSE 'No'
           END
              "SHIPPED_FLAG",
           "MMT"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
           "MMT"."ORGANIZATION_ID" "ORGANIZATION_ID",
           "DDDV"."SOURCE_LINE_ID" "LINE_ID",
           "DSHV"."HEADER_ID" "HEADER_ID",
           "DSHV"."TRIAL_SITE_ID" "TRIAL_SITE_ID",
           "DSHV"."SHIP_TO_CONTACT_ID" "SHIP_TO_CONTACT_ID",
           "ISNV"."CURRENT_ORGANIZATION_ID" "CURRENT_ORGANIZATION_ID",
           "ISNV"."CURRENT_STATUS" "STATE_CODE",
           "MMT"."TRANSACTION_ID" "TRANSACTION_ID"
      FROM "APPS"."MTL_UNIT_TRANSACTIONS" "MUT",
           (SELECT "MTLN"."LOT_NUMBER" "LOT_NUMBER",
                   "MMT"."SUBINVENTORY_CODE" "SUBINVENTORY_CODE",
                   "MMT"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
                   "MMT"."ORGANIZATION_ID" "ORGANIZATION_ID",
                   "MMT"."TRX_SOURCE_LINE_ID" "TRX_SOURCE_LINE_ID",
                   "MMT"."TRANSACTION_ID" "TRANSACTION_ID",
                   "MTLN"."SERIAL_TRANSACTION_ID" "SERIAL_TRANSACTION_ID"
              FROM "APPS"."MTL_MATERIAL_TRANSACTIONS" "MMT",
                   "APPS"."MTL_TRANSACTION_LOT_NUMBERS" "MTLN"
             WHERE     "MMT"."TRANSACTION_QUANTITY" < 0
                   AND "MTLN"."SERIAL_TRANSACTION_ID" <> 0
                   AND "MMT"."TRANSACTION_ID" = "MTLN"."TRANSACTION_ID"
                   AND EXISTS
                          (SELECT 'X'
                             FROM "APPS"."MTL_TRANSACTION_TYPES" "MTT"
                            WHERE     EXISTS
                                         (SELECT 'X'
                                            FROM "COSMOS_REPORTS"."COSMOS_REPORT_PARAMETERS" "COSMOS_REPORT_PARAMETERS"
                                           WHERE     "COSMOS_REPORT_PARAMETERS"."PARAM_CODE" =
                                                        'ASSIGNED_MEDNO_TRX_NAMES'
                                                 AND UPPER (
                                                        "MTT"."TRANSACTION_TYPE_NAME") =
                                                        "COSMOS_REPORT_PARAMETERS"."PARAM_VALUE")
                                  AND "MTT"."TRANSACTION_TYPE_ID" =
                                         "MMT"."TRANSACTION_TYPE_ID"
                                  AND "MTT"."TRANSACTION_SOURCE_TYPE_ID" =
                                         "MMT"."TRANSACTION_SOURCE_TYPE_ID")
                   AND EXISTS
                          (SELECT 'X'
                             FROM "APPS"."MTL_TXN_SOURCE_TYPES" "MTST"
                            WHERE     EXISTS
                                         (SELECT 'X'
                                            FROM "COSMOS_REPORTS"."COSMOS_REPORT_PARAMETERS" "COSMOS_REPORT_PARAMETERS"
                                           WHERE     "COSMOS_REPORT_PARAMETERS"."PARAM_CODE" =
                                                        'ASSIGNED_MEDNO_TRX_SRC_NAME'
                                                 AND UPPER (
                                                        "MTST"."TRANSACTION_SOURCE_TYPE_NAME") =
                                                        "COSMOS_REPORT_PARAMETERS"."PARAM_VALUE")
                                  AND "MTST"."TRANSACTION_SOURCE_TYPE_ID" =
                                         "MMT"."TRANSACTION_SOURCE_TYPE_ID")) "MMT",
           (SELECT "WDD"."SOURCE_LINE_ID" "SOURCE_LINE_ID",
                   "WDD"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
                   "WDD"."ORGANIZATION_ID" "ORGANIZATION_ID",
                   "WDD"."SOURCE_HEADER_ID" "SOURCE_HEADER_ID",
                   "WDD"."RELEASED_STATUS" "RELEASED_STATUS",
                   "WDD"."CONTAINER_NAME" "CONTAINER_NAME",
                   "WDD"."LOT_NUMBER" "LOT_NUMBER",
                   "WDD"."TRANSACTION_ID" "TRANSACTION_ID"
              FROM "APPS"."WSH_DELIVERY_DETAILS" "WDD"
             WHERE     (   "WDD"."RELEASED_STATUS" = 'C'
                        OR "WDD"."RELEASED_STATUS" = 'Y')
                   AND "WDD"."SOURCE_LINE_ID" IS NOT NULL
                   AND "WDD"."SOURCE_HEADER_ID" IS NOT NULL
                   AND "WDD"."LOT_NUMBER" IS NOT NULL) "DDDV",
           (SELECT "OOHA"."ATTRIBUTE16" "USER_STATUS",
                   TO_CHAR ("OOHA"."ORDER_NUMBER") "DISTRIBUTION_ORDER_NO",
                   "OOHA"."HEADER_ID" "HEADER_ID",
                   "OOHA"."SHIP_TO_ORG_ID" "TRIAL_SITE_ID",
                   "OOHA"."SHIP_TO_CONTACT_ID" "SHIP_TO_CONTACT_ID"
              FROM "APPS"."OE_ORDER_HEADERS_ALL" "OOHA"
             WHERE     UPPER ("OOHA"."CONTEXT") = 'DISTRIBUTION'
                   AND EXISTS
                          (SELECT 'X'
                             FROM "COSMOS_REPORTS"."COSMOS_REPORT_PARAMETERS" "COSMOS_REPORT_PARAMETERS"
                            WHERE     "COSMOS_REPORT_PARAMETERS"."PARAM_CODE" =
                                         'DIS_STATUS_ASSIGNED_MEDNO'
                                  AND "COSMOS_REPORT_PARAMETERS"."PARAM_VALUE1" =
                                         'RESERVED'
                                  AND "COSMOS_REPORT_PARAMETERS"."PARAM_VALUE" =
                                         UPPER ("OOHA"."ATTRIBUTE16"))) "DSHV",
           (SELECT "MSN"."SERIAL_NUMBER" "SERIAL_NUMBER",
                   "MSN"."CURRENT_SUBINVENTORY_CODE"
                      "CURRENT_SUBINVENTORY_CODE",
                   "MSN"."INVENTORY_ITEM_ID" "INVENTORY_ITEM_ID",
                   "MSN"."CURRENT_ORGANIZATION_ID" "CURRENT_ORGANIZATION_ID",
                   "MSN"."LOT_NUMBER" "LOT_NUMBER",
                   "FLC"."MEANING" "SERIAL_STATE",
                   "MSN"."ATTRIBUTE1" "PATIENT_NUMBER",
                   "MSN"."ATTRIBUTE2" "VISIT_NUMBER",
                   "MSN"."CURRENT_STATUS" "CURRENT_STATUS"
              FROM "APPS"."MTL_SERIAL_NUMBERS" "MSN",
                   (SELECT TO_NUMBER ("FLC"."LOOKUP_CODE") "LOOKUP_CODE",
                           "FLC"."MEANING" "MEANING"
                      FROM "APPS"."FND_LOOKUP_VALUES" "FLC"
                     WHERE     "FLC"."VIEW_APPLICATION_ID" = 700
                           AND "FLC"."LOOKUP_TYPE" = 'SERIAL_NUM_STATUS') "FLC"
             WHERE "MSN"."CURRENT_STATUS" = "FLC"."LOOKUP_CODE") "ISNV"
     WHERE     "MUT"."TRANSACTION_ID" = "MMT"."SERIAL_TRANSACTION_ID"
           AND "MUT"."INVENTORY_ITEM_ID" = "MMT"."INVENTORY_ITEM_ID"
           AND "MUT"."ORGANIZATION_ID" = "MMT"."ORGANIZATION_ID"
           AND "ISNV"."INVENTORY_ITEM_ID" = "MUT"."INVENTORY_ITEM_ID"
           AND "ISNV"."SERIAL_NUMBER" = "MUT"."SERIAL_NUMBER"
           AND "ISNV"."LOT_NUMBER" = "MMT"."LOT_NUMBER"
           AND "DDDV"."TRANSACTION_ID" = "MMT"."TRANSACTION_ID"
           AND "DDDV"."INVENTORY_ITEM_ID" = "MMT"."INVENTORY_ITEM_ID"
           AND "DDDV"."ORGANIZATION_ID" = "MMT"."ORGANIZATION_ID"
           AND "DDDV"."LOT_NUMBER" = "MMT"."LOT_NUMBER"
           AND "DSHV"."HEADER_ID" = "DDDV"."SOURCE_HEADER_ID")

PROMPT Start Dropping INDEX on dis_shipment_med_numbers_mvw materialized view
DROP INDEX COSMOS_REPORTS.IDX_SHIP_MEDNO_HEADER_LINE_ID
/
DROP INDEX COSMOS_REPORTS.IDX_SHIP_MEDNO_ITEM_ORG_ID
/
DROP INDEX COSMOS_REPORTS.IDX_SHIP_MEDNO_trialsite_ID
/
PROMPT Finished Dropping INDEX on dis_shipment_med_numbers_mvw materialized view

execute dbms_mview.refresh('DIS_SHIPMENT_MED_NUMBERS_MVW', 'C', PARALLELISM=>4, atomic_refresh=> TRUE);

PROMPT Start Creating INDEX on dis_shipment_med_numbers_mvw materialized view

CREATE INDEX COSMOS_REPORTS.IDX_SHIP_MEDNO_HEADER_LINE_ID
 ON  cosmos_reports.dis_shipment_med_numbers_mvw
(header_id, line_id) LOGGING TABLESPACE XBOLX NOPARALLEL
/
CREATE INDEX COSMOS_REPORTS.IDX_SHIP_MEDNO_ITEM_ORG_ID ON  cosmos_reports.dis_shipment_med_numbers_mvw
(inventory_item_id, organization_id, lot_number) LOGGING TABLESPACE XBOLX NOPARALLEL
/
CREATE INDEX COSMOS_REPORTS.IDX_SHIP_MEDNO_trialsite_ID ON cosmos_reports.dis_shipment_med_numbers_mvw
(trial_site_id) LOGGING TABLESPACE XBOLX NOPARALLEL
/
PROMPT Finished Creating INDEX on dis_shipment_med_numbers_mvw materialized view

生成的实体化视图中有多少行？因为我怀疑它是你的 “atomic_refresh” 参数。


原子 _ 刷新 = 真

-删除旧数据
-插入新数据
-因为这是一笔交易，所以您不会获得平行的收益

原子 _ 刷新 = 假

-截断数据
-插入数据
-因为它是空负载，所以您会获得直接负载插入加上并行电位

您可能还想看看out_of_place =>true，以最大程度地减少此处的干扰。