Timeline for Oracle date not returning the right data
Current License: CC BY-SA 3.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 26, 2016 at 6:14 | comment | added | Marco |
Do as proposed by @BalazsPapp. Use the next day with the trunc: as_of_dt < to_date('2016-05-16', 'yyyy-MM-dd'). Now the index can be used. I say can because with the < it is very well possible that oracle decides not to use the index. You need to verify this with the explain plan function.
|
|
| May 25, 2016 at 22:43 | comment | added | Gandalf StormCrow | @BalazsPapp if I don't truncate I get back wrong results, as stated in the question, but I hear your concern, is there a way without truncate? Date provided is used to find anything in the budget table for that date or before that date, time is irrelevant | |
| May 25, 2016 at 19:55 | comment | added | Gandalf StormCrow |
@Marco and @BalazsPapp but if I don't use trucate, and I don't get the right results back from the DB. If my database row date is 2016-05-15 and if I use as_of_dt <= to_date('2016-05-15', 'yyyy-MM-dd') or as_of_dt < to_date('2016-05-15', 'yyyy-MM-dd') I m not getting any results. By using trunc I do, I don't want to use trunc but what are my other option
|
|
| May 25, 2016 at 17:44 | comment | added | Marco |
When you use a function like trunc with a field that is in an index then the optimizer does not use the index but will do a full table scan. This means that all the rows will be read. This can take more time then when it uses the index. This all depends on how many rows there are in your table and how many rows are expected to be returned.
|
|
| May 25, 2016 at 13:41 | comment | added | Gandalf StormCrow |
@BalazsPapp whats a huge performance penalty? using trunc?
|
|
| May 21, 2016 at 9:45 | comment | added | Balazs Papp |
If you expect to use indexes, this is huge performance penalty. Creating a function-based index is just unnecessary overhead. You could just go with as_of_dt < to_date('2016-05-16', 'yyyy-MM-dd'). And to_date creates the date with 0 hours, 0 minutes and 0 seconds when not specified.
|
|
| May 20, 2016 at 15:42 | vote | accept | Gandalf StormCrow | ||
| May 20, 2016 at 14:55 | comment | added | Marco | I never measured it but I think that it does not make a big difference unless the date is indexed. In that case the index will not be used and this can influence the performance. | |
| May 20, 2016 at 14:54 | comment | added | Gandalf StormCrow | excellent, seems like it's working, is there any big performance penalty on truncating it and converting to date? is there a better way I can approach this? | |
| May 20, 2016 at 14:51 | history | answered | Marco | CC BY-SA 3.0 |