有一个语句
SELECT * FROM
(SELECTPAAF.PERSON_ID M_PERSON_ID ,
PAPF.LAST_NAME,PAPF.EMPLOYEE_NUMBER,
TO_CHAR(PAPF.DATE_OF_BIRTH,'YYYY-MM-DD') DATE_OF_BIRTH,
DECODE('F',PAPF.SEX,'女','男') SEX,
PAPF.REGION_OF_BIRTH JG,
PAPF.ATTRIBUTE1 MZ,
PAP.NAME POS_NAME,
HOU.NAME OU_NAME
FROM PER_ALL_ASSIGNMENTS_F PAAF,PER_ALL_PEOPLE_F PAPF,HR_ORGANIZATION_UNITS HOU,PER_ALL_POSITIONS PAP
WHEREPAAF.PRIMARY_FLAG='Y'
AND HOU.ORGANIZATION_ID=PAAF.ORGANIZATION_ID AND PAAF.POSITION_ID =PAP.POSITION_ID(+)
AND (SYSDATE) BETWEENPAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND (SYSDATE) BETWEENPAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PAAF.PERSON_ID=PAPF.PERSON_ID
--ANDPAAF.PERSON_ID BETWEEN 2826 AND 2827
) S01 ,
(SELECT PPA.PERSON_ID,
TO_CHAR(PPA.DATE_FROM ,'YYYY-MM-DD') ZZMM_DATE_FROM ,
PPA.DATE_TO ZZMM_DATE_TO,PAC.SEGMENT1 ZZMM
FROM PER_PERSON_ANALYSES PPA,
PER_ANALYSIS_CRITERIA PAC,
PER_SPECIAL_INFO_TYPES_v PSIV
WHEREPSIV.NAME='政治面貌' AND PAC.ID_FLEX_NUM=PSIV.ID_FLEX_NUMANDPPA.ANALYSIS_CRITERIA_ID=PAC.ANALYSIS_CRITERIA_ID
AND PPA.DATE_FROM=(SELECT MAX(PPA2.DATE_FROM)
From PER_PERSON_ANALYSES PPA2,
PER_ANALYSIS_CRITERIA PAC2
WHERE
PAC2.ID_FLEX_NUM=PAC.ID_FLEX_NUM
AND PPA2.ANALYSIS_CRITERIA_ID=PAC2.ANALYSIS_CRITERIA_ID
AND PPA.PERSON_ID=PPA2.PERSON_ID
GROUP BY PPA2.PERSON_ID)--政治面目
) S02
where s01.M_PERSON_ID =s02.PERSON_ID(+)
这样运行需要6秒,但是如果加上trunc函数变成
trunc (SYSDATE) BETWEENPAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND trunc (SYSDATE) BETWEENPAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
运行时间就变成未知数了,反正我是没等到它出来结果过,
但现在如果不加trunc 那么就会少计算一天,是不对的,这是怎么回事???
|
