问题描述
我们有一个具体化的观点，已经经历了一些调整。创建新的MVW时，需要15分钟。
然后使用外壳刷新MVW，其中按顺序刷新许多MVW。作为刷新的一部分，为了使其更快，在刷新之前删除了MVW上的索引，并在刷新之后重新创建。
问题是刷新最多需要12个小时 (并且大多数情况下会因快照太旧而失败)。
当我查看在刷新期间执行的Insert语句时，Select子句与调谐的SQL不同。这是什么原因导致的？例如在调谐的Create MVW中，我们已经移动了一个inner Select，使其成为wit语句，并在其中添加了/* materialize */ hint。然后，在通过刷新执行的insert语句中，wit语句消失了，并且再次位于主SELECT中。注意-这是一个原子刷新，所以逐行。
刷新或创建中是否有可能影响此设置？我以为mvw刷新会从初始创建中执行select？
然后使用外壳刷新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，以最大程度地减少此处的干扰。
原子 _ 刷新 = 真
-删除旧数据
-插入新数据
-因为这是一笔交易，所以您不会获得平行的收益
原子 _ 刷新 = 假
-截断数据
-插入数据
-因为它是空负载，所以您会获得直接负载插入加上并行电位
您可能还想看看out_of_place =>true，以最大程度地减少此处的干扰。
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
676次阅读
2025-04-18 14:18:38
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
634次阅读
2025-04-15 17:24:06
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
541次阅读
2025-04-20 10:07:02
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
491次阅读
2025-04-17 17:02:24
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
485次阅读
2025-04-22 00:20:37
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
469次阅读
2025-04-22 00:13:51
一页概览：Oracle GoldenGate
甲骨文云技术
466次阅读
2025-04-30 12:17:56
火焰图--分析复杂SQL执行计划的利器
听见风的声音
413次阅读
2025-04-17 09:30:30
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
372次阅读
2025-04-15 14:48:05
OR+DBLINK的关联SQL优化思路
布衣
352次阅读
2025-05-05 19:28:36
