| query taking lot of time [message #380642] |
Mon, 12 January 2009 16:01  |
varunvir
Messages: 389
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 #380644 is a reply to message #380643] |
Mon, 12 January 2009 16:49  |
varunvir
Messages: 389
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 #380695 is a reply to message #380644] |
Tue, 13 January 2009 01:00 |
 |
Littlefoot
Messages: 21823
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
