Home » RDBMS Server » Performance Tuning » query taking lot of time (10.2.0.2)
query taking lot of time [message #380642] Mon, 12 January 2009 16:01 Go to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Hi Experts,
I am running the following query to do bulk inserts:-
insert into bhi_tracking_archive(select * from bhi_tracking where to_date(tO_char(time_stamp,'DD-MON-YY'))>'31-DEC-06' AND to_date(tO_char(time_stamp,'DD-MON-YY'))<'01-MAY-07');

I have been running this query for last 45 minutes without
getting any results.
There is no index on the time_stamp column of bhi_tracking.
Regards,
Varun Punj,
Re: query taking lot of time [message #380643 is a reply to message #380642] Mon, 12 January 2009 16:21 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
It will work (better) if/when you compare DATE datatypes & not strings.

Strings for Oracle are delimited by single quote marks.
'this is a string 31-DEC-07 and not a date'


Re: query taking lot of time [message #380644 is a reply to message #380643] Mon, 12 January 2009 16:49 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Thakns for your reply.
But If I give:-
select * from bhi_tracking where to_date(tO_char(time_stamp,'DD-MON-YY'))>'31-DEC-06' AND to_date(tO_char(time_stamp,'DD-MON-YY'))<'01-MAY-07'

It is comparing dates only and giving me all the records
after 31 dec 06 and before 01 may 07.
Regards,
Varun Punj
Re: query taking lot of time [message #380645 is a reply to message #380642] Mon, 12 January 2009 17:10 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>It is comparing dates
NO you are comparing STRINGS!


> '31-DEC-06' & '01-MAY-07'
are STRINGS! not DATE datatype

It will work (better) if/when you compare DATE datatypes & not strings.

Strings for Oracle are delimited by single quote marks.
'this is a string 31-DEC-07 and not a date'
Re: query taking lot of time [message #380666 is a reply to message #380642] Mon, 12 January 2009 23:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Keep your lines in 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.

Regards
Michel

Re: query taking lot of time [message #380695 is a reply to message #380644] Tue, 13 January 2009 01:00 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If we suppose (as you didn't provide enough information) that BHI_TRACKING.TIME_STAMP is a column of a DATE datatype (i.e. not a CHARACTER one), here's what you are doing:
convert to DATE ( convert to STRING ( DATE value ) ) ) 
i.e. you have a date, then convert it to string, and then back to date. Awkward, isn't it? Finally, you are comparing this newly created DATE with a STRING.

As there's no much sense in doing that, why wouldn't you compare a DATE (this is BHI_TRACKING.TIME_STAMP column) with another date (which is represented by a string such as '31-dec-06'). In order to do that, obviously - you'll have to convert a string ('31-dec-06') to a date. It can be done by using the TO_DATE function.

Here's how the above might be coded (based on Scott's schema EMP table):
SELECT ename, job, sal 
FROM emp e
WHERE e.hiredate > TO_DATE('31.12.1981', 'dd.mm.yyyy')

I hope that you got the idea; try to implement it to your case and see what happens.
Re: query taking lot of time [message #380845 is a reply to message #380695] Tue, 13 January 2009 10:23 Go to previous message
varunvir
Messages: 363
Registered: November 2007
Senior Member
Thanks Littlefoot...It worked.

Regards,
Varun Punj,
Previous Topic: Speed of query
Next Topic: Tempfile readtime and Writetime
Goto Forum:
  


Current Time: Fri Dec 09 03:54:20 CST 2016

Total time taken to generate the page: 0.16959 seconds