| Cost is high [message #208738] |
Tue, 12 December 2006 00:39  |
subhajit
Messages: 14
Registered: December 2006
|
Junior Member |
|
|
Hi all,
I wrote a quary in a VB application like,
1.Here A,B,C are three tables.
2.A,B,C are properly indexed
3.Size of A,B is high but C is low
SELECT DISTINCT RR.GNO GNO,R.VGNO VGNO,R.PDE
FROM A R,B RR
Where TO_DATE(TO_CHAR(R.DATELOD,'DD-MON-YYYY'),'DD-MON-YYYY') ='12-DEC-2005'
AND R.PDE IS NOT NULL
AND R.PDE <> 'NEW'
AND R.STATE IS NULL
AND R.VGNO
NOT IN (SELECT VGNO FROM C
where TO_DATE(TO_CHAR(DATELOD,'DD-MON-YYYY'),'DD-MON-YYYY') ='12-DEC-2005')
AND RR.RNO = R.CH
AND RR.RNO= R.VGNO
ORDER BY VGNO
but the cost of this quary is around 1000 and the process taking 15 min to complete.
Can anybody help me to reduce cost of this and also reduce time.I am using ORACLE 9i
[Updated on: Tue, 12 December 2006 12:04] Report message to a moderator |
|
|
|
|
|
| Re: Cost is high [message #208931 is a reply to message #208738] |
Tue, 12 December 2006 11:24  |
dba_blr
Messages: 43
Registered: December 2006
|
Member |
|
|
|
Please do not simply post the queries and ask to tune. To tune queries one need a lot of other information as well, like indexes, size of objects, statisicts collection details etc etc.
|
|
|
|
| Re: Cost is high [message #209040 is a reply to message #208738] |
Wed, 13 December 2006 01:00 |
michael_bialik
Messages: 621
Registered: July 2006
|
Senior Member |
|
|
Quote:
"2.A,B,C are properly indexed" - Actually it means "I think the indexes are OK" - it's your opinion without any proof for it.
Please explain following condition:
... AND RR.RNO= R.CH AND RR.RNO = R.VGNO
( 2 columns from table A reference the same column of B)
Anyway the query can NOT use indexes because of TO_DATE(TO_CHAR(..)).
Try following:
SELECT DISTINCT RR.GNO GNO,R.VGNO VGNO,R.PDE
FROM A R,B RR
Where
R.DATELOD BETWEEN TO_DATE('12-DEC-2005 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AND TO_DATE('12-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
AND R.PDE IS NOT NULL
AND R.PDE <> 'NEW'
AND R.STATE IS NULL
AND NOT EXISTS
( SELECT 1 FROM C
WHERE C.VGNO = R.VGNO AND
C.DATELOD BETWEEN TO_DATE('12-DEC-2005 00:00:00', 'DD-MON-YYYY HH24:MI:SS') AND TO_DATE('12-DEC-2005 23:59:59', 'DD-MON-YYYY HH24:MI:SS')
AND RR.RNO = R.CH
AND RR.RNO= R.VGNO
ORDER BY VGNO
Verify that you have following indexes:
1. Index on C with following 2 leading columns ( VGNO, DATELOD )
2. Index on A with DATELOD as first index column.
3. Index on B with ( RNO, (CH???) )
HTH.
Michael
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
