问题描述
Hi, Tom,
A developer complains the gradually slowness in his query from a view. But query was run much faster from the same logic without using the view. Basically using the same SELECT logic directly from the view creation script, it is much faster.
Here is the direct SELECT query, inside the FROM is the view definition:
select /* SQL using the View */ /* + monitor */
Loc_Name "Pavilion"
, Pat_MRN_ID "MRN"
, Pat_ID "Patient ID"
, Pat_Enc_CSN_ID "CSN"
, Pat_Name "Patient"
, Age "Age"
, Birth_Date "Patient DOB"
, Sex "Sex"
, Ethnicity "Ethnicity"
, hsp_Account_ID "HAR"
, OR_Case_ID "Case ID"
, Hosp_Admsn_Time "Admission Date/Time"
, Hosp_Disch_Time "Discharge Date/Time"
, Surgery_Date "Surgery Date"
, Case_Confirmed_YN "Case Confirmed YN"
, Case_Type "Case Type"
, Case_Class "Case Class"
, Case_Progress "Case Progress"
, Void_Reason "Void Reason"
, Void_Comments "Void Comments"
, PreOP_Diagnosis "Pre-OP Diagnosis"
, PreOP_Diagnosis_Codes "Pre-OP Diagnosis Codes"
, PostOP_Diagnosis "Post-OP Diagnosis"
, PostOP_Diagnosis_Codes "Post-Op Diagnosis Codes"
, Primary_Physician "Primary Physician"
, Surgeon "Surgeon"
, Service "Service"
, Role "Role"
, Start_Time "Start Time"
, End_Time "End Time"
, Num_of_Panels "Number of Panels"
, Panel_Wound_Class "Panel/Procedure Wound Class"
, Panel_Anes_Type "Panel/Procedure Anesthesia"
, Panel_Procedure_Card "Panel/Procedure (Card)"
, Panel_Procedure_as_Ordered "Panel/Procedure (As Ordered)"
, Panel_Procedure_Start "Panel/Procedure Start"
, Panel_Procedure_End "Panel/Procedure End"
, Panel_Incision "Panel/Procedure Incision"
, Panel_Closure_Start "Panel/Procedure Closure Start"
, PDT_Procedures "PDT Procedures"
, Anesthesia_Type "Anesthesia Type"
, Patient_Class "Patient Class"
, OR_Service "OR Service"
, Scheduled_OR "Scheduled OR"
, Actual_OR "Actual OR"
, OR_Location "OR Location"
, Schedule_Status "Schedule Status"
, Cancel_Reason "Cancel Reason"
, Cancel_Comments "Cancel Comment"
, Facility_In "Facility IN"
, Waiting_Area_IN "Waiting Area IN"
, Preop_in_PACU "PreOp in PACU"
, Pre_OP_Holding_IN "Pre-OP Holding IN"
, Pre_Procedure_Complete "Pre-Procedure Complete"
, Pre_OP_Holding_Out "Pre-OP Holding OUT"
, Room_Ready "Room Ready"
, Room_IN "Room IN"
, Room_Out "Room OUT"
, Anesthesia_Start "Anesthesia Start"
, Anesthesia_Ready "Anesthesia Ready"
, Anesthesia_Finish "Anesthesia Finish"
, Procedure_Start "Procedure Start"
, Incision_Start "Incision Start"
, Closure_Begin "Closure Begin"
, Procedure_Closing "Procedure Closing"
, Procedure_Finish "Procedure Finish"
, Case when Procedure_Finish >= Procedure_Start
then Procedure_Finish - Procedure_Start
else null end "Procedure Duration"
, Procedural_Care_Complete "Procedural Care Complete"
, Recovery_in_PreOp "Recovery in PreOp"
, Recovery_IN "Recovery IN"
, Recovery_Care_Complete "Recovery Care Complete"
, Recovery_Out "Recovery OUT"
, Recovery_Return "Recovery Return"
, Recovery_Out_II "Recovery OUT II"
, PhaseII_IN "Phase II IN"
, PhaseII_Care_Complete "Phase II Care Complete"
, PhaseII_Out "Phase II OUT"
, PhaseII_Return "Phase II Return"
, PhaseII_Out_II "Phase II OUT II"
, Ready_For_Discharge "Ready For Discharge"
, oal_Pat_Enc_CSN_ID "Pat Enc CSN ID"
, OR_Link_CSN
, Log_ID "OR Log"
, Log_Status "Log Status"
, asa_score "AN ASA Score"
, Emergent "AN Emergent"
, an_TYpe "AN Anesthesia Type"
, Anesthesia_Details "AN Anesthesia Details"
, Coded_Procedure_Date "Coded Procedure Date"
, Coded_Procedure "Coded Procedure"
, Coded_Provider "Coded Provider"
, Height "Height"
, Weight "Weight"
, BMI
from (SELECT loc.Loc_Name,
p.Pat_MRN_ID,
orc.Pat_ID,
pe.Pat_Enc_CSN_ID,
p.Pat_Name,
pe.hsp_Account_ID,
peh.Hosp_Admsn_Time,
peh.Hosp_Disch_Time,
ORC.OR_Case_ID,
orl.Surgery_Date,
orl.Trauma_Case_YN,
orc.Case_Confirmed_YN,
(SELECT name
FROM zc_or_Case_Type
WHERE case_Type_C = orl.Case_Type_C)
Case_Type,
(SELECT abbr
FROM zc_OR_Case_Class
WHERE Case_Class_C = orl.Case_Class_C)
Case_Class,
(SELECT name
FROM zc_OR_Pat_Status
WHERE Case_Progress_C = orc.Case_Progress_C)
Case_Progress,
(SELECT name
FROM zc_or_Void_Reason
WHERE Void_Reason_C = orl.Void_Reason_C)
Void_Reason,
orc.Void_Comments,
( SELECT LISTAGG (antype, CHR (10)) WITHIN GROUP (ORDER BY Line)
FROM (SELECT Log_ID,
Line,
(SELECT name
FROM zc_or_Anesth_Type
WHERE anesthesia_type_C = a.anesth_Type_C)
antype
FROM or_log_anestloc A)
WHERE log_ID = orl.Log_ID
GROUP BY Log_ID)
Anesthesia_Type,
( SELECT LISTAGG (dx, CHR (10)) WITHIN GROUP (ORDER BY Line)
FROM (SELECT Log_ID,
Line,
TO_CHAR (Line) || ' - ' || pre_OP_diag dx
FROM or_log_Preopdx A)
WHERE Log_ID = orl.Log_ID
GROUP BY Log_ID)
PreOP_Diagnosis,
( SELECT LISTAGG (dx, CHR (10)) WITHIN GROUP (ORDER BY Line)
FROM (SELECT Log_ID,
Line,
TO_CHAR (Line)
|| ' - '
|| (SELECT dx_name
|| ' ['
|| Historical_Ref_Code
|| ']'
FROM Clarity_edg
WHERE dx_ID = preOP_dx_Codes_ID)
dx
FROM or_log_Preop_Diags A)
WHERE Log_ID = orl.Log_ID
GROUP BY Log_ID)
PreOP_Diagnosis_Codes,
( SELECT LISTAGG (dx, CHR (10)) WITHIN GROUP (ORDER BY Line)
FROM (SELECT Log_ID,
Line,
TO_CHAR (Line) || ' - ' || Post_OP_dx dx
FROM or_log_postopdx A)
WHERE Log_ID = orl.Log_ID
GROUP BY Log_ID)
PostOP_Diagnosis,
( SELECT LISTAGG (dx, CHR (10)) WITHIN GROUP (ORDER BY Line)
FROM (SELECT Log_ID,
Line,
TO_CHAR (Line)
|| ' - '
|| (SELECT dx_name
|| ' ['
|| Historical_Ref_Code
|| ']'
FROM Clarity_edg
WHERE dx_ID = PostOP_dx_Codes_ID)
dx
FROM or_log_postop_Diag A)
WHERE Log_ID = orl.Log_ID
GROUP BY Log_ID)
PostOP_Diagnosis_Codes,
CASE
WHEN orl.Primary_Phys_ID IS NOT NULL
THEN
(SELECT COALESCE (External_Name, Prov_Name)
|| ' ['
|| Primary_Phys_ID
|| ']'
FROM clarity_Ser
WHERE prov_id = orl.Primary_Phys_ID)
ELSE
NULL
END
Primary_Physician,
( SELECT LISTAGG (Surgeon, CHR (10)) WITHIN GROUP (ORDER BY Line)
FROM (SELECT Log_ID,
Line,
TO_CHAR (Line)
|| ' - '
|| (SELECT COALESCE (External_Name, Prov_Name)
FROM clarity_SER
WHERE Prov_ID = a.surg_ID)
|| ' ['
|| a.surg_id
|| ']'
|| ' ('
|| (SELECT Prov_Type
FROM Clarity_SER
WHERE Prov_ID = a.surg_ID)
|| ')'
|| ' Panel-'
|| TO_CHAR (Panel)
Surgeon
FROM OR_log_All_Surg A)
WHERE Log_ID = oal.Log_ID
GROUP BY Log_ID)
Surgeon,
( SELECT LISTAGG (SVC, CHR (10)) WITHIN GROUP (ORDER BY Line)
FROM (SELECT Log_ID,
Line,
TO_CHAR (Line)
|| ' - '
|| (SELECT name
FROM zc_or_Service
WHERE service_c = a.Service_C)
SVC
FROM OR_log_All_Surg A)
WHERE Log_ID = oal.Log_ID
GROUP BY Log_ID)
Service,
( SELECT LISTAGG (role, CHR (10)) WITHIN GROUP (ORDER BY Line)
FROM (SELECT Log_ID,
Line,
TO_CHAR (Line)
|| ' - '
|| (SELECT name
FROM zc_or_Panel_Role
WHERE role_C = a.role_C)
role
FROM or_log_all_Surg A)
WHERE log_ID = oal.Log_ID
GROUP BY Log_ID)
Role,
( SELECT LISTAGG (Start_Time, CHR (10))
WITHIN GROUP (ORDER BY Line)
FROM (SELECT Log_ID,
Line,
TO_CHAR (Line)
|| ' - '
|| TO_CHAR (Start_Time, 'mm/dd/yyyy hh24:mi')
Start_Time
FROM or_log_all_Surg A)
WHERE log_ID = oal.Log_ID
GROUP BY Log_ID)
Start_Time,
( SELECT LISTAGG (end_Time, CHR (10)) WITHIN GROUP (ORDER BY Line)
FROM (SELECT Log_ID,
Line,
TO_CHAR (Line)
|| ' - '
|| TO_CHAR (End_Time, 'mm/dd/yyyy hh24:mi')
end_Time
FROM or_Log_All_Surg A)
WHERE Log_ID = oal.Log_ID
GROUP BY Log_ID)
End_Time,
( SELECT LISTAGG (WND, CHR (10))
WITHIN GROUP (ORDER BY Panel, Ordinal)
FROM (SELECT Log_ID,
Line,
panel,
ordinal,
TO_CHAR (Panel)
|| '/'
|| TO_CHAR (Ordinal)
|| ' - '
|| Anes_Type
WND
FROM HCHD_VW_Panel_Times A)
WHERE Log_ID = orl.Log_ID
GROUP BY Log_ID)
Panel_Anes_Type,
( SELECT LISTAGG (WND, CHR (10))
WITHIN GROUP (ORDER BY Panel, Ordinal)
FROM (SELECT Log_ID,
Line,
panel,
ordinal,
TO_CHAR (Panel)
|| '/'
|| TO_CHAR (Ordinal)
|| ' - '
|| Wound_Class
WND
FROM HCHD_VW_Panel_Times A)
WHERE Log_ID = orl.Log_ID
GROUP BY Log_ID)
Panel_Wound_Class,
( SELECT LISTAGG (proc, CHR (10))
WITHIN GROUP (ORDER BY Panel, Ordinal)
FROM (SELECT Log_ID,
Line,
panel,
ordinal,
TO_CHAR (Panel)
|| '/'
|| TO_CHAR (Ordinal)
|| ' - '
|| Procedure
|| ' ['
|| a.or_Proc_ID
|| ']'
Proc
FROM HCHD_VW_Panel_Times A)
WHERE Log_ID = orl.Log_ID
GROUP BY Log_ID)
Panel_Procedure_Card,
( SELECT LISTAGG (proc, CHR (10))
WITHIN GROUP (ORDER BY Panel, Ordinal)
FROM (SELECT Log_ID,
Line,
Panel,
Ordinal,
TO_CHAR (Panel)
|| '/'
|| TO_CHAR (Ordinal)
|| ' - '
|| all_proc_as_ordered
Proc
FROM HCHD_VW_Panel_Times A)
WHERE Log_ID = orl.Log_ID
GROUP BY Log_ID)
Panel_Procedure_As_Ordered,
( SELECT LISTAGG (tim, CHR (10))
WITHIN GROUP (ORDER BY Panel, Ordinal)
FROM (SELECT Log_ID,
Line,
Panel,
Ordinal,
TO_CHAR (Panel)
|| '/'
|| TO_CHAR (Ordinal)
|| ' - '
|| TO_CHAR (Procedure_Start, 'mm/dd/yyyy hh24:mi')
tim
FROM HCHD_VW_Panel_Times A)
WHERE Log_ID = orl.Log_ID
GROUP BY Log_ID)
Panel_Procedure_Start,
( SELECT LISTAGG (tim, CHR (10))
WITHIN GROUP (ORDER BY Panel, Ordinal)
FROM (SELECT Log_ID,
Line,
Panel,
Ordinal,
TO_CHAR (Panel)
|| '/'
|| TO_CHAR (Ordinal)
|| ' - '
|| TO_CHAR (Procedure_end, 'mm/dd/yyyy hh24:mi')
tim
FROM HCHD_VW_Panel_Times A)
WHERE Log_ID = orl.Log_ID
GROUP BY Log_ID)
Panel_Procedure_End,
( SELECT LISTAGG (tim, CHR (10))
WITHIN GROUP (ORDER BY Panel, Ordinal)
FROM (SELECT Log_ID,
Line,
Panel,
Ordinal,
TO_CHAR (Panel)
|| '/'
|| TO_CHAR (Ordinal)
|| ' - '
|| TO_CHAR (Incision, 'mm/dd/yyyy hh24:mi')
tim
FROM HCHD_VW_Panel_Times A)
WHERE Log_ID = orl.Log_ID
GROUP BY Log_ID)
Panel_Incision
........
)复制 专家解答
信息请求
============================
你能为我们做以下事情吗？
从my_view中选择/gather_plan_statistics */ *;
从表中选择 * (dbms_xplan.display_curs(null，null，'ALLSTATS LAST'));
从 [大 _ 长 _ sql中选择/gather_plan_statistics */ *]
从表中选择 * (dbms_xplan.display_curs(null，null，'ALLSTATS LAST'));
并将这两个计划 (请将等宽字体) 邮寄到
asktom_us@oracle.com
使用主题行中的问题ID，然后通过链接更新问题来ping我们
============================
好的，当您查看计划时，我看到了一些对窗口排序的引用，因此我假设您在其中使用了一些分析功能。这是导致视图出现效率不高的常见原因。
即使在最简单的例子中 -- 想想对客户银行余额进行排名的观点，例如
假设CUST_ID上有一个索引。
从V中选择 *，其中cust_id = 123
* 必须 * 扫描整个表，因为视图的逻辑定义是 “向我显示 * 所有 * 客户的排名”。
如果我将cust = 123推入视图，我会得到一行...猜猜是什么，排名结果总是1，因为排名1行总是给出 “1”。
显然你的例子要复杂得多，但总的来说，优化器会采取谨慎的方法来确保它甚至没有通过将谓词向下推入视图来返回错误结果的可能性。
============================
你能为我们做以下事情吗？
从my_view中选择/gather_plan_statistics */ *;
从表中选择 * (dbms_xplan.display_curs(null，null，'ALLSTATS LAST'));
从 [大 _ 长 _ sql中选择/gather_plan_statistics */ *]
从表中选择 * (dbms_xplan.display_curs(null，null，'ALLSTATS LAST'));
并将这两个计划 (请将等宽字体) 邮寄到
asktom_us@oracle.com
使用主题行中的问题ID，然后通过链接更新问题来ping我们
============================
好的，当您查看计划时，我看到了一些对窗口排序的引用，因此我假设您在其中使用了一些分析功能。这是导致视图出现效率不高的常见原因。
即使在最简单的例子中 -- 想想对客户银行余额进行排名的观点，例如
create view V as
select cust_id,
rank() over ( order by bank_balance )
from bank_accounts
复制假设CUST_ID上有一个索引。
从V中选择 *，其中cust_id = 123
* 必须 * 扫描整个表，因为视图的逻辑定义是 “向我显示 * 所有 * 客户的排名”。
如果我将cust = 123推入视图，我会得到一行...猜猜是什么，排名结果总是1，因为排名1行总是给出 “1”。
显然你的例子要复杂得多，但总的来说，优化器会采取谨慎的方法来确保它甚至没有通过将谓词向下推入视图来返回错误结果的可能性。
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
相关阅读
Oracle RAC 一键安装翻车？手把手教你如何排错！
Lucifer三思而后行
598次阅读
2025-04-15 17:24:06
【纯干货】Oracle 19C RU 19.27 发布，如何快速升级和安装？
Lucifer三思而后行
579次阅读
2025-04-18 14:18:38
XTTS跨版本迁移升级方案（11g to 19c RAC for Linux）
zwtian
492次阅读
2025-04-08 09:12:48
Oracle数据库一键巡检并生成HTML结果，免费脚本速来下载!
陈举超
475次阅读
2025-04-20 10:07:02
【ORACLE】记录一些ORACLE的merge into语句的BUG
DarkAthena
460次阅读
2025-04-22 00:20:37
Oracle 19c RAC更换IP实战，运维必看！
szrsu
437次阅读
2025-04-08 23:57:08
【ORACLE】你以为的真的是你以为的么?--ORA-38104: Columns referenced in the ON Clause cannot be updated
DarkAthena
436次阅读
2025-04-22 00:13:51
【活动】分享你的压箱底干货文档，三篇解锁进阶奖励！
墨天轮编辑部
422次阅读
2025-04-17 17:02:24
火焰图--分析复杂SQL执行计划的利器
听见风的声音
368次阅读
2025-04-17 09:30:30
3月“墨力原创作者计划”获奖名单公布
墨天轮编辑部
358次阅读
2025-04-15 14:48:05
