sql - 需要在我的查询 oracle r12 中删除重复项

Question

我是 Oracle 数据库的新手。我目前正在查询员工的月薪。我可以得到员工的工资。问题是查询非常慢。我相信这是因为它返回重复项。我已经找到并使用了我知道会有重复的表的数据跟踪。我似乎找不到删除最后一组重复项的方法。我想我应该使用一个我没有使用过的表（在我当前的查询中）来使用该表来删除重复项。我感谢您的帮助，并感谢您抽出宝贵的时间。

SELECT  (EEV.SCREEN_ENTRY_VALUE)/12 SALARY
FROM apps.PER_JOBS_TL JBT, 
apps.PER_ALL_ASSIGNMENTS_F ASF,
apps.PER_PEOPLE_F PER,
apps.PAY_PAYROLL_ACTIONS PPA, 
apps.PAY_ASSIGNMENT_ACTIONS PAA,
PAY_ELEMENT_ENTRY_VALUES_F EEV, 
apps.PAY_ELEMENT_ENTRIES_F EE,
apps.PER_PAY_BASES PPB
----------------------

----------------------
WHERE JBT.JOB_ID(+) = ASF.JOB_ID
and upper(PER.Employee_number) LIKE '%'
AND PER.PERSON_ID = ASF.PERSON_ID
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID 
AND PPA.ACTION_TYPE IN ('R','Q','P','U','V')
AND PAA.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID

AND PPB.PAY_BASIS_ID        = ASF.PAY_BASIS_ID
AND EEV.INPUT_VALUE_ID+0    = PPB.INPUT_VALUE_ID
AND PPA.DATE_EARNED  BETWEEN ASF.EFFECTIVE_START_DATE    AND ASF.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED  BETWEEN PER.EFFECTIVE_START_DATE    AND PER.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED  BETWEEN EEV.EFFECTIVE_START_DATE    AND EEV.EFFECTIVE_END_DATE
AND PPA.DATE_EARNED                    BETWEEN EE.EFFECTIVE_START_DATE     AND EE.EFFECTIVE_END_DATE 
AND EE.ASSIGNMENT_ID        = ASF.ASSIGNMENT_ID
AND EE.ELEMENT_ENTRY_ID     = EEV.ELEMENT_ENTRY_ID


AND ASF.CREATION_DATE  BETWEEN ASF.EFFECTIVE_START_DATE AND ASF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN '01-dec-2019' AND '31-dec-2019'

标签： sqloracle

Answer 1

通过使用现代连接语法的查询，它看起来像 -

SELECT  (EEV.SCREEN_ENTRY_VALUE)/12 SALARY
FROM apps.PER_JOBS_TL JBT
LEFT JOIN apps.PER_ALL_ASSIGNMENTS_F ASF ON JBT.JOB_ID = ASF.JOB_ID
JOIN apps.PER_PEOPLE_F PER ON PER.PERSON_ID = ASF.PERSON_ID
JOIN apps.PAY_ASSIGNMENT_ACTIONS PAA ON PAA.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID
JOIN apps.PAY_PAYROLL_ACTIONS PPA ON PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID 
                                 AND PPA.DATE_EARNED BETWEEN ASF.EFFECTIVE_START_DATE AND ASF.EFFECTIVE_END_DATE
                                 AND PPA.DATE_EARNED BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
JOIN apps.PER_PAY_BASES PPB ON PPB.PAY_BASIS_ID = ASF.PAY_BASIS_ID
JOIN PAY_ELEMENT_ENTRY_VALUES_F EEV ON EEV.INPUT_VALUE_ID+0 = PPB.INPUT_VALUE_ID
                                   AND PPA.DATE_EARNED BETWEEN EEV.EFFECTIVE_START_DATE AND EEV.EFFECTIVE_END_DATE
JOIN apps.PAY_ELEMENT_ENTRIES_F EE ON EE.ASSIGNMENT_ID = ASF.ASSIGNMENT_ID
                                  AND EE.ELEMENT_ENTRY_ID = EEV.ELEMENT_ENTRY_ID
                                  AND PPA.DATE_EARNED BETWEEN EE.EFFECTIVE_START_DATE AND EE.EFFECTIVE_END_DATE
WHERE UPPER(PER.Employee_number) LIKE '%'
AND PPA.ACTION_TYPE IN ('R','Q','P','U','V')
AND ASF.CREATION_DATE  BETWEEN ASF.EFFECTIVE_START_DATE AND ASF.EFFECTIVE_END_DATE
AND PPA.EFFECTIVE_DATE BETWEEN '01-dec-2019' AND '31-dec-2019'

我刚刚意识到您可能缺少表的连接条件apps.PAY_ASSIGNMENT_ACTIONS。这可能是结果重复的主要问题。如果您对此表没有其他连接条件，那一切都很好。

为了加快您的查询速度，您可以在您的表上尝试以下索引 -

apps.PER_JOBS_TL (JOB_ID)              -- Not required if it is primary key
apps.PER_ALL_ASSIGNMENTS_F (JOB_ID, PERSON_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE )    -- Not required on primary key columns
apps.PER_PEOPLE_F (PERSON_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)    -- Not required on primary key columns
apps.PAY_ASSIGNMENT_ACTIONS(ASSIGNMENT_ID)        -- Not required if it is primary key
apps.PAY_PAYROLL_ACTIONS(ACTION_TYPE, EFFECTIVE_DATE, DATE_EARNED)   -- Not required on primary key columns
apps.PER_PAY_BASES(PAY_BASIS_ID, INPUT_VALUE_ID)  -- Not required on primary key columns
PAY_ELEMENT_ENTRY_VALUES_F(INPUT_VALUE_ID, ELEMENT_ENTRY_ID, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE)   -- Not required on primary key columns
apps.PAY_ELEMENT_ENTRIES_F(ASSIGNMENT_ID,ELEMENT_ENTRY_ID, EFFECTIVE_START_DATE,EFFECTIVE_END_DATE)     -- Not required on primary key columns