Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> REPOST: Re: Explain plan problem
Mike
I assume the bind variable in the first query is a date. If this is the case then I assume the problem is that you are subtracting 2 dates from each other and the using number functions on them. Have you tried adding to_number around the date subtraction function?
SQL> explain plan for
2 select to_number(end_time - :b1)*60*24
3 from test
4 where end_time < :b1
5 and start_time > :b1;
Hope thins works (can't try it currently - having fun installing 9i @ Home)
Nathan
"Mike F" <u518615722_at_spawnkill.ip-mobilphone.net> wrote in message
news:l.1012051105.1249572753_at_pool-151-197-235-121.phil.east.verizon.net...
> I have a table test
> SQL> desc test
> Name Type
> --------------- ----------------------------
> START_TIME DATE
> END_TIME DATE
>
> SQL> explain plan for
> 2 select (end_time - :b1)*60*24
> 3 from test
> 4 where end_time < :b1
> 5 and start_time > :b1;
> select (end_time - :b1)*60*24
> *
> ERROR at line 2:
> ORA-00932: inconsistent datatypes
>
> but if I remove *60*24, I can explain it.
> 1 explain plan for
> 2 select (end_time - :b1)
> 3 from test
> 4 where end_time < :b1
> 5* and start_time > :b1
> SQL> /
>
> Explained.
>
> But the first statement does not seem to use the same plan as the
> second one, instead, the first statement use a much more expensive
> one, why?
>
> The second question, for the 1st statement, if oracle can not explain
> it, why oracle can execute it without giving error message?
>
> Thanks for you help.
>
>
>
>
>
> --
> Sent by dbadba62 from hotmail within field com
> This is a spam protected message. Please answer with reference header.
> Posted via http://www.usenet-replayer.com/cgi/content/new
This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Sun Jan 27 2002 - 05:04:52 CST