oracle - 当我通过 emplid 它运行但是当我通过 Campus_id 它需要无限时间
问题描述
当我通过 empli 时它会运行,但是当我通过 Campus_id 时它会花费无限时间
WITH aa
AS (
SELECT DISTINCT emplid
,strm
,catalog_nbr
,COUNT(ATTEND_PRESENT) OVER (
PARTITION BY emplid
,strm
,catalog_nbr
) AS present_days
FROM ps_SRM_ATT_2_VW
WHERE ATTEND_PRESENT = 'Y'
)
,bb
AS (
SELECT DISTINCT emplid
,strm
,catalog_nbr
,COUNT(ATTEND_PRESENT) OVER (
PARTITION BY emplid
,strm
,catalog_nbr
) AS total_days
FROM ps_SRM_ATT_2_VW
)
,cc
AS (
SELECT DISTINCT emplid
,strm
,catalog_nbr
,nvl(sum(days) OVER (
PARTITION BY emplid
,strm
,catalog_nbr
), 0) AS relax_days
FROM PS_SRM_RELAX_TBL
)
,kk
AS (
SELECT DISTINCT emplid
,strm
,days
,CATALOG_NBR
FROM ps_srm_relax_tbl
)
,hh
AS (
SELECT DISTINCT a.emplid
,b.strm
,a.campus_id
,b.CATALOG_NBR
FROM ps_personal_data a
,ps_SRM_ATT_2_VW b
WHERE a.emplid = b.emplid
ORDER BY a.emplid
)
SELECT DISTINCT AA.emplid
,gg.name_display
,GG.campus_id
,nvl(kk.days, 0) AS relax_lectures
,(
CASE
WHEN tt.ssr_component = 'LEC'
OR tt.ssr_component = 'TUT'
THEN 'Theory'
ELSE 'Practical'
END
) AS component1
,nvl(round((
(
nvl(aa.present_days, 0) + nvl((
SELECT relax_days
FROM cc
WHERE emplid = aa.emplid
AND strm = aa.strm
AND catalog_nbr = aa.catalog_nbr
), 0)
) / nvl(bb.total_days, 1)
) * 100, 2), 0) AS total
,dd.acad_career
,dd.acad_prog
,ee.acad_plan
,aa.strm
,ff.ACAD_LEVEL_BOT
,bb.total_days AS total_lecture
,aa.present_days AS Lecture_attend
,pp.subject || ' ' || pp.catalog_nbr AS SubjectCatalog
,tt.COURSE_TITLE_LONG
FROM aa
LEFT JOIN cc ON aa.emplid = cc.emplid
AND aa.strm = cc.strm
AND aa.catalog_nbr = cc.catalog_nbr
这是 ps_personal_data gg 和校园 id 提示导致问题与此相关联
LEFT JOIN ps_personal_data gg ON AA.EMPLID = GG.EMPLID
LEFT JOIN hh ON aa.emplid = hh.emplid
AND aa.strm = hh.strm
AND aa.catalog_nbr = hh.catalog_nbr
LEFT JOIN kk ON cc.emplid = kk.emplid
AND cc.strm = kk.strm
AND cc.catalog_nbr = kk.catalog_nbr
LEFT JOIN ps_crse_offer pp ON aa.CATALOG_NBR = pp.catalog_nbr
LEFT JOIN ps_crse_catalog tt ON pp.crse_id = tt.crse_id
,bb
,PS_ACAD_PROG dd
,PS_ACAD_PLAN ee
,PS_STDNT_CAR_TERM ff
WHERE dd.effdt = (
SELECT max(effdt)
FROM ps_acad_prog
WHERE emplid = dd.emplid
)
AND dd.effseq = (
SELECT max(effseq)
FROM ps_acad_prog
WHERE emplid = dd.emplid
AND effdt = dd.effdt
)
AND ee.effdt = (
SELECT max(effdt)
FROM ps_acad_prog
WHERE emplid = ee.emplid
)
AND ee.effseq = (
SELECT max(effseq)
FROM ps_acad_prog
WHERE emplid = ee.emplid
AND effdt = ee.effdt
)
AND TT.EFFDT = (
SELECT max(effdt)
FROM ps_CRSE_CATALOG
WHERE CRSE_ID = PP.CRSE_ID
)
AND dd.emplid = aa.emplid
AND aa.emplid = hh.emplid
AND ee.emplid = aa.emplid
AND aa.emplid = ff.emplid
AND aa.emplid = ee.emplid
AND aa.emplid = bb.emplid
AND
--GG.CAMPUS_ID=HH.CAMPUS_ID AND
aa.catalog_nbr = hh.catalog_nbr
AND aa.strm = bb.strm
AND tt.eff_status = 'A'
AND aa.catalog_nbr = bb.catalog_nbr
AND aa.strm = ff.strm
AND tt.effdt = (
SELECT max(effdt)
FROM ps_crse_catalog
WHERE crse_id = pp.crse_id
)
AND AA.STRM = '1901'
AND
--AA.EMPLID='00000009724';
如果我隐藏 gg.campus_id 并通过 aa.emplid 它将在 3-4 秒内顺利运行。
这是我提供校园 ID 时的提示,它需要无限时间
GG.CAMPUS_id = : 1;
解决方案
推荐阅读
- postgresql - 如何在不丢失数据的情况下控制数据库版本,更像是 github
- sql - 如何在 SELECT 查询中间进行调试?
- ios - 如何将图像保存到 ImageView (SwiftUI) 中显示的照片库
- javascript - 根据现代规范,JS 中的提升顺序(函数声明与 var 声明)是什么?
- unreal-engine4 - 我怎样才能让我的角色不会随着我的车辆移动
- spring-boot - @EnableBatchProcessing 打破了其他 JPA junit 测试
- json - 使用 Swift 在 JSON 中解码多类型数组和未知长度数组
- python - Selenium 无法初始化
- java - 为什么 Spring 服务器不渲染我的 html 页面?
- c# - 如何在 Unity 中为按下按钮添加延迟