| Query Performance [message #218833] |
Sun, 11 February 2007 00:47  |
sarfraz_attari
Messages: 123
Registered: July 2005
|
Senior Member |
|
|
Dear All
In Our Database the Query Performance is Very Poor Specially whenever we are using "Not In" Function or "Order By" or "Group By" Clause , sometimes it takes minutes to execute a query but same query if we use minus it takes fraction of seconds to execute what is the exact reason can anyone tell us
thanks
|
|
|
|
| Re: Query Performance [message #218836 is a reply to message #218833] |
Sun, 11 February 2007 02:33  |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
without posting the query and the statistics it will difficult for anybody tell you why it is behaving like this.
Information needed are
a) first the actual query which you have executed
b) Statistics of the table
c) Explain plan
cheers
|
|
|
|
| Re: Query Performance [message #218837 is a reply to message #218836] |
Sun, 11 February 2007 02:59 |
sarfraz_attari
Messages: 123
Registered: July 2005
|
Senior Member |
|
|
Query is
SELECT A.PART1,A.PART2,A.PART3,SUM(STOCK_QTY) STK,
NVL(B.WAVG_COST,0) COST,
(SELECT MAX(INV_DT)
FROM ituser.PURCHASE_DETAILS_it
WHERE PART1 = A.PART1 AND PART2 = A.PART2
AND PART3 = A.PART3 )INV_DT,
(SELECT
DECODE(TRUNC((SYSDATE-MAX(TO_DATE(INV_DT,'DD-MON-RR')))/365),0,10,1,10,2,10,3,10
,4,20,5,20,6,30,7,30,8,30,9,30,10,30,50)
FROM ITUSER.PURCHASE_dETAILS_IT
WHERE PART1 = A.PART1 AND PART2 = A.PART2
AND PART3 = A.PART3 ) DISC
FROM WHOUSE_LOC_STOCK A,M_PART B
WHERE A.PART1 = B.PART1
AND A.PART2 = B.PART2 AND A.PART3 = B.PART3
GROUP BY A.PART1,A.PART2,A.PART3,NVL(B.WAVG_COST,0)
It is taking at least an hour to finish
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
