| Query [message #468668] |
Sun, 01 August 2010 01:56  |
ramesh55.sse
Messages: 262
Registered: December 2008
Location: Hyderabad
|
Senior Member |
|
|
Hi All,
How can we improve the perforamnce of this query ,it is taking nearly 1hr 30m , and this query retrieving 2000 records only.
Please help me on this.
SELECT Z.ID , Z.PTS , Z.DI FROM
( SELECT
A.ID ,
NVL(
( SELECT 'Y' FROMPS@ABC.world E
WHERE E.CD= A.CD
AND E.ID = A.ID
AND EXISTS ( SELECT 'X' FROM
PS@ABC.world J
WHERE E.ID = J.DEPTID
AND J.EMPL_STATUS IN
('A','L','P') AND J.EMPL_RCD = 0)),'N') PT,
NVL(
( SELECT 'Y'
FROMPS@ABC.world F , PS_JOBCODE_TBL@ABC.world JC
WHERE F.EMPL_RCD =A.EMPL_RCD
AND F.ID = A.ID
AND F.SETID_JOBCODE = JC.SETID
AND F.JOBCODE = JC.JOBCODE
AND JC.EFFDT =
( SELECT MAX(JC1.EFFDT) FROM PS_JOBCODE_TBL@ABC.world JC1
WHERE JC1.SETID = JC.SETID
AND JC1.JOBCODE = JC.JOBCODE
AND JC1.EFFDT <= SYSDATE)
AND (EXISTS ( SELECT 'X' FROMPS@ABC.world J1
WHERE F.ID = J1.DEPTID
AND J1.EMPL_STATUS IN ('A','L','P') AND J1.EMPL_RCD = 0) OR (JC.MANAGER_LEVEL = '65'))),'N')
DI FROMPS@ABC.world A
WHERE A.EMPL_STATUS IN ('A','L','P') AND A.EMPL_RCD = 0) Z
Thanks in advance.
|
|
|
|
|
|
|
|
|
|
| Re: Query [message #468743 is a reply to message #468730] |
Mon, 02 August 2010 00:46  |
 |
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
|
Senior Member |
|
|
Can you please Copy and post the Explain plan of this Query?
Explain plan
set pagesize 25
set linesize 121
EXPLAIN PLAN FOR
< Your Query >;
SELECT * FROM TABLE(dbms_xplan.display);
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
