SQL Performance issue?

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

myworldntl

Thread Starter
Joined
Jan 19, 2007
Messages
1
The SQL below was given to me and i have altered and it still works and quicker, but still is too slow...

The SQL below that i have altered to run faster but i think at least one line is incorrect and and also, the explain plan says that it costs more...

Unfortunately the customer at work i am doing this for is running Oracle 8....

Original SQL

SELECT SYSDATE AS DATE_ROW_CREATED,
con.con_pos_ref_no AS POSITION_REFERENCE,
job.party_nm AS JOB_TITLE,
con.party_lnk_d AS START_DATE,
con.party_lnk_ed AS END_DATE,
spt.scale_pt_refcd AS SCALE_POINT,
phs.party_hrs AS HOURS,
mpy.con_pos_ref_no AS LINE_MANAGER,
pay.payroll_nm AS PAYROLL_NAME,
NVL(GREATEST(con.last_mod_d, pip.last_mod_d, man.last_mod_d, phs.last_mod_d, pgd.last_mod_d),SYSDATE) AS LAST_UPDATED

FROM

trentadm.tparty_lnk con,
trentadm.tparty_in_pay pip,
trentadm.tpayroll pay,
trentadm.tparty job,
trentadm.tpty_det_own pdo,
trentadm.tparty_hrs phs,
trentadm.tparty_grade pgd,
trentadm.tscale_pt spt,
trentadm.tparty_lnk man,
trentadm.tparty_lnk mpy

WHERE 1=1
AND job.party_id = con.party_id2
AND con.party_id2 = man.party_id(+)
AND con.party_id2 = pdo.party_id(+)
AND man.party_id2 = mpy.party_id2(+)
AND pip.party_id = con.party_id
AND pay.payroll_id = pip.payroll_id
AND pdo.owner_id = phs.party_id(+)
AND pgd.scale_pt_act = spt.scale_pt_id(+)




AND pip.party_in_pay_d < SYSDATE
AND (pip.party_in_pay_ed >= SYSDATE OR pip.party_in_pay_ed IS NULL)




AND pdo.detail_group(+) = 'CONT_HRS'
AND pdo.detail_type(+) = 'CONT_HRS'
AND pdo.pty_det_own_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(pdo.pty_det_own_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')


AND phs.hrs_type_id(+) = 'CONT_HRS'
AND phs.party_hrs_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(phs.party_hrs_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')


AND pgd.party_grade_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(pgd.party_grade_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')


AND man.link_type_id(+) = 'POSN_POSN'
AND man.party_lnk_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(man.party_lnk_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')


AND mpy.link_type_id(+) = 'CONT'
AND mpy.party_lnk_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(mpy.party_lnk_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')

AND 'Y' = DECODE(pgd.party_id(+), con.party_id2, 'Y', 'N')
--LINE ABOVE IS THE 1 I THINK I GOT WRONG IN THE NEW SQL

AND con.PARTY_LNK_D <= SYSDATE
AND (con.PARTY_LNK_ED >= SYSDATE OR con.PARTY_LNK_ED IS NULL)

AND job.party_d < SYSDATE
AND (job.party_ed >= SYSDATE OR job.party_ed IS NULL)

AND EXISTS
(
SELECT position_id FROM ZTSDPOSITIONID
WHERE
position_id = job.party_id
)


NEW SQL

SELECT SYSDATE AS DATE_ROW_CREATED,
con.con_pos_ref_no AS POSITION_REFERENCE,
job1.party_nm AS JOB_TITLE,
con.party_lnk_d AS START_DATE,
con.party_lnk_ed AS END_DATE,
spt.scale_pt_refcd AS SCALE_POINT,
phs.party_hrs AS HOURS,
mpy.con_pos_ref_no AS LINE_MANAGER,
pay.payroll_nm AS PAYROLL_NAME,
NVL(GREATEST(con.last_mod_d, pip.last_mod_d, man.last_mod_d, phs.last_mod_d, pgd.last_mod_d),SYSDATE) AS LAST_UPDATED

FROM trentadm.tparty job1

INNER JOIN
(
SELECT party_id, party_id2, con_pos_ref_no, party_lnk_d, party_lnk_ed, last_mod_d FROM trentadm.tparty_lnk
WHERE PARTY_LNK_D <= SYSDATE
AND (PARTY_LNK_ED >= SYSDATE OR PARTY_LNK_ED IS NULL)
) con ON job1.party_id = con.party_id2

INNER JOIN
(
SELECT party_id, payroll_id, last_mod_d FROM trentadm.tparty_in_pay
WHERE party_in_pay_d < SYSDATE
AND (party_in_pay_ed >= SYSDATE OR party_in_pay_ed IS NULL)
) pip ON pip.party_id = con.party_id

LEFT OUTER JOIN
(
SELECT party_id, party_id2, last_mod_d FROM trentadm.tparty_lnk
WHERE party_lnk_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(party_lnk_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')
AND link_type_id(+) = 'POSN_POSN'
) man ON con.party_id2 = man.party_id --(+)

LEFT OUTER JOIN
(
SELECT party_id, last_mod_d, scale_pt_act FROM trentadm.tparty_grade
WHERE party_grade_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(party_grade_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')
) pgd ON 'Y' = DECODE(pgd.party_id, con.party_id2, 'Y', 'N')
--THIS 'ON' is DERIVED FROM THE LINE I ANNOTATED IN THE ORIGINAL SQL

LEFT OUTER JOIN
(
SELECT party_id, owner_id FROM trentadm.tpty_det_own
WHERE pty_det_own_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(pty_det_own_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')
AND detail_group(+) = 'CONT_HRS'
AND detail_type(+) = 'CONT_HRS'
) pdo ON con.party_id2 = pdo.party_id --(+)


LEFT OUTER JOIN
(
SELECT party_id, party_hrs, last_mod_d FROM trentadm.tparty_hrs
WHERE party_hrs_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(party_hrs_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')
AND hrs_type_id(+) = 'CONT_HRS'
) phs ON pdo.owner_id = phs.party_id --(+)

INNER JOIN trentadm.tpayroll pay
ON pay.payroll_id = pip.payroll_id

LEFT OUTER JOIN
(
SELECT party_id2, con_pos_ref_no FROM trentadm.tparty_lnk
WHERE party_lnk_d(+) < SYSDATE
AND 'Y' = DECODE(LEAST(party_lnk_ed(+),SYSDATE), NULL, 'Y', SYSDATE, 'Y', 'N')
AND link_type_id(+) = 'CONT'
) mpy ON man.party_id2 = mpy.party_id2 --(+)


LEFT OUTER JOIN trentadm.tscale_pt spt ON pgd.scale_pt_act = spt.scale_pt_id --(+)

WHERE 1=1

AND (job1.party_ed >= SYSDATE OR job1.party_ed IS NULL)
AND job1.party_d < SYSDATE
AND EXISTS
(
SELECT position_id FROM ZTSDPOSITIONID
WHERE
position_id = job1.party_id
)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top