Re: Explain plan problem

Mike,

in explain plan all bind variables are assumed to be varchar2. This is documented.

So try

explain plan for
select (end_time - to_date (:b1, 'yyyy-mm-dd.hh24-mi-ss')) from test
where end_time < to_date (:b1, 'yyyy-mm-dd.hh24-mi-ss') and start_time > to_date (:b1, 'yyyy-mm-dd.hh24-mi-ss') /

Regards,

Martin

Nathan Carney wrote:
>
> 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
Received on Tue Jan 29 2002 - 06:35:13 CST