Query optimisation

raak · Posted: Thu Jan 10, 2008 9:52 am

Is there any way that this query can be optimised??

SELECT B.SOC_SEC_NUM || ';' ||
B.LAST_NAME || ';' ||
B.FIRST_NAME || ';' ||
IFNULL(CHAR (B.BRTH_BIRTH_D), ' ') || ';' ||
B.GROUP_NUMBER || ';' ||
C.GROUP_NAME
FROM EMP_TABLE B
JOIN
GRP_TABLE C
ON B.GROUP_NUMBER = C.GROUP_NUMBER
WHERE B.PART_KEY IN
(
SELECT DISTINCT A.PART_KEY
FROM PAY_TABLE A
WHERE A.PAY_D > CURRENT DATE - 6 MONTHS
)
AND
B.PAY_STATUS IN
('00', '01', '07')

stodolas · Posted: Thu Jan 10, 2008 6:28 pm

Probably. Have you run an explain on it?

raak · Posted: Thu Jan 10, 2008 6:41 pm

ya.. it was saying that DISTINCT should be avoided and Nonqualified function ( I think it is the IFNULL condition) should also be avoided.

but I am creating a report out of this and DISTINCT cannot be avoided since there is gud possibility of duplicates occuring in that field.

Any thoughts on this???