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
........
)
Request for info
===============================
Can you do the following for us.
select /+ gather_plan_statistics*/ * from my_view;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
select /+ gather_plan_statistics*/ * from [the_big_long_sql]
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
and mail the two plans (monospace font please) to
asktom_us@oracle.com
with the question ID in the subject line, then ping us back by updating the question via the link
===============================
Ok, when you look at the plans, I see some references to WINDOW SORT, so I'm presuming you've got some analytic functions in use in there. That is a common cause of views giving the *appearance* of not being efficient.
Even in the simplest of examples - think of a view which ranks customers bank balance, eg
create view V as
select cust_id,
rank() over ( order by bank_balance )
from bank_accounts
and let's say there is an index on CUST_ID.
select * from V where cust_id = 123
*must* scan the entire table, because the logical definition of the view is "show me the ranking of *all* customers".
If I pushed the cust=123 into the view, I'd get one row ... and guess what, the rank result would be 1 always, because ranking 1 row always gives "1".
Obviously your example is much more complicated, but in general, the optimizer is going to take a cautious approach to ensure it doesn't even have the *possibility* of returning wrong results by pushing predicates down into views.