Home » SQL & PL/SQL » SQL & PL/SQL » Trunc Creating Problem (10g)
Trunc Creating Problem [message #418636] Mon, 17 August 2009 23:29 Go to next message
mamalik
Messages: 266
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 #418639 is a reply to message #418636] Mon, 17 August 2009 23:37 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Have you checked query execution plan?
Does it uses index while executing query?

regards,
Delna
Re: Trunc Creating Problem [message #418640 is a reply to message #418636] Mon, 17 August 2009 23:39 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Trunc function is applied on DATE field
EXACTLY how do you apply TRUNC function to DATE field?

CUT & PASTE example back here

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

[Updated on: Mon, 17 August 2009 23:43]

Report message to a moderator

Re: Trunc Creating Problem [message #418642 is a reply to message #418636] Mon, 17 August 2009 23:47 Go to previous messageGo to next message
mamalik
Messages: 266
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 #418652 is a reply to message #418642] Tue, 18 August 2009 00:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And G15.Pac_Dte < trunc(:P_TOODTE)+1
or simply
And G15.Pac_Dte < :P_TOODTE+1
if :P_TOODTE does not contain a time part.


Regards
Michel

[Updated on: Tue, 18 August 2009 00:22]

Report message to a moderator

Re: Trunc Creating Problem [message #418662 is a reply to message #418636] Tue, 18 August 2009 00:57 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Sir,

Our database date field contains time also. we are truncting tiime from the database field and comparing with the data with user parameter of report.


Regards.
Asif.
Re: Trunc Creating Problem [message #418670 is a reply to message #418662] Tue, 18 August 2009 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please CAREFULLY read what I posted.

And click on REPLY button associated to the message you want to answer not on your own first post.

Regards
Michel

[Updated on: Tue, 18 August 2009 01:27]

Report message to a moderator

Re: Trunc Creating Problem [message #418688 is a reply to message #418636] Tue, 18 August 2009 02:42 Go to previous messageGo to next message
mamalik
Messages: 266
Registered: November 2008
Location: Pakistan
Senior Member

Dear Sir,

Our database field G15.Pac_Dte contains the time also, if we compare this field with a parameter field which has only date then the query leave the record containing time because database consider a variable having date = date + 12:00:00 AM.


Regards.
Asif.
Re: Trunc Creating Problem [message #418689 is a reply to message #418688] Tue, 18 August 2009 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 18 August 2009 08:25
Please CAREFULLY read what I posted.

Explain what you understand from my answer.

Regards
Michel

[Updated on: Tue, 18 August 2009 02:47]

Report message to a moderator

Re: Trunc Creating Problem [message #418690 is a reply to message #418688] Tue, 18 August 2009 03:01 Go to previous messageGo to next message
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 Go to previous messageGo to next message
mamalik
Messages: 266
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 #418718 is a reply to message #418636] Tue, 18 August 2009 05:59 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
What does Msec mean?
Re: Trunc Creating Problem [message #418724 is a reply to message #418715] Tue, 18 August 2009 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Query 1 shows result in 19 Msecs while Query 2 shows result in 15 Msecs

And then?
You didn't post the usual and requested information to tune the query.
You didn't post information that allow us to compare.
You didn't post the time for your query without trunc.
You didn't post the time for the last (and faster) option I made.
And so on.

Regards
Michel

Re: Trunc Creating Problem [message #418726 is a reply to message #418715] Tue, 18 August 2009 06:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
And what was the original query that ran faster?
Re: Trunc Creating Problem [message #418788 is a reply to message #418715] Tue, 18 August 2009 09:47 Go to previous message
Frank
Messages: 7880
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.
Previous Topic: open a URL using Oracle Pl/Sql (merged 3)
Next Topic: how to make the value with comma separated values by grouping
Goto Forum:
  


Current Time: Sat Dec 10 07:02:19 CST 2016

Total time taken to generate the page: 0.07912 seconds