471,084 Members | 1,046 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,084 software developers and data experts.

problem in to_date function

hi
i have a table c_table with two columns
no,
date
it contains records

no n_date
1 10-AUG-03
2 21-AUG-03

when we issue
select * from c_table where n_date='10-AUG-03';
it will successfully return the record

but when we give
select * from c_table where n_date='21-AUG-03';

it will not return the record

after using to_date function like
select * from c_table where to_date(n_date,'DD/MM/YY')='21/08/03';

it will successfully return the records
but the main problem is that
when we have table that contains 50,000,00 records
**and then using to_date function then it will slow down the performance

how we can solve the.
Jul 19 '05 #1
2 25134
Nic
Hi,
for part of your question related to the performance, i think you could modify your query to get

select * from c_table where n_date = to_date('21/08/03','DD/MM/YY');

so the to_date function get call only once, of course if you have store the date data with differents time you still will need to
call the trunc function.

select * from c_table where trunc(n_date) = to_date('21/08/03','DD/MM/YY');

"KULJEET" <ku***********@hotmail.com> wrote in message news:fe**************************@posting.google.c om...
hi
i have a table c_table with two columns
no,
date
it contains records

no n_date
1 10-AUG-03
2 21-AUG-03

when we issue
select * from c_table where n_date='10-AUG-03';
it will successfully return the record

but when we give
select * from c_table where n_date='21-AUG-03';

it will not return the record

after using to_date function like
select * from c_table where to_date(n_date,'DD/MM/YY')='21/08/03';

it will successfully return the records
but the main problem is that
when we have table that contains 50,000,00 records
**and then using to_date function then it will slow down the performance

how we can solve the.

Jul 19 '05 #2
ku***********@hotmail.com (KULJEET) wrote in message news:<fe**************************@posting.google. com>...
[]

the main problem is that in
when i issue
---------------------
select to TO_CHAR(N_DATE,'DD/MM/YY HH24:MI') from c_table
the it will return
10/08/03 00:00
10/08/03 00:00
10/08/03 00:00
21/08/03 16:21
----------------
so
10/08/03 date it will store time as 00:00
10/08/03 00:00

but in 21/08/03 it will store time as 16:21

21/08/03 16:21
BUT IT WILL NOT WORK
select * from c_table where n_date = to_date('21/08/03','DD/MM/YY');
BUT THIS WILL WORK
select * from c_table where trunc(C_date) = to_date('21/08/03','DD/MM/YY');
CAN TRUNC FUNCTION SLOW DOWN THE PERFORMANCE????


Unless you have created a matching function-based index, any function
on a column will prevent the use of indices and thus Possibly affect
performance.

The questions you need to ask yourself are:
A:Is the time portion of the date really needed?
B:Is the performance unacceptable using the various function based
solutions?

If the answer to A is NO, then update the data to truncate (or round)
the time portion away, then all your dates will be at midnight and
will match appropriately and use possible indices as the Optimizer
sees fit.

If the answer to A is YES and the answer to B is NO, then using the
trunc() doesn't hurt you case.

If A is YES and B is YES, then consider either: creating a function
based index OR adding a search date column (another column that
contains the truncated date values used only for searching and
indexing).

finally here's an untested query that should still use possible
indices and gets by the trunc():

SELECT * FROM c_table WHERE n_date BETWEEN
to_date('21/08/03','DD/MM/YY') AND to_date('21/08/03','DD/MM/YY')+1
;

(NOTE the plus one).

HTH
Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Damjan | last post: by
1 post views Thread by Amin Schoeib | last post: by
2 posts views Thread by gimme_this_gimme_that | last post: by
5 posts views Thread by gane kol | last post: by
reply views Thread by ina | last post: by
2 posts views Thread by KULJEET | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.