Home » SQL & PL/SQL » SQL & PL/SQL » How to optimize this SQL statement
How to optimize this SQL statement [message #229811] Tue, 10 April 2007 01:55 Go to next message
oralover
Messages: 97
Registered: January 2005
Member
hi all gurus!

i m using Oracle 10g (9.0.2)

i have a table below

myTable
the_code   varchar2(3),
a_date     date,
b_date     date,
qty        number,
rate       number(8,2)

the a_date have current date when new record generated and b_date is whatever like '01-jan-2049', when some kind of process done the b_date is updated with to_date(sysdate)

now if records are:
the_code a_date    b_date         qty     rate
-------- --------- --------- -------- --------
001      01-feb-07 01-jan-49      100     10.2  -> say its Opened (not yet processed)
001      01-feb-07 03-feb-07       50     10.2
002      01-feb-07 04-feb-07      125    10.15
002      02-feb-07 01-jan-49       30    10.15  -> say its Opened (not yet processed)
001      02-feb-07 05-feb-07       20     10.2
001      03-feb-07 01-jan-49       25     10.2  -> say its Opened (not yet processed)
002      03-feb-07 08-feb-07       10    10.15


now today is 10-feb-07 and i want Open positions date wise from 01-feb-07 till date (on 01-feb-07 what was the actual Open position - this will include that days processed records, means b_date has changed) one method i used as the date which is going to process
Select the_code, sum(qty) volume, sum(qty*rate) value
  from myTable
 where a_date < '01-feb-07'
   and b_date >= '01-feb-07'
 group by the_code

when using this method in HUGE data, its response is very slow and i have to do this Date by Date but i want it to give a Date Range to produce the desired data.

hope to receive an early response.
much thnx in advance.

PS: sorry for my bad english
Re: How to optimize this SQL statement [message #229994 is a reply to message #229811] Tue, 10 April 2007 08:48 Go to previous messageGo to next message
dewildeh
Messages: 3
Registered: February 2007
Junior Member
Hi Oralover,

This kind of problem was discussed in depth in:

http://orafaq.com/node/1798

Happy Coding!

Hendrik
Re: How to optimize this SQL statement [message #230032 is a reply to message #229811] Tue, 10 April 2007 12:47 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
oralover wrote on Tue, 10 April 2007 02:55


Select the_code, sum(qty) volume, sum(qty*rate) value
  from myTable
 where a_date < '01-feb-07'
   and b_date >= '01-feb-07'
 group by the_code

PS: sorry for my bad english



It's not you english that's bad, it's your use of comparing DATE columns to character strings Wink

'01-feb-07' is a character string, not a date.
Re: How to optimize this SQL statement [message #230132 is a reply to message #229811] Wed, 11 April 2007 00:32 Go to previous messageGo to next message
oralover
Messages: 97
Registered: January 2005
Member
weldone joy_division
very funny to relate my Sorry to SQL statement....

its slow even after i used to_date() to convert it to date data type.

dewildeh: thnx 4 the link.
Re: How to optimize this SQL statement [message #230143 is a reply to message #229811] Wed, 11 April 2007 01:04 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Post EXPLAIN. (Read the sticky)
Previous Topic: Hierachical Query - ORA-01436 Error
Next Topic: DATE datatype should accept only date.
Goto Forum:
  


Current Time: Sat Dec 03 17:55:07 CST 2016

Total time taken to generate the page: 0.04406 seconds