| count and sum making a query slow [message #276735] |
Fri, 26 October 2007 03:13  |
subhajit
Messages: 14
Registered: December 2006
|
Junior Member |
|
|
I want to run a query from D2K,
SELECT COUNT(APP_NO), SUM(AMNT_REIM)
FROM MED_QUERY2
WHERE APP_DATE >= '01-APR-07'
AND APP_DATE <= '30-APR-07'
AND APP_NO IS NOT NULL
AND KSO ='N'....... here MED_QUERY2 is a view ...this count and sum making this query very slow....but when i run the same query with only * then results comes very fast....
I am not able to understand why? plzz help me..
|
|
|
|
|
|
|
|
|
|
| Re: count and sum making a query slow [message #277101 is a reply to message #276817] |
Sun, 28 October 2007 22:37  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
When you SELECT *, the FIRST ROW is returned very quickly, but it will take a long time to return EVERY row.
COUNT/SUM require that every row is retrieved from disk before a single row is displayed. It's not actually slower, it just seems that way.
Ross Leishman
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: count and sum making a query slow [message #277423 is a reply to message #276735] |
Tue, 30 October 2007 01:13 |
subhajit
Messages: 14
Registered: December 2006
|
Junior Member |
|
|
i am now sending you the explain plan of the query that is making the view
SELECT
B.EMP_APP_NO,D.EMP_APP_DATE,B.EMP_APP_REIM_DT,C.EMP_TYPE,C.EMP_DESIG,C.EMP_DEPT,C.SUB_CAT,
C.EMP_PLACE,C.EMP_PROFIT_CENTER,A.EMP_PER_NO,A.EMP_RELATION_NAME,A.EMP_RELATION,
A.EMP_R_GENDER,A.EMP_RELATION_AGE,GET_AILMENTS(NVL(A.EMP_RELATION_ALIMENT,'0')) AILMENTS
,D.EMP_REIM_AMT RIM,A.EMP_HOSPITAL,B.STATUS,B.EXCHL_FLG,A.EMP_DEP_PLACE,
C.KSO,B.INHOUSE,E.HEAD_DESC, E.HEAD_TYPE_DESC,
GET_PROCEDURE(B.EMP_APP_NO,A.EMP_PER_NO,A.EMP_RELATION_NAME,A.EMP_RELATION) PROCEDURE_NM
,B.EUSER
FROM
TTD_EMP_MEDICAL A
, TT_EMP_MEDICAL B
, TM_EMP C
, TTD_EXP_DTL D
, TM_HEAD E
WHERE B.EMP_PER_NO = A.EMP_PER_NO
AND B.EMP_APP_NO = A.EMP_APP_NO
AND C.EMP_PER_NO = B.EMP_PER_NO
AND B.EMP_PER_NO = D.EMP_PER_NO
AND B.EMP_APP_NO = D.EMP_APP_NO
AND B.EMP_APP_DATE = D.EMP_APP_DATE
AND (A.EMP_RELATION_NAME||A.EMP_RELATION) = (D.EMP_RELATION_NAME||D.EMP_RELATION_TYPE)
AND A.EMP_RELATION = D.EMP_RELATION_TYPE
AND B.EMP_APP_STATUS = 'Y'
AND (D.EMP_HEAD||D.EMP_HEAD_TYPE) = (E.HEAD_CODE||E.HEAD_TYPE_CODE)
AND D.EMP_HEAD_TYPE = E.HEAD_TYPE_CODE
explain plan is attached.
-
Attachment: untitled.bmp
(Size: 364.76KB, Downloaded 710 times)
|
|
|
|
| Re: count and sum making a query slow [message #277540 is a reply to message #277423] |
Tue, 30 October 2007 08:34 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You seem to have sent us the second half of the explain plan.
The joins of the type (A.col_1||A.col2) = (B.col_1||B.col_2) will mean that indexes on col_1 or col_2 won't be used.
What does the function GET_PROCEDURE do?
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
