Trunc Creating Problem [message #418636] |
Mon, 17 August 2009 23:29  |
mamalik
Messages: 270 Registered: November 2008 Location: Pakistan
|
Senior Member |

|
|
Dear Sir,
I have a query in report which was running fine. query process 8 lacs records. now its too much slow but when i remove trunc function from query then it works fine.Trunc function is applied on DATE field and index is also applied on that field.
what may problem in trunc while same query was running fine before with trunc.
Regards.
Asif.
|
|
|
|
|
Re: Trunc Creating Problem [message #418642 is a reply to message #418636] |
Mon, 17 August 2009 23:47   |
mamalik
Messages: 270 Registered: November 2008 Location: Pakistan
|
Senior Member |

|
|
Select Sum(Nvl(Pac_Qty,0)) Stk
From Grey.Gry_02_14 G14,Grey.Gry_02_15 G15
Where G14.Pac_Dte=G15.Pac_Dte
And G14.Bar_Num=G15.Bar_Num
And G14.Pac_Typ=G15.Pac_Typ
And G14.Con_Mrk='AP/CM'
And G15.Avl_Dsp='A'
And Trunc(G15.Pac_Dte) <=:P_TOODTE
[Updated on: Mon, 17 August 2009 23:47] Report message to a moderator
|
|
|
|
|
|
|
|
Re: Trunc Creating Problem [message #418690 is a reply to message #418688] |
Tue, 18 August 2009 03:01   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You really do need to re-read @Michel's reply - it handles the problems that you are dealing with.
The TRUNC is probably slowing things down because you have an index on the PAC_DTE field, and use of that index is suppressed because of the TRUNC.
some of @Michels suggestions will fix that too - otherwise you'd need to create a function based index on TRUNC(PAC_DTE)
|
|
|
Re: Trunc Creating Problem [message #418715 is a reply to message #418636] |
Tue, 18 August 2009 05:24   |
mamalik
Messages: 270 Registered: November 2008 Location: Pakistan
|
Senior Member |

|
|
Query 1
Select Sum(Nvl(Pac_Qty,0)) Stk
From Grey.Gry_02_14 G14,Grey.Gry_02_15 G15
Where G14.Pac_Dte=G15.Pac_Dte
And G14.Bar_Num=G15.Bar_Num
And G14.Pac_Typ=G15.Pac_Typ
And G14.Con_Mrk='EB/CM' And G15.Avl_Dsp='A'
And G15.Pac_Dte <=Trunc(:P_TOODTE)+1
Query 2
Select Sum(Nvl(Pac_Qty,0)) Stk
From Grey.Gry_02_14 G14,Grey.Gry_02_15 G15
Where G14.Pac_Dte=G15.Pac_Dte
And G14.Bar_Num=G15.Bar_Num
And G14.Pac_Typ=G15.Pac_Typ
And G14.Con_Mrk='EB/CM' And G15.Avl_Dsp='A'
And TRUNC(G15.Pac_Dte) <=:P_TOODTE
Query 1 shows result in 19 Msecs while Query 2 shows result in 15 Msecs
Regards.
Asif.
[Updated on: Tue, 18 August 2009 05:52] Report message to a moderator
|
|
|
|
|
|
Re: Trunc Creating Problem [message #418788 is a reply to message #418715] |
Tue, 18 August 2009 09:47  |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Are you talking about milliseconds?
You really try to tune a query from 19 to 15 milliseconds?
If so: go home and try again tomorrow. It will run in 14.
or 21.
|
|
|