Re: strange proble with to_date function in select query
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: bpawlows@xxxxxxxxx
- Date: Fri, 24 Jul 2009 19:34:19 +0200
Bartek,
I presume that you have a query rewrite occurring that changes the
order in which the various conditions are evaluated. In the example that
fails, it is likely that the subquery is unnested, Oracle evaluates
values that you don't want (formats) BEFORE they are filtered out
because the optimizer believes that's the smart thing to do and you're
toast. I suspect that histograms may play a part here, if you are
running Oracle 10 and they are automatically computed. I also suspect
that SUBMITDATE is not null, possibly YYYYMMDD is what is used when you
have no known value, etc.
Root cause of the problem is, obviously, a bad design - perhaps this is
a staging table inherited from another source?
What you want is the inner query to *stay*nested. Although I don't like
hints, the NO_UNNEST hint is a possibility; another solution is adding
and rownum > 0
inside the subquery, which should have the same effect but may be less
explicit for people who will maintain the query.
If you can, the best solution is by far to redesign the table so as to
use real Oracle dates.
HTH
S Faroult
Bartek wrote:
> Hello,
>
> I have a strange problem with to_date function when I use it in select query.
>
> The following query does not give any ORA-nnnnn errors:
>
> [snipped to avoid over quoting]
> gives expected result without any ORA- errors.
>
> Could somebody give me any hint how can I solve this problem?
>
> Thanks in advance,
> Bartek
> --
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
